Search code examples
google-sheetsgoogle-bigquery

How to know Sheet ID that using BigQuery Sheet Connector

Currently there is many user in my place that analyze BigQuery data with using feature connected sheet. For security reason, I wonder if i can get all spreadsheet ID that using my data from BigQuery. Is there any ways to do that?

In this article, it says that I can find spreadsheet ID in the audit log. But I can't find it.

Solution

  • Option 1: You can pick the sheetId from the open google sheet

    enter image description here

    and query the cloud logging - you should be able to find docId as highlighted.

    enter image description here

    However, this is manual one sheet at a time, other option can be below - to 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.

    SELECT job_id, lbl.key,lbl.value,creation_time
FROM `region-australia-southeast1`.INFORMATION_SCHEMA.JOBS, unnest(labels) as lbl
WHERE job_id like 'sheets_dataconnector_%'
order by creation_time desc --2023-07-12 10:22:26.558000 UTC

    See screenshot:

    enter image description here

    Step 2: Use the JOB_IDs and enter in CLoud logging dashboard as above, this should give you all docIds

    Hope this helps