BigQuery data in Google Sheets using Data connector

Published in

code.kiwi.com

6 min readFeb 21, 2023

Big data is becoming a significant challenge for businesses as the amount of data grows. To address this challenge, many businesses are turning to cloud-based storage solutions for their data and using easily scalable business intelligence (BI) tools. Google offers a vast range of tools and services for managing and analyzing data, most of which are free to use.

What is Google Sheets?

Google Sheets is a free, web-based spreadsheet application offered by Google as part of its Google Drive service, which also includes Docs and Slides for online document sharing. It is a powerful tool with many functions and built-in formulas.

Benefits of Google Sheets:

Easy and real-time collaboration
Sharing capabilities
Version history to track changes
Easy manipulation and analysis of data
Integration with various tools
Includes all essential spreadsheet functions familiar to Excel users
Customization options through add-ons and custom code writing
Convenient online access allowing automatic data gathering and versatility

Google Sheets provides a comprehensive spreadsheet solution with many benefits for personal or business use. With its online accessibility, collaboration features and customizability, it offers a great alternative to traditional spreadsheet software.

Useful links:

Google Sheets official website: link
Getting started with Google Sheets: link
Google Sheets function list: link
Google Workspace YouTube channel: link

What is Google BigQuery?

Google BigQuery is a serverless and cost-effective enterprise data warehouse solution offered by Google as a component of Google Cloud platform. It is one of the core data analytics solutions provided by Google, alongside Google Sheets and Looker. BigQuery can automatically and independently scale, compute and store resources across petabytes of data, without the need for infrastructure management.

Benefits of Google BigQuery:

Cost-effective solution for data warehousing
Seamless integration with Google Cloud platform and other tools
All-in-one data management and analysis
Access to massive amounts of data on-demand
Fast and simple to use
Continuously supported and developed by Google developers
Integration with machine learning (ML) and artificial intelligence (AI) technologies
Supports multiple data sources and formats
No need for physical servers

Google BigQuery provides a comprehensive data warehousing solution with many benefits for businesses. With its serverless architecture, cost-effectiveness and advanced analytics capabilities, it is a game-changer in the data warehousing industry.

Useful links:

Google BigQuery official website: link
Getting started with Google BigQuery: link
Google BigQuery documentation: link
Google Cloud YouTube channel: link

Why connect BigQuery to Google Sheets?

Connecting BigQuery to Google Sheets offers numerous benefits for businesses and organizations looking to manage and analyze large amounts of data. Here are some reasons why connecting BigQuery to Google Sheets is a great decision:

Access to Google’s wide range of features and tools
Collaboration and data sharing through a single platform
Built-in analytical tools for efficient data analysis
Real-time data streaming from BigQuery
Efficient data optimization and management
Simplified reporting process without the need for SQL knowledge
Easy sharing of specific data with stakeholders
Increased data accuracy and reliability
No more limitations on the size of datasets, ability to work with customized extracts

How to connect data from BigQuery to Google Sheets

The Data Connector feature in Google Sheets is a built-in solution that allows you to analyze BigQuery data directly in Google Sheets without the need for manual exports. This feature supports data refresh on a schedule, making it easier for you to access up-to-date information.

Google Cloud platform account configuration

To get started, you’ll need to configure your Google Cloud platform account. This requires:

Access to the Google Cloud platform
Access to BigQuery
Project with billing setup in BigQuery (Set up Google Cloud billing). If you don’t have a project with a billing setup, you can work with BigQuery data in a trial environment or try BigQuery for free. In this case you’ll be asked for a credit card number to verify your identity, but you won’t be signed up for automatic renewal and will only pay for what you use (BigQuery customers can store up to 10GB and analyze up to 1TB monthly for free)

Add BigQuery connection

Once your Google Cloud platform account is configured, here’s how to connect BigQuery to Google Sheets:

Open Google Sheets and log in to your Google account
Open an existing spreadsheet or create a new one
Go to the Data menu and select Data connectors
From the list of available connectors, select Connect to BigQuery
Choose a project (if you haven’t added any projects yet, you can use public dataset or create your own project)
Once you have selected a project, you will be able to choose the table, view, or write a custom query you want to connect to your spreadsheet
– To connect a table or view, simply select it from the list
– If you want to write a custom query, click the Custom query button and enter your query in the text box
Click the Connect button to establish the connection between Google Sheets and BigQuery

How to connect Google BigQuery to Google Sheets

After connecting BigQuery to Google Sheets, your data will be populated in the sheet. You can then create pivot tables, charts and add calculated columns to analyze your data and access your data in real-time.

Query parameterization

Query parameterization is a powerful feature that allows users to build more dynamic and customizable reports. By selecting a cell in an existing sheet and naming it as a parameter, you can add a level of customization to your queries that can be easily changed without having to modify the original code.

To use query parameterization:

Open the Query editor in the BigQuery Data Connector
Replace any static values in your query with references to your named parameter, using the @PARAMETER_NAME notation
Click on the Connect button

The query results will be displayed in the sheet and any changes to the named parameter value will dynamically update the query results.

Data updating

Data updating in the BigQuery Data Connector in Google Sheets allows you to keep your reports current and up-to-date. You have the option to update the data:

Manually, by clicking the Refresh preview button

Refresh preview option for manual data updating

2. Automatically by scheduling a refresh:
– Click the Schedule refresh button
– The Refresh options panel will open on the right
– Customize the schedule by setting the repeat frequency and start date
– Save your changes

Schedule refresh option for automatic data updating

Pros and Cons

Pros:

Access to large amounts of data for analysis and visualization
The ability to handle billions of rows of data, making it suitable for large-scale data analysis
Collaboration with stakeholders in a familiar spreadsheet interface
Single source of truth for data analysis without the need for additional exports
Streamlined reporting and dashboard workflows

Cons:

Google Cloud account configuration is required
A maximum of 10,000 rows of data can be extracted into Google Sheets
The highest automatic refresh frequency is once an hour
Risk of inefficient usage leading to uncontrolled costs
Stakeholders must have access to the connected BigQuery project in order to manipulate the data in Google Sheets
Limited functionality if you do not have the necessary permissions to the BigQuery project

Conclusion

The integration of BigQuery and Google Sheets provides a valuable solution for managing and analyzing data. The integration offers access to various Google features and tools, including real-time data streaming and simplified reporting. In addition, BigQuery’s scalability and storage capabilities are combined with the intuitive interface and visualization tools of Google Sheets, creating a comprehensive solution for data management and analysis. The process of connecting BigQuery and Google Sheets is straightforward and simple. The outcome is a robust and user-friendly solution that can help organizations effectively handle and analyze large amounts of data, leading to better data-driven decisions and improved overall efficiency.