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

In this blog post, we will be covering extensively how you can move data from Google Sheets to BigQuery. Before we get started, let’s cover some background concepts to help build our understanding. 

Introduction to Google Sheets

Spreadsheets are electronic worksheets that contain rows and columns which users can input, manage and carry out mathematical operations on their data. It gives users the unique ability to create tables, charts, and graphs to perform analysis.

Google Sheets is a spreadsheet program that is offered by Google as a part of their Google Docs Editor suite. This suite also includes Google Drawings, Google Slides, Google Forms, Google Docs, Google Keep, and Google Sites.

Google Sheets gives you the option to choose from a vast variety of schedules, budgets, and other pre-made spreadsheets that are designed to make your work that much better and your life easier.

Here are a few key features of Google Sheets:

• In Google Sheets, all your changes are saved automatically as you type. You can use revision history to see old versions of the same spreadsheet. It is sorted by the people who made the change and date.
• It also allows you to get instant insights with its Explore panel. It allows you to get an overview of data from a selection of pre-populated charts to informative summaries to choose from.
• Google Sheets allows everyone to work together in the same spreadsheet at the same time.
• You can create, access, and edit your spreadsheets wherever you go- from your tablet, phone, or computer.  

Introduction to BigQuery

Google BigQuery is a data warehouse technology designed by Google to make data analysis more productive by providing fast SQL-querying for big data. The points below reiterate how BigQuery can help improve our overall data architecture:

• When it comes to Google BigQuery size is never a problem. You can analyze up to 1TB of data and store up to 10GB for free each month.
• BigQuery gives you the liberty to focus on analytics while fully abstracting all forms of infrastructure, so you can focus on what matters.
• Incorporating BigQuery into your architecture will open you to the services on GCP(Google Cloud Platform). GCP provides a suite of cloud services such as data storage, data analysis, and machine learning.
• With BigQuery in your architecture, you can apply Machine learning to your data by using BigQuery ML. 
• If you and your team are collaborating on google sheets you can make use of Google Data Studio to build interactive dashboards and graphical rendering to better represent the data. These dashboards are updated as data is updated on the spreadsheet. 
• BigQuery offers a strong security regime for all its users. It offers a 99.9% service level agreement and strictly adheres to privacy shield principles. GCP provides its users with Identity and Access Management (IAM), where you as the main user can decide the specific data each member of your team can access.
• BigQuery offers an elastic warehouse model that scales automatically according to your data size and query complexity.

Reasons to Migrate Data from Google Sheets to BigQuery

Spreadsheets, despite their high degree of functionality, have a lot of shortcomings when it comes to handling large datasets and data from various sources. Spreadsheets are easiest to maintain when your company is small and very few people need to access your 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 roll up your sleeves and incorporate a data warehouse like BigQuery into your data architecture. 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. 

Methods to Connect Google Sheets to BigQuery

Now that we have built some background information on the spreadsheets and why it is important to incorporate BigQuery into your data architecture, next we will look at how to move data from Google Sheets to BigQuery. Here, it is assumed that you already have a GCP, account. If you don’t already have one, you can set it up here. Google offers new users $300 free credits for a year. You can always use these free credits to get a feel of GCP and access BigQuery.

 This blog will cover 3 ways in which you can move data from Google Sheets to BigQuery. Additionally, we will also cover the limitations of these methods and an easier way to move data into BigQuery. The 3 ways are as follows:

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

Hevo is an automated data pipeline that provides an easy-to-use,cost-free User Interface with the ability to copy data from Google Sheets to BigQuery without writing any code. Hevo enables the lowest time to production for such copy operations, allowing developers to focus on their core business logic rather than waste time on the configuration nightmares involved in setting these up.

Sign up here for a 14-Day Free Trial!

Using a fully managed platform like Hevo you bypass all the aforementioned complexities and load data from Google Sheets (Free Data Source) 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 Facebook Ads to BigQuery by providing the project ID, dataset ID, Data Warehouse name, GCS bucket.

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

You can easily connect your data from sheets into BigQuery using BigQuery’s data connector. The steps below illustrate how:

Login to your GCP console and Navigate to the BigQuery UI using the hamburger menu.

Inside BigQuery select Create Data set.

After creating the dataset, next up we create a BigQuery table that will contain our incoming data from sheets. 

We click on create a table. In the create a table tab, we select Drive.

Under the source window, we choose google drive as our source and populate the Select Drive URL tab with the URL from our google sheet. We can select either CSV or sheets as the format. Both formats allow us to select the auto-detect schema and we could also specify the column names and data types. 

Fill in the table name and select create a table. With your sheets linked to your BigQuery, you can always commit changes to your sheet and it will automatically appear in BigQuery.

Now that we have data in BigQuery, we can perform SQL queries on our ingested data.
The image following 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 is only available for Business, Enterprise, or Education G suite account. 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, 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. 

Conclusion

This blog talks about the 3 different methods you can use to move data from Google Sheets to BigQuery in a seamless fashion.

Visit our Website to Explore Hevo

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.

Before you go ahead and build custom processes to move data from Google Sheets to BigQuery – SIGN UP for a 14-day free trial here to experience Hevo’s hassle-free Data Integration platform.