Automating reports and dashboards

  1. Click on "Run Sheet" (this will add a new sheet called "runSheet")

  2. 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

  3. 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

  4. Click "Run Sheet" again to run all your queries

  5. Click the "Schedule" button if you'd like the data updated automatically (e.g. every hour)

  6. If you want some schedules update hourly and others daily, select hourly and see below how to control individual queries

  7. Use the "options" column to specify additional options (in JSON format), e.g. to clear the columns before writing the new data, use {"clearColumns" : true} 

  8. Here are a full list of options:

clearColumns  (boolean, default = false) - Setting this option to true will clear the columns in your resultSheet for the number of columns in your query results. For example, if you set the startCell to A1 and your result data has 3 columns, this would clear all the data in columns A, B, and C. Example usage {"clearColumns" : true} 

clearSheet (boolean, default = false) - Setting this option to true  will clear the entire  resultSheet before adding new data. Example usage {"clearSheet" : true} 

omitColumnNames (boolean, default = false) - Setting this option to true  will omit the column names when writing data to your resultSheet . For example if you had a query like SELECT count(1) as total_users from users where the result is 100, only the value 100 will be sent to sheets with no column label. Example usage {"omitColumnNames" : true} 

Controlling when a query runs

The following options can be used to control when a query is run:

period (string) - This option can be set to hourly or daily for example:

{"period" : "hourly"}

hourly  must be used in conjunction with minutesPast and daily must be used with runTime

This will run every hour at 23 minutes past the hour
{"period" : "hourly", "minutesPast" : 23}


This will run every day at 8:30 UTC (24 hour clock)
{"period" : "daily", "runTime" : "8:30"}

runTime (string) - The time in UTC using a 24 hour clock (do not use AM or PM) that you want the schedule to run. For example, if you wanted the schedule to run daily at 8:15 AM EST (New York):

{"period" : "daily", "runTime" : "12:15"}

minutesPast (integer) - The number of minutes past the hour to run an hourly schedule. Do not use decimals. For example, if you want the data updated every hour at 15 minutes past the hour:

{"period" : "hourly", "minutesPast" : 15}

You can also specify weekly schedules as shown below using dayOfWeek and runTime:

{
 "period": "weekly",
 "dayOfWeek": "friday",
 "runTime": "14:00"
}

Putting it all together

Here's an example of what a full set of options might look like

{"period" : "hourly", "minutesPast" : 1, "clearColumns" : true}

Please keep in mind everything is optional, however, if you use the period option on one row, you should use it on every row in the sheet.

You can leave the options column completely blank and everything will still work just fine. The order of the options does not matter. Here is how a full row would look after the first run:


Did this answer your question?