Skip to content
Skip to chapter

Chapter 44

Advanced integrations and data

15 min read

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.

By the end

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.

Chapter navigation

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 pathWhere rows liveBest fitKey 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 BigQueryInteractive exploration, scheduled refreshes, and tables larger than a spreadsheet gridRequires an eligible Google Workspace edition and a billing-enabled Cloud project. Preview tabs are not ordinary editable cell ranges
SpreadsheetApp.BigQueryDataSourceSpecSame Connected Sheets engine, created or refreshed from Apps ScriptAutomating Connected Sheets setup, parameter updates, or refreshes across workbooksRequires 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 RangeSmall aggregated tables fed into ordinary formulas, custom validation before writing, or multi-step Workspace scriptsSubject 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: true byte 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.

warning

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:

  1. 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 as my-bq-practice-412), not the display name or numeric project number.

  2. Create a new Google Sheet that you own and rename its first tab to BigQuery Summary.

  3. Enter the exact headers Corpus, Word, and Occurrences in A1:C1. Leave A2:C11 empty and free of formulas.

  4. Open Extensions > Apps Script, replace Code.gs with the complete source below, and open Project Settings to enable Show "appsscript.json" manifest file in editor.

  5. Return to Editor, replace appsscript.json with the supplied manifest, and save both files. Confirm Services lists BigQuery API (v2) with the identifier BigQuery, and do not add a second entry through the sidebar.

  6. In BIGQUERY_WORKFLOW_CONFIG, replace PASTE_YOUR_CLOUD_PROJECT_ID_HERE with your Cloud project ID. Keep location: 'US', sheetName: 'BigQuery Summary', corpus: 'hamlet', minOccurrences: 150, maxBytesProcessed: 25 * 1024 * 1024 (26,214,400 bytes, or 25 MiB), and dryRunOnly: true. Save Code.gs.

Code.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,
  };
}

Download Code.gs

The manifest declares the BigQuery v2 wrapper and locks the project to two narrow OAuth scopes:

appsscript.json
{
  "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"
  ]
}

Download appsscript.json

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 BigQuery is in enabledAdvancedServices. 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 the projectId argument passed to BigQuery.Jobs.query and BigQuery.Jobs.getQueryResults. BigQuery checks bigquery.jobs.create and meters scanned bytes against this project. You do not need to change the Apps Script backing project in Project Settings to pass your projectId to BigQuery.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:

  1. Wrapper and API enablement: enabledAdvancedServices in appsscript.json exposes BigQuery (serviceId: "bigquery", version: "v2"), and BigQuery API is enabled in both the script's backing Cloud project and config.projectId.

  2. OAuth token scopes: Without explicit oauthScopes, Apps Script reads the BigQuery discovery document and requests https://www.googleapis.com/auth/bigquery, which allows creating, updating, and deleting datasets and tables. Declaring https://www.googleapis.com/auth/bigquery.readonly and https://www.googleapis.com/auth/spreadsheets.currentonly restricts 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).

  3. 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.create on config.projectId and bigquery.tables.getData on each queried table. Google grants public read access on bigquery-public-data.samples.shakespeare. Private company tables require BigQuery Data Viewer or an equivalent dataset role for the executing account.

  4. 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:

  1. Client-checked dry run (dryRun: true): runBigQueryDryRun_ calls BigQuery.Jobs.query with dryRun: true and useLegacySql: false. BigQuery validates the GoogleSQL syntax, parameter types, and table access and returns totalBytesProcessed without running the job or billing for scanned bytes.

  2. Server-enforced billing cap (maximumBytesBilled): On live runs, runBoundedBigQueryQuery_ sets maximumBytesBilled: 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_ checks totalBytesProcessed in Apps Script first, so you can lower maxBytesProcessed to 1048576 bytes (1 MiB) during testing to confirm oversized scans are rejected before a live query starts.

  • BigQuery's server-side maximumBytesBilled rejects any live table query set below 10485760 bytes (10 MiB) with Query exceeded limit for bytes billed. writeBigQuerySummaryToSheet enforces config.maxBytesProcessed >= BIGQUERY_MIN_BYTES_BILLED whenever dryRunOnly is false.

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:

Source
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 in value. Even for 'INT64' and 'FLOAT64', QueryParameterValue.value must be a string (String(config.minOccurrences)), not a JavaScript number.

note

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:

Source
// 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:

  1. 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. QueryRequest sets location: config.location ('US' for bigquery-public-data.samples.shakespeare), and polling forwards jobReference.location in { location: initialLocation }. Polling a regional job without location fails with 404 Not found: Job.

  2. Cap both attempts and elapsed time: Polling stops after BIGQUERY_MAX_POLL_ATTEMPTS (5) calls or BIGQUERY_MAX_POLL_ELAPSED_MS (25000 milliseconds), backing off from 500 to 4000 milliseconds via Utilities.sleep and waiting up to 5000 milliseconds per getQueryResults request.

  3. Check errors and log jobId on timeout: Both QueryResponse and GetQueryResultsResponse can populate errors. assertNoBigQueryErrors_ throws on the first ErrorProto entry. If the deadline expires while jobComplete remains false, the error message names initialJobId and initialLocation so 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:

