Skip to main content
How-to

Scheduling AI Report Builder SQL in Google Sheets

Table of Contents​

  1. Overview
  2. Before you start
  3. Step 1: Copy the SQL out of the Report Builder
  4. Step 2: Decide what happens to the report's parameters
  5. Step 3: Confirm the dataset matches your access level
  6. Step 4: Check what a refresh will cost
  7. Step 5: Paste the query into a connected sheet
  8. Step 6: Bind the parameters to cells
  9. Step 7: Schedule the refresh
  10. Worked example: subscription status by item
  11. Keeping the sheet and the report in sync
  12. Troubleshooting
  13. Related documentation

Overview​

The AI-Powered Report Builder turns a plain-English question into SQL and shows you the result inside UltraCart. This guide takes that SQL one step further: into a Google Sheet that re-runs it on a schedule, so the numbers are waiting for whoever needs them without anybody opening UltraCart.

The two tools split the work cleanly. The Report Builder is where you design the query — it knows your schema, and iterating on a question there is far faster than writing SQL by hand. Connected Sheets is where you operationalize it — scheduled refresh, pivot tables, charts, and a link you can send someone.

Moving between them is mostly copy and paste. The one part that needs thought is what happens to the report's date parameters, because the Report Builder fills those in for you and a scheduled sheet has nobody to ask.

Before you start​

You need a saved report, data warehouse access on a Google account, and a sheet you can connect.

  • A saved report in the AI Report Builder whose results you are happy with. Get the report right there first — it is a much faster loop than editing SQL in Sheets.
  • Data warehouse access on the Google account you will use in Sheets. The account owner grants it under Configuration → Account & Users → Users, and the level has to cover the dataset the SQL reads from. See Providing Users Access to the Data Warehouse.
  • The Sheets connection basics. Data Warehouse (BigQuery) to Google Sheets covers connecting, refreshing, and extracting; this guide assumes it and focuses on what is different about generated SQL.

Step 1: Copy the SQL out of the Report Builder​

Open the report and copy the query from the SQL & Parameters panel:

  1. Navigate to Operations → Reporting and click Build Custom Reports.
  2. Open the saved report.
  3. In the SQL & Parameters panel, copy the full query.

While you are there, note which parameters the panel lists — typically @start_date and @end_date. Those are the values the Report Builder supplies when you set a timeframe in the Report Viewer, and they are the one thing that does not travel with a copy and paste. Step 2 deals with them.

Step 2: Decide what happens to the report's parameters​

A generated query that references @start_date will not run in Sheets until each parameter has a value. You have two ways to supply one, and the right choice depends on who uses the sheet.

Option A — bind the parameters to spreadsheet cells​

Best when a person opens the sheet and wants to change the window. You put the dates in two cells, and Connected Sheets passes whatever is in them into the query.

Parameters arrive from a cell as text, so cast them in the SQL rather than comparing a string to a date:

WHERE payment.payment_dts >= DATETIME(PARSE_DATE('%Y-%m-%d', @start_date))
  AND payment.payment_dts < DATETIME(DATE_ADD(PARSE_DATE('%Y-%m-%d', @end_date), INTERVAL 1 DAY))

The DATE_ADD(..., INTERVAL 1 DAY) on the end makes the range inclusive of the whole end date, which is what people expect when they type 2026-07-31 into a cell. Cells hold dates as YYYY-MM-DD.

Step 6 covers wiring the cells up.

Option B — replace the parameters with a rolling window​

Best for a sheet that refreshes unattended — a Monday-morning dashboard nobody has to touch. Delete the parameters and express the window relative to the moment the query runs:

WHERE payment.payment_dts >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 90 DAY)

Every refresh then re-reads "the last 90 days" as of that refresh, so the sheet never goes stale and there is nothing for anyone to reset.

Both options can coexist: a rolling default window for the scheduled refresh, plus a parameter for something a reader legitimately wants to change, like a minimum-volume threshold. The worked example below does exactly that.

Step 3: Confirm the dataset matches your access level​

Generated SQL names its tables in full — ultracart-dw-{merchantid}.ultracart_dw.uc_orders. Check the dataset in the middle before you schedule anything, because the Report Builder may have written the query against a level your Google account does not hold.

ultracart_dw covers everything without sensitive information. ultracart_dw_low, ultracart_dw_medium, and ultracart_dw_high add data at each higher permission level — customer PII, for instance, only becomes readable at ultracart_dw_medium. If the query reads a dataset above your level, the sheet fails at refresh with an access error, and the fix is either a higher level from the account owner or a query that does not need those columns.

