API call to bigquery.jobs.query failed with error: Access Denied: Project ppc-big-query: User does not have bigquery.jobs.create permission

user_14993721 0
Viewed 712

I created a Google Sheet that extracts data from Big Query via a sidebar. I am able to get the data when I run the function via a custom menu or if I execute the function from the Editor. The function simply queries a table in BQ and outputs the data in the sheet.

However, I'm getting an error when I try to run the function via a submit button in the sidebar. I am using google.script.run in the HTML file to execute the Apps Script function. Here's the error that I'm getting:

Error: GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Access Denied: Project xxx: User does not have bigquery.jobs.create permission in project xxx.

Again, I am able to access BQ from a custom menu or from the Editor which means I have access to BQ, I believe. So I am not sure how to resolve this kind of error. I have been trying to rummage the internet for a solution but to no avail. I hope someone can help. Thanks in advance!

google-bigquery google-apps-script html
1 Answers

This error tells you that you are missing a role that is required by Apps Script.

How to resolve this issue:

You need to tell the administrator to Edit your existing role in Google Cloud IAM and add the bigquery.jobs.create. There are other roles that you can add such as bigquery.jobUser and bigquery.user, these two roles have bigquery.jobs.create and other roles.

Why does this happen?

There are different BigQuery roles, maybe you can see the data and you probably have the Bigquery DataViewer role or the BigQuery Editor role, these two roles allow you to read data of your project but they does not have the specific role that Apps Script needs to use.

You can see here all the BigQuery roles.

user_169293580
Related