1. Install the Sheets Add-on
  2. Connecting and Exploring
    • Give ensembldb a test spin
    • Server: ensembldb.ensembl.org:3306
    • Username: anonymous
    • Password: leave blank
    • Click connect
    • Select a database by starting to type the same (e.g. starting typing "ail")
    • Click connect
    • Click "Get Tables" to see a list of tables in the database
    • Click on one of the tables on the newly added sheet and click "Table Details"
    • Click on one of the column names in the newly added sheet and click "Column Details"
  3. Upgrading
    • Note: Make sure you use the email that you'll be logging into Sheets with when you set up your SeekWell account. Our Sheets add-on will automatically check your SeekWell account for your plan type.
    • If you want to use databases other than MySQL or you're going to exceed the basic tier's limits (e.g. max of 100 rows), please click here to upgrade
    • After you upgrade, be sure to log out of Google and restart your browser so the changes to your plan can take effect
  4. Accessing your database
    • SeekWell uses JDBC to connect to your database
    • You'll need to make your database available to Google's IPs, here are the ranges (source)
    • 64.18.0.0 - 64.18.15.255
      64.233.160.0 - 64.233.191.255
      66.102.0.0 - 66.102.15.255
      66.249.80.0 - 66.249.95.255
      72.14.192.0 - 72.14.255.255
      74.125.0.0 - 74.125.255.255
      173.194.0.0 - 173.194.255.255
      207.126.144.0 - 207.126.159.255
      209.85.128.0 - 209.85.255.255
      216.239.32.0 - 216.239.63.255
    • Here's an example of how an AWS security group would look for SQL Server:
    • You can paste the string below into one box on AWS and it will automatically create the ranges you need: 64.18.0.0/20, 64.233.160.0/19, 66.102.0.0/20, 66.249.80.0/20, 72.14.192.0/18, 74.125.0.0/16,173.194.0.0/16, 207.126.144.0/20, 209.85.128.0/17, 216.239.32.0/19
    • Here's an example with MySQL on AWS:

    • You may want to create a new user if your database has personal information (e.g. customer names and addresses). You can grant this new user access to just the neccassary data
    • If you are trying to access a local database (i.e. on your computer), check out this Stack Overflow answer
  5. Automating reports and dashboards
    • Click on "Run Sheets" (this will add a new sheet called "runSheet")
    • Put the queries you want to run in the "query" column and specify the sheet (e.g. result1) and cell (e.g. A1) you'd like the results written to
    • SeekWell will automatically create the sheets you specify if they don't already exist, so you don't need to create them ahead of time
    • You can also specify paramaters / filters to make your query dynamic. For example, below we have a column specifying the country to filter results for:

Trouble Shooting