My BigQuery stories – Finding Google Sheets(ID) linked with BigQuery table

By Bihag Kashikar | @intelia | November 6

Finding Google Sheets(ID) linked with BigQuery table

Google Cloud BigQuery and Google Sheets provide native integration to do basic data exploration — both ways — i.e. Google Sheets to BigQuery using BigQuery sheets connector, and BigQuery to Google Sheets using BigQuery external tables.

Recently in one of my projects, the use of Google Sheets to BigQuery with BigQuery sheets connector was heavily used by the users and its usage grew to the point where we were not able to find out which Sheets / Sheet IDs are referenced with which table in BigQuery as there is no visual lineage to find out within Google Sheets or in underlying table.

To find out the lineage, I learned there are two options other than using Python SDKs…

Option 1:

Find all “connected sheets” — first step can be to query BigQuery information schema — see below query, the job_id and labels give away the connected sheets queries run on BigQuery. The data registered in the information_schema for all queries initiated from Google Sheets contains job_id value as ‘sheets_dataconnector_%’.

 

 

Below is the output:

 

Option 2: Manual one sheet at a time

Step 1: You can pick the sheetId from the open google sheet.

 

Step 2: and query the Cloud logging — you should be able to find docId as highlighted. Expanding the logs, it gives away the linked table name.