Logs for Connected Sheets are not recorded in BigQuery INFORMATION_SCHEMA

[tagasyksk]

I have the following questions regarding Connected Sheets and BigQuery:

•When connecting Google Sheets to BigQuery using Connected Sheets, I noticed that some job histories are not recorded.

•While some jobs do appear in the history, there are inconsistencies, and the differences are unclear.

•Additionally, the processed bytes displayed in Connected Sheets often show as “0B,” which makes me suspect this might be related.

Could you clarify why this might be happening?

[mars124]

When/how do you expect a record in job histories?
Why is this important to you?

[tagasyksk]

---

@mars124 wrote:

When/how do you expect a record in job histories?

---

I expect that when the table on the spreadsheet is updated via the schedule or refresh button on the Connected Sheets side, the table access will be logged in either INFORMATION_SCHEMA or Cloud Logging.

This is because I want to identify which spreadsheet is accessing the BigQuery table.

[mars124]

From https://support.google.com/docs/answer/9702507:
When you access BigQuery data in Connected Sheets, entries are recorded in Cloud Audit Logs. The logs show who accessed the data and when. Only people with proper authorization can access log records. 

Access to these "special" logs are usually disabled by default, even for project owners. Try enabling the "Private Logs Viewer" role for access to all logs in the _Required and _Default buckets, including data access logs. (https://cloud.google.com/logging/docs/access-control)

[marckevin]

Hi @tagasyksk,

Welcome to Google Cloud Community!

In addition to @mars124, you can also explore this documentation on different methods on how to check log events on Connected Sheets, including Drive log events and Cloud Audit Logs for BigQuery.

Instead of INFORMATION_SCHEMA in BigQuery, I suggest using Cloud Audit Logs as it provides more complete and detailed logs with proper documentation on Connected Sheets. This documentation includes instruction and best practices on how to analyze Cloud Audit Logs using the Logs Explorer. Just ensure you have a proper role/permission to use the Logs Explorer and it is properly configured.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.