Content
- What is Google BigQuery?
- What is Google Sheets?
- Why Cloud Data Warehouses to Google Sheets?
- BigQuery Data to Sheets Integration: 3 Ways for Data Analysis
- Method 1: Automatically Connect BigQuery to Google Sheets with OWOX Reports
- Method 2: Save Query Results in BigQuery
- Method 3: Use Connected Sheets to Connect BigQuery to Google Sheets
- Bonus: How to Email Reports using Google App Script
Connect BigQuery to Google Sheets: 3 Ways to Move Your Data
Ievgen Krasovytskyi, Head of Marketing @ OWOX
We’ve already shared an article about how to automatically load data from Google Sheets to BigQuery. In this article, you’ll learn how to do the opposite - build reports in Google Sheets using data stored in BigQuery.
Note: This article about BigQuery Sheets Connectors was originally published in August 2019 and was completely updated in October 2024 for the accuracy and comprehensiveness of all the solutions.a
What is Google BigQuery?
Before we talk about the settings, we will briefly discuss what Google BigQuery is, and what are its features, advantages, and benefits.
Google BigQuery is one of the best on the market fully managed, serverless cloud dwh provided by Google Cloud Platform (GCP) in addition to other Google Cloud Products like Google Cloud Storage (GCS) and Google Cloud Console.
BigQuery supports all data types, works across clouds, and has built-in machine learning and business intelligence, all within a unified platform.
It’s designed to store, analyze, and process large datasets using SQL queries, addressing the challenges of handling big data by moving computations closer to the data, thereby making data analysis faster and more efficient.
What is Google Sheets?
It's difficult to find a data analyst, a manager, a marketer, or any other business user who doesn't work with Spreadsheets. Whether it’s Google Sheets or Excel, business users are mankind revenue-oriented decisions based on some spreadsheet data.
Google Sheets is a free tool, with many functions and built-in formulas, smart access management, and it is very convenient to work with.
In addition, it's probably the best spreadsheet tool out there to collaborate with team members as well as other stakeholders whenever and wherever.
The problem is, that spreadsheet tools are rarely suitable for storing data. Modern businesses store vast amounts of data in databases, data warehouses, and data lakes such as PostgreSQL, MongoDB, Snowflake, Google BigQuery, or Redshift.
That is why corporate data is not accessible for most of the business spreadsheet users that are not SQL experts.
If you want to learn more about the problems of corporate reporting in Spreadsheets - watch the video down below.
Why Cloud Data Warehouses to Google Sheets?
Connecting a data warehouse to Google Sheets enables complex corporate data from Google Cloud Platform integration into the tool where humans work - spreadsheets, fostering data access to business users, and so informed business decisions that drive growth.
Here are a few advantages of Connecting BigQuery to Google Sheets:
Seamless Data Access: This integration allows direct access to BigQuery’s large datasets within the familiar Sheets interface. This simplifies working with big corporate data, making it as straightforward as handling a standard spreadsheet. BigQuery's managed storage ensures fast query performance, enhancing the speed and efficiency of data access.
Real-time Insights: The capability to refresh data in real-time ensures that reports in Google Sheets are always based on the latest corporate data. This is essential for timely decision-making in dynamic business environments.
Collaborative Analysis: Combining BigQuery’s data storage with Google Sheets’ easy interface promotes collaborative data analysis. Business and Data teams can work together, sharing insights and contributing to data-driven projects within a familiar spreadsheet environment.
Simplified Reporting: The connection simplifies the reporting process, enabling users to access data and analyze reports on top of the BigQuery data without advanced SQL knowledge.
Visual Data Exploration: With Google Sheets’ visualization tools, users can generate dynamic pivots, charts, and graphs directly from BigQuery data, facilitating trend and pattern identification with ease. So complex corporate data can be communicated in a visually engaging manner.
BigQuery Data to Sheets Integration: 3 Ways for Data Analysis
There are several methods available for replicating data from Google BigQuery into a Google Sheets spreadsheet.
To directly access your BigQuery data in Sheets, you can use third party tools like OWOX, the ‘Save Query Results in BigQuery’ method, the ‘Explore with Sheets’ feature or Connected Sheets will help you do the same but also enable automated updates.
However, to overcome the limitations and more advanced functionality, flexibility, and cost-efficiency, it might be worth looking into other reliable data integration tools, which we’ll get into in more detail.
You can also use Google Apps Script or another custom code – but that’s a way for data engineers or software developers to apply their coding skills, rather than data analysts or spreadsheet users.
Let’s look at the most popular ways to link BigQuery to Google Sheets together.
Method 1: Automatically Connect BigQuery to Google Sheets with OWOX Reports
If you're looking for a repetitive or ad-hoc way to load BigQuery data into Google Sheets, consider using a fully automated tool by OWOX. Let's explore the process step-by-step:
Step 1: Install OWOX Reports Extension
Add OWOX BI Reports Extension from the Google Workspace Marketplace to your Sheets. Simply type in BigQuery and just select the first one:
Step 2: Create your report
1. Create a new table or open an existing Spreadsheet.
2. Go to ‘Extensions' and select ‘OWOX Reports' — ‘Add a new report':
3. Next, in the right sidebar, specify the ‘Google BigQuery Project’ you want to run your reports from to simply connect BigQuery.
Note: Please, ensure you have a Google Cloud project with billing enabled to access BigQuery datasets and tables.
4. Now, let's add a SQL Query. If you want to create a new query just click on ‘Data Mart’ -> then ‘+ Add new data mart...'.
Note: If you or any of your team members have already loaded the SQL query for your report, you can just select it from the drop-down menu.
We’ve created this Data Marts Library (A collection of Querries) to avoid having a data professional to build the same query twice for different team members or departments.
If you want to edit an existing query, simply click ‘Edit’ and adjust the code.
If you need help crafting your query or editing an existing one, you can use our free OWOX SQL Copilot for BigQuery that allows to review, edit, or modify any existing query, perform complex JOINs, and validate within the chapGPT window.
This smart AI SQL copilot also has a direct integration with BigQuery to fetch the data schema, find relationships between tables within your database, and finally, craft the query with YOUR unique context.
5. Add your SQL query, give it a name, and click 'Save & Run'.
I'll use one of the public datasets with GA4 data.
There you have it!
The data is then processed in BigQuery, and the query results, a holistic view of that is automatically imported into Sheets to a new sheet, which is renamed automatically to the data mart title:
Step 3: Share data mart with your team
If you want to make your data mart used by your less technical team members, or if you simply don't want to share BigQuery credentials, you can allow anyone run, customize & schedule reports (but not editing SQL) on your behalf using your shared access to the specific datamart.
To share access to the data mart click the 'pencil' icon to edit the data mart, go to the 'Share' tab, enter email addresses of your colleagues and provide them with the delegated access to run reports on their own. It's 100% Secure.
Note: You can always withdraw your permissions.
Step 4: Customize report to fit your needs
1. FILTER data without editing SQL code:
OWOX BI doesn’t have a connected sheets’ 100,000 rows limit, however, there are still some of the Google Sheets restrictions, for example, 10M cells.
Plus when the spreadsheet becomes ‘heavy’ - it’s more complicated to build pivots, charts on top of your report and analyze your data.
So it’s time to add filters and specify how many rows do we want to retrieve.
For example, let’s say we want to retrieve only the: Medium CONTAINS organic.
2. Apply LIMITs:
There is also a LIMIT functionality that allows you to specify the number of rows (starting from the first) that you want to receive in the Sheets table.Let’s ‘LIMIT’ our data to 10 rows.
3. SORT data automatically:
Plus, let's sort our data by Total_users Descending.
When we click on ‘RUN’ - we’ll get the filtered data. Just look how simple it is for a spreadsheet user who doesn’t know SQL to filter the data in the spreadsheet.
Note: Filters can be added to fields of the following types: String, Integer/Float, Boolean, Date, DateTime, Time.
Step 5: Set automated report refreshes
With this BigQuery to Sheets OWOX Reports Extension, you can enable automated refreshes for query results to avoid manually running updates when you or another stakeholder needs data.
To set updates on a schedule, just use the sidebar section ‘Scheduled Refresh' or open the report, use the Extension sidebar or select OWOX Reports, Charts & Pivots — ‘Scheduled Refresh' from the ‘Extensions' tab:
Next, Select a desired update frequency. Specify how often you want to update the data in your report (which time of the day, what days of the week, and which weeks of the month) and set it just once to run the query and all of your reports will be saved automatically.
If you want to receive an email alert when the report is updated, check the appropriate box.
Save the settings.
Your report will then be automatically updated at the specified time and frequency!
Step 6: Automatically generate pivots & charts
Finally, you can visualize the query results - report: build awesome pivot tables, graphs, charts, share with your teammates and make decisions based on those data.
To visualize your data, head up to 'Visualize now' button, select the metrics & dimensions to include, and get the beautiful and actionable dashboard with everything you need to start exploring data.
Learn more about our FREE Pivots & Charts generator in this article.
Step 7: Customize deeper [Optional]
But what if you want to filter the data by the fields, that are not retrieved using this query, but still give a spreadsheet user the power to handle this?
In our example, on the screenshot below, the parameters are event_name, start_date, and end_date. To apply dynamic parameters, just click 'Save & Run'.
BigQuery Reports Extension in Action
- Run Cohort analysis in Google Sheets. You can calculate the revenue as well as the cost of attracting each user cohort and optimize the budget.
- Segment users by behavioral characteristics and send each segment unique advertising offers.
Seamless BigQuery Integration in Sheets
Get real-time, automated, and deeply insightful reporting at your fingertips. Connect BigQuery, run reports, and prepare dashboards in your favorite Google Sheets
Method 2: Save Query Results in BigQuery
Next. The simplest solution, but the most limited.
Run the query in BigQuery interface and manually save the SQL query results to a spreadsheet using the web UI.
After you’ve run your query, click the ‘Save Results’ button and choose ‘Google Sheets’.
In a few moments later, a newly created Google Sheets with the name ‘results-YYYYMMDDD-#######’ document with your transfer data will be created and you can open in in a new tab.
This option allows you to export query results into Google Sheets document of up to 10 MB.
The main drawback here is that there is no automated updates here. You can query data only once. So, every time you’ll need to update the data or refresh, you’ll have to manually export your query results again and again, flooding your Google Drive with newly created spreadsheets. So, let’s take a look at a better way to export BigQuery to Google Sheets.
Explore BigQuery Data in Google Sheets
Bridge the gap between corporate BigQuery data and business decisions. Simplify reporting in Google Sheets without manual data blending and relying on digital analyst resources availability
Method 3: Use Connected Sheets to Connect BigQuery to Google Sheets
Google's connected sheets, a former BigQuery data connector from Sheets, are designed for BigQuery users to download BigQuery tables and queries into Google Sheets.
It is a built-in native solution to explore data in Google Sheets. In addition, Connected Sheets supports data refresh on a schedule (every week, every day, or every hour), which means you no longer need to manually export data from BigQuery, but you’ll pay for processing every time data is loaded.
Let’s see how it works:
Important! To use a connected sheets connector, you might need to upgrade your account. As of 2024, this connector isn't available to all users, but to customers who purchased G-Suite Enterprise Standard and Enterprise Plus; Education Standard and Education Plus; Enterprise Essentials and Enterprise Essentials Plus.
Export BigQuery tables to Google Sheets with Connected Sheets
In order to retrieve an entire BigQuery table into Connected Sheets:
- Log in to your BigQuery account,
- navigate to the table you want to work with,
- Right-click on the table and select ‘Open in new tab’,
- click on the table name, then ‘Export’ → ‘Explore with Sheets’.
Or, you can find your table in the left Explore bar, click three dots next to the table name, and select ‘Open in’ → ‘Connected Sheets’.
In just a few moments, a Connected Sheets doc will be prepared and opened, and you’ll get a message ‘Success. Your data is connected’.
Click ‘Get started’ and you’ll see your GBQ table data in Connected Sheets.
Note: Your data is not imported into a Connected Sheets document. It’s a preview of your tabular data in a special interface.
You can manipulate those data in different ways, filter, pivot, build charts, apply functions. If you need to export data into sheets - click the ‘Extract’ button and select how many rows you need to load.
Select ‘new’ or ‘existing sheet’, then confirm the extraction by clicking ‘Apply’.
Here is how the extracted data looks like:
On the right side of the screen, you will see the ‘Extract editor’, which allows you to select columns, filter and sort your data, as well as change the row limit with fixed positions from 10 to 100,000 rows or select your number.
Note: You won’t be able to extract more than 100,000 rows.
Your Data to Dashboards in 1 Click
Create full reports in Google Sheets in no time!
...plus, it's 100% Free!
Export SQL Queries from BigQuery to Sheets with Connected Sheets
If you need to export a SQL query containing data from multiple tables with JOINs or UNIONs from BigQuery to Google Sheets, then the BigQuery Sheets Connector can do this for you as well.
Run your query, then click ‘Explore data’ → ‘Explore with Sheets’.
You already know what would happen next, a new Google Sheets Table with your data and a Connected Sheets window.
Automate Refresh Schedule for BigQuery Export to Google Sheets
After data is extracted, you can work with the query result, create pivots, and charts, change the number of rows, filter data, and automate the refresh of your data.
BigQuery not only enables descriptive analytics but also emphasizes its capability for predictive analytics, allowing users to create machine learning models and make batch predictions directly within the data warehouse:
- Click on the Schedule refresh option.
- Customize Repeat frequency and Start date and time.
- Click Save to apply the changes.
Now, your BigQuery export to Google Sheets is automated, and it will refresh according to the specified schedule.
Note: With connected sheets you will be able to update your reports weekly, daily or hourly. So no flexibility here.
Connected Sheets limitations
While Connected Sheets as a native Google Sheets connector offers a lot of advantages, there are notable challenges that users might encounter when using it:
1. First and foremost, size limitation: There is a 100,000 rows limit for data extraction that can be downloaded at one time. If your document contains a large amount of information, you might need to break it into different sheets.
2. Another challenge is the need for a specific type of Workspace account. To use Connected Sheets Connector for BigQuery, users must have a Business, Enterprise, or Education GSuite account (an Education G Suite account would not work).
3. The refresh frequency is weekly, daily or hourly. There is no ability to refresh report let’s say twice a day. This leads to 10X higher data processing costs.
4. Last but not least, stakeholders must have access to the BigQuery project in order to be able to manipulate the data in Google Sheets. Sounds strange? Yes, it is. Because Connected sheets is not actually a google sheet. This means that if you do not have the respective permissions to the BigQuery project connected to Google Sheets, then you won’t be able to create pivot tables, add charts.
So, you may want to consider a solution without those limitations like connecting BigQuery to Google Sheets using OWOX Reports.
See Your Data Come Alive
Instant charts and pivots at your fingertips
...plus, it's 100% Free!
Bonus: How to Email Reports using Google App Script
In order not to miss important changes in your KPIs, you can configure the sending of reports by email using Google App Script.
Step #1: Prepare or create a Google Sheet and retreive the data you want to include in your report.
Step #2: Access the Apps Script editor within Google Sheets by navigating to ‘Extensions’ –> ‘Apps Script’. Give your project a name in the Apps Script editor.
Step #3: Develop a script function to retrieve data from the sheet.
Format the data for email, either as HTML or plain text. Use MailApp or GmailApp to create a function for sending emails.
Step #4: Execute the function in the script editor to ensure it runs smoothly. Check the recipient's email to confirm the delivery and formatting are correct.
Step #5: Set up a trigger in the Apps Script editor for automatic execution at specific intervals like daily, weekly, etc.
Step #6: Deploy the script for regular use and keep an eye out for any errors or issues that may arise during its operation.
Done! Now your spreadsheet reports will come to your email, you won’t miss anything, and you’ll be able to make changes to your marketing activities in time.
Your Data to Dashboards in 1 Click
Create full reports in Google Sheets in no time!
...plus, it's 100% Free!
FAQ
-
How do I install the BigQuery Sheets Connector?
To install the BigQuery Sheets Connector, simply navigate to the Google Sheets Extensions menu, click 'Extensions', then 'Get Add-ons'. Search for BigQuery and click 'install' on OWOX Reports, Charts & Pivots. Once it's complete, you should see a new OWOX Reports in the Extensions menu in your Google Sheets Extension toolbar.
-
What is the BigQuery Sheets Connector?
The BigQuery Sheets Connector is a tool that allows users to import data from Google Sheets directly into BigQuery, enabling data analysts to easily access crucial information and perform advanced analysis.
-
Can I connect Google Sheets to BigQuery?
Yes, you can connect Google Sheets to BigQuery, enabling access to underlying data directly within Sheets. This integration empowers users to pull raw data into Sheets by specifying a sheet and cell range. -
What are the limitations of BigQuery in Google Sheets?
While there may be limitations in query performance compared to direct BigQuery queries, the integration streamlines workflows and enhances collaboration within Sheets. -
What is the difference between BigQuery and sheets?
The difference lies in the approach: BigQuery focuses on high-performance data analysis at scale, while Sheets offers familiar spreadsheet functionality for data manipulation and visualization. Integrating them allows for seamless data access and analysis within Sheets, complementing BigQuery’s capabilities. -
Can You Connect BigQuery to Google Sheets?
Yes, you can connect BigQuery to Google Sheets using the Google Sheets Data Connector for BigQuery. This allows you to directly access and query your BigQuery datasets from within Google Sheets, enabling real-time data analysis and reporting without leaving the spreadsheet environment. -
How to Save BigQuery Results to Google Sheets?
To save BigQuery results to Google Sheets, use the BigQuery Data Connector in Google Sheets or export your query results to a Google Sheets document via the BigQuery UI. This process can be automated by scripting with Google Apps Script, allowing for regular updates and analysis within Sheets. -
Can You export BigQuery to Google Sheets?
Yes, you can export data from BigQuery to Google Sheets with OWOX Reports. Simply add a query to the library and run it. You can also share that query with your access so business users can run on their own, filter, sort & schedule refreshes. OWOX Automatically inserts the results. of that query into a spreadsheet. This approach allows for automation, such as scheduling the script to run at regular intervals, thus keeping your Google Sheets data up-to-date with the latest information from your BigQuery datasets.
-
What are the benefits of automating reports in Google Sheets using Google BigQuery?
Automating reports in Google Sheets via Google BigQuery saves time, ensures real-time data access, enhances accuracy, enables complex analysis, and fosters collaboration. It streamlines processes, updates reports seamlessly, reduces errors, leverages data power, and facilitates team communication for more informed decision-making.