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

Have a look here: https://stackoverflow.com/questions/40731823/encountered-an-error-while-globbing-file-pattern-error-when-using-bigquery-api — Graham Polley, Sep 08 '17 at 07:57
@Ilja Have you tried Graham Polley's solution? This error does mean that you are improperly authenticated. If you're still having trouble with this you might want to show us how you are performing the authentication. — Yannick MG, Sep 18 '17 at 14:22
Yes, I tried it did not work. I ended up here: https://issuetracker.google.com/issues/65654399 — Ilja, Sep 18 '17 at 15:17

score 4 · Accepted Answer · answered Sep 25 '17 at 15:58

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.

score 1 · Answer 2 · answered Sep 08 '17 at 16:23

1

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

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

Hope that helps.

answered Sep 08 '17 at 16:23

Teyam

BigQuery services were enabled and scripts work just fine as long as no GSheets-BQ-tables involved – Ilja Sep 10 '17 at 04:40

2 Answers2