Write bigQuery table in google sheets via appScript.

[caioricciuti]

Expected Behavior

Write bigQuery table in google sheets via appScript.
Sample URL: https://developers.google.com/apps-script/advanced/bigquery || https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs

Description: I need to get some data from my bigquery table and send it to google sheets. I'm using the script available on google docs, but it's throwing errors.

Actual Behavior

After running the script, I get the following:

GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Not found: Job myprojectId:job_byZRvvg6HM6AYCPPsumGNXmlgDp9D

Steps to Reproduce the Problem

1. Try to query a partitioned table with the following script:

function runQuery() {
  var projectId = 'myprojectId';

 var request = {
    query: 'SELECT * FROM `myprojectId.myDataset.mytable`',
      location: 'europe-west3',
      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);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var spreadsheet = SpreadsheetApp.create('BiqQuery Results');
    var sheet = spreadsheet.getActiveSheet();

    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // 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;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

[caioricciuti]

Hello,
I believe the problem is that the script is looking for the jobId without considering the location.
The following error message : Not found: Job myprojectId:job_byZRvvg6HM6AYCPPsumGNXmlgDp9D, shows it.

As soon I run the script I can see on my project job History the following:

Query succeeded:

jobId: myprojectId:europe-west3.job_byZRvvg6HM6AYCPPsumGNXmlgDp9D

The job runs successfully, the problem is this script is looking in the wrong place.

[caioricciuti]

Got it, I've created a variable named location, and pass it on:

 queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
   pageToken: queryResults.pageToken,
   location: location
 });