访自治区政协委员、固原市润泽粮油公司总经理王黎君

Question

Everyday I get some data into a Google Sheet, the data from today overwrites the data from yesterday.

I created a BigQuery table based on this Google Sheet - initial connection works and the data from the sheet can be correctly queried in BigQuery.

Q: Tomorrow the data in Google Sheet will be overwritten. How can I append everyday data from Google Sheet to BigQuery so, that I have in BigQuery data from everyday consecutively, like archived?

I didn't found any tutorial for such task - everything I found is about connecting Google Sheets to BigQuery and getting data from one to another, but not about a kind of archiving.

Answer 1

If you are using an external table to query the Google Sheet, the BigQuery not store the table, so the data will reflect the current state of the sheet.

An option is create a native BigQuery table to store the data and use the BigQuery Scheduled Queries to run a query that get the data from the external table and insert into the native one. Something like this:

INSERT INTO `project.dataset.native_table`
SELECT * FROM `project.dataset.sheets_external_table`

Answer 2

As often, immediately after I post a question I get an answer by myself.

The trick is the workflow:

1. Create a BigQuery table with the datasource Google Drive document (URL of Google Sheet),
2. After the schema is recognized, the today data from Google Sheet is visible in BigQuery,
3. Run a query to display all of your daily data, like SELECT * FROM 'myproject.mydataset.mytable' LIMIT 10,
4. And now comes the trick! Save the query result in another BigQuery table, which will be your archive,
5. Create a scheduled query - while creating it you'll get an option of overwriting or appending data. We select for sure the appending and we are done.