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!

Tess · April 24, 2023, 6:07pm

We should have a fix for this bug on Cloud on Wednesday It'll ship to on-premise in v2.118.1

Hiroaki_KARASAWA · May 8, 2023, 6:43am

@Tess Hi, I'm still having the same problem on Cloud. Even though the version of my environment is v2.118.2-a622a2c (Build 87223). I can provide additional info if you need it.

Tess · May 8, 2023, 6:11pm

Hi @Hiroaki_KARASAWA Interesting! I'm sorry to hear the issue is still persisting. I'm looking into it internally. Thanks for flagging!

Tess · May 8, 2023, 6:32pm

I'm not able to reproduce this error on cloud anymore. Could you share your resource set up? Or give me written permission to log in to your account to take a closer look?

Hiroaki_KARASAWA · May 10, 2023, 7:32am

Hi @Tess Thank you for checking. Here is my environment:

authenticated by "service account" not "OAuth"
the service account email is added to the shared list of the Google Sheet as a "Viewer" role
the service account has "BigQuery Admin" role on Google Cloud IAM

I think my service account is configured correctly. I judged it because this service account is also used in Redash, and it can show the correct data without any errors.

The query is available here:

https://dinii.retool.com/querylibrary/636864/invalid_query

I can invite you to my team space if you provide me with your email.

Thanks,

Tess · May 19, 2023, 10:39pm

Hi @Hiroaki_

Hmm, I was looking at the suggestions here & you might need `` around the name. I'm also curious if it needs the project in the query. It looks like this is the database & table