How to: Load Google Sheets or CSVs into BigQuery

This support article outlines the steps to two different methods for loading CSVs and Google Sheets into BigQuery. Once imported, you can view and query your uploaded data directly from the BigQuery console. Note that only users with Admin, Editor or Contributor permissions can create datasets and tables in PAD. If you aren't sure of your permission level, you can ask your organization's PAD administrator or go to groups.google.com to see which groups you are a member of. 

If you are looking for steps on how to move a number of flat files on a regular/automated basis, check out our Medium article showing how to utilize Google Workflows to move files from Drive to BigQuery here!

Loading your data as an external data source

By loading your data to PAD as an external data source, BigQuery will ensure the resulting table always reflects the data in the spreadsheet. So, for example, when a row is updated in the sheet, it also updates in the BigQuery table. This can be useful for getting data from organizers’ spreadsheets and forms into tables for analysts to use. 

Since your data is not being stored in BQ, there is no query cost associated with using this data. However, it can be slower than querying a native table since PAD first accesses the entire table before running the SQL query. In limited occasions, a query pulling from a Google Sheets source may result in a Resource error due to service overload. In these cases, the service overload is caused by resource utilization in the Google Sheet source; simplifying the Sheet by minimizing use of formulas is the best solution to resolve this issue.

Follow these steps to load your data correctly:

1. Share the Google Sheet with your "@cta-tech.app" drive account.   
2. From within your BigQuery Project, right-click on the three dots and select the “Create dataset”: 

3. Enter the Dataset ID (dataset name) and set the data location as pictured. Once complete, click “Create Dataset”:

4. Next to the new dataset, right-click the actions menu and select “Create table”:

5. To create the table:

1. Select “Drive” in the “Create Table From” drop-down
2. Paste in the source URL for the google sheet or CSV
3. Select the file format (CSV/Google Sheet/etc.)
4. Enter the table name
5. Select “Auto Detect” for the schema

6. In the Advanced options (Optional):

1. Identify the delimiter type if applicable
2. If the sheet includes a header row, clip the drop-down next to Advanced Options, and update rows to be skipped to 1. BQ will use the header row to label columns in the table

7. Select “Create Table” to complete

Loading your data directly to PAD as a native table

If you have a static dataset that you want to store and query in PAD, you’ll want to load your data directly into BigQuery. Follow these steps to upload the data correctly:

1. Download your CSV or Google Sheet directly to your computer.
   
2. From within your BigQuery Project, right-click on the three dots and select the “Create dataset”:
   

3. Enter the Dataset ID (dataset name) and set the data location as pictured. Once complete, click “Create Dataset”:

4. Next to the new dataset, right-click the actions menu and select “Create table”:

5. To create the table:

1.   Select “Upload” in the “Create Table From” drop-down
2.   Browse your files and select the file you want to upload
3.   Select the file format (CSV)
4.   Enter the table name
5.   Either select “Auto Detect” for the schema or manually add the schema fields for your table

6. In the Advance options (optional):

1. Identify the delimiter type if applicable
2. If the sheet includes a header row, clip the drop-down next to Advanced Options, and update rows to be skipped to 1. BQ will use the header row to label columns in the table

7. Select “Create Table” to complete

Loading Large CSVs as BigQuery tables

For large CSVs that exceeds the maximum size in BigQuery, you can leverage Google Cloud Storage (GCS) buckets for your work!

1. Create a Google Cloud Storage bucket. Google Cloud Storage buckets are containers for holding and organizing data. Google has step-by-step instructions on how to create a bucket here.
2. Upload your file to your newly-created GCS bucket
3. From within your BigQuery Project, right-click on the three dots and select the “Create dataset”: 

4. Enter the Dataset ID (dataset name) and set the data location as pictured. Once complete, click “Create Dataset”:

5. Next to the new dataset, right-click the actions menu and select “Create table”:

6. To create the table:

1. Select “Google Cloud Storage” in the “Create Table From” drop-down
2. Select “Browse” and select your file from the bucket directory
3. Select the file format (in this example, CSV)
4. Enter the table name
5. Select “Auto Detect” for the schema. Or, you can also specify field names and datatypes manually, either by entering each field in the provided boxes, or editing as text.

f. In the Advanced options (Optional):

• Identify the delimiter type if applicable
• If the sheet includes a header row, clip the drop-down next to Advanced Options, and update rows to be skipped to 1. BQ will use the header row to label columns in the table

g. Select “Create Table” to complete

7. Confirm that your new table appears in the appropriate project when the load job completes

If you need additional support or have questions, please contact [email protected].