Set up the template Weekly report from SQL

Weekly report from SQL runs every Monday at 09:00. It counts the past seven days' orders in your Microsoft SQL database, totals them, and adds the week as a row to a Google Sheet your team can read. Week by week, the sheet becomes your report. This article shows how to install it and fill in each step.

Who can do this

Workspace Admins and Editors. Templates are on every plan. Microsoft SQL ◇ is on paid plans: on Free you can set the template up and test it, and publishing asks for a paid plan. See Premium connections on the Free plan.

Before you start

• A Microsoft SQL connection, to a server on the public internet, with a SQL Server user and password. See Connect a Microsoft SQL database.
• A table of orders. The template's query reads a table named orders, with a total column and a created_at column for when each order was made. Your own table works too, if you change the query.
• A Google Workspace connection, and a sheet whose first row has the headings Week, Orders and Total. See Add a row to Google Sheets.

Steps

Install it

1. On Workflows, select the arrow beside + New workflow, then From a template.
2. In Search templates, type SQL.
3. On Weekly report from SQL, select Use this template.
4. In Install into, choose the workspace or project, then select Install as a draft.
5. The toast says Installed as a draft, for example Weekly report from SQL is in Consulace. Connect Microsoft SQL and Google Sheets, then publish. An app it doesn't name had one connection in the workspace, and the template has chosen it. Select Open it. The workflow opens in the designer with three steps.

You can also start it on a new, empty workflow, under Start from a template. See Starting a workflow from a template.

Fill in the steps

1. Select Every Monday at 09:00 (Schedule). To change the day or time, choose another after on or at. Check Next runs.
2. Select Count last week's orders (Microsoft SQL · Query).
   • Choose the Connection.
   • Query already counts and totals the orders made in the past seven days: select count(*) as orders, sum(total) as total from orders where created_at >= dateadd(day, -7, sysutcdatetime()). Change orders, total and created_at to your own table and columns if they differ. Keep the names after as: the next step reads them.
   • Select Run this step, then check Output. rows has one row, with orders and total.
3. Select Add the week to the report (Google Sheets · Add a row).
   • Choose the Connection, the Spreadsheet and the Sheet.
   • Under Columns, Week is {{ today }}, the date the report runs. Orders and Total come from the query's one row. If your headings differ, change the names to match.
4. Select Test run. A test starts at once, without waiting for Monday, and really adds the row. See Testing a workflow before you publish it.
5. Publish the workflow and switch it on.

What happens next

• It runs every Monday at 09:00 while it's published and switched on. Each run adds one row, and counts in the month's allowance.
• The times are in the time zone of whoever published it. If someone in another zone publishes it next, its times move to theirs.

Good to know

• The seven days are counted back from when it runs, in UTC, as sysutcdatetime() gives the time. The query compares that with created_at, so keep created_at in UTC too, or change the query to suit.
• A week with no orders gives Orders 0 and an empty Total, because SQL's sum of no rows is empty. Write coalesce(sum(total), 0) as total to get 0 instead.
• Values never go inside the query. To filter by something from an earlier step, use Parameters. See Query a Microsoft SQL database.

If something goes wrong

Related

• Running a workflow on a schedule
• Query a Microsoft SQL database
• Add a row to Google Sheets
• Installing a template from the gallery