A lot of business and marketing data is stored in Google Sheets or sent from various 3rd party tools to Google Sheets. However, for this data to be stored for the long term you might need to transfer it into BigQuery. Having this data along with other data, such as GA4 data, in BigQuery, you will be able to combine the various data sources.
In this post, I will show you how to transfer the data from Google Sheets into BigQuery.
Here are the steps
1. Prepare your Google Sheet:
- Ensure your data in Google Sheets is structured, i.e., the first row contains the column headers, and each subsequent row contains the corresponding data.
- Make sure your Google Sheet is publicly accessible or shared with the service account you're using for BigQuery.
2. Navigate to the BigQuery Console:
- Go to the BigQuery Console: https://console.cloud.google.com/bigquery
- Choose or create the dataset where you want to store your table.
3. Create a Table:
- Click on "Create Table" in the dataset you've chosen.
- For the source, choose "Google Drive" and then "Google Sheets".
- Click on "Browse" and pick your Google Sheet file.
- Specify the table name and other configurations as necessary.
4. Configure Schema:
- You can either let BigQuery auto-detect the schema or specify it manually.
- If you're letting BigQuery auto-detect, ensure the data in your Google Sheets is clean and consistent, as inconsistencies might cause errors.
5. Set Google Sheets Options:
- Specify the number of header rows (typically 1 if you have one row of column names).
- If your Google Sheet has multiple sheets, specify the sheet name you want to import.
6. Advanced (optional):
- Set partition and clustering settings if you know you'll be dealing with large data sets and need to optimize for querying. This isn't necessary for smaller datasets.
7. Create Table:
- Once everything looks good, click the "Create Table" button. BigQuery will now load the data from your Google Sheet into a new table.
8. Verify Data:
- After the table has been created, run a simple
SELECTquery to ensure your data has been imported correctly.
9. Scheduled Refresh (optional):
- If you wish to refresh your BigQuery table data periodically based on updates in the Google Sheet, you'll need to set up a scheduled query.
- Go to the "Scheduled queries" option in the BigQuery Console.
- Set your SQL query, destination table, and refresh schedule.
- Note: This will overwrite the table with the refreshed data from Google Sheets based on the schedule you set.