Step 4: Check what a refresh will cost​

Click Preview results at the bottom right of the Sheets query editor before you insert. It reports how much data the query scans, which is what BigQuery bills, and UltraCart passes through on your monthly invoice.

A scheduled sheet turns a one-time query into a recurring one, so this is worth ten seconds of attention. Two habits keep it cheap: keep the date window as narrow as the report actually needs, and aggregate in SQL rather than pulling raw rows into the sheet and summarizing there. A report that scans a few megabytes costs effectively nothing to refresh daily; one that scans the entire order history every hour is a different story.

Step 5: Paste the query into a connected sheet​

  1. In a new Google Sheet, choose Data → Data connectors → Connect to BigQuery.
  2. Select your ultracart-dw-{merchantid} project.
  3. Click Saved queries and query editor (older versions of Sheets label this Write custom query).
  4. Paste the query.
  5. Insert the results.

The column aliases in the SQL become the column headings in the sheet, so this is the moment to rename anything cryptic. AS true_active_customer_count reads fine in a query and poorly in a report someone else opens — AS active_subscribers is better. Renaming an alias changes nothing about the results.

Step 6: Bind the parameters to cells​

Skip this if you chose Option B and removed the parameters.

  1. Put your values in two cells on a sheet — for example B1 and B2 on a tab named Settings, holding 2026-01-01 and 2026-06-30.
  2. In the query editor, click Parameters and then Add.
  3. Name the parameter exactly as it appears in the SQL, without the @ — start_date.
  4. Choose Cell reference and select the cell.
  5. Repeat for each parameter, then insert the results.

Anyone can now change the window by typing in those cells and refreshing — no SQL, no permissions beyond the sheet itself.

note

A scheduled refresh uses whatever the parameter cells contain at the moment it runs. If someone narrows the dates to investigate something and walks away, the next scheduled refresh inherits their narrower window. When a sheet must always show a fixed window, use the rolling-window approach from Option B instead of a parameter.

Step 7: Schedule the refresh​

Click Refresh options, choose a frequency under Scheduled refresh, and click Save.

Match the schedule to how fast the answer actually changes. Subscription counts and lifetime-value reports move slowly — daily is generous. An operational list someone works from during the day may deserve hourly. Every refresh is a billed query, so an hourly schedule costs about 24 times a daily one for the same report.

Then click Extract to copy the results into a plain sheet for charts, filters, and sharing. The connected sheet previews 500 rows; pivot tables, charts, and formulas built on it use the whole result set.

warning

A scheduled sheet keeps producing data long after everyone forgets it exists, and it does not inherit your UltraCart permissions — anyone with the link reads everything in it. Before sharing, check what the query actually returns: reports built on ultracart_dw_medium or ultracart_dw_high carry customer PII. Build the shared version from the lowest dataset that answers the question.

Worked example: subscription status by item​

This query came out of the Report Builder in response to a question about how many customers are genuinely still subscribed to each item, as opposed to merely carrying an active status. It classifies every auto order into one bucket, then counts the buckets per item:

