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