Connect Google BigQuery to Google Sheets: 2 Easy Methods

• March 16th, 2022

bigquery to google sheets - Featured Image

Offering on-demand scalability, best-in-class performance, and reliability, Google BigQuery has become one of the leading Cloud Data Warehousing & Data Analytics platforms in the market. With flexible plans designed to cater to businesses of all sizes, Google BigQuery is a preferred solution over traditional on-premise data storage solutions. You can also integrate Google BigQuery with several other products present in the Google ecosystem such as Google Sheets.

Connecting BigQuery to Google Sheets allows you to analyze billions of rows of data and gain essential business insights. You can also share your data from Google Sheets and collaborate with your teams for better results. Importing your data from BigQuery to Google Sheets also streamlines your reporting and dashboard workflows. 

In this article, you will learn how to effectively connect BigQuery to Google Sheets using 2 different methods.

Table of Contents

What is Google BigQuery?

bigquery to google sheets - BigQuery Logo
Image Source

Launched in 2010, BigQuery is a Cloud-Based Data Warehouse service offered by Google. It is built to handle petabytes of data and can automatically scale as your business flourishes. Developers at Google have designed its architecture keeping the storage and computing resources separate. This makes querying more fluid as you can scale them independently without sacrificing performance.

Since there is no physical infrastructure present similar to the conventional server rooms for you to manage and maintain, you can focus all your workforce and effort on important business goals. Using standard SQL, you can accurately analyze your data and execute complex queries from multiple users simultaneously.

Key Features of Google BigQuery

Google BigQuery has continuously evolved over the years and is offering some of the most intuitive features :

  • User Friendly: With just a few clicks, you can start storing and analyzing your data in Big Query. An easy-to-understand interface with simple instructions at every step allows you to set up your cloud data warehouse quickly as you don’t need to deploy clusters, set your storage size, or compression and encryption settings.    
  • On-Demand Storage Scaling: With ever-growing data needs, you can rest assured that it will scale automatically when required. Based on Colossus (Google Global Storage System), it stores data in a columnar format with the ability to directly work on the compressed data without decompressing the files on the go.
  • Real-Time Analytics: Stay updated with real-time data transfer and accelerated analytics as BigQuery optimally allots any number of resources to provide the best performance and provide results so that you can generate business reports when requested.
  • BigQuery ML: Armed with machine learning capabilities, you can effectively design and build data models using existing SQL Commands. This eliminates the need for technical know-how of machine learning and empowers your data analysts to directly evaluate ML models.
  • Optimization Tools: To boost your query performance, Google provides BigQuery partitioning and clustering features for faster results. You also change the default datasets and table’s expiration settings for optimal storage costs and usage.   
  • Secure: BigQuery allows administrators to set access permissions to the data by groups and individuals. You can also enable row-level security for access to certain rows of a dataset. Data is encrypted before being written on the disk as well as during the transit phase. It also allows you to manage the encryption keys for your data.
  • Google Environment: Maintained and managed by Google, BigQuery enjoys the easy and fluid integrations with various applications present in the Google Ecosystem. With little to no friction at all, you can connect BigQuery to Google Sheets and Google Data Studio for further analysis.

What is Google Sheets?

bigquery to google sheets - Google Sheets Logo
Image Source

Google Sheets is a Web-based free Spreadsheet tool launched by Google in 2012. You can create, edit, and share the Spreadsheets with your colleagues. With multiple user access, people from your team can use and work on the same sheet to enhance collaboration. You can even track the revision history of the sheet and check which user made the changes.

A single sheet can store data in up to 5 million cells with 15 GB max as a Free User and can scale up with different G-Suite Plans. Hence, this acts as a powerful Data Store also. Integrating Google Sheets with other G-Suite products is a smooth process. For example, you can directly import data from your Google BigQuery to Google Sheets. Whether it is tracking and reporting your expenses or daily data entry for company sales, Google Sheets can do it all.

