Google App脚本:Javascript运行时意外退出

问题描述

我已经编写了一个应用脚本，该脚本将与bigQuery API交互并获取bigQuery结果.

I have written an app script that will interact with the bigQuery API and fetch the bigQuery results.

API被击中的次数接近60-70次.在此过程中，有时会收到以下错误消息:

API is been hit around close to 60-70 times. In this process sometimes I'm getting the following error message:

有人可以帮我吗?

让我知道您是否需要其他信息

Let me know if you need any additional information

function fetchTables() {
  var timezone = "GMT+" + new Date().getTimezoneOffset()/60;
  var date = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd HH:mm");
  Logger.log(date);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Tables');
  var value = sheet.getRange('A1').getValue();

  var projectId = 'corpbi-dev';

  var request = {
    query: 'select table_id from [corpbi-dev:' + value + '.__TABLES__]'
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

   var rows = queryResults.rows;

  if (rows) {
    // Append the results.
    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;
      }
    }
  }

  var queries = [];
  var totalbytes = [];
  var timelapsed = [];
  var rowcount = [];
  var jobcode = [];

  var finalSheet = SpreadsheetApp.getActive().getSheetByName('Final Metrics');

  for(var i=1; i<=data.length; i++) {
  var sql = "SELECT stage_loading_query FROM (SELECT stage_loading_query, job_start_time FROM [corpbi-dev:etl_log.job_details] WHERE target_table LIKE '%"+ value + "." + data[i-1]+"%' " +
    "GROUP BY 1, 2 ORDER BY 2 desc) LIMIT 1"

  var request1 = {
    query: sql
  };
  var queryResults1 = BigQuery.Jobs.query(request1, projectId);
  var sql1 = queryResults1.rows;
    //Logger.log(sql1);
 //   break;

  if (sql1) {
    // Append the results.
    var data1 = new Array(sql1.length);
    for (var k = 0; k < sql1.length; k++) {
      var cols = sql1[k].f;
      data1[k] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data1[k][j] = cols[j].v;
      }
    }
  }
   queries.push(data1);
     
  }
  //Logger.log(queries.length);
  finalSheet.getRange(2, 1, queries.length).setValues(queries);
//  queries = finalSheet.getRange(2, 1, finalSheet.getLastRow()-1).getValues();
  var endtime = (new Date()).getTime();
  Logger.log(endtime);


  for(var i=1; i<=queries.length; i++) {
    var sqlstmt = queries[i-1];


    try{
      var request2 = {
    query: sqlstmt,
    useLegacySql: true
  };
    var sleepTimeMs = 50000;
  var queryResults2 = BigQuery.Jobs.query(request2, projectId);
       var jobId1 = queryResults2.jobReference.jobId;
      while (!queryResults2.jobComplete) {
        Utilities.sleep(sleepTimeMs);
        sleepTimeMs *= 2;
        queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
        //continue;
      }

  var queryDetails = BigQuery.Jobs.get(projectId, jobId1);
  totalbytes.push(queryDetails.statistics.totalBytesProcessed);
  timelapsed.push(queryDetails.statistics.totalSlotMs);
  rowcount.push(queryResults2.totalRows);
}
    catch(e){
      try{
      var request2 = {
    query: sqlstmt,
    useLegacySql: false
  };

    var sleepTimeMs = 50000;
    var queryResults2 = BigQuery.Jobs.query(request2, projectId);
    var jobId1 = queryResults2.jobReference.jobId;
    while(!queryResults2.jobComplete) {
          Utilities.sleep(sleepTimeMs);
        sleepTimeMs *= 2;
        queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
       // continue;
      }

  var queryDetails = BigQuery.Jobs.get(projectId, jobId1);
  totalbytes.push(queryDetails.statistics.totalBytesProcessed);
  timelapsed.push(queryDetails.statistics.totalSlotMs);
  rowcount.push(queryResults2.totalRows);
}
      catch(e){
      Logger.log(e);
    }
      Logger.log(e);
    }
  finalSheet.getRange(i+1, 2,).setValue((totalbytes[i-1]/1048576).toFixed(2) + 'MB');
  finalSheet.getRange(i+1, 3,).setValue((timelapsed[i-1]/1000).toFixed(2) + 's');
  finalSheet.getRange(i+1, 4,).setValue((rowcount[i-1]));

  }

}

推荐答案

已知Google Apps脚本的V8运行时处于Alpha阶段，这意味着您可能会遇到一些错误/错误.

The V8 Runtime for Google Apps Script is known to be on Alpha stage, which means you may encounter some bugs/errors.

要使用稳定的GAS版本，只需要将脚本清单中的 runtimeVersion 值从 V8 更改为 STABLE .

In order to use the stable GAS version, you only need to change the runtimeVersion value in your script's manifest from V8 to STABLE.

这篇关于Google App脚本:Javascript运行时意外退出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！