Aperture link to Googlesheet thru BigQuery

George_Stephen

I am in Aperture Version 2.8.8.27
I am trying to get connected to Googlesheet thru BigQuery.
BigQuery is set up with link to Googlesheet and connection to BigQuery from Aperture seems to work ok.
External system Connection is OK, using Service Account with .jason Private Key File.

But when I try to add dataset in aperture table named ‘Response’ I am getting the following error message:

[ApertureDataStudio][GoogleBigQuery JDBC Driver][GoogleBigQuery]Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials. in statement [SELECT `Timestamp`,`ID`,`Site`,`Region`,`Division`,`Country`,`Date`,`Reporter`,`Place`,`Eventtitle`,`Description` FROM `Response`]

The service account email is granted access to the googlesheet as well as the table in BigQuery, but the above error is not going away.

Any clues anyone?

Henry Simms

Hi @George_Stephen , I have reproduced the same set-up as you, and get the same result when I try to add the sheet-backed BigQuery table as a Dataset:

Looking at the service account's logs in the GCP console, I see that this is a permissions issue in GCP, "BigQuery: Permission denied while getting Drive credentials".

Searching suggests that this is an error hit often by people trying to connect apps like Aperture Data Studio to a BigQuery table connected to a GSheet, but none of the solutions suggested in responses to these questions have worked for me. Switching the driver to use OAuth authentication rather than Service Account, and then defining Scope appropriately may work but I have not tested it

George_Stephen

@Henry Simms Thanks for trying.

What is next? Will it be taken up by Experian to find a fix?

Henry Simms

Hi @George_Stephen , the problem is within GCP - Providing the service account with access to the relevant Drive GSheet - so I think Google support should be able to advise on the permissions / access needed. I see similar questions from people looking to connect in this way using a variety of tools, but none of the suggested solutions worked for me:

• https://support.google.com/looker-studio/thread/178478960/data-studio-error-bigquery-permission-denied-while-getting-drive-credentials?hl=en
• https://cloud.google.com/bigquery/docs/troubleshoot-queries#drive_permission_denied

I suggested trying OAuth authentication as an alternative because it was suggested on several forums.