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.
Understanding Google Spreadsheets
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. That being said, some of the reasons that spreadsheets are so important to businesses include:
When does it become necessary to incorporate a data warehousing tool?
Spreadsheets, despite its high degree of functionality, has 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 endeavour 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.
BigQuery(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 analyse 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 form 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 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 as automatically according to your data size and query complexity.
How to move data from Google Sheets to Google 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 credit to get a feel of GCP and access BigQuery.
This blog will cover 2 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.
1. Using BigQuery’s Data Connector:
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 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 auto-detect schema and we could also specify the column names and data types.
- Fill the table name and select create 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.
2. Using Sheets Data Connector:
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:
- On your google sheet workspace click on data.
- A drop-down pops up, scroll down to Data Connectors and select BigQuery.
- Authenticate your BigQuery account in the resulting window.
- A new window opens, click on write query.
- In the resulting pop-up, select schema.
- In the schema, you’d find all the datasets and table in your BigQuery project.
- Select the table you want to query.
- Populate the BigQuery editor window with your SQL code. Once your query becomes valid, you’d see the validator at the bottom of the page turn green.
- Click on insert results, the new table will now show in sheets.
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 made here will also be replicated on BigQuery. This will serve as a form of IAM for your data set.
Limitations of Moving from Google Sheets to BigQuery using
In this blog post, we covered how you can incorporate BigQuery into Google sheets in two ways. 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.
An Easier Way to Load Data from Google Sheets to Google BigQuery:
Using a fully managed platform like Hevo Data you bypass all the aforementioned complexities and load data from Google Sheets to BigQuery in just a few mins. You can achieve this in 2 simple steps:
- Connect and configure your Google Drive URL where the Google Sheet is stored
- Configure your BigQuery Warehouse the data has to be streamed
In addition to Google Sheets, Hevo can move data from a variety of 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 custom processes to move data from Google Sheets to BigQuery – Do experience Hevo’s hassle-free Data Integration platform by signing up for a 14-day free trial here.