WITH base AS (
  SELECT
    ao.auto_order_oid,
    UPPER(item_data.original_item_id) AS item_id,
    ao.status,
    ao.enabled,
    ao.completed,
    item_data.paused,
    item_data.remaining_repeat_count,
    item_data.no_order_after_dts,
    item_data.next_shipment_dts
  FROM `ultracart-dw-{merchantid}.ultracart_dw.uc_auto_orders` ao,
  UNNEST(ao.items) AS item_data
),
classified AS (
  SELECT
    *,
    CASE
      WHEN status != 'active' THEN status
      WHEN COALESCE(enabled, TRUE) = FALSE THEN 'disabled_flag'
      WHEN COALESCE(completed, FALSE) = TRUE THEN 'completed'
      WHEN COALESCE(paused, FALSE) = TRUE THEN 'paused_flag'
      WHEN COALESCE(remaining_repeat_count, -1) = 0 THEN 'repeat_exhausted'
      WHEN no_order_after_dts IS NOT NULL AND no_order_after_dts < CURRENT_DATETIME() THEN 'past_no_order_after'
      WHEN next_shipment_dts < CURRENT_DATETIME() THEN 'overdue'
      ELSE 'true_active'
    END AS classification
  FROM base
)
SELECT
  item_id,
  COUNT(DISTINCT CASE WHEN status = 'active' THEN auto_order_oid END) AS raw_active_status_count,
  COUNT(DISTINCT CASE WHEN classification = 'true_active' THEN auto_order_oid END) AS true_active_customer_count,
  COUNT(DISTINCT CASE WHEN classification = 'paused_flag' THEN auto_order_oid END) AS excluded_paused_flag,
  COUNT(DISTINCT CASE WHEN classification = 'overdue' THEN auto_order_oid END) AS excluded_overdue,
  COUNT(DISTINCT CASE WHEN classification = 'past_no_order_after' THEN auto_order_oid END) AS excluded_past_no_order_after,
  COUNT(DISTINCT CASE WHEN classification = 'repeat_exhausted' THEN auto_order_oid END) AS excluded_repeat_exhausted,
  COUNT(DISTINCT CASE WHEN classification = 'completed' THEN auto_order_oid END) AS excluded_completed,
  COUNT(DISTINCT CASE WHEN classification = 'disabled_flag' THEN auto_order_oid END) AS excluded_disabled_flag,
  COUNT(DISTINCT CASE WHEN status = 'canceled' THEN auto_order_oid END) AS canceled_count,
  COUNT(DISTINCT CASE WHEN status = 'disabled' THEN auto_order_oid END) AS disabled_count,
  COUNT(DISTINCT CASE WHEN status = 'merged' THEN auto_order_oid END) AS merged_count,
  COUNT(DISTINCT auto_order_oid) AS total_all_time
FROM classified
GROUP BY item_id
HAVING COUNT(DISTINCT CASE WHEN classification = 'true_active' THEN auto_order_oid END) >= 10
ORDER BY true_active_customer_count DESC

Four things about this query are worth noticing, because they generalize to most Report Builder output:

  • It has no date parameters, so it is ready to schedule as-is. The only time references are CURRENT_DATETIME(), which re-evaluates on every refresh. A subscription's status is a present-tense fact — there is no window to pick — so Step 2 has nothing to do here.
  • UNNEST(ao.items) produces one row per subscription item, which is why every count is COUNT(DISTINCT auto_order_oid) rather than COUNT(*). A subscription with three items would otherwise be counted three times. Generated SQL uses this pattern constantly, because most warehouse tables nest their detail.
  • The aliases become the column headings. raw_active_status_count and true_active_customer_count sitting side by side is the entire point of the report — the gap between them is what the question was about — but for a sheet other people read, rename them to something like status_says_active and actually_active.
  • The HAVING ... >= 10 is a hard-coded threshold, and a good candidate for a parameter. Replace it with >= @min_active_customers, bind that to a cell as in Step 6, and a reader can widen the report to every item or narrow it to the top handful without touching SQL.

Keeping the sheet and the report in sync​

The sheet holds a copy of the SQL, not a link to the report. Editing the report in UltraCart does not change the sheet, and editing the query in Sheets does not change the report.

That independence is usually what you want — the sheet keeps working while you experiment in the Report Builder. It does mean that when you refine a report you care about, you have to re-copy the SQL into the sheet's query editor and re-apply whatever changes you made in Steps 2 and 5. Keep those changes small and obvious so the re-copy stays cheap; a comment at the top of the query noting what you altered saves the next person a diff.

Troubleshooting​

SymptomCause and fix
Query fails on a missing parameterThe SQL still references @start_date or similar with nothing bound. Either add the parameter (Step 6) or replace it with a rolling window (Step 2, Option B).
No rows, or wildly wrong datesA string parameter is being compared to a date column. Wrap it: DATETIME(PARSE_DATE('%Y-%m-%d', @start_date)).
The last day of the range is missingThe query uses <= @end_date against a timestamp column, which excludes everything after midnight. Use < DATETIME(DATE_ADD(PARSE_DATE('%Y-%m-%d', @end_date), INTERVAL 1 DAY)).
Access denied at refreshThe query reads a dataset above your permission level. See Step 3.
Counts higher than in the Report ViewerA nested field was unnested without COUNT(DISTINCT …), so rows are multiplying. See the worked example.
The scheduled refresh returns a different window each timeA parameter cell was changed by a reader. Switch to a rolling window if the schedule must be fixed.
The sheet did not pick up a report changeThe sheet holds its own copy of the SQL. Re-copy it from the SQL & Parameters panel.
Was this page helpful?