Personal Account - BigQuery: Permission denied while getting Drive credentials

Google Cloud Data Analytics
1

I am the owner of the project I am working in. I have a table linked to an external gogole sheet which has been granted view access to my personal google account (the onwer of the project) and the default dataform service account.

In BigQuery console I can query the table linked ot the Google Sheet without problem. I can also query it by clicking on the RUN button on dataform and see the result of the query.

However, when I execute the query as an action, I get a Drive access permission error only when I execute it as my personal google account (the owner fo the project). When I execute it as the default dataform service account it works well.

This is the test query I am using, which returns the error “Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.” when I use my personal account to execute the action in dataform:

-- config {

--   type: "table"

-- } 

SELECT *

FROM `my_project.my_dataset.my_google_sheet_table\`

Any ideas about what is going on? Both accounts have read access tot he google sheet and my perosnal google account is the owner of the project.

Best

2

Hi @Miguel_SantĂ­n,

Have you checked if your personal user account has the same roles as the default service account? Alternatively, you could try the roles suggested in the documentation, as they may help resolve your issue.

3

Matches my experience: always got errors when trying to execute with a personal account but works with a service account (any service account that is allowed to access the gsheet). Unexpected but not a big deal so we just documented it.

4

What’s happening is that BigQuery actions in Dataform run using your credentials differently than manual queries. Even though your personal account owns the project and has view access to the Sheet, executing an action requires BigQuery to access Drive on behalf of your account, which needs the https://www.googleapis.com/auth/drive OAuth scope enabled for that user in Dataform. The default Dataform service account works because it already has proper Drive access configured. To fix this, you need to either grant your personal account the proper Drive OAuth scope in Dataform or continue running the action using the service account, which has the required Drive access.

1 Like