Apps Script can call BigQuery.Jobs.query when you enable the BigQuery Advanced Service and the script project has access to a GCP project.
This example reads start/end dates from a Params sheet, runs a NAMED-parameter SQL query against analytics.orders, and polls until jobComplete.
Result schema field names become header cells; row values write under BqResults starting at row 2.
Use this for analyst extracts under ~10k rows. Larger results belong in BigQuery BI Engine / Looker, or export to Cloud Storage first.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Resource | Value | Notes |
|---|---|---|
| GCP project | your-gcp-project | PROJECT_ID constant |
| Dataset.table | analytics.orders | Queried relation |
| Params!B1 | Start date | yyyy-MM-dd |
| Params!B2 | End date | yyyy-MM-dd |
| BqResults | Output | Cleared and rewritten each run |
| Services | BigQuery API | Enable under Services in Apps Script |
What this script does
runBigQueryToSheet() submits a parameterized query, waits for completion, and materializes rows into BqResults.
NAMED parameters avoid string-concatenating dates into SQL.
Prerequisites
BigQuery Advanced Service enabled; script GCP project billed; IAM permission bigquery.jobs.create and data viewer on the dataset.
- Params sheet with B1/B2 dates
- Table analytics.orders exists
- Apps Script linked to the correct GCP project
Walkthrough
Build the Jobs.query request, call BigQuery.Jobs.query, poll getQueryResults with backoff, map schema + rows into the sheet.
Edge cases
queryResults may page — this sample assumes one page under LIMIT 10000. For more rows, loop pageToken.
- NULL cells arrive as null — coerce if downstream formulas break
- Legacy SQL is disabled on purpose
GCP permissions checklist
The Apps Script execution identity (user or cloud project service) needs jobUser on the project and read on the dataset. Mismatched GCP project linking is the most common failure mode.
How to test
Set a one-day window known to have a few orders; compare row count to the BigQuery console.
Hardening for production
Store PROJECT_ID in Script Properties; cap LIMIT; log jobId on failure for console debugging.
Variations
Write to Drive as CSV, or call BigQuery.Jobs.insert for async queries that email when done.
Full code: runBigQueryToSheet()
Enable BigQuery under Services, set PROJECT_ID, fill Params!B1/B2, then run runBigQueryToSheet().
/**
* Run a parameterized BigQuery job and write rows to BqResults.
* Enable Advanced Service: BigQuery API.
*/
const PROJECT_ID = "your-gcp-project";
const DATASET = "analytics";
const RESULTS_SHEET = "BqResults";
function runBigQueryToSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const start = ss.getSheetByName("Params").getRange("B1").getDisplayValue(); // yyyy-MM-dd
const end = ss.getSheetByName("Params").getRange("B2").getDisplayValue();
const request = {
query: "SELECT order_id, customer_id, amount, order_date " +
"FROM `" + PROJECT_ID + "." + DATASET + ".orders` " +
"WHERE order_date BETWEEN @start AND @end " +
"ORDER BY order_date DESC LIMIT 10000",
useLegacySql: false,
parameterMode: "NAMED",
queryParameters: [
{ name: "start", parameterType: { type: "DATE" }, parameterValue: { value: start } },
{ name: "end", parameterType: { type: "DATE" }, parameterValue: { value: end } },
],
};
let queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
const jobId = queryResults.jobReference.jobId;
let sleepMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepMs);
sleepMs = Math.min(sleepMs * 2, 5000);
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
}
const headers = queryResults.schema.fields.map(function (f) { return f.name; });
const rows = (queryResults.rows || []).map(function (r) {
return r.f.map(function (cell) { return cell.v; });
});
const sheet = ss.getSheetByName(RESULTS_SHEET) || ss.insertSheet(RESULTS_SHEET);
sheet.clearContents();
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
if (rows.length) {
sheet.getRange(2, 1, rows.length + 1, headers.length).setValues(rows);
}
Logger.log("Wrote %s BigQuery rows", rows.length);
}- Line 5: Must match the GCP project linked to the Apps Script project.
- Line 20: NAMED parameters bind @start / @end safely.
- Line 27: Starts the query job via the Advanced Service.
- Line 30: Poll loop waits until BigQuery finishes.
- Line 33: Fetches schema and rows for the completed job.
- Line 36: Header names come from BigQuery field names.
- Line 43: Writes the result matrix in batch.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: BigQuery extract
- 1BigQuery Advanced Service enabled
- 2Script settings → GCP project set correctly
- 3IAM allows jobs.create + dataset read
- 4Params!B1 and B2 are valid dates
- 5PROJECT_ID / DATASET constants updated
- 6BqResults can be cleared safely
- 7LIMIT fits Apps Script execution time
Frequently asked questions
Enable the BigQuery API under Services (+). Saving alone is not enough — pick the service from the list.
Editor runs use the signed-in user. Triggers use the installing account. Both need BigQuery IAM.
Page with pageToken from getQueryResults, or export to GCS and load from there.
Yes — useLegacySql: false is set for standard SQL.
On the GCP project billing account. Prefer partitioned tables and tight date filters.
Set dryRun: true on the request to estimate bytes processed without writing results.
Log jobReference.jobId and open the job in the BigQuery console for the error message.
Split by a key column in Apps Script after fetch, or use separate queries per segment.