Source
{
  "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: 10 limits one response page, not the SQL result set. If a future edit drops LIMIT 10 from SQL, BigQuery returns 10 rows alongside totalRows: "4789" and a pageToken. decodeValidatedSummaryRows_ throws if pageToken is present, if totalRows exceeds BIGQUERY_MAX_SHEET_ROWS, or if rows.length differs from totalRows, 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 that schema.fields matches corpus (STRING), normalized_word (STRING), and total_occurrences (INTEGER or INT64). decodeBigQueryRow_ parses integer strings with BigInt(cell.v) and converts with Number(cell.v) only inside Number.MIN_SAFE_INTEGER through Number.MAX_SAFE_INTEGER (-9,007,199,254,740,991 to 9,007,199,254,740,991). Counts arrive in Sheets as numeric values for SUM and 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.

warning

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:

Source
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:

Run the dry-run preview, then write the summary

Verify the byte estimate with a dry run before enabling live writes:

  1. Check that BigQuery Summary has Corpus, Word, and Occurrences in A1:C1, that A2:C11 is empty, and that BIGQUERY_WORKFLOW_CONFIG has your projectId and dryRunOnly: true.

  2. Select previewBigQueryCost in 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.

  3. Inspect the JSON report in Execution log:

Report fieldExpected value in dry-run mode
mode"dry_run"
projectId, locationYour Google Cloud project ID and "US"
sheetName"BigQuery Summary", confirming A1:C1 headers and formula-free A2:C11
parameters{ "corpus": "hamlet", "minOccurrences": "150" }
estimatedBytesProcessedDecimal byte string (around 5 MB for the three referenced shakespeare columns)
maxBytesProcessed"26214400" (25 MiB)
wroteSheet, rowCountfalse and 0, with A2:C11 unchanged
  1. Set dryRunOnly: false in BIGQUERY_WORKFLOW_CONFIG, save Code.gs, select writeBigQuerySummaryToSheet, and click Run.

  2. Open BigQuery Summary in Google Sheets. Cells A2:C11 now contain the ten most frequent words in hamlet with 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.

  3. Read the live report in Execution log: mode is "live", wroteSheet is true, rowCount is 10, pollCount is 0 when the query finished within the initial 10-second wait (or 1 to 5 after polling), and totalBytesBilled shows "10485760" on an uncached on-demand run (BigQuery's 10 MiB minimum) or "0" when cacheHit is true.

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_CONFIGPredictionWhat to observe
Keep dryRunOnly: false and set maxBytesProcessed: 1024 * 1024 (1 MiB)runBigQueryDryRun_ rejects the ~5 MB scan before starting a live queryThrows Dry run estimated ... bytes, exceeding maxBytesProcessed (1048576 bytes) while A2:C11 keeps the prior ten rows
Restore maxBytesProcessed: 25 * 1024 * 1024 and set minOccurrences: 700Fewer 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: 50000Zero 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: Save appsscript.json with enabledAdvancedServices (userSymbol: "BigQuery", serviceId: "bigquery", version: "v2") and confirm BigQuery API appears under Services.

  • Set BIGQUERY_WORKFLOW_CONFIG.projectId... or invalid projectId format: Supply the lowercase Cloud Project ID (my-project-123), not the display name or numeric project number.

  • Access Not Configured or BigQuery API disabled (403): Enable BigQuery API in config.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 on config.projectId.

  • Insufficient authentication scopes or ScriptApp.requireScopes failure: Confirm appsscript.json lists https://www.googleapis.com/auth/bigquery.readonly and https://www.googleapis.com/auth/spreadsheets.currentonly, then rerun from the editor and approve both scopes.

  • Not found: Job (404) during getQueryResults: Keep config.location aligned with the dataset region ('US' for bigquery-public-data.samples.shakespeare) and pass jobReference.location when polling.

  • Query exceeded limit for bytes billed (400 or 500): Either the scan exceeded maximumBytesBilled, or maxBytesProcessed was set below BigQuery's 10485760-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 logged jobId before rerunning so you do not queue duplicate jobs.

  • Missing tab, header mismatch, or formula in A2:C11: Fix the BigQuery Summary tab name, restore Corpus, Word, Occurrences in A1:C1, or move formulas out of A2:C11. Because getVerifiedSummarySheet_ 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
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,
  };
}

appsscript.json

Download file
appsscript.json
{
  "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"
  ]
}