How to Connect Google Sheets data in BigQuery

bigquery external table google sheet

The following guide will show you how to connect Google Sheets to BigQuery in a few simple steps.

Google BigQuery is a Platform as a Service offering. It is commonly used by performance marketers to store their marketing information. Connecting Google Sheets data to BigQuery helps access it.

 

Connecting Google Sheets to BigQuery has become important for more than one reason. Here are a few:

  • Data in BigQuery has become a source of data validation for most businesses
  • To add additional/historical data from Google Sheets to BigQuery
  • It enables interaction with the data Google Sheets using SQL

 

Method 1: How to query Google Sheets data in BigQuery

So, here we understand how to query Google Sheets in BigQuery in 4 simple steps:

 

1. Get the necessary permissions in BigQuery

The following permissions are necessary for your Google Cloud project before you start:

  • tables.create
  • tables.getData
  • jobs.create

 

2. Create a dataset in your BigQuery project

Datasets serve as folders for creating tables. So you need to create them first. Here’s how you do it.

Go to BigQuery in the Google Cloud interface and select your project.

Google Cloud Platform Select Project

Create a new dataset.

Create dataset in BigQuery

 

3. Create a table in the dataset

Click the ‘+’ to create a new table.

Create table BigQuery

 

Create the table from ‘Drive’ using the URL and specifying the file format as Google Sheets. If you wish to specify the range, use Google Sheets syntax, say ‘Sheet3!A3:H19’.

Create table BigQuery 2

Name the table in line with BigQuery guidelines.

You may manually specify the Schema or let BigQuery auto-detect it.

Now click on ‘Create Table’ to create an external table for BigQuery to run queries on.

 

4. Run Query

Once you create an external table, you can run queries on it.

RunQuery 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 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 Project1 in the search box, and then select the Project1_… dataset. From this dataset choose the taxi_trips table and then click on the Connect button to finish this step.

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

 

5 Reasons Why Connect Google Sheets to BigQuery

Migrating data from Google Sheets to BigQuery offers several benefits and addresses the limitations of spreadsheets when it comes to handling large datasets and complex data analysis. Here are some reasons to consider migrating:

  1. Scalability: Google Sheets has limitations in terms of the amount of data it can handle efficiently. As your company grows and generates larger datasets, BigQuery can handle terabytes or even petabytes of data at scale. It provides a powerful infrastructure that can handle the storage and processing requirements of massive datasets.
  2. Performance: BigQuery is designed for high-performance analytics. It uses distributed computing and parallel processing to perform complex queries on large datasets quickly. This allows for faster data analysis and reporting, enabling users to gain insights more efficiently.
  3. Data consolidation: Google Sheets may not be the most suitable tool for consolidating data from different sources. BigQuery, on the other hand, supports data integration from various systems and sources. It allows you to centralize and unify your data in one place, making it easier to analyze and derive insights from data originating from different platforms and databases.
  4. Advanced analytics: BigQuery provides powerful analytical capabilities, including support for advanced SQL queries, machine learning models, and data visualization tools. It enables you to perform complex calculations, build predictive models, and create sophisticated visualizations to gain deeper insights from your data.
  5. Collaboration and access control: Google Sheets can become challenging to manage when multiple users need access to the same dataset simultaneously. BigQuery offers robust access control mechanisms, allowing you to define permissions and roles for different users or teams. It supports collaborative work, enabling multiple users to work on the same dataset concurrently without conflicts.

By migrating your data from Google Sheets to BigQuery, you can overcome the limitations of spreadsheets and unlock the full potential of your data for more sophisticated and scalable data analysis.

 

Limitations of using Sheets Connector to Connect Google Sheets to BigQuery

When using the Sheets Connector to connect Google Sheets to BigQuery, there are indeed a few limitations to consider:

  • Data volume limitations: The Sheets Connector has a limitation on the amount of data that can be pulled from BigQuery into a single spreadsheet. It supports a maximum of 10,000 rows of data, which may be insufficient for large datasets. This limitation can impact the ability to analyze and work with extensive data in a single sheet.
  • Requirement of a G Suite account: To utilize the Sheets Connector for BigQuery, you need to have a Business, Enterprise, or Education G Suite account. These types of accounts often come at a higher cost compared to standard Google accounts. This requirement can be a limiting factor for organizations or individuals who don’t have access to or don’t want to invest in a G Suite account.

It’s worth noting that the limitations mentioned above are specific to using the Sheets Connector for BigQuery and may not apply when directly querying BigQuery using other methods or tools. If you require more flexibility, scalability, and features, you may consider alternative approaches such as using SQL queries in BigQuery or utilizing dedicated data integration and visualization tools that offer more robust capabilities.

 

How to move data into BigQuery or Google Sheets by Windsor.ai

  1. Google BigQuery

 

2. Google Sheets

 

Summary: Connect Google Sheets data in BigQuery

Google Sheet here becomes an external data source to BigQuery. Any changes on Google Sheet reflect in BigQuery query results.

You may specify a bounded table specify the end row number in sheet range (‘Sheet3!A2:G99’) or skip it for an unbounded table. An unbounded table range looks like ‘Sheet3!A2:G’. Specifying an unbounded range makes any new data added to the sheet accessible in BigQuery.

To query Google Sheets BigQuery, you need to get the necessary permissions, create a dataset and a table in BigQuery.

Windsor.ai automates the streaming of all your marketing data in a few clicks. Simply choose the platforms, tools you would like to connect and authenticate them. Now your data is connected.  After choosing the synchronization interval your data will start appearing in BigQuery. Try it for free to understand how it works.

 

You might also be interested in…