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 be covering extensively how you can move data from Google Sheets to BigQuery.
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 import 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.
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.
Sign up here for a 14-Day Free Trial!
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:
Key features of Hevo are,
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
- Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
Method 2: Using BigQuery Connector to Move Data from Google Sheets to BigQuery
You can easily upload Google Sheets to BigQuery 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 Sheet 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.
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 to experience Hevo’s hassle-free Data Integration platform.