Query BigQuery into Google Sheets
Query BigQuery from Apps Script into Google Sheets with parameterized SQL, dry-run byte limits, bounded job polling, and one batched write.
Choose between Connected Sheets and the BigQuery v2 advanced service, check estimated bytes with a dry run before running a billed query, bind named query parameters without string interpolation, and write validated summary rows to a reserved Sheet tab.
Run a parameterized GoogleSQL summary query in BigQuery, check how many bytes it will scan with a free dry run before starting a billed job, and write up to ten validated rows into a reserved Google Sheets tab in one setValues() call. By default, the script stops after the dry run so you can inspect the byte estimate before running a live query or changing spreadsheet cells.
You need Runs, permissions, and execution context for OAuth scopes and execution identity, Read and write Sheets in batches for header checks and rectangular 2D arrays, and Inspect a folder with Advanced Drive v3 for how Apps Script wraps Google REST APIs through enabledAdvancedServices.
Choose Connected Sheets or the BigQuery advanced service
Google Sheets offers two ways to work with BigQuery data: Connected Sheets and the BigQuery v2 advanced service. Pick the integration that matches how workbook readers will use the output before writing SQL in Apps Script.
| Integration path | Where rows live | Best fit | Key constraints |
|---|---|---|---|
Connected Sheets (Data > Data connectors > Connect to BigQuery) | BigQuery keeps the dataset. Sheets renders a linked DataSourceSheet preview and runs pivots, charts, and calculated columns in BigQuery | Interactive exploration, scheduled refreshes, and tables larger than a spreadsheet grid | Requires an eligible Google Workspace edition and a billing-enabled Cloud project. Preview tabs are not ordinary editable cell ranges |
SpreadsheetApp.BigQueryDataSourceSpec | Same Connected Sheets engine, created or refreshed from Apps Script | Automating Connected Sheets setup, parameter updates, or refreshes across workbooks | Requires SpreadsheetApp.enableBigQueryExecution() and obeys Connected Sheets Workspace and billing eligibility |
BigQuery v2 advanced service (BigQuery.Jobs.query) | BigQuery aggregates the rows. Apps Script decodes the JSON response and writes plain values to a bounded Range | Small aggregated tables fed into ordinary formulas, custom validation before writing, or multi-step Workspace scripts | Subject to Apps Script execution time limits, JSON reply paging, and Google Sheets' 10-million-cell workbook ceiling |
When teammates want to filter, pivot, or schedule refreshes over millions of warehouse rows, open Data > Data connectors > Connect to BigQuery in Sheets and write zero code (see Get started with BigQuery data in Google Sheets). To configure or refresh those Connected Sheets connections from code, build a spec with SpreadsheetApp.BigQueryDataSourceSpec and call SpreadsheetApp.enableBigQueryExecution().
Use the Advanced BigQuery service when your workflow needs plain cell values or pre-write checks:
Downstream formulas, data validation rules, or embedded charts read ordinary cells in a fixed summary tab.
The script must run a
dryRun: truebyte check first and refuse to touch the sheet if a schema or row count changes.Your Google account uses BigQuery's free tier or Sandbox without a Workspace edition that includes Connected Sheets.
The query result drives another Apps Script step, such as updating a status table or building a report archive.
Keep the workload split strict. Group, filter, and sort in SQL inside BigQuery, and transfer only the small summary table to Apps Script. Do not copy hundreds of thousands of raw fact rows into V8 memory to aggregate them in JavaScript.
Prepare a billing project and a summary sheet
Every BigQuery query job runs inside the Google Cloud project that you pass as projectId to BigQuery.Jobs.query(request, projectId). Even when your SQL reads a public dataset in bigquery-public-data, BigQuery authorizes, runs, and meters the query job in your projectId.
Use a new disposable Google Sheet and an isolated Google Cloud project whose BigQuery quota and billing settings you control. Under BigQuery on-demand pricing, Google provides a monthly free query usage tier, enforces a 10 MiB (10,485,760 bytes) minimum per referenced table and per uncached billed query, and bills the executing Cloud project once free monthly usage is exhausted. Review BigQuery pricing and keep dryRunOnly: true until you check estimatedBytesProcessed in the execution log.
Set up the Google Cloud project, the summary spreadsheet, and the bound Apps Script project:
In the Google Cloud console, select or create a dedicated practice project with BigQuery API enabled. Confirm your Google account has permission to create BigQuery jobs on that project (
bigquery.jobs.create, included in BigQuery Job User and BigQuery User). Copy the Project ID string (such asmy-bq-practice-412), not the display name or numeric project number.Create a new Google Sheet that you own and rename its first tab to
BigQuery Summary.Enter the exact headers
Corpus,Word, andOccurrencesinA1:C1. LeaveA2:C11empty and free of formulas.Open Extensions > Apps Script, replace
Code.gswith the complete source below, and open Project Settings to enable Show "appsscript.json" manifest file in editor.Return to Editor, replace
appsscript.jsonwith the supplied manifest, and save both files. Confirm Services lists BigQuery API (v2) with the identifierBigQuery, and do not add a second entry through the sidebar.In
BIGQUERY_WORKFLOW_CONFIG, replacePASTE_YOUR_CLOUD_PROJECT_ID_HEREwith your Cloud project ID. Keeplocation: 'US',sheetName: 'BigQuery Summary',corpus: 'hamlet',minOccurrences: 150,maxBytesProcessed: 25 * 1024 * 1024(26,214,400bytes, or 25 MiB), anddryRunOnly: true. SaveCode.gs.
const BIGQUERY_READONLY_SCOPE = 'https://www.googleapis.com/auth/bigquery.readonly';
const SPREADSHEET_CURRENTONLY_SCOPE = 'https://www.googleapis.com/auth/spreadsheets.currentonly';
const BIGQUERY_MIN_BYTES_BILLED = 10 * 1024 * 1024;
const BIGQUERY_MAX_ALLOWED_BYTES_CAP = 250 * 1024 * 1024;
const BIGQUERY_MAX_SHEET_ROWS = 10;
const BIGQUERY_INITIAL_TIMEOUT_MS = 10000;
const BIGQUERY_POLL_REQUEST_TIMEOUT_MS = 5000;
const BIGQUERY_MAX_POLL_ATTEMPTS = 5;
const BIGQUERY_INITIAL_POLL_SLEEP_MS = 500;
const BIGQUERY_MAX_POLL_SLEEP_MS = 4000;
const BIGQUERY_MAX_POLL_ELAPSED_MS = 25000;
const BIGQUERY_SUMMARY_HEADERS = Object.freeze(['Corpus', 'Word', 'Occurrences']);
const BIGQUERY_EXPECTED_FIELDS = Object.freeze([
Object.freeze({ name: 'corpus', kind: 'STRING' }),
Object.freeze({ name: 'normalized_word', kind: 'STRING' }),
Object.freeze({ name: 'total_occurrences', kind: 'INT64' }),
]);
const BIGQUERY_SHAKESPEARE_SQL = [
'SELECT',
' corpus,',
' LOWER(word) AS normalized_word,',
' SUM(word_count) AS total_occurrences',
'FROM `bigquery-public-data.samples.shakespeare`',
'WHERE corpus = @corpus',
'GROUP BY corpus, normalized_word',
'HAVING SUM(word_count) >= @minOccurrences',
'ORDER BY total_occurrences DESC, normalized_word ASC',
'LIMIT 10',
].join('\n');
const BIGQUERY_WORKFLOW_CONFIG = Object.freeze({
projectId: 'PASTE_YOUR_CLOUD_PROJECT_ID_HERE',
location: 'US',
sheetName: 'BigQuery Summary',
corpus: 'hamlet',
minOccurrences: 150,
maxBytesProcessed: 25 * 1024 * 1024,
dryRunOnly: true,
});
/**
* @typedef {{
* projectId: string,
* location: string,
* sheetName: string,
* corpus: string,
* minOccurrences: number,
* maxBytesProcessed: number,
* dryRunOnly: boolean
* }} BigQueryWorkflowConfig_
*/
/**
* @typedef {{
* result: BigQuery.Schema.QueryResponse | BigQuery.Schema.GetQueryResultsResponse,
* jobId: string | null,
* jobLocation: string,
* pollCount: number,
* totalBytesBilled: string | null
* }} CompletedBigQueryRun_
*/
/**
* Estimates scanned bytes with a BigQuery dry run without executing a billed job
* or modifying the active spreadsheet.
*/
function previewBigQueryCost() {
const config = requireBigQueryConfig_();
ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [
BIGQUERY_READONLY_SCOPE,
SPREADSHEET_CURRENTONLY_SCOPE,
]);
const sheet = getVerifiedSummarySheet_(config.sheetName);
const parameters = buildNamedQueryParameters_(config);
const estimate = runBigQueryDryRun_(config, parameters);
const report = {
mode: 'dry_run',
projectId: config.projectId,
location: config.location,
sheetName: sheet.getName(),
parameters: {
corpus: config.corpus,
minOccurrences: String(config.minOccurrences),
},
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
maxBytesProcessed: String(config.maxBytesProcessed),
wroteSheet: false,
rowCount: 0,
};
console.log(JSON.stringify(report, null, 2));
return report;
}
/**
* Verifies the target summary sheet and dry-run byte estimate first, then runs
* the parameterized query and writes at most 10 validated rows in one batch
* when dryRunOnly is false.
*/
function writeBigQuerySummaryToSheet() {
const config = requireBigQueryConfig_();
ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [
BIGQUERY_READONLY_SCOPE,
SPREADSHEET_CURRENTONLY_SCOPE,
]);
const sheet = getVerifiedSummarySheet_(config.sheetName);
const parameters = buildNamedQueryParameters_(config);
const estimate = runBigQueryDryRun_(config, parameters);
if (config.dryRunOnly) {
const dryRunReport = {
mode: 'dry_run',
projectId: config.projectId,
location: config.location,
sheetName: sheet.getName(),
parameters: {
corpus: config.corpus,
minOccurrences: String(config.minOccurrences),
},
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
maxBytesProcessed: String(config.maxBytesProcessed),
wroteSheet: false,
rowCount: 0,
};
console.log(JSON.stringify(dryRunReport, null, 2));
return dryRunReport;
}
if (config.maxBytesProcessed < BIGQUERY_MIN_BYTES_BILLED) {
throw new Error(
'Live queries require maxBytesProcessed of at least ' +
BIGQUERY_MIN_BYTES_BILLED +
' bytes (10 MiB), BigQuery\'s minimum maximumBytesBilled floor.',
);
}
const completed = runBoundedBigQueryQuery_(config, parameters);
const rows = decodeValidatedSummaryRows_(completed.result);
const bodyRange = sheet.getRange(
2,
1,
BIGQUERY_MAX_SHEET_ROWS,
BIGQUERY_SUMMARY_HEADERS.length,
);
bodyRange.clearContent();
if (rows.length > 0) {
sheet
.getRange(2, 1, rows.length, BIGQUERY_SUMMARY_HEADERS.length)
.setValues(rows);
}
const liveReport = {
mode: 'live',
projectId: config.projectId,
location: completed.jobLocation,
jobId: completed.jobId,
pollCount: completed.pollCount,
cacheHit: completed.result.cacheHit === true,
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
totalBytesProcessed:
typeof completed.result.totalBytesProcessed === 'string'
? completed.result.totalBytesProcessed
: null,
totalBytesBilled: completed.totalBytesBilled,
sheetName: sheet.getName(),
wroteSheet: true,
rowCount: rows.length,
};
console.log(JSON.stringify(liveReport, null, 2));
return liveReport;
}
/**
* @param {BigQueryWorkflowConfig_} config
* @returns {BigQuery.Schema.QueryParameter[]}
*/
function buildNamedQueryParameters_(config) {
return [
{
name: 'corpus',
parameterType: { type: 'STRING' },
parameterValue: { value: config.corpus },
},
{
name: 'minOccurrences',
parameterType: { type: 'INT64' },
// BigQuery REST v2 represents 64-bit integer parameters as decimal strings.
parameterValue: { value: String(config.minOccurrences) },
},
];
}
/**
* @param {BigQueryWorkflowConfig_} config
* @param {BigQuery.Schema.QueryParameter[]} parameters
* @returns {{estimatedBytesProcessed: string}}
*/
function runBigQueryDryRun_(config, parameters) {
const response = BigQuery.Jobs.query(
{
query: BIGQUERY_SHAKESPEARE_SQL,
useLegacySql: false,
dryRun: true,
location: config.location,
parameterMode: 'NAMED',
queryParameters: parameters,
},
config.projectId,
);
if (!response || response.kind !== 'bigquery#queryResponse') {
throw new Error('BigQuery returned an unexpected dry-run response.');
}
assertNoBigQueryErrors_(response.errors, 'Dry-run validation');
const estimatedBytes = parseNonNegativeInt64_(
response.totalBytesProcessed,
'dry-run totalBytesProcessed',
);
if (estimatedBytes > BigInt(config.maxBytesProcessed)) {
throw new Error(
'Dry run estimated ' +
response.totalBytesProcessed +
' bytes, exceeding maxBytesProcessed (' +
config.maxBytesProcessed +
' bytes). The live query was not started.',
);
}
return {
estimatedBytesProcessed: /** @type {string} */ (response.totalBytesProcessed),
};
}
/**
* @param {BigQueryWorkflowConfig_} config
* @param {BigQuery.Schema.QueryParameter[]} parameters
* @returns {CompletedBigQueryRun_}
*/
function runBoundedBigQueryQuery_(config, parameters) {
const startedAtMs = Date.now();
const initialResponse = BigQuery.Jobs.query(
{
query: BIGQUERY_SHAKESPEARE_SQL,
useLegacySql: false,
dryRun: false,
location: config.location,
parameterMode: 'NAMED',
queryParameters: parameters,
maxResults: BIGQUERY_MAX_SHEET_ROWS,
timeoutMs: BIGQUERY_INITIAL_TIMEOUT_MS,
maximumBytesBilled: String(config.maxBytesProcessed),
},
config.projectId,
);
if (!initialResponse || initialResponse.kind !== 'bigquery#queryResponse') {
throw new Error('BigQuery returned an unexpected query response.');
}
assertNoBigQueryErrors_(initialResponse.errors, 'Query execution');
const initialJobRef = initialResponse.jobReference;
const initialJobId =
initialJobRef && typeof initialJobRef.jobId === 'string' && initialJobRef.jobId.length > 0
? initialJobRef.jobId
: null;
const initialLocation =
initialJobRef &&
typeof initialJobRef.location === 'string' &&
initialJobRef.location.length > 0
? initialJobRef.location
: config.location;
const totalBytesBilled =
typeof initialResponse.totalBytesBilled === 'string' &&
/^\d+$/.test(initialResponse.totalBytesBilled)
? initialResponse.totalBytesBilled
: null;
if (initialResponse.jobComplete === true) {
return {
result: initialResponse,
jobId: initialJobId,
jobLocation: initialLocation,
pollCount: 0,
totalBytesBilled: totalBytesBilled,
};
}
if (initialResponse.jobComplete !== false) {
throw new Error('BigQuery returned a response without a boolean jobComplete flag.');
}
if (!initialJobId) {
throw new Error('BigQuery returned jobComplete: false without a jobReference.jobId.');
}
const jobProjectId =
initialJobRef &&
typeof initialJobRef.projectId === 'string' &&
initialJobRef.projectId.length > 0
? initialJobRef.projectId
: config.projectId;
let sleepMs = BIGQUERY_INITIAL_POLL_SLEEP_MS;
for (let attempt = 1; attempt <= BIGQUERY_MAX_POLL_ATTEMPTS; attempt += 1) {
if (Date.now() - startedAtMs >= BIGQUERY_MAX_POLL_ELAPSED_MS) {
break;
}
Utilities.sleep(sleepMs);
sleepMs = Math.min(sleepMs * 2, BIGQUERY_MAX_POLL_SLEEP_MS);
const polled = BigQuery.Jobs.getQueryResults(jobProjectId, initialJobId, {
location: initialLocation,
maxResults: BIGQUERY_MAX_SHEET_ROWS,
timeoutMs: BIGQUERY_POLL_REQUEST_TIMEOUT_MS,
});
if (!polled || polled.kind !== 'bigquery#getQueryResultsResponse') {
throw new Error('BigQuery returned an unexpected getQueryResults response.');
}
assertNoBigQueryErrors_(polled.errors, 'Query polling');
if (polled.jobComplete === true) {
return {
result: polled,
jobId: initialJobId,
jobLocation: initialLocation,
pollCount: attempt,
totalBytesBilled: totalBytesBilled,
};
}
if (polled.jobComplete !== false) {
throw new Error('BigQuery polling returned a response without a boolean jobComplete flag.');
}
}
throw new Error(
'BigQuery job ' +
initialJobId +
' in location ' +
initialLocation +
' did not complete within the bounded polling window. Check BigQuery job history before rerunning.',
);
}
/**
* @param {BigQuery.Schema.QueryResponse | BigQuery.Schema.GetQueryResultsResponse} result
* @returns {(string | number)[][]}
*/
function decodeValidatedSummaryRows_(result) {
if (typeof result.pageToken === 'string' && result.pageToken.length > 0) {
throw new Error(
'BigQuery returned a pageToken indicating additional result pages. Narrow the SQL summary before writing to Sheets.',
);
}
const totalRows = parseNonNegativeInt64_(result.totalRows, 'totalRows');
if (totalRows > BigInt(BIGQUERY_MAX_SHEET_ROWS)) {
throw new Error(
'BigQuery reported ' +
result.totalRows +
' total rows, exceeding the ' +
BIGQUERY_MAX_SHEET_ROWS +
'-row summary range.',
);
}
const fields = requireExpectedSchemaFields_(result.schema);
const rawRows = result.rows === undefined ? [] : result.rows;
if (!Array.isArray(rawRows)) {
throw new Error('BigQuery returned rows in an unexpected format.');
}
if (BigInt(rawRows.length) !== totalRows) {
throw new Error(
'BigQuery returned ' +
rawRows.length +
' rows on the first page for totalRows=' +
result.totalRows +
'. Refusing to write a partial page.',
);
}
return rawRows.map((row, rowIndex) => decodeBigQueryRow_(row, fields, rowIndex));
}
/**
* @param {BigQuery.Schema.TableSchema | undefined} schema
* @returns {BigQuery.Schema.TableFieldSchema[]}
*/
function requireExpectedSchemaFields_(schema) {
if (!schema || !Array.isArray(schema.fields)) {
throw new Error('BigQuery completed the query without returning schema.fields.');
}
if (schema.fields.length !== BIGQUERY_EXPECTED_FIELDS.length) {
throw new Error(
'Expected ' +
BIGQUERY_EXPECTED_FIELDS.length +
' result columns, received ' +
schema.fields.length +
'.',
);
}
schema.fields.forEach((field, index) => {
const expected = BIGQUERY_EXPECTED_FIELDS[index];
if (!field || field.name !== expected.name) {
throw new Error(
'Result column ' + (index + 1) + ' must be named ' + expected.name + '.',
);
}
if (expected.kind === 'STRING' && field.type !== 'STRING') {
throw new Error('Column ' + expected.name + ' must have STRING type.');
}
if (
expected.kind === 'INT64' &&
field.type !== 'INTEGER' &&
field.type !== 'INT64'
) {
throw new Error('Column ' + expected.name + ' must have INTEGER or INT64 type.');
}
});
return schema.fields;
}
/**
* @param {BigQuery.Schema.TableRow} row
* @param {BigQuery.Schema.TableFieldSchema[]} fields
* @param {number} rowIndex
* @returns {(string | number)[]}
*/
function decodeBigQueryRow_(row, fields, rowIndex) {
if (!row || !Array.isArray(row.f) || row.f.length !== fields.length) {
throw new Error(
'BigQuery row ' + (rowIndex + 1) + ' does not match the result schema column count.',
);
}
return row.f.map((cell, colIndex) => {
const field = fields[colIndex];
const fieldName = typeof field.name === 'string' ? field.name : 'column_' + (colIndex + 1);
if (!cell || cell.v === null || cell.v === undefined) {
throw new Error(
'BigQuery returned an unexpected NULL in column ' +
fieldName +
' on row ' +
(rowIndex + 1) +
'.',
);
}
if (field.type === 'STRING') {
if (typeof cell.v !== 'string') {
throw new Error('Column ' + fieldName + ' returned a non-string cell value.');
}
if (/^[=+\-@]/.test(cell.v)) {
throw new Error(
'Refusing to write string starting with a spreadsheet formula prefix in column ' +
fieldName +
'.',
);
}
return cell.v;
}
if (field.type === 'INTEGER' || field.type === 'INT64') {
if (typeof cell.v !== 'string' || !/^-?\d+$/.test(cell.v)) {
throw new Error(
'Column ' + fieldName + ' returned an integer outside BigQuery\'s decimal-string format.',
);
}
const parsed = BigInt(cell.v);
if (
parsed >= BigInt(Number.MIN_SAFE_INTEGER) &&
parsed <= BigInt(Number.MAX_SAFE_INTEGER)
) {
return Number(cell.v);
}
// Keep 64-bit integers outside IEEE-754 safe bounds as exact decimal text.
return cell.v;
}
throw new Error('Unsupported BigQuery column type: ' + String(field.type));
});
}
/**
* @param {string} sheetName
* @returns {SpreadsheetApp.Sheet}
*/
function getVerifiedSummarySheet_(sheetName) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Run this bound script from a Google Sheet.');
}
const sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error(
'Sheet "' +
sheetName +
'" is missing. Create the tab and add headers Corpus, Word, Occurrences in A1:C1.',
);
}
const headerValues = sheet
.getRange(1, 1, 1, BIGQUERY_SUMMARY_HEADERS.length)
.getValues()[0];
const headersMatch = BIGQUERY_SUMMARY_HEADERS.every(
(expected, index) => headerValues[index] === expected,
);
if (!headersMatch) {
throw new Error(
'Sheet "' +
sheetName +
'" must have exact headers in A1:C1: ' +
BIGQUERY_SUMMARY_HEADERS.join(', ') +
'.',
);
}
const bodyFormulas = sheet
.getRange(2, 1, BIGQUERY_MAX_SHEET_ROWS, BIGQUERY_SUMMARY_HEADERS.length)
.getFormulas();
const hasFormula = bodyFormulas.some((formulaRow) =>
formulaRow.some((formula) => formula !== ''),
);
if (hasFormula) {
throw new Error(
'Reserved output range A2:C' +
(BIGQUERY_MAX_SHEET_ROWS + 1) +
' on "' +
sheetName +
'" contains a formula. Clear or move formulas before writing BigQuery values.',
);
}
return sheet;
}
/**
* @param {BigQuery.Schema.ErrorProto[] | undefined} errors
* @param {string} stepLabel
*/
function assertNoBigQueryErrors_(errors, stepLabel) {
if (!Array.isArray(errors) || errors.length === 0) {
return;
}
const first = errors[0];
const reason = first && typeof first.reason === 'string' ? first.reason : 'unknownError';
const message =
first && typeof first.message === 'string' ? first.message : 'BigQuery reported an error.';
throw new Error(stepLabel + ' failed (' + reason + '): ' + message);
}
/**
* @param {unknown} rawValue
* @param {string} label
* @returns {bigint}
*/
function parseNonNegativeInt64_(rawValue, label) {
if (typeof rawValue !== 'string' || !/^\d+$/.test(rawValue)) {
throw new Error('BigQuery returned an invalid decimal integer string for ' + label + '.');
}
return BigInt(rawValue);
}
/**
* @returns {BigQueryWorkflowConfig_}
*/
function requireBigQueryConfig_() {
if (typeof BIGQUERY_WORKFLOW_CONFIG.projectId !== 'string') {
throw new Error('projectId must be a Google Cloud project ID string.');
}
const projectId = BIGQUERY_WORKFLOW_CONFIG.projectId.trim();
if (projectId === 'PASTE_YOUR_CLOUD_PROJECT_ID_HERE') {
throw new Error(
'Set BIGQUERY_WORKFLOW_CONFIG.projectId to your Google Cloud project ID before running.',
);
}
if (!/^[a-z][a-z0-9-]{4,28}[a-z0-9]$/.test(projectId)) {
throw new Error(
'projectId must be a valid Google Cloud project ID (6 to 30 lowercase letters, digits, or hyphens), not a display name or project number.',
);
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.location !== 'string') {
throw new Error('location must be a BigQuery location string such as US.');
}
const location = BIGQUERY_WORKFLOW_CONFIG.location.trim();
if (!/^[A-Za-z0-9-]+$/.test(location)) {
throw new Error('location must contain only letters, digits, and hyphens.');
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.sheetName !== 'string') {
throw new Error('sheetName must be a non-empty string.');
}
const sheetName = BIGQUERY_WORKFLOW_CONFIG.sheetName.trim();
if (sheetName.length === 0) {
throw new Error('sheetName must be a non-empty string.');
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.corpus !== 'string') {
throw new Error('corpus must be a non-empty string.');
}
const corpus = BIGQUERY_WORKFLOW_CONFIG.corpus.trim();
if (!/^[a-z0-9_]+$/.test(corpus)) {
throw new Error('corpus must match a lowercase Shakespeare corpus identifier such as hamlet.');
}
const minOccurrences = BIGQUERY_WORKFLOW_CONFIG.minOccurrences;
if (
!Number.isSafeInteger(minOccurrences) ||
minOccurrences < 1 ||
minOccurrences > 100000
) {
throw new Error('minOccurrences must be an integer from 1 through 100000.');
}
const maxBytesProcessed = BIGQUERY_WORKFLOW_CONFIG.maxBytesProcessed;
if (
!Number.isSafeInteger(maxBytesProcessed) ||
maxBytesProcessed < 1 ||
maxBytesProcessed > BIGQUERY_MAX_ALLOWED_BYTES_CAP
) {
throw new Error(
'maxBytesProcessed must be an integer from 1 through ' +
BIGQUERY_MAX_ALLOWED_BYTES_CAP +
' bytes.',
);
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.dryRunOnly !== 'boolean') {
throw new Error('dryRunOnly must be true or false.');
}
return {
projectId: projectId,
location: location,
sheetName: sheetName,
corpus: corpus,
minOccurrences: minOccurrences,
maxBytesProcessed: maxBytesProcessed,
dryRunOnly: BIGQUERY_WORKFLOW_CONFIG.dryRunOnly,
};
}
The manifest declares the BigQuery v2 wrapper and locks the project to two narrow OAuth scopes:
{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/bigquery.readonly",
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
Two distinct Google Cloud projects can participate in one run:
The Apps Script backing Cloud project owns OAuth consent and wrapper API enablement (see Apps Script Google Cloud projects). Apps Script's default Cloud project enables the BigQuery API automatically when
BigQueryis inenabledAdvancedServices. If your script is linked to a standard Cloud project in Project Settings, an administrator of that linked project must also enable BigQuery API under APIs & Services.The query execution project (
BIGQUERY_WORKFLOW_CONFIG.projectId) is theprojectIdargument passed toBigQuery.Jobs.queryandBigQuery.Jobs.getQueryResults. BigQuery checksbigquery.jobs.createand meters scanned bytes against this project. You do not need to change the Apps Script backing project in Project Settings to pass yourprojectIdtoBigQuery.Jobs.query.
Check permissions across Apps Script, OAuth, and IAM
A bound script that queries BigQuery and writes to Google Sheets crosses four independent permission checks:
Wrapper and API enablement:
enabledAdvancedServicesinappsscript.jsonexposesBigQuery(serviceId: "bigquery",version: "v2"), and BigQuery API is enabled in both the script's backing Cloud project andconfig.projectId.OAuth token scopes: Without explicit
oauthScopes, Apps Script reads the BigQuery discovery document and requestshttps://www.googleapis.com/auth/bigquery, which allows creating, updating, and deleting datasets and tables. Declaringhttps://www.googleapis.com/auth/bigquery.readonlyandhttps://www.googleapis.com/auth/spreadsheets.currentonlyrestricts the OAuth token to read-only BigQuery queries without destination tables (see BigQuery authentication and OAuth scopes) and to the active workbook only (see Authorization for Google services).Google Cloud IAM roles: OAuth scopes cap what the script may do on your behalf, whereas Cloud IAM controls what your Google account may access. Your account needs
bigquery.jobs.createonconfig.projectIdandbigquery.tables.getDataon each queried table. Google grants public read access onbigquery-public-data.samples.shakespeare. Private company tables require BigQuery Data Viewer or an equivalent dataset role for the executing account.Granular consent check:
ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [BIGQUERY_READONLY_SCOPE, SPREADSHEET_CURRENTONLY_SCOPE])verifies that both scopes were granted before reading the sheet or calling BigQuery.
Neither OAuth scope filters individual tables or cell ranges. bigquery.readonly covers every dataset your account can read, and spreadsheets.currentonly covers every tab in the bound spreadsheet. Your SQL query, projectId, and getVerifiedSummarySheet_ enforce those boundaries in code.
Estimate scanned bytes with a dry run first
In BigQuery's columnar storage engine, adding LIMIT 10 does not reduce the bytes a query scans or bills (see Estimate and control BigQuery costs). BigQuery reads the complete column blocks referenced in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY across every matching table or partition, computes the aggregates, and returns the top ten rows.
Selecting SELECT * on a wide table scans every column even when you write only three columns to Sheets. Name only the columns the calculation needs. BIGQUERY_SHAKESPEARE_SQL reads three of the four columns in bigquery-public-data.samples.shakespeare (corpus, word, and word_count, skipping corpus_date), keeping the logical scan near 5 MB across 164,656 rows.
Code.gs enforces two byte limits before writing to the sheet:
Client-checked dry run (
dryRun: true):runBigQueryDryRun_callsBigQuery.Jobs.querywithdryRun: trueanduseLegacySql: false. BigQuery validates the GoogleSQL syntax, parameter types, and table access and returnstotalBytesProcessedwithout running the job or billing for scanned bytes.Server-enforced billing cap (
maximumBytesBilled): On live runs,runBoundedBigQueryQuery_setsmaximumBytesBilled: String(config.maxBytesProcessed). If new data lands in the table between the dry run and the live call, BigQuery aborts the query above that ceiling without charging for the scan.
Both totalBytesProcessed and maximumBytesBilled use BigQuery REST v2's 64-bit integer (int64) JSON format: base-10 decimal strings such as "5312744" or "26214400". Because warehouse byte counts can exceed Number.MAX_SAFE_INTEGER (9,007,199,254,740,991), parseNonNegativeInt64_ checks /^\d+$/ and compares BigInt(response.totalBytesProcessed) against BigInt(config.maxBytesProcessed) without lossy floating-point conversion.
Account for BigQuery's 10 MiB minimum billing floor when tuning maxBytesProcessed (see BigQuery pricing):
runBigQueryDryRun_checkstotalBytesProcessedin Apps Script first, so you can lowermaxBytesProcessedto1048576bytes (1 MiB) during testing to confirm oversized scans are rejected before a live query starts.BigQuery's server-side
maximumBytesBilledrejects any live table query set below10485760bytes (10 MiB) withQuery exceeded limit for bytes billed.writeBigQuerySummaryToSheetenforcesconfig.maxBytesProcessed >= BIGQUERY_MIN_BYTES_BILLEDwheneverdryRunOnlyisfalse.
Bind named query parameters instead of interpolating SQL
Concatenating config strings, prompt input, or cell values into SQL ("WHERE corpus = '" + corpus + "'") breaks on apostrophes and lets crafted text rewrite the WHERE clause or UNION data from other readable tables.
Bind dynamic filter values with GoogleSQL named parameters instead (see Run parameterized BigQuery queries). Set useLegacySql: false and parameterMode: 'NAMED' on QueryRequest, reference @corpus and @minOccurrences in SQL, and pass matching objects in queryParameters:
function buildNamedQueryParameters_(config) {
return [
{
name: 'corpus',
parameterType: { type: 'STRING' },
parameterValue: { value: config.corpus },
},
{
name: 'minOccurrences',
parameterType: { type: 'INT64' },
parameterValue: { value: String(config.minOccurrences) },
},
];
} Each QueryParameter entry supplies three properties:
name: the parameter name without the@prefix ('corpus'for@corpus).parameterType: the GoogleSQL type descriptor, such as{ type: 'STRING' },{ type: 'INT64' },{ type: 'BOOL' }, or{ type: 'DATE' }.parameterValue: an object holding the serialized scalar invalue. Even for'INT64'and'FLOAT64',QueryParameterValue.valuemust be a string (String(config.minOccurrences)), not a JavaScript number.
Query parameters bind literal data values only. BigQuery rejects @parameter placeholders for project IDs, dataset names, table names, column names, ORDER BY directions, or LIMIT clauses (see Run parameterized BigQuery queries). LIMIT 10 stays a fixed SQL constant in BIGQUERY_SHAKESPEARE_SQL. When a script must switch between known tables or sort columns, look up the choice in a frozen allowlist object in Apps Script instead of concatenating raw cell text.
Poll incomplete jobs with a bounded deadline
BigQuery.Jobs.query starts a query job whose execution can outlast a single HTTP request. Setting timeoutMs: 10000 on QueryRequest caps how long the initial HTTP call waits before returning. It does not cancel the job after 10 seconds. When the job finishes inside that wait window, BigQuery returns jobComplete: true with schema, rows, and totalRows. Otherwise it returns jobComplete: false with jobReference ({ projectId, jobId, location }) and omits the result fields.
Minimal samples often poll with an unbounded loop:
// Unsafe: can block until Apps Script's 6-minute kill without logging jobId,
// and omits the job location required outside the US and EU multi-regions.
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
} runBoundedBigQueryQuery_ puts three safeguards around BigQuery.Jobs.getQueryResults:
Pass the job's geographic location: Every BigQuery job runs in a specific multi-region (
US,EU) or region (us-central1,europe-west1). See BigQuery locations.QueryRequestsetslocation: config.location('US'forbigquery-public-data.samples.shakespeare), and polling forwardsjobReference.locationin{ location: initialLocation }. Polling a regional job withoutlocationfails with404 Not found: Job.Cap both attempts and elapsed time: Polling stops after
BIGQUERY_MAX_POLL_ATTEMPTS(5) calls orBIGQUERY_MAX_POLL_ELAPSED_MS(25000milliseconds), backing off from500to4000milliseconds viaUtilities.sleepand waiting up to5000milliseconds pergetQueryResultsrequest.Check
errorsand logjobIdon timeout: BothQueryResponseandGetQueryResultsResponsecan populateerrors.assertNoBigQueryErrors_throws on the firstErrorProtoentry. If the deadline expires whilejobCompleteremainsfalse, the error message namesinitialJobIdandinitialLocationso you can inspect or cancel the running job in Cloud Console before rerunning.
In Apps Script's BigQuery v2 declarations, QueryResponse defines totalBytesBilled whereas GetQueryResultsResponse omits it. runBoundedBigQueryQuery_ reads initialResponse.totalBytesBilled before polling so the return shape stays identical whether pollCount is 0 or positive.
Decode TableRow cells and batch the Sheet write
In QueryResponse and GetQueryResultsResponse, rows is an array of TableRow objects. Each TableRow wraps its cells in an f array of { v } objects aligned by index with schema.fields:
{
"schema": {
"fields": [
{ "name": "corpus", "type": "STRING", "mode": "NULLABLE" },
{ "name": "normalized_word", "type": "STRING", "mode": "NULLABLE" },
{ "name": "total_occurrences", "type": "INTEGER", "mode": "NULLABLE" }
]
},
"totalRows": "2",
"rows": [
{ "f": [{ "v": "hamlet" }, { "v": "the" }, { "v": "1145" }] },
{ "f": [{ "v": "hamlet" }, { "v": "and" }, { "v": "967" }] }
]
} BigQuery serializes every non-null scalar v as a JSON string, including INTEGER (INT64) counts such as "1145". Writing row.f.map((cell) => cell.v) straight into setValues() leaves three hazards unchecked:
Silent page truncation:
maxResults: 10limits one response page, not the SQL result set. If a future edit dropsLIMIT 10from SQL, BigQuery returns 10 rows alongsidetotalRows: "4789"and apageToken.decodeValidatedSummaryRows_throws ifpageTokenis present, iftotalRowsexceedsBIGQUERY_MAX_SHEET_ROWS, or ifrows.lengthdiffers fromtotalRows, preventing partial pages from masquerading as complete summaries or blowing past Sheets' 10-million-cell limit.String-typed numbers and 64-bit precision:
requireExpectedSchemaFields_checks thatschema.fieldsmatchescorpus(STRING),normalized_word(STRING), andtotal_occurrences(INTEGERorINT64).decodeBigQueryRow_parses integer strings withBigInt(cell.v)and converts withNumber(cell.v)only insideNumber.MIN_SAFE_INTEGERthroughNumber.MAX_SAFE_INTEGER(-9,007,199,254,740,991to9,007,199,254,740,991). Counts arrive in Sheets as numeric values forSUMand charts, while 64-bit IDs outside IEEE-754 safe bounds remain exact decimal strings in JavaScript (format target ID columns as plain text@in Sheets so values over 15 digits are not rounded).Spreadsheet formula injection:
Range.setValues()evaluates strings starting with=,+,-, or@as formulas.decodeBigQueryRow_rejects string cells with a leading formula prefix before writing to value cells.
writeBigQuerySummaryToSheet clears values in A2:C11 on BigQuery Summary and overwrites that range with decoded query rows. Keep formulas, notes, and manual edits outside A2:C11. Before calling BigQuery, getVerifiedSummarySheet_ verifies the tab name, checks Corpus, Word, Occurrences in A1:C1, and checks getFormulas() across A2:C11 so layout mistakes stop the run before spending query bytes.
When writing rows to BigQuery Summary, the script clears the 10-row slot first and then sizes the write range to rows.length:
const bodyRange = sheet.getRange(
2,
1,
BIGQUERY_MAX_SHEET_ROWS,
BIGQUERY_SUMMARY_HEADERS.length,
);
bodyRange.clearContent();
if (rows.length > 0) {
sheet
.getRange(2, 1, rows.length, BIGQUERY_SUMMARY_HEADERS.length)
.setValues(rows);
} Clearing A2:C11 removes stale tail rows when a stricter @minOccurrences filter returns four rows after an earlier ten-row run. Guarding rows.length > 0 and sizing getRange from rows.length prevents two SpreadsheetApp errors:
Passing
0asnumRowstosheet.getRange(2, 1, rows.length, 3)when zero rows match throws The coordinates or dimensions of the range are invalid.Passing four rows to
bodyRange.setValues(rows)on the 10-row rangeA2:C11throws The number of rows in the data does not match the number of rows in the range.
Run the dry-run preview, then write the summary
Verify the byte estimate with a dry run before enabling live writes:
Check that
BigQuery SummaryhasCorpus,Word, andOccurrencesinA1:C1, thatA2:C11is empty, and thatBIGQUERY_WORKFLOW_CONFIGhas yourprojectIdanddryRunOnly: true.Select
previewBigQueryCostin the editor toolbar and click Run. Approve the OAuth consent prompt for read-only BigQuery access and current-spreadsheet access. If granular consent halts the first run, click Run once more after granting both scopes.Inspect the JSON report in Execution log:
| Report field | Expected value in dry-run mode |
|---|---|
mode | "dry_run" |
projectId, location | Your Google Cloud project ID and "US" |
sheetName | "BigQuery Summary", confirming A1:C1 headers and formula-free A2:C11 |
parameters | { "corpus": "hamlet", "minOccurrences": "150" } |
estimatedBytesProcessed | Decimal byte string (around 5 MB for the three referenced shakespeare columns) |
maxBytesProcessed | "26214400" (25 MiB) |
wroteSheet, rowCount | false and 0, with A2:C11 unchanged |
Set
dryRunOnly: falseinBIGQUERY_WORKFLOW_CONFIG, saveCode.gs, selectwriteBigQuerySummaryToSheet, and click Run.Open
BigQuery Summaryin Google Sheets. CellsA2:C11now contain the ten most frequent words inhamletwith at least 150 occurrences (the,and,to,of,i, and the next five words in descending order), with strings in columns A and B and numbers in column C.Read the live report in Execution log:
modeis"live",wroteSheetistrue,rowCountis10,pollCountis0when the query finished within the initial 10-second wait (or1to5after polling), andtotalBytesBilledshows"10485760"on an uncached on-demand run (BigQuery's 10 MiB minimum) or"0"whencacheHitistrue.
Rerunning writeBigQuerySummaryToSheet with identical parameters within roughly 24 hours normally hits BigQuery's per-user result cache, returning cacheHit: true and totalBytesBilled: "0" (see Estimate and control BigQuery costs).
Predict, change, observe, restore
Test each guard on the practice spreadsheet and compare the log output with A2:C11:
Change in BIGQUERY_WORKFLOW_CONFIG | Prediction | What to observe |
|---|---|---|
Keep dryRunOnly: false and set maxBytesProcessed: 1024 * 1024 (1 MiB) | runBigQueryDryRun_ rejects the ~5 MB scan before starting a live query | Throws Dry run estimated ... bytes, exceeding maxBytesProcessed (1048576 bytes) while A2:C11 keeps the prior ten rows |
Restore maxBytesProcessed: 25 * 1024 * 1024 and set minOccurrences: 700 | Fewer than ten words in hamlet reach 700 occurrences (the, and, to, of) | A2:C11 is cleared, matching rows fill from A2 downward, and A6:C11 stays blank |
Keep maxBytesProcessed: 25 * 1024 * 1024 and set minOccurrences: 50000 | Zero rows match, so rows.length === 0 skips setValues() after clearContent() | A2:C11 becomes blank without a range-dimension error, and the report logs rowCount: 0 with wroteSheet: true |
Restore minOccurrences: 150 and maxBytesProcessed: 25 * 1024 * 1024, run writeBigQuerySummaryToSheet once to restore the ten-row summary, and set dryRunOnly: true.
Classify failures before rerunning
A failed API call or validation check throws an Error before touching A2:C11 (clearContent() runs only after the query and all row and schema checks pass). Match the error in Executions to its cause before retrying:
BigQuery is not defined: Saveappsscript.jsonwithenabledAdvancedServices(userSymbol: "BigQuery",serviceId: "bigquery",version: "v2") and confirm BigQuery API appears under Services.Set BIGQUERY_WORKFLOW_CONFIG.projectId...or invalidprojectIdformat: Supply the lowercase Cloud Project ID (my-project-123), not the display name or numeric project number.Access Not Configuredor BigQuery API disabled (403): Enable BigQuery API inconfig.projectId(and in the Apps Script standard Cloud project if linked).Access Denied: Project ...: User does not have bigquery.jobs.create permission(403): Grant your Google account BigQuery Job User (roles/bigquery.jobUser) or BigQuery User onconfig.projectId.Insufficient authentication scopes or
ScriptApp.requireScopesfailure: Confirmappsscript.jsonlistshttps://www.googleapis.com/auth/bigquery.readonlyandhttps://www.googleapis.com/auth/spreadsheets.currentonly, then rerun from the editor and approve both scopes.Not found: Job(404) duringgetQueryResults: Keepconfig.locationaligned with the dataset region ('US'forbigquery-public-data.samples.shakespeare) and passjobReference.locationwhen polling.Query exceeded limit for bytes billed(400 or 500): Either the scan exceededmaximumBytesBilled, ormaxBytesProcessedwas set below BigQuery's10485760-byte (10 MiB) floor. Filter partitions and drop unused columns before raising the limit.BigQuery job ... did not complete within the bounded polling window: Check BigQuery > Job history in Google Cloud Console for the loggedjobIdbefore rerunning so you do not queue duplicate jobs.Missing tab, header mismatch, or formula in
A2:C11: Fix theBigQuery Summarytab name, restoreCorpus,Word,OccurrencesinA1:C1, or move formulas out ofA2:C11. BecausegetVerifiedSummarySheet_runs first, layout mistakes cost zero BigQuery bytes.
Clean up the practice project
When you finish, set BIGQUERY_WORKFLOW_CONFIG.dryRunOnly back to true and reset projectId to 'PASTE_YOUR_CLOUD_PROJECT_ID_HERE' so your Google Cloud project ID does not stay in shared source. Clear A2:C11 on BigQuery Summary or move the disposable spreadsheet to Trash.
To remove BigQuery access from the script project, delete the BigQuery object from enabledAdvancedServices in appsscript.json and remove https://www.googleapis.com/auth/bigquery.readonly from oauthScopes. Leave BigQuery API enabled in a shared Cloud project unless you confirm no other workloads depend on it. To revoke the OAuth grant from your Google Account, open Google Account > Security > Your connections to third-party apps & services and remove the practice project.
Next, read Use AI models from Apps Script to classify, extract, and summarize spreadsheet rows with validated JSON outputs and a human review step.
Project files
Complete source files for this chapter's examples.
Query BigQuery into a reserved Google Sheets summary tab
Code.gs
Download fileconst BIGQUERY_READONLY_SCOPE = 'https://www.googleapis.com/auth/bigquery.readonly';
const SPREADSHEET_CURRENTONLY_SCOPE = 'https://www.googleapis.com/auth/spreadsheets.currentonly';
const BIGQUERY_MIN_BYTES_BILLED = 10 * 1024 * 1024;
const BIGQUERY_MAX_ALLOWED_BYTES_CAP = 250 * 1024 * 1024;
const BIGQUERY_MAX_SHEET_ROWS = 10;
const BIGQUERY_INITIAL_TIMEOUT_MS = 10000;
const BIGQUERY_POLL_REQUEST_TIMEOUT_MS = 5000;
const BIGQUERY_MAX_POLL_ATTEMPTS = 5;
const BIGQUERY_INITIAL_POLL_SLEEP_MS = 500;
const BIGQUERY_MAX_POLL_SLEEP_MS = 4000;
const BIGQUERY_MAX_POLL_ELAPSED_MS = 25000;
const BIGQUERY_SUMMARY_HEADERS = Object.freeze(['Corpus', 'Word', 'Occurrences']);
const BIGQUERY_EXPECTED_FIELDS = Object.freeze([
Object.freeze({ name: 'corpus', kind: 'STRING' }),
Object.freeze({ name: 'normalized_word', kind: 'STRING' }),
Object.freeze({ name: 'total_occurrences', kind: 'INT64' }),
]);
const BIGQUERY_SHAKESPEARE_SQL = [
'SELECT',
' corpus,',
' LOWER(word) AS normalized_word,',
' SUM(word_count) AS total_occurrences',
'FROM `bigquery-public-data.samples.shakespeare`',
'WHERE corpus = @corpus',
'GROUP BY corpus, normalized_word',
'HAVING SUM(word_count) >= @minOccurrences',
'ORDER BY total_occurrences DESC, normalized_word ASC',
'LIMIT 10',
].join('\n');
const BIGQUERY_WORKFLOW_CONFIG = Object.freeze({
projectId: 'PASTE_YOUR_CLOUD_PROJECT_ID_HERE',
location: 'US',
sheetName: 'BigQuery Summary',
corpus: 'hamlet',
minOccurrences: 150,
maxBytesProcessed: 25 * 1024 * 1024,
dryRunOnly: true,
});
/**
* @typedef {{
* projectId: string,
* location: string,
* sheetName: string,
* corpus: string,
* minOccurrences: number,
* maxBytesProcessed: number,
* dryRunOnly: boolean
* }} BigQueryWorkflowConfig_
*/
/**
* @typedef {{
* result: BigQuery.Schema.QueryResponse | BigQuery.Schema.GetQueryResultsResponse,
* jobId: string | null,
* jobLocation: string,
* pollCount: number,
* totalBytesBilled: string | null
* }} CompletedBigQueryRun_
*/
/**
* Estimates scanned bytes with a BigQuery dry run without executing a billed job
* or modifying the active spreadsheet.
*/
function previewBigQueryCost() {
const config = requireBigQueryConfig_();
ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [
BIGQUERY_READONLY_SCOPE,
SPREADSHEET_CURRENTONLY_SCOPE,
]);
const sheet = getVerifiedSummarySheet_(config.sheetName);
const parameters = buildNamedQueryParameters_(config);
const estimate = runBigQueryDryRun_(config, parameters);
const report = {
mode: 'dry_run',
projectId: config.projectId,
location: config.location,
sheetName: sheet.getName(),
parameters: {
corpus: config.corpus,
minOccurrences: String(config.minOccurrences),
},
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
maxBytesProcessed: String(config.maxBytesProcessed),
wroteSheet: false,
rowCount: 0,
};
console.log(JSON.stringify(report, null, 2));
return report;
}
/**
* Verifies the target summary sheet and dry-run byte estimate first, then runs
* the parameterized query and writes at most 10 validated rows in one batch
* when dryRunOnly is false.
*/
function writeBigQuerySummaryToSheet() {
const config = requireBigQueryConfig_();
ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [
BIGQUERY_READONLY_SCOPE,
SPREADSHEET_CURRENTONLY_SCOPE,
]);
const sheet = getVerifiedSummarySheet_(config.sheetName);
const parameters = buildNamedQueryParameters_(config);
const estimate = runBigQueryDryRun_(config, parameters);
if (config.dryRunOnly) {
const dryRunReport = {
mode: 'dry_run',
projectId: config.projectId,
location: config.location,
sheetName: sheet.getName(),
parameters: {
corpus: config.corpus,
minOccurrences: String(config.minOccurrences),
},
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
maxBytesProcessed: String(config.maxBytesProcessed),
wroteSheet: false,
rowCount: 0,
};
console.log(JSON.stringify(dryRunReport, null, 2));
return dryRunReport;
}
if (config.maxBytesProcessed < BIGQUERY_MIN_BYTES_BILLED) {
throw new Error(
'Live queries require maxBytesProcessed of at least ' +
BIGQUERY_MIN_BYTES_BILLED +
' bytes (10 MiB), BigQuery\'s minimum maximumBytesBilled floor.',
);
}
const completed = runBoundedBigQueryQuery_(config, parameters);
const rows = decodeValidatedSummaryRows_(completed.result);
const bodyRange = sheet.getRange(
2,
1,
BIGQUERY_MAX_SHEET_ROWS,
BIGQUERY_SUMMARY_HEADERS.length,
);
bodyRange.clearContent();
if (rows.length > 0) {
sheet
.getRange(2, 1, rows.length, BIGQUERY_SUMMARY_HEADERS.length)
.setValues(rows);
}
const liveReport = {
mode: 'live',
projectId: config.projectId,
location: completed.jobLocation,
jobId: completed.jobId,
pollCount: completed.pollCount,
cacheHit: completed.result.cacheHit === true,
estimatedBytesProcessed: estimate.estimatedBytesProcessed,
totalBytesProcessed:
typeof completed.result.totalBytesProcessed === 'string'
? completed.result.totalBytesProcessed
: null,
totalBytesBilled: completed.totalBytesBilled,
sheetName: sheet.getName(),
wroteSheet: true,
rowCount: rows.length,
};
console.log(JSON.stringify(liveReport, null, 2));
return liveReport;
}
/**
* @param {BigQueryWorkflowConfig_} config
* @returns {BigQuery.Schema.QueryParameter[]}
*/
function buildNamedQueryParameters_(config) {
return [
{
name: 'corpus',
parameterType: { type: 'STRING' },
parameterValue: { value: config.corpus },
},
{
name: 'minOccurrences',
parameterType: { type: 'INT64' },
// BigQuery REST v2 represents 64-bit integer parameters as decimal strings.
parameterValue: { value: String(config.minOccurrences) },
},
];
}
/**
* @param {BigQueryWorkflowConfig_} config
* @param {BigQuery.Schema.QueryParameter[]} parameters
* @returns {{estimatedBytesProcessed: string}}
*/
function runBigQueryDryRun_(config, parameters) {
const response = BigQuery.Jobs.query(
{
query: BIGQUERY_SHAKESPEARE_SQL,
useLegacySql: false,
dryRun: true,
location: config.location,
parameterMode: 'NAMED',
queryParameters: parameters,
},
config.projectId,
);
if (!response || response.kind !== 'bigquery#queryResponse') {
throw new Error('BigQuery returned an unexpected dry-run response.');
}
assertNoBigQueryErrors_(response.errors, 'Dry-run validation');
const estimatedBytes = parseNonNegativeInt64_(
response.totalBytesProcessed,
'dry-run totalBytesProcessed',
);
if (estimatedBytes > BigInt(config.maxBytesProcessed)) {
throw new Error(
'Dry run estimated ' +
response.totalBytesProcessed +
' bytes, exceeding maxBytesProcessed (' +
config.maxBytesProcessed +
' bytes). The live query was not started.',
);
}
return {
estimatedBytesProcessed: /** @type {string} */ (response.totalBytesProcessed),
};
}
/**
* @param {BigQueryWorkflowConfig_} config
* @param {BigQuery.Schema.QueryParameter[]} parameters
* @returns {CompletedBigQueryRun_}
*/
function runBoundedBigQueryQuery_(config, parameters) {
const startedAtMs = Date.now();
const initialResponse = BigQuery.Jobs.query(
{
query: BIGQUERY_SHAKESPEARE_SQL,
useLegacySql: false,
dryRun: false,
location: config.location,
parameterMode: 'NAMED',
queryParameters: parameters,
maxResults: BIGQUERY_MAX_SHEET_ROWS,
timeoutMs: BIGQUERY_INITIAL_TIMEOUT_MS,
maximumBytesBilled: String(config.maxBytesProcessed),
},
config.projectId,
);
if (!initialResponse || initialResponse.kind !== 'bigquery#queryResponse') {
throw new Error('BigQuery returned an unexpected query response.');
}
assertNoBigQueryErrors_(initialResponse.errors, 'Query execution');
const initialJobRef = initialResponse.jobReference;
const initialJobId =
initialJobRef && typeof initialJobRef.jobId === 'string' && initialJobRef.jobId.length > 0
? initialJobRef.jobId
: null;
const initialLocation =
initialJobRef &&
typeof initialJobRef.location === 'string' &&
initialJobRef.location.length > 0
? initialJobRef.location
: config.location;
const totalBytesBilled =
typeof initialResponse.totalBytesBilled === 'string' &&
/^\d+$/.test(initialResponse.totalBytesBilled)
? initialResponse.totalBytesBilled
: null;
if (initialResponse.jobComplete === true) {
return {
result: initialResponse,
jobId: initialJobId,
jobLocation: initialLocation,
pollCount: 0,
totalBytesBilled: totalBytesBilled,
};
}
if (initialResponse.jobComplete !== false) {
throw new Error('BigQuery returned a response without a boolean jobComplete flag.');
}
if (!initialJobId) {
throw new Error('BigQuery returned jobComplete: false without a jobReference.jobId.');
}
const jobProjectId =
initialJobRef &&
typeof initialJobRef.projectId === 'string' &&
initialJobRef.projectId.length > 0
? initialJobRef.projectId
: config.projectId;
let sleepMs = BIGQUERY_INITIAL_POLL_SLEEP_MS;
for (let attempt = 1; attempt <= BIGQUERY_MAX_POLL_ATTEMPTS; attempt += 1) {
if (Date.now() - startedAtMs >= BIGQUERY_MAX_POLL_ELAPSED_MS) {
break;
}
Utilities.sleep(sleepMs);
sleepMs = Math.min(sleepMs * 2, BIGQUERY_MAX_POLL_SLEEP_MS);
const polled = BigQuery.Jobs.getQueryResults(jobProjectId, initialJobId, {
location: initialLocation,
maxResults: BIGQUERY_MAX_SHEET_ROWS,
timeoutMs: BIGQUERY_POLL_REQUEST_TIMEOUT_MS,
});
if (!polled || polled.kind !== 'bigquery#getQueryResultsResponse') {
throw new Error('BigQuery returned an unexpected getQueryResults response.');
}
assertNoBigQueryErrors_(polled.errors, 'Query polling');
if (polled.jobComplete === true) {
return {
result: polled,
jobId: initialJobId,
jobLocation: initialLocation,
pollCount: attempt,
totalBytesBilled: totalBytesBilled,
};
}
if (polled.jobComplete !== false) {
throw new Error('BigQuery polling returned a response without a boolean jobComplete flag.');
}
}
throw new Error(
'BigQuery job ' +
initialJobId +
' in location ' +
initialLocation +
' did not complete within the bounded polling window. Check BigQuery job history before rerunning.',
);
}
/**
* @param {BigQuery.Schema.QueryResponse | BigQuery.Schema.GetQueryResultsResponse} result
* @returns {(string | number)[][]}
*/
function decodeValidatedSummaryRows_(result) {
if (typeof result.pageToken === 'string' && result.pageToken.length > 0) {
throw new Error(
'BigQuery returned a pageToken indicating additional result pages. Narrow the SQL summary before writing to Sheets.',
);
}
const totalRows = parseNonNegativeInt64_(result.totalRows, 'totalRows');
if (totalRows > BigInt(BIGQUERY_MAX_SHEET_ROWS)) {
throw new Error(
'BigQuery reported ' +
result.totalRows +
' total rows, exceeding the ' +
BIGQUERY_MAX_SHEET_ROWS +
'-row summary range.',
);
}
const fields = requireExpectedSchemaFields_(result.schema);
const rawRows = result.rows === undefined ? [] : result.rows;
if (!Array.isArray(rawRows)) {
throw new Error('BigQuery returned rows in an unexpected format.');
}
if (BigInt(rawRows.length) !== totalRows) {
throw new Error(
'BigQuery returned ' +
rawRows.length +
' rows on the first page for totalRows=' +
result.totalRows +
'. Refusing to write a partial page.',
);
}
return rawRows.map((row, rowIndex) => decodeBigQueryRow_(row, fields, rowIndex));
}
/**
* @param {BigQuery.Schema.TableSchema | undefined} schema
* @returns {BigQuery.Schema.TableFieldSchema[]}
*/
function requireExpectedSchemaFields_(schema) {
if (!schema || !Array.isArray(schema.fields)) {
throw new Error('BigQuery completed the query without returning schema.fields.');
}
if (schema.fields.length !== BIGQUERY_EXPECTED_FIELDS.length) {
throw new Error(
'Expected ' +
BIGQUERY_EXPECTED_FIELDS.length +
' result columns, received ' +
schema.fields.length +
'.',
);
}
schema.fields.forEach((field, index) => {
const expected = BIGQUERY_EXPECTED_FIELDS[index];
if (!field || field.name !== expected.name) {
throw new Error(
'Result column ' + (index + 1) + ' must be named ' + expected.name + '.',
);
}
if (expected.kind === 'STRING' && field.type !== 'STRING') {
throw new Error('Column ' + expected.name + ' must have STRING type.');
}
if (
expected.kind === 'INT64' &&
field.type !== 'INTEGER' &&
field.type !== 'INT64'
) {
throw new Error('Column ' + expected.name + ' must have INTEGER or INT64 type.');
}
});
return schema.fields;
}
/**
* @param {BigQuery.Schema.TableRow} row
* @param {BigQuery.Schema.TableFieldSchema[]} fields
* @param {number} rowIndex
* @returns {(string | number)[]}
*/
function decodeBigQueryRow_(row, fields, rowIndex) {
if (!row || !Array.isArray(row.f) || row.f.length !== fields.length) {
throw new Error(
'BigQuery row ' + (rowIndex + 1) + ' does not match the result schema column count.',
);
}
return row.f.map((cell, colIndex) => {
const field = fields[colIndex];
const fieldName = typeof field.name === 'string' ? field.name : 'column_' + (colIndex + 1);
if (!cell || cell.v === null || cell.v === undefined) {
throw new Error(
'BigQuery returned an unexpected NULL in column ' +
fieldName +
' on row ' +
(rowIndex + 1) +
'.',
);
}
if (field.type === 'STRING') {
if (typeof cell.v !== 'string') {
throw new Error('Column ' + fieldName + ' returned a non-string cell value.');
}
if (/^[=+\-@]/.test(cell.v)) {
throw new Error(
'Refusing to write string starting with a spreadsheet formula prefix in column ' +
fieldName +
'.',
);
}
return cell.v;
}
if (field.type === 'INTEGER' || field.type === 'INT64') {
if (typeof cell.v !== 'string' || !/^-?\d+$/.test(cell.v)) {
throw new Error(
'Column ' + fieldName + ' returned an integer outside BigQuery\'s decimal-string format.',
);
}
const parsed = BigInt(cell.v);
if (
parsed >= BigInt(Number.MIN_SAFE_INTEGER) &&
parsed <= BigInt(Number.MAX_SAFE_INTEGER)
) {
return Number(cell.v);
}
// Keep 64-bit integers outside IEEE-754 safe bounds as exact decimal text.
return cell.v;
}
throw new Error('Unsupported BigQuery column type: ' + String(field.type));
});
}
/**
* @param {string} sheetName
* @returns {SpreadsheetApp.Sheet}
*/
function getVerifiedSummarySheet_(sheetName) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Run this bound script from a Google Sheet.');
}
const sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error(
'Sheet "' +
sheetName +
'" is missing. Create the tab and add headers Corpus, Word, Occurrences in A1:C1.',
);
}
const headerValues = sheet
.getRange(1, 1, 1, BIGQUERY_SUMMARY_HEADERS.length)
.getValues()[0];
const headersMatch = BIGQUERY_SUMMARY_HEADERS.every(
(expected, index) => headerValues[index] === expected,
);
if (!headersMatch) {
throw new Error(
'Sheet "' +
sheetName +
'" must have exact headers in A1:C1: ' +
BIGQUERY_SUMMARY_HEADERS.join(', ') +
'.',
);
}
const bodyFormulas = sheet
.getRange(2, 1, BIGQUERY_MAX_SHEET_ROWS, BIGQUERY_SUMMARY_HEADERS.length)
.getFormulas();
const hasFormula = bodyFormulas.some((formulaRow) =>
formulaRow.some((formula) => formula !== ''),
);
if (hasFormula) {
throw new Error(
'Reserved output range A2:C' +
(BIGQUERY_MAX_SHEET_ROWS + 1) +
' on "' +
sheetName +
'" contains a formula. Clear or move formulas before writing BigQuery values.',
);
}
return sheet;
}
/**
* @param {BigQuery.Schema.ErrorProto[] | undefined} errors
* @param {string} stepLabel
*/
function assertNoBigQueryErrors_(errors, stepLabel) {
if (!Array.isArray(errors) || errors.length === 0) {
return;
}
const first = errors[0];
const reason = first && typeof first.reason === 'string' ? first.reason : 'unknownError';
const message =
first && typeof first.message === 'string' ? first.message : 'BigQuery reported an error.';
throw new Error(stepLabel + ' failed (' + reason + '): ' + message);
}
/**
* @param {unknown} rawValue
* @param {string} label
* @returns {bigint}
*/
function parseNonNegativeInt64_(rawValue, label) {
if (typeof rawValue !== 'string' || !/^\d+$/.test(rawValue)) {
throw new Error('BigQuery returned an invalid decimal integer string for ' + label + '.');
}
return BigInt(rawValue);
}
/**
* @returns {BigQueryWorkflowConfig_}
*/
function requireBigQueryConfig_() {
if (typeof BIGQUERY_WORKFLOW_CONFIG.projectId !== 'string') {
throw new Error('projectId must be a Google Cloud project ID string.');
}
const projectId = BIGQUERY_WORKFLOW_CONFIG.projectId.trim();
if (projectId === 'PASTE_YOUR_CLOUD_PROJECT_ID_HERE') {
throw new Error(
'Set BIGQUERY_WORKFLOW_CONFIG.projectId to your Google Cloud project ID before running.',
);
}
if (!/^[a-z][a-z0-9-]{4,28}[a-z0-9]$/.test(projectId)) {
throw new Error(
'projectId must be a valid Google Cloud project ID (6 to 30 lowercase letters, digits, or hyphens), not a display name or project number.',
);
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.location !== 'string') {
throw new Error('location must be a BigQuery location string such as US.');
}
const location = BIGQUERY_WORKFLOW_CONFIG.location.trim();
if (!/^[A-Za-z0-9-]+$/.test(location)) {
throw new Error('location must contain only letters, digits, and hyphens.');
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.sheetName !== 'string') {
throw new Error('sheetName must be a non-empty string.');
}
const sheetName = BIGQUERY_WORKFLOW_CONFIG.sheetName.trim();
if (sheetName.length === 0) {
throw new Error('sheetName must be a non-empty string.');
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.corpus !== 'string') {
throw new Error('corpus must be a non-empty string.');
}
const corpus = BIGQUERY_WORKFLOW_CONFIG.corpus.trim();
if (!/^[a-z0-9_]+$/.test(corpus)) {
throw new Error('corpus must match a lowercase Shakespeare corpus identifier such as hamlet.');
}
const minOccurrences = BIGQUERY_WORKFLOW_CONFIG.minOccurrences;
if (
!Number.isSafeInteger(minOccurrences) ||
minOccurrences < 1 ||
minOccurrences > 100000
) {
throw new Error('minOccurrences must be an integer from 1 through 100000.');
}
const maxBytesProcessed = BIGQUERY_WORKFLOW_CONFIG.maxBytesProcessed;
if (
!Number.isSafeInteger(maxBytesProcessed) ||
maxBytesProcessed < 1 ||
maxBytesProcessed > BIGQUERY_MAX_ALLOWED_BYTES_CAP
) {
throw new Error(
'maxBytesProcessed must be an integer from 1 through ' +
BIGQUERY_MAX_ALLOWED_BYTES_CAP +
' bytes.',
);
}
if (typeof BIGQUERY_WORKFLOW_CONFIG.dryRunOnly !== 'boolean') {
throw new Error('dryRunOnly must be true or false.');
}
return {
projectId: projectId,
location: location,
sheetName: sheetName,
corpus: corpus,
minOccurrences: minOccurrences,
maxBytesProcessed: maxBytesProcessed,
dryRunOnly: BIGQUERY_WORKFLOW_CONFIG.dryRunOnly,
};
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/bigquery.readonly",
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}