GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Access Den

[syde23]

I'm trying to get data out of bigQuery and into Google Sheet through AppScript.
This works perfectly as long as the table I try to access is not generated from a Google Sheet. If so, I get the following: GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Access Den

I've added the service account as a viewer into both Google Sheet but it is not working unfortunately. Any clue?

[Poala_Tenorio]

Since the error message is  "Access Denied", it suggests that there might be an issue with the permissions.

Even though you've added the service account as a viewer to the Google Sheets, make sure that the Google Sheets itself isn't restricted further. Ensure that the sharing settings of the Google Sheet allow access to the service account, also that the service account has the "BigQuery Data Viewer" role at the project or dataset level.

You can also add the `bigquery.jobs.create`. Other roles can also be added such as `bigquery.jobUser` and `bigquery.user`, these two roles have `bigquery.jobs.create` and other roles.

Sometimes, access issues can arise if your BigQuery project has billing issues. Make sure that your billing account is active and there are no issues with billing.

[Snoshone07]

Hi @syde23 this issue is likely related to BigQuery’s permissions for external tables created from Google Sheets. Even if the service account has viewer access, external tables require additional permissions since they reference a Google Drive file.

Possible Solutions:

• Grant Explicit Access to the Google Sheet
  Ensure the service account has at least Viewer access to both BigQuery and the Google Sheet itself. You can do this by going to Share → Add the service account email.

• Use a Scheduled Query to Create a Native Table
  Instead of querying the external table directly, schedule a query in BigQuery to store the data in a native table. This helps avoid permission issues.

• Consider an Alternative ETL Approach
  If you’re looking for a more seamless way to sync Google Sheets with BigQuery, Windsor.ai offers a BigQuery-to-Sheets connector that simplifies the process without dealing with service account permissions

Hope this helps!