Big query error on Power BI

[HL78]

Hello, 

I'm trying to retrieve some Big query data on Power BI. Everything was well working until I added a table in my SQL request. It's a table created recently, and this data provides from an google sheet file.

I have this error message : "DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (100) Error interacting with REST API: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials."

That's weird, because the service account has the Viewer access on the sheet file, and the service accoutn IDs on Power BI seem to be correct.

Do you have any idea?

Thanks in advance.

[ms4446]

The error message indicates that the service account used by Power BI does not have the necessary permissions to access the Google Drive file. Even if the service account has Viewer access to the Google Sheet, there might be other permissions or configurations missing.

To resolve this issue:

1. Ensure that the service account has been given Viewer (or higher) access directly to the Google Sheet in question.
2. In the Google Cloud Console, navigate to the IAM & Admin page.
   • Click the Add button.
   • In the New principals field, enter the email address of the service account.
   • In the Roles field, select an appropriate role that grants the necessary BigQuery permissions. If unsure, BigQuery Data Viewer might be a good starting point.
   • Click the Save button.

If you've ensured the service account has the necessary permissions and are still facing issues:

• Double-check the service account ID you're using in Power BI.
• Consider re-authenticating or setting up the connection again in Power BI.
• If the issue persists, you might want to consult Google Cloud's documentation 

[andrewbox]

I don't suppose there's anything else to try on this?  I've got the same issue - trying to access a bigquery view from power BI, and the view is derived from an external table which is a google sheet.   And I get this Denied: BigQuery BigQuery: Permission denied while getting Drive credentials error.

I have confirmed the sheet is definitely shared with the identity user I'm using to connect in power BI. 

I have confirmed the identity user has bigquery roles, all the way up to trying bigquery admin, the user is also owner on the gcp project. 

I really don't understand what would be missing, the docs all just say "make sure the sheet is shared" but that doesn't work.  

[Nandha100]

Hi @ms4446 ,

i am also had this issue once, i just followed the below steps that works for me. You can try this one

1. Ensure BigQuery Has Permission to Access Google Drive
BigQuery needs permission to read from your Google Drive, where the Google Sheet is stored. To enable this:

Grant access to BigQuery:
Go to your Google Cloud Console.
Navigate to IAM & Admin > IAM.
Find the service account associated with your BigQuery project (typically it will look something like bigquery@project-id.iam.gserviceaccount.com).
Ensure this service account has the roles/drive.file or roles/drive.readonly permissions for the project or folder containing the Google Sheet.

2. Enable the Google Sheets API
To allow BigQuery to read Google Sheets, ensure that the Google Sheets API is enabled in your Google Cloud project: Open the Google Cloud Console.
Go to APIs & Services > Library.
Search for Google Sheets API and enable it.

3. Share the Google Sheet with the BigQuery Service Account
If the Google Sheet is private or restricted, you need to explicitly share it with the BigQuery service account.

Open your Google Sheet.
Click the Share button.
Add the email address of the BigQuery service account (e.g., bigquery@project-id.iam.gserviceaccount.com) with at least "Viewer" permissions.

4. Ensure Correct Permissions in Google Cloud Console
If the issue persists, you might need to verify that you have the proper permissions in Google Cloud to link BigQuery with Google Drive:

Go to IAM & Admin > IAM.
Check that the account you're using has the necessary roles, such as BigQuery Data Viewer or BigQuery Data Editor, as well as Drive File or Drive Viewer permissions.