Google Sheets to BigQuery: 3 Ways to Connect & Migrate Data

As your company grows and starts generating terabytes of complex data, and you have data stored in different sources. That’s when you have to incorporate a data warehouse like BigQuery into your data architecture for migrating data from Google Sheets to BigQuery. Sieving through terabytes of data on sheets is quite a monotonous endeavor and places a ceiling on what is achievable when it comes to data analysis.

At this juncture, incorporating a data warehouse like BigQuery becomes a necessity. In this blog post, we will cover extensively how to move data from Google Sheets to BigQuery.

Overview of Google Sheets

It’s a cloud-based spreadsheet application offering a flexible and user-friendly platform for data organization, analysis, and collaboration. Users can access it from any device connected to the Internet, enabling real-time collaboration and many other useful features.

Key Features:

1. Real-Time Collaboration: It allows many users to work on the same spreadsheet, and all changes are saved automatically. The changes show up in real-time.
2. Accessibility and Convenience: It is accessible through any web browser or mobile Google Sheets app. It also supports offline editing and automatically synchronizes changes once back online.
3. Data Management and Analysis: It offers extensive support for functions and formulas used in calculations and data analysis, together with tools for creating various charts and graphs that make it easy for its users to analyze data.
4. Integration and Automation: It offers a variety of add-ons to extend functionality, including third-party integrations.

Overview of BigQuery

Google BigQuery is a fully managed serverless data warehouse offered by Google Cloud for running super-fast SQL queries, harnessing the power of Google’s infrastructure. It’s designed for large-scale data analytics, helping businesses and organizations analyze huge datasets quickly and effectively.

Key Features:

1. Serverless Architecture: BigQuery manages servers and storage in the background, so a user does not need to.
2. High Scalability: It scales seamlessly to handle petabytes of data.
3. SQL Compatibility: It supports ANSI SQL, which is useful for people who already know SQL and want to write and run queries. This also allows a user to combine various BI tools for data visualization.
4. Machine Learning: BigQuery ML allows users to train and run machine learning models in BigQuery using only SQL syntax.

Why Migrate from Google Sheets to BigQuery?

• Automation and Real-Time Data: BigQuery allows seamless integration with other Google Cloud services for automated, real-time data ingestion and reporting, which is more manual in Google Sheets.
• Scalability: Google Sheets is limited in handling large datasets, while BigQuery can manage and analyze petabytes of data, making it ideal for growing data needs.
• Performance: BigQuery offers faster query execution and analytics on large datasets compared to Google Sheets, which slows down with larger data volumes.
• Advanced Analytics: BigQuery supports SQL-based queries and integrates with machine learning tools, enabling more advanced data analysis than what’s possible in Google Sheets.

Methods to Connect Google Sheets to BigQuery

Prerequisites:

1. Google Cloud account.
2.  Business, Enterprise, or Education G Suite BigQuery accounts.
3. Google Cloud project with billing enabled.
4. Necessary IAM permissions. 

Method 1: Using Hevo to Move Data from Google Sheets to BigQuery

Hevo is the only real-time ELT No-code data pipeline platform that cost-effectively automates data pipelines that are flexible to your needs.

Using a fully managed platform like Hevo you bypass all the aforementioned complexities and (supports as a free data source) import Google Sheet to BigQuery in just a few mins. You can achieve this in 2 simple steps:

• Step 1: Configure Google Sheets as a source, by entering the Pipeline Name and the spreadsheet you wish to replicate.

• Step 2: Connect to your BigQuery account and start moving your data from Google Sheets to BigQuery by providing the project ID, dataset ID, Data Warehouse name, and GCS bucket.

For more details, Check out:

• Google Sheets Source Connector
• BigQuery Destinations Connector

Method 2: Using BigQuery Web Console to Move Data from Google Sheets to BigQuery

You can easily upload using BigQuery’s data connector. The steps below illustrate how:

• Step 1: Log in to your GCP console and Navigate to the BigQuery UI using the hamburger menu.

• Step 2: Inside BigQuery, select ‘Create Dataset’.

• Step 3: After creating the dataset, next up we create a BigQuery table that will contain our incoming data from sheets. To create  BigQuery table from Google Sheet, click on ‘Create a table.’ In the ‘create a table‘ tab, select Drive.

• Step 4: Under the source window, choose Google Drive as your source and populate the Select Drive URL tab with the URL from your Google Sheet. You can select either CSV or Sheets as the format. Both formats allow you to select the auto-detect schema. You could also specify the column names and data types. 
• Step 5: Fill in the table name and select ‘Create a table.’ With your Google Sheets linked to your Google BigQuery, you can always commit changes to your sheet and it will automatically appear in Google BigQuery.

• Step 6: Now that we have data in BigQuery, we can perform SQL queries on our ingested data. The following image shows a short query we performed on the data in BigQuery.

Method 3: Using Sheets Connector to Move Data from Google Sheets to BigQuery

This method to upload Google Sheets to BigQuer is only available for Business, Enterprise, or Education G Suite accounts. This method allows you to save your SQL queries directly into your Google Sheets. Steps to using the Sheet’s data connector are highlighted below with the help of a public dataset:

• Step 1: For starters, open or create a Google Sheets spreadsheet.
• Step 2: Next, click on Data > Data Connectors > Connect to BigQuery.
• Step 3: Click Get Connected and select a Google Cloud project with billing enabled.
• Step 4: Next, click on Public Datasets. Type Chicago in the search box, and then select the Chicago_taxi_trips dataset. From this dataset, choose the taxi_trips table and then click on the Connect button to finish this step.

This is what your Google Sheets spreadsheet will look like:

You can now use this spreadsheet to create formulas, charts, and pivot tables using various Google Sheets techniques.

Managing Access and Controlling Share Settings

It is pertinent that your data is protected across both Sheet and BigQuery, hence you can manage who has access to both the sheet and BigQuery. To do this; all you need to do is create a Google Group to serve as an access control group.

By clicking the share icon on sheets, you can grant access to which of your team members can edit, view or comment.

Whatever changes are made here will also be replicated on BigQuery.

This will serve as a form of IAM for your data set.

Limitations of using Sheets Connector to Connect Google Sheets to BigQuery

In this blog post, we covered how you can incorporate BigQuery into Google Sheets in two ways so far. Despite the immeasurable benefits of the process, it has some limitations. 

• This process cannot support volumes of data greater than 10,000 rows in a single spreadsheet. 
• To make use of the sheets data connector for BigQuery, you need to operate a Business, Enterprise, or Education G suite account. This is an expensive option. 

Additional Resources on Google Sheets to Bigquery

• Move Data from Excel to Bigquery

Conclusion

• This blog talks about the 3 different methods you can use to move data from Google Sheets to BigQuery in a seamless fashion.
• In addition to Google Sheets, Hevo can move data from a variety of Free & Paid Data Sources (Databases, Cloud Applications, SDKs, and more).
• Hevo ensures that your data is consistently and securely moved from any source to BigQuery in real-time.

FAQs to connect Google Sheets to BigQuery