如何每日将Google Sheet数据追加至BigQuery实现归档存储？

Got it, let's work through this problem—this is a super common need when your source Google Sheet gets overwritten daily but you want to keep a historical trail in BigQuery. Here are a couple of solid, doable approaches using GCP tools you already have access to:

1. BigQuery Scheduled Queries (No-Code, Easiest Option) #

This is the simplest path if you don't need any data preprocessing before archiving:

• First, set up two BigQuery tables:
  • A live sync table: This is the one you already have connected to your Google Sheet (it updates automatically as the sheet changes)
  • An archive table: Create this manually with the exact same schema as your live table, plus an extra archive_date column (date type) to track when each batch was saved
• Create the scheduled query:
  1. Open the BigQuery console, click "Create query"
  2. Write an INSERT statement that pulls the latest data from your live table and appends it to the archive table, with the current date as a marker:

     INSERT INTO `your-project.your-dataset.archive_table`
     SELECT *, CURRENT_DATE() AS archive_date
     FROM `your-project.your-dataset.live_sheet_sync_table`
     

  3. Click the "Schedule" button at the top of the query editor, set the schedule to run after you update your Google Sheet each day (e.g., 1 AM daily, to ensure the sheet's new data is fully synced)
  4. Save the schedule—BigQuery will automatically run this every day and append the latest sheet data to your archive table

Pro Tip for Avoiding Duplicates #

If you're worried the scheduled query might run twice accidentally, add a check to skip inserting if the day's data already exists:

INSERT INTO `your-project.your-dataset.archive_table`
SELECT *, CURRENT_DATE() AS archive_date
FROM `your-project.your-dataset.live_sheet_sync_table`
WHERE NOT EXISTS (
  SELECT 1 FROM `your-project.your-dataset.archive_table`
  WHERE archive_date = CURRENT_DATE()
)

2. Cloud Functions + APIs (For Custom Logic) #

If you need to clean, transform, or validate the sheet data before archiving, this flexible option works better:

• Steps to implement:
  1. In the Google Cloud Console, create a Cloud Function (use Python or Node.js—whichever you're comfortable with)
  2. Enable the Google Sheets API and BigQuery API in your project, then grant the Cloud Function's service account permissions to:
     • Read data from your Google Sheet
     • Write data to your BigQuery archive table
  3. Write the function code:
     • Use the Sheets API to fetch the latest data from your sheet
     • Add the archive_date field to each row
     • Use the BigQuery client library to append the processed data to your archive table
  4. Set up a Cloud Scheduler trigger to run the function daily, right after your sheet is updated

This approach lets you handle edge cases like missing values, data type fixes, or filtering specific rows before archiving.

Critical Notes to Remember #

• Never skip the archive_date field: Without this, you'll have no way to distinguish which data came from which day
• Test first: Run your insert query or function manually once to make sure it works before setting up the daily schedule
• Check permissions: Make sure the service account running the scheduled query/Cloud Function has the right access (you can test this by running the action with the account's credentials)

内容的提问来源于stack exchange，提问作者Evgeniy