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:
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! :)
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
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.
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!
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.