Key Features of Google Sheets

  • Remote Access: You can access and edit your Spreadsheets from anywhere, anytime, and from any device as all the data is stored in a remote server.
  • Increased Collaboration: Multiple users, according to the permission granted to them, can leave comments and edit the Spreadsheet.
  • Offline Editing: When the Internet connection is not available, you can still edit the sheets and the changes would be updated later on when you come online.
  • Always Free: To use Google Sheets, you just need a free Google Account and you are all set.
  • Reporting: It offers a variety of templates for data visualizations by charts, graphs, and various other diagrams.
  • Security: It provides options to set different permission levels for particular users for copying, editing, or downloading.

To know more about Google Sheets, you can visit the Official Google Sheets Website.

Why connect Google BigQuery to Google Sheets?

As an integral part of the Google Ecosystem, you can easily connect BigQuery to Google Sheets. Connecting BigQuery to Google Sheets has several benefits:

  • When you connect BigQuery to Google Sheets, you analyze your data with various analytical formatting tools offered by Google Sheets. For example, pivot tables, charts, and formulas are an effective way to transform your data into visual representations that you can use as needed.
  • You can efficiently stream and analyze massive volumes of data while maintaining a live connection from BigQuery to Google Sheets,  thereby keeping your data up to date.
  • Using the Google Sheets BigQuery Data connector, you can easily access, analyze, visualize, and share billions of rows of data with partners, analysts, or other stakeholders in a familiar spreadsheet interface.

Ways to Connect BigQuery to Google Sheets

Method 1: Manually Connect BigQuery to Google Sheets

This method allows you to manually connect BigQuery to Google Sheets using the Connected Sheets Data Connector.

Method 2: Using Hevo Activate to connect Google BigQuery to Google Sheets

Hevo Activate provides a hassle-free solution and helps you directly transfer data from Google BigQuery, Snowflake, Amazon Redshift, Facebook, etc., to Google Sheets, Salesforce, HubSpot, Zendesk, etc without any intervention in an effortless manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Hevo’s pre-built integration with Google BigQuery and Google Sheets and CRM Sources will take full charge of the data transfer process, allowing you to focus on key business activities. Hevo also provides Google Sheets as a Free Source and Google BigQuery as a Destination for seamlessly loading data into it.

Get Started with Hevo for Free

How to Connect Google BigQuery to Google Sheets?

By connecting Google BigQuery to Google Sheets, you can establish a single source of truth for Data Analysis. This also assists you in streamlining your reporting and dashboard workflows. To effectively connect BigQuery to Google Sheets, you can follow the 2 different methods given below: 

Method 1: Using Connected Sheets to connect Google BigQuery to Google Sheets

Connected Sheets is the Google Sheets BigQuery connector offered by Google. Before moving towards setting up the connection, check if you have the required Google Cloud account setup. To do that, follow these simple steps:

  • Step 1: Ensure that you have an enterprise workspace account to use Connected Sheets with BigQuery. You can create a new Google Cloud account for your use case.
  • Step 2: For creating a new Google Cloud project, navigate to Google Cloud Console > Project Selector Page and choose or create a Google Cloud Project.
  • Step 3: You must make sure that the billing is Toggled On for your Cloud Project. For each new project, Google BigQuery is automatically enabled. For your existing Cloud Projects, you enable BigQuery by going to Enable the BigQuery API option.

1. Using a Public Dataset to establish the Connection 

To manually set up this connection, follow the easy steps given below:

  • Step 1: Open an existing Google Sheets spreadsheet or create a new one.
  • Step 2: Click on the Data Menu and navigate to Data Connectors > Connect to BigQuery. 
BigQuery to Google Sheets - Data Menu
Image Source
  • Step 3: Now, click on the Get Connected option and choose the Google Cloud project that has billing enabled as discussed above.
  • Step 4: In this article, a public dataset is taken as an example. For that, click on the Public Datasets option and enter “Chicago” in the search box.
  • Step 5: Click on the chicago_taxi_trips dataset. In that, choose the taxi_trips table, and finally, click on the Connect button. 
bigquery to google sheets - taxi_trips table
Image Source

