0

I'm using bigquery API from the Google App script and running the queries in a loop and the code is running fine. At the end of the code I'm fetching few stats of the query and writing it into the spreadsheet.

While we see the sheet, few of the cell are filled with undefined. Could someone help me here?

Here is the SS enter image description here

Also if i'm running more than 15 queries its throwing me an error saying execution time exceeded.

Here is the code:

function finalqueries() {

  var projectId = 'xxxxxx';
  var queries = [];
  var totalbytes = [];
  var timelapsed = [];
  var rowcount = [];

  var finalSheet = SpreadsheetApp.getActive().getSheetByName('Final Metrics');
  queries = finalSheet.getRange(2, 1, finalSheet.getLastRow()-1).getValues();

//  var userProperties = PropertiesService.getUserProperties();
//  var loopCounter = Number(userProperties.getProperty('loopCounter'));
//  var limit = queries.length;

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

    try{
      var request2 = {
    query: sqlstmt,
    useLegacySql: true
  };

  var queryResults2 = BigQuery.Jobs.query(request2, projectId);
  var jobId1 = queryResults2.jobReference.jobId;
//      timer:
//      while (!queryResults2.jobComplete) {
//        Utilities.sleep(sleepTimeMs);
//        sleepTimeMs *= 2;
//        queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
//         if(sleepTimeMs>120000){
//            break timer;
//          }
//      }

  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 queryResults2 = BigQuery.Jobs.query(request2, projectId);
    var jobId1 = queryResults2.jobReference.jobId;
    var sleepTimeMs = 5000;
//        timer:
//        while(!queryResults2.jobComplete) {
//          Utilities.sleep(sleepTimeMs);
//        sleepTimeMs *= 2;
//        queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
//          if(sleepTimeMs>120000){
//            break timer;
//          }
//      }

  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]);
  finalSheet.getRange(i+1, 3).setValue(timelapsed[i-1]);
//  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]);     
  }

}
sravan kumar
  • 43
  • 1
  • 5
  • I looked for documentations: https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobstatistics2 and https://developers.google.com/resources/api-libraries/documentation/bigquery/v2/java/latest/com/google/api/services/bigquery/model/JobStatistics2.html . I believe, that you can use it in other way: BigQuery.model.JobStatistics2.getTotalSlotMs() instead of queryDetails.statistics.totalSlotMs. Let me know about the results. – aga Jan 03 '20 at 10:26
  • still its same the results are not showing up...in some cases its showing as NAN in most of the cases its showing as undefined. – sravan kumar Jan 03 '20 at 16:08
  • @sravankumar, about the execution time exceeded. There is a limitation of **6min/execution** using Google App Script, to avoid this issue you can set up a time driven trigger or run from stored data. You can have a look this answer for further clarification how it would work: _https://stackoverflow.com/questions/7854573/exceeded-maximum-execution-time-in-google-apps-script_ About the undefined values, I am working on it to find a reasonable cause and solution. – Alexandre Moraes Jan 07 '20 at 12:49
  • @Alexandre Moraes, thanks for looking into this... I can keep the time driven triggers, but in my case we never knew when the end user wants to load the data to the table. So, I can't keep the triggers in this case. Now i made changes to my SQL query. Hence the execution time is fine now. But need to look into the undefined values. In my view undefined is cmg when its pulling the results from the cache. Need to investigate more on this – sravan kumar Jan 09 '20 at 12:12

0 Answers0