Google Apps Script与BigQuery查询结果不一致问题咨询

现象 #

通过Google Apps Script执行查询时会间歇性返回无结果，但直接在BigQuery中执行相同查询却始终能得到预期结果。

预期结果 #

使用同一账号和权限执行时，Google Apps Script中的查询结果应与BigQuery直接执行的结果一致。

复现步骤 #

• 通过Google Apps Script执行已知在BigQuery中有结果的查询。
• 观察到该查询有时在Google Apps Script中返回无结果。
• 在BigQuery中直接执行相同查询，确认始终返回预期结果。

补充信息 #

• 出现问题时无错误提示，仅返回无结果，问题随机出现，晨间发生概率更高。
• 已检查脚本、查询语句及权限，未发现问题；未找到其他用户有类似问题。
• 怀疑是API问题或服务器查询队列过载导致。

示例脚本 #

projectId='my_project_ID';

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu("Loading BQ")
  .addItem("Get delivery and visit data", 'getresult_query1')
  .addToUi();
}

function mapToArray(rows) {
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
  }
  return data;
};

function getresult_query1() {
  var sheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Context');
  var campaignName = sheet.getRange('C6').getValue()

   var request = {query: 'My_query',
     location: 'EU',
     useLegacySql: false,
     timeoutMs: 1000000
   };

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var sheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall'); 
  sheet.getRange(7,2,1,9).setValue(''); 
  if(queryResults.rows){
    sheet.getRange(7,2,queryResults.rows.length,9).setValues(mapToArray(queryResults.rows));
    }else{
    sheet.getRange(7,2).setValue('No results'); 
  }
}

1. 处理异步查询状态 #

BigQuery.Jobs.query在查询未完成时会返回无rows的结果但不抛出错误，必须检查jobComplete状态并轮询等待：

function getresult_query1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Context');
  var campaignName = sheet.getRange('C6').getValue()

  var request = {
    query: 'My_query',
    location: 'EU',
    useLegacySql: false,
    timeoutMs: 1000000
  };

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // 轮询直到查询完成
  while (!queryResults.jobComplete) {
    Utilities.sleep(1000); // 等待1秒后重试
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall'); 
  sheet.getRange(7,2,1,9).setValue(''); 
  if(queryResults.rows){
    sheet.getRange(7,2,queryResults.rows.length,9).setValues(mapToArray(queryResults.rows));
  }else{
    sheet.getRange(7,2).setValue('No results'); 
  }
}

2. 添加错误捕获与日志 #

隐性API调用问题不会触发显性错误，添加日志可定位问题：

function getresult_query1() {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Context');
    var campaignName = sheet.getRange('C6').getValue()

    var request = {
      query: 'My_query',
      location: 'EU',
      useLegacySql: false,
      timeoutMs: 1000000
    };

    var queryResults = BigQuery.Jobs.query(request, projectId);
    Logger.log('查询完成状态：%s，返回行数：%s', queryResults.jobComplete, queryResults.rows ? queryResults.rows.length : 0);

    var jobId = queryResults.jobReference.jobId;
    while (!queryResults.jobComplete) {
      Utilities.sleep(1000);
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
      Logger.log('轮询查询状态：%s', queryResults.jobComplete);
    }

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall'); 
    sheet.getRange(7,2,1,9).setValue(''); 
    if(queryResults.rows){
      sheet.getRange(7,2,queryResults.rows.length,9).setValues(mapToArray(queryResults.rows));
    }else{
      sheet.getRange(7,2).setValue('No results'); 
    }
  } catch (e) {
    Logger.log('查询异常：%s', e.toString());
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall').getRange(7,2).setValue('查询出错：' + e.message);
  }
}

3. 增加重试机制应对资源拥堵 #

晨间BigQuery资源紧张时，指数退避重试可提升成功率：

function runQueryWithRetry(request, projectId, maxRetries = 3) {
  let attempt = 0;
  while (attempt < maxRetries) {
    try {
      let results = BigQuery.Jobs.query(request, projectId);
      while (!results.jobComplete) {
        Utilities.sleep(2000);
        results = BigQuery.Jobs.getQueryResults(projectId, results.jobReference.jobId);
      }
      return results;
    } catch (e) {
      attempt++;
      if (attempt >= maxRetries) throw e;
      Utilities.sleep(5000 * attempt); // 指数退避等待
    }
  }
}

// 在getresult_query1中替换原查询调用
function getresult_query1() {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Context');
    var campaignName = sheet.getRange('C6').getValue()

    var request = {
      query: 'My_query',
      location: 'EU',
      useLegacySql: false,
      timeoutMs: 1000000
    };

    var queryResults = runQueryWithRetry(request, projectId);

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall'); 
    sheet.getRange(7,2,1,9).setValue(''); 
    if(queryResults.rows){
      sheet.getRange(7,2,queryResults.rows.length,9).setValues(mapToArray(queryResults.rows));
    }else{
      sheet.getRange(7,2).setValue('No results'); 
    }
  } catch (e) {
    Logger.log('查询失败：%s', e.toString());
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overall').getRange(7,2).setValue('查询失败：' + e.message);
  }
}

4. 验证参数一致性 #

确保脚本中的location、useLegacySql、时区等参数与BigQuery控制台完全一致，避免隐性环境差异导致结果不同。

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