I have some external tables created in BigQuery using Google Spreadsheets as sources, they are fairly small, not more than 100 rows each, created for the purpose of making Row Level access control on Looker Studio data. These tables have been working correctly for the last 3 months and today they are working erratically. Whenever I try to query one of these external tables from the BigQuery console the following error pops up:

Error while reading table: #########, error message: Failed to read the spreadsheet.
Error message: API key not valid. Please pass a valid API key. File: ########

Replaced my File ID and table names with #.
The strangest thing is that while running the same query back to back multiple times, 7 out of 10 times, the query works, and the other 3 times it shows the above error. I have not made any changes to the spreadsheets’ sharing configurations.

The charts on Looker Studio that were referencing these external tables are also not working anymore.

Has anyone experienced this before? Any insights/workarounds or permanent fixes for this?

I tried authenticating Big Query to enable drive access on the gcloud console as shown here. Before trying this no matter how many times I queried the tables, it wouldn’t work. After trying this, it now works 70% of the time but I can’t have it failing now and then.