Access failure for BigQuery linked to Google Sheet

oliver.ng · February 25, 2021, 5:17pm

Hi there,

Issue

I am accessing a BigQuery table
The table is linked to a google sheet
When I query that table in retool, I am denied access to the table.
This issue does not exist when the BigQuery table is not linked to a google sheet

Question
Is this something I can solve in Retool or what setting I should make in BigQuery/ Google Sheet, please?

Thanks,
Oliver

vfolque · August 12, 2021, 7:51pm

Same problem here I've given access the service account to my spreadsheet but it didn't solve anything. Here the same issue but with Tableau asked in Stackoverflow:

Chris-Thompson · August 19, 2021, 5:13pm

Hey @oliver.ng and @vfolque!

It sounds like our engineers were able to track this bug down and we will keep this thread updated as a fix is merged! :)

vfolque · September 15, 2021, 2:56pm

Hi @Chris-Thompson !

Has there been any progress?

~~Thanks~~

jordanbrooks · July 6, 2022, 3:38am

I'm having the same issue. Was there ever a resolution?

Chris-Thompson · July 6, 2022, 7:49pm

Hey @jordanbrooks — thanks for the ping on this, it seems like this is still not yet supported in Retool but I will bump this feature request internally and update this thread as soon as a feature is added

rally-tech · September 18, 2022, 7:01pm

@Chris-Thompson et. al. Was this issue ever resolved or is current guidance to copy Drive-backed tables to native tables?

Pierre-at-Echo · October 21, 2022, 8:50am

Hi @Chris-Thompson, is there any update on this topic ?

I believe it can be fixed by adding an additional scope ("https://www.googleapis.com/auth/drive") when making the query to BigQuery but there may be additional requirements that I'm not aware of.

Thanks in advance for your reply.

smarj · March 13, 2023, 4:00pm

Hi @Chris-Thompson
I am also running into this problem - I've been to Google Support and they advise that it's an issue (as Pierre says above) of the Oauth scope being added per this support article
Thank you!

Tess · March 15, 2023, 6:18pm

Hi @smarj Thanks for checking in! Unfortunately, we haven't shipped the fix for this bug yet

Emma_Jerzyk · March 20, 2023, 6:16pm

I am also having this issue and would like to be notified when the fix for this bug gets shipped!

Tess · March 20, 2023, 8:58pm

Thanks, we'll keep this thread updated as we hear from our team internally

Emma_Jerzyk · March 23, 2023, 3:37am

BTW I found a workaround that worked for me, so I thought I would share here. In BQ, you can set up a scheduled query that outputs to a BQ table. For the BQ table that I wanted to use in Retool, which I couldn't use before because it's linked to a Google Sheet, I just created another table that serves as an extract of the original sheet, and I have a scheduled query that updates it from the Google sheet once a day (though you can schedule it to be more frequent if you want). I'm able to access the extracted sheet via Retool.

Tess · March 23, 2023, 3:41am

Thank you for sharing, @Emma_Jerzyk!