Connecting BigQuery and Google Sheets to help with hefty data analysis

As enterprises amass terabytes of complex data, they need tools to house and make better sense of their information. This is why we’ve built BigQuery, to help data analysts deal with large datasets. But not all of us are data wizards. Many of us use spreadsheets to perform ad-hoc analysis.

We want to make it easier for employees outside of data analytics functions to run queries, like analyzing sales data, so we’re combining the power of BigQuery and Google Sheets to make that possible. The Sheets data connector for BigQuery will be generally available to G Suite Business, Enterprise and Education customers in the coming weeks.

With the Sheets data connector for BigQuery, you can analyze and share large datasets from BigQuery right from within your spreadsheet. Connect up to 10,000 rows of data from BigQuery into Sheets (with a simple SQL statement that you can get from a data analyst), and analyze it using the Explore feature, or by creating charts or pivot tables, in your spreadsheet. As always, you can control share permissions to limit who can view, edit or share your data. Those with “edit” access in Sheets and the BigQuery table have the ability to refresh the dataset.

You can also use the data connector to:

• Collaborate with partners, analysts or other stakeholders in a familiar spreadsheet interface. 
• Ensure a single source of truth for data without having to create additional CSV exports.
• Streamline your reporting and dashboard workflows.

Yahoo! data analysts have found the connector helpful in simplifying workflows. Says Nikhil Mishra, director of Engineering at Verizon Media, “The data connector for BigQuery saves our analysts time and errors by reducing multiple steps otherwise needed to get data into spreadsheets.”

Take it a step further

Doing analysis in Sheets is most useful when the data is up-to-date. In the coming weeks, you’ll be able to use tools like Apps Script and the macro recorder to schedule automatic updates to the connected BigQuery data. For example, you can automatically refresh data in your spreadsheet so that when you turn on your computer at the beginning of your day, your data is fresh and ready for analysis. If you’re curious about how to record or run macros in Sheets, check out this G Suite Pro Tip to learn more.