Google Sheets Apps Script无法获取BigQuery请求的TotalRows，如何修改？

Problem Statement #

I've written a Google Apps Script for Google Sheets to update a table in BigQuery, and I want it to return details including the total number of rows in the updated table. Right now, the script can return the job status and totalBytesProcessed, but I can't get the totalRows value. I checked the BigQuery API docs but still can't figure out what's wrong—how should I modify the script to get the total row count?

Current Script #

// Need to provoke a drive dialog
// DriveApp.getFiles()
// Replace this value with your project ID and the name of the sheet to update.
var projectId = 'my-project';
var sheetName = 'my-sheet';
// Use standard SQL to query BigQuery
var request = {
  query: 'DROP TABLE `my-project.xyz.targetgroup_5_tbl`; CREATE TABLE `my-project.xyz.targetgroup_5_tbl` AS SELECT * FROM `waipu-app-prod.Access.targetgroup_5_std`;',
  useLegacySql: false
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2;
  queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
if (queryResults.jobComplete=true) {
  // Append the results.
  var status= queryResults.jobComplete;
  var dayc = new Date();
  var totalbytes = queryResults.totalBytesProcessed;
  var totalRows = queryResults.totalRows;
  var rows = [
    ['target group',status,dayc,totalbytes,totalRows],
  ]
  var status = queryResults.jobComplete;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName(sheetName);
  currentSheet.getRange(23,1, 1, 5).setValues(rows);
  console.info('%d rows inserted.', queryResults.totalRows);
} else {
  console.info('No results found in BigQuery');
}
}

Root Cause #

Your script runs DDL statements (DROP TABLE + CREATE TABLE AS SELECT), which don't return any result rows like a standard SELECT query does. That's why BigQuery.Jobs.getQueryResults() doesn't populate the totalRows field—it only exists for queries that return a dataset.

Also, you have a small bug in your condition check: queryResults.jobComplete=true is an assignment, not a comparison. It should be queryResults.jobComplete === true (or just queryResults.jobComplete for simplicity).

Solution #

To get the total rows of the newly created table, you need to fetch the full job metadata using BigQuery.Jobs.get(). The statistics.query.totalRows field in the job details will show the number of rows processed (and inserted into the new table) by the CREATE TABLE AS SELECT operation.

Modified Script #

// Need to provoke a drive dialog
// DriveApp.getFiles()
// Replace this value with your project ID and the name of the sheet to update.
var projectId = 'my-project';
var sheetName = 'my-sheet';
// Use standard SQL to query BigQuery
var request = {
  query: 'DROP TABLE `my-project.xyz.targetgroup_5_tbl`; CREATE TABLE `my-project.xyz.targetgroup_5_tbl` AS SELECT * FROM `waipu-app-prod.Access.targetgroup_5_std`;',
  useLegacySql: false
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2;
  queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
if (queryResults.jobComplete) {
  // Fetch full job details to get row count from statistics
  var job = BigQuery.Jobs.get(projectId, jobId);
  var totalRows = Number(job.statistics.query.totalRows); // Convert string value to number
  
  // Append the results to the sheet
  var status = queryResults.jobComplete;
  var dayc = new Date();
  var totalbytes = queryResults.totalBytesProcessed;
  var rows = [
    ['target group', status, dayc, totalbytes, totalRows],
  ];
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName(sheetName);
  currentSheet.getRange(23, 1, 1, 5).setValues(rows);
  console.info('%d rows inserted.', totalRows);
} else {
  console.info('No results found in BigQuery');
}

Key Changes Explained #

1. Fixed condition check: Changed the assignment queryResults.jobComplete=true to a proper check queryResults.jobComplete to verify if the job finished successfully.
2. Fetched full job metadata: Added var job = BigQuery.Jobs.get(projectId, jobId); to retrieve the complete job stats, which includes row count data.
3. Extracted total rows: Used job.statistics.query.totalRows to get the row count, and converted it to a number (the API returns it as a string by default).

内容的提问来源于stack exchange，提问作者Albrecht