Automating reports and dashboards
Click on "Run Sheet" (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
Click "Run Sheet" again to run all your queries
Click the "Schedule" button if you'd like the data updated automatically (e.g. every hour)
If you want some schedules update hourly and others daily, select hourly and see below how to control individual queries
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}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:
