I like using Google Sheets as an external data source in BigQuery. It is strange in some ways, but has its benefits:
-
You can use Gsheet as manual input from end users.
-
You can use Gsheet as a temporary solution to cover the need for data (in my case I used the daily exchange rate that I pulled from Google Finance and used in BigQuery calculations).
-
Or you can use it as additional data to be combined with the rest of the data. You can have a version of a business plan where you take the target values to compare with the actual metrics.
What I want to dive into, is an error that I got for different projects within the last weeks, and that I couldn’t find an answer to quickly.
The solution is quite odd, but simple and effective. All you need to do is add any Google Sheet as a direct data source to your Looker Studio report. This will trigger an authorization process, and once it is connected, all other problems with querying tables, connected to Google Sheets disappear. You have to do it once, and then it will take effect on the user account level, so other Looker Studio reports will start working as well.
