Difficulty using Bigquery with a table linked to Google Sheets

Get Help
1

Hello, I have the latest version of Metabase (0.38.1), and have connected it to Google BigQuery as described in the documentation in an attempt to connect to information stored in a Google Sheet. It is able to see the table, but when I try to ask a question on Metabase, it throws up the error “Access Denied: BigQuery BigQuery: Permission denied while globbing file pattern.” In the Data Model section of Metabase I can see all the columns, etc, but it seems like it can’t access the actual data. From previous posts, its seems that this is to do with an authentication problem with Google (since version 0.36, when service accounts started to be used), whereby the service account has access to BigQuery and not Google Sheets, but maybe I am mistaken and it is a different problem. I would like to know if a way around this problem has been found yet, or alternatively, if there is any reasonably easy way to connect Metabase to Google Sheets/Smartsheet/Excel Online or any similar service, as this was my original goal.

Thanks in advance for any ideas/news on the status of that problem.

2

Hi @quentin95
You’re likely seeing this issue - see my comment for a possible workaround:
https://github.com/metabase/metabase/issues/13001 - upvote by clicking :+1: on the first post

3

Thanks, I’ll see if I can figure that out and let you know.

4

OK, so that worked as far as enabling the Oauth setting instead of Service Account, but it gives me the following error once I’ve created the Client ID, Client Secret, and Auth Code and try to save changes.
Unexpected character (’ ’ (code 160)): was expecting double-quote to start field name at [Source: (ByteArrayInputStream); line: 2, column: 4]

5

@quentin95 Something is telling me that there might have been something that broke something.
Make sure that the JSON object is valid - try using something like this to check it: https://jsonformatter.org/json-pretty-print

6

OK, thanks for that, jsonlint says its valid. I might stop wasting your time and see if I can use Zapier or something similar instead, to move the data from Google Sheets to an sql database I can then use metabase with.
Thanks again!