Feb 21, 2023

BigQuery data in Google Sheets using Data connector

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:

  1. Access to the Google Cloud platform
  2. Access to BigQuery
  3. 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:

  1. Open Google Sheets and log in to your Google account
  2. Open an existing spreadsheet or create a new one
  3. Go to the Data menu and select Data connectors
  4. From the list of available connectors, select Connect to BigQuery
  5. Choose a project (if you haven’t added any projects yet, you can use public dataset or create your own project)
  6. 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
  7. 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:

  1. Open the Query editor in the BigQuery Data Connector
  2. Replace any static values in your query with references to your named parameter, using the @PARAMETER_NAME notation
  3. Click on the Connect button
How to use parameters in custom query

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:

  1. 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.


Sources and inspiration

Search
Share
Featured articles
Generating SwiftUI snapshot tests with Swift macros
Don’t Fix Bad Data, Do This Instead