Your Google Sheets spreadsheet is now ready with approx 194 M rows. However, Google sheets only display a handful of rows as a preview. Though, while performing any calculation or using any pivot tables, graphs & charts all the rows are considered. You can also import more data from BigQuery to Google Sheets using the Extract feature. To do this follow these simple steps:

  • Step 1: Click on the Extract button. To add the new data to a new sheet, check the New Sheet option and click on the Create button.
BigQuery to Google Sheets - Extract Function
Image Source
  • Step 2: From the Extract Editor present on the right side of your screen, choose the desired columns, filters, sorting order, and the number of rows you want to import. For instance, here 25000 rows have been imported with the columns trip_start_timestamp, fares, tips, tolls and sorted in descending order of trip_start_timestamp.
  • Step 3: Finally, click on the Apply button to view your imported data.
bigquery to google sheets - extract editor
Image Source

Google Sheets also allows you to update the data for each of the graphs, tables & charts individually. You can do it in 3 ways:

  • Manually update a chart/graph by hovering over the chart/graph and clicking the Refresh option.
bigquery to google sheets - Update chart separately
Image Source
  • You can update all your tables by clicking the Refresh options present on the top left corner of your screen and then clicking on the Refresh all button. 
bigquery to google sheets - Refresh all
Image Source
  • From the Refresh options, you can also schedule your updates by clicking on the setup now option. Google Sheets allows you to set the frequency and the time of the day at which you want to refresh your data.
bigquery to google sheets - Scheduled Refresh
Image Source

2. Example Use Cases

Without requiring any prior knowledge of SQL, the Connected Sheets Data Connector allow you to analyze huge amounts of data. Given below are some real-life use cases where connecting BigQuery to Google Sheets is advantageous: 

  • Business Planning: You can create, organize and share data with others to gain important insights and collaborate in informed decision making. For instance, you can go through your sales data and identify products that are selling better in different locations. 
  • Customer Service: Check out which stores have recorded the most complaints per 10,000 customers and find out what are the main causes for them in order to improve customer satisfaction. 
  • Sales: Generate internal financial and sales reports and share these reports with your sales reps.

The manual method to connect BigQuery to Google Sheets is a good choice when you are rarely required to import data from BigQuery to Google Sheets. However, to create, manage & maintain multiple connections requires a considerable portion of your engineering bandwidth. For a more efficient & effortless approach, you can go for the second method to automate your workflow.

Method 2: Using Hevo Activate to connect Google BigQuery to Google Sheets

BigQuery to Google Sheets - hevo logo
Image Source

Hevo Activate helps you directly transfer data from Google BigQuery, Snowflake, Amazon Redshift to CRMs such as Salesforce, HubSpot, various SaaS applications like Google Sheets, Data Warehouses, and a lot more, in a completely hassle-free & automated manner.

Hevo also provides Google Sheets as a Free Source and Google BigQuery as a Destination for seamlessly loading data into it. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

Check out what makes Hevo Activate amazing:

  • Real-time Data Transfer: Hevo Activate, with its strong Integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc. such as Airflow, HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks. 
  • Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls
Sign up here for a 14-Day Free Trial!

With Hevo Activate, you can easily connect Google BigQuery to Google Sheets in 2 simple steps:

  • Step 1: Configure Google BigQuery Data Warehouse
  • Step 2: Configure Google Sheets as your Target Destination  

Step 1: Configure Google BigQuery Data Warehouse

To configure your Google BigQuery Data Warehouse, follow these easy steps:

  • Step 1: To set Google BigQuery as your source you need to extract details such as your Project ID, Dataset ID, and GCS bucket. First, go to the Google API Console, and in the top left corner, besides the hamburger menu icon, click the drop-down to view all the projects. Copy the ID of the required project.
BigQuery to Google Sheets - Project ID
Image Source
  • Step 2: Go to your BigQuery instance & select the Project ID. Copy the Dataset ID and Data Location. The dataset ID is displayed as project-name:dataset-ID. Copy only the dataset ID. For example, in the image shown below, the dataset ID is test-dataset.
  • Step 3: Go to Storage in Google Cloud Platform. In the Storage browser, check for the bucket Name and Location.
  • Step 4: Read Enable billing for a project to set up the billing details of your project.
  • Step 5: To configure BigQuery Data Warehouse for Activate, Click ACTIVATE in the Asset Palette, and in the ACTIVATIONS tab, click + CREATE ACTIVATION.
