Easily Modify Data in BigQuery Tables w/ Google Sheets

When working with BigQuery, you often need to modify table data during the development and testing stages. While SQL statements can achieve this, they can be cumbersome when dealing with tables with many columns and manageable number of rows.

You can use Google Sheets to modify data for development purposes

1- Export Data to Sheets: Begin by exporting your BigQuery table data to Google Sheets:

• Navigate to your BigQuery table in the Cloud Console.
• Click Export > Explore with Sheets.
• A connected sheet containing your table data will open.

This will open up a new Google Sheets window with a connected tab to BigQuery

However this sheet is not editable as it is a connected one.

2- Creating an Editable Copy: To freely edit the data:

• Add a new tab in Google Sheet.
• Copy the entire dataset from the connected sheet (Ctrl/Cmd + A -> C).
• Paste the data into the new sheet (Ctrl/Cmd + V).

3- Edit this sheet as you would like;

Now you need to load data from this sheet.

4- Download the modified sheet as a CSV file.

Data Import with Cloud Shell and the bq Command

It is easier to use Google Cloud Shell and bq command to achieve this.

1. Activate Cloud Shell: Access Google Cloud Shell by clicking the icon in the top-right corner of the Cloud Console.
2. Upload the CSV : For small data, you can directly upload the CSV to Cloud Shell. For larger files, consider uploading to Google Cloud Storage for improved performance.

Activate Google Cloud Shell on top right

Load via bq Command: Execute the following command in Cloud Shell, replacing placeholders with your actual values:

bq load \
  --source_format=CSV \
  --autodetect \
  --replace=true \
  dataset_id.table_name \
  your_sheet.csv

Example:
bq load \
  --source_format=CSV \
  --autodetect \
  --replace=true \
  dev_cur_bq_0.customers \
 'test1 - Sheet2.csv'

And that is it.

If you have frequent needs, 3rd party applications like DataGrip or DBeaver may also be helpful for achieving the same.

Let me know of you have other ways for manually modifying BQ data.