"Permission denied while getting Drive credentials" error occurs when accessing GoogleSheet linked BigQuery table from Google Apps Script

0
votes

Below was my original question, but I have tried several queries, and finally got closer to fixing the problem.

Following the tutorial, I have managed to access the BigQuery from Google Apps Script, but it gives "Permission denied while getting Drive credentials" error when the query contains GoogleSheet linked table.

As BigQuery gives an option to create the table linked with GoogleSheet, I have several tables in this setting.

A query without linked table works, so the basic setting for the code is fine, and I believe the credentials setting is in BigQuery.
I do have the access to the GoogleSheet which is linked in BigQuery.

If anyone has an idea how to fix this, I would very much appreciate it.




I am trying to access BigQuery from Google Apps Scripts, and going through the tutorial in the link below.
https://developers.google.com/apps-script/advanced/bigquery#run_query

I have managed to run script in the tutorial, so moved on to access my own dataset where I get the error as below

GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

For python, there is samples where you can add the local file like service_account.json which has all the credential data. GAS has appsscript.json, but this seems to be in different format.

Is there anywhere else I can set the credentials?

BigQuery and Google Apps Script is owned by the same google user, so the access from chrome browser is granted.

1
google-apps-scriptgoogle-bigquery
have you updated the project ID in the code ? - Karan
@Karan yes. I have amended the question. The error is somewhere around google sheet linked table, but don't know where the next step is. - ysd

1 Answers

1
votes

Got the same issue with my Google Apps script. Steps to fix:

  1. Enable Google Drive API under 'Services'

  2. Add DriveApp.getRootFolder(); in the beginning of your project/function to get Drive Auth Scope.

Should work!