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.
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.
Note: In case you are looking to move data from Google Sheets to BigQuery, you can read our step-by-step guide here.
Table of Contents
- Introduction to Google Sheets
- Introduction to BigQuery
- Understanding the Methods to Connect Google Sheets to BigQuery
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 reiterates 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.
Loading Data from Google Sheets to BigQuery
One can choose one of three approaches to move data from Google Sheets to BigQuery:
You can use this 4-step method to establish a connection between Google Sheets and BigQuery easily. This method starts with logging in to your GCP console and navigating to the BigQuery UI and ends with loading data to BigQuery in CSV format.
This is a simpler process for connecting Google Sheets and BigQuery where all you need to do is open a Google Sheets spreadsheet and click on Data > Data Connectors > Connect to BigQuery to set up the connection.
Implementing a platform like Hevo can get your data projects flying in a snap. There’s no need to write any code as Hevo offers a visual point and click interface to move your data in real-time.Get Started with Hevo for Free
Hevo is a completely managed platform, which means there is no monitoring and maintenance required from your end. It enables you to make the most of using Google Sheets with BigQuery.
Understanding the 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 BigQuery Connector to Move Data from Google Sheets to BigQuery
- Method 2: Using Sheets Connector to Move Data from Google Sheets to BigQuery
- Method 3: Using Hevo Data to Move Data from Google Sheets to BigQuery
Method 1: 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 2: 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.
Method 3: Using Hevo Data 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 Data 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.
Here are a few benefits of using Hevo:
- Easy Setup and Implementation – With Hevo, your data projects can take off in a jiffy as it will only take a few minutes to set up and configure your first data pipeline.
- Change Data Capture – Hevo can automatically detect new files on the Google Sheets location and load them to Google BigQuery without any manual intervention.
- Transformations – Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors – Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few.
- Zero Maintainance Overhead – Hevo automatically takes care of handling all the errors that may occur, ridding you of any data pipeline maintenance tasks.
- Additional Data Sources – In addition to Google Sheets, Hevo can bring data from 100+ other data sources into BigQuery in real-time. This will ensure that Hevo is the perfect companion for your businesses’ growing data integration needs.
- Live Support – Hevo has a dedicated product support team available at all points to swiftly resolve any queries and ensure you always have up-to-date data in your warehouse.
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.