BigQuery to Google Sheets - Create Activation
Image Source – Self
  • Step 6: In the Select Warehouse page, click + ADD WAREHOUSE. In the Select Warehouse Type page, select Google BigQuery.
bigquery to google sheets - Add Warehouse
  • Step 7: In the Configure your Google BigQuery Account page,
    • Step 1: Click + ADD GOOGLE BIGQUERY ACCOUNT.
    • Step 2: Sign in as a user with BigQuery Admin and Storage Admin permissions.
    • Step 3: Click Allow to authorize Hevo to access your data.
  • Step 8: In the Configure your Google BigQuery Warehouse page, specify the following details:
bigquery to google sheets - Google BigQuery Warehouse settings
  • Step 9: Click TEST CONNECTION to test and SAVE DESTINATION or SAVE WAREHOUSE, as applicable, to complete the setup.

Step 2: Configure Google Sheets as your Target Destination

To set Google Sheets as your Target destination, you can follow the simple steps given below:

  • Step 1: Click Activate in the Asset Palette.
  • Step 2: Do one of the following:
BigQuery to Google Sheets - Create a new Target
Image Source
BigQuery to Google Sheets - Create an Activation
Image Source
  1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.
  2. In the Select a Target page, click + ADD TARGET.
BigQuery to Google Sheets - Add New Target
Image Source
  • Step 3: In the Select a Target Type page, click on Google Sheets.
BigQuery to Google Sheets - Select a Target Type
Image Source
  • Step 4: In the Configure your Google Sheets account page, select the authentication method for connecting to Google Sheets.
BigQuery to Google Sheets - Select Authentication Method
Image Source
  • Step 5: Do one of the following:
    • Connect using a User Account:
      1. Click + ADD GOOGLE SHEETS ACCOUNT.
      2. Select the Google account associated with your Google Sheets data and click ALLOW to authorize Hevo to access your data.
    • Connect using a Service Account:
      1. Attach the Service Account Key. Read Downloading the key file for steps to create a new key. Note: Hevo supports only JSON format for the key file.
      2. Click CONFIGURE GOOGLE SHEETS ACCOUNT.
  • Step 6: In the Configure your Google Sheets Target page, specify details such as a unique Target Name, your Google Drive folder(optional), and your Google Spreadsheet.
BigQuery to Google Sheets - Configure Google Sheets Target
Image Source
  • Step 7: Click TEST & CONTINUE. You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page. Refer to section, Field Mapping in Google Sheets to know how to map the Warehouse fields to your selected Google spreadsheet.
BigQuery to Google Sheets - Targets List View
Image Source

Conclusion

In this article, you have learned how to effectively connect Google BigQuery to Google Sheets using 2 different methods. The first method uses the Connected Sheets Data connector to connect BigQuery to Google Sheets. After importing your data from BigQuery to Google Sheets, you create Pivot Tables, Charts and add Cacluated Columns. As Google only shows 500 rows as a preview, you can also import more data using the Extract function. You can Refresh the data for each chart individually or update all the tables with a single click.

However, creating & managing multiple connections between Google BigQuery, Google sheets and all the applications in your business is a time-consuming & resource-intensive task. Using the second method, you can opt for a more economical & effortless approach by automating your workflow via a Cloud-Based Reverse ETL Tool like Hevo Activate

Visit our Website to Explore Hevo

Hevo Activate helps you directly transfer data from a source of your choice such as Google BigQuery, Snowflake, Amazon Redshift, Facebook, etc., to any SaaS application like Google Sheets, CRMs like Salesforce, etc., in a fully automated and secure manner without having to write the code repeatedly. Hevo also provides Google Sheets as a Free Source and Google BigQuery as a Destination for seamlessly loading data into it. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.  

Hevo, with its strong integration with CRM Tools like HubSpot, Salesforce, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of connecting BigQuery to Google Sheets! Share your thoughts with us in the comments section below.

No-code Data Pipeline for Google BigQuery