BigQuery - script-run queries on GSheet tables don't work

Question

A number of my BQ-tables take their data from GSheets. However, once I run queries on these tables from Apps Script I get this error:

Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found. 

The error refers to this code line:

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

From my BigQuery interface (Query History) I can see the unsuccessful request, and when I rerun it from there I get results and everything is ok.

Any idea how to make the query run with the script?

Thanks

Answer 1

As was pointed out by Google support in https://issuetracker.google.com/issues/65654399, the recommended way to ensure the required scope is present is adding the following to the code:

// Forces the script to require
// https://www.googleapis.com/auth/drive.readonly scope.
// DriveApp.getFiles();

To emphasize, the DriveApp.getFiles(); call can be put in the comment.

Answer 2

In line with the encountered error, try to enable advanced services before using BigQuery Service.

Also, as mentioned in Advanced Google Services,

Apps Script handles the authorization flow automatically but, are not enabled by default.

See Big Query And Google Spreadsheet Integration for additional insights.

Hope that helps.