1. Install the Sheets Add-on
  2. Connecting and Exploring
    • Give ensembldb a test spin
    • This database will already be saved for you, so just click Connect to give it a go
  3. Upgrading
    • Note: Make sure you use the email that you'll be logging into Sheets with (i.e. use your business email if you are a GSuite customer)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). The last IP is an address dedicated to SeekWell on Google's infrastructure that is used for scheduling and Postgres support.
    • 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
      35.193.207.79
    • 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, 35.193.207.79/32
    • 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:

  6. Accessing CSV's, JSON, Excel, and other Google Sheets
    • If you are querying another Google Sheet, share the Sheet you want to query so SeekWell can find it (You don't need to share the Sheet you are querying from).
    • After launching SeekWell, click "Skip Database"
    • Click the "Files / API" button and select the file type (use CSV for other Google Sheets), name the table (i.e. what you want to call the table in your SQL query) and the URL to the file. For other Google Sheets, simply copy and paste the URL you see in your browser. Note that the URL is different for spreadsheets with multiple Sheets.
    • Click "Done" and the code to pull your table will be inserted into the query editor. For example, if you wanted to query another Google Sheet and you named your table "example", the code to SELECT everything from the new file would look like this:
    • SELECT *
      FROM example
      ;
      ++={
        "files" : {
          "example" :
            {"url" : "https://docs.google.com/spreadsheets/d/CHANGE-THIS-TO-YOUR-URL!!!!!/edit#gid=0",
            "type" : "csv",
            "columns" : "",
            "values" : ""}}}
    • You can add multiple files and JOIN them as you would with any database tables

Trouble Shooting