Transfer data from BigQuery to Google Sheets automatically

Growing businesses usually turn to cloud-based storage solutions for their growing data needs. BigQuery is an increasingly popular option for data management.

With BigQuery’s data warehouse, businesses can centralize their information in one place. Moreover, they can access and analyze this data at any time.

BigQuery can be easily integrated with Google Sheets. This enables organization members to collaborate on projects using a familiar tool while also accessing a live data stream from BigQuery.

First, we’ll explore BigQuery in more detail. Then we’ll go step by step through two ways to connect BigQuery to Google Sheets: Google’s Connected Sheets, and a scheduled transfer with Sheetgo, the AI-powered data automation platform.

What is BigQuery?

BigQuery is one of Google’s many products, it is a data warehouse storage system that enterprises can use to store all of their data.

Its huge processing power allows BigQuery to carry out rapid SQL queries whenever you need them. Furthermore, under Google’s infrastructure, your data in BigQuery is constantly secure and accessible, perfect for project collaborations within organizations.

For a longer introduction, read What is BigQuery?

Why connect Google Sheets to BigQuery?

As part of Google’s products, BigQuery can easily be integrated with Google Sheets. This has two main advantages:

1. Connected sheets have all of the features and tools Google offers.
2. Google Sheets can turn your data into powerful visual representations.

To learn more about Google Sheet’s analytical tools for data, read How to analyze BigQuery data within Google Sheets.

Connecting BigQuery to Google Sheets lets you tap into huge amounts of data, and a Sheetgo automation ensures this data is always updated.

An example

You want to import data on current Google search trends into a spreadsheet.

Connecting Google Sheets to a BigQuery database using Sheetgo would let you select updated trends data and analyze it in Google Sheets.

This powerful combination would help you identify the most popular trends or predict search trends to optimize your business’ marketing.

How to connect BigQuery to Google Sheets

There are two ways to connect BigQuery to Google Sheets:

• Manually through Google Sheets’ data menu.
• Automatically using Sheetgo.

Watch the video below or continue reading to learn both approaches.

Use Google’s connected sheets

This Google Sheets feature links a sheet to a BigQuery table and can refresh the data on a schedule. You need access to BigQuery and a Google Cloud project with billing set up.

To connect a BigQuery table from Google Sheets:

1. Go to Data > Data connectors > Connect to BigQuery.

2. You will see a message like the one below. Click on “Get connected”.

3. The next pop-up will ask you to choose a cloud project.

4. Next, you need to select a dataset in that project. In my example, I will choose a public dataset, “census_bureau_usa”.

5. Once you have selected your dataset, you can choose a table or view, then click on “Connect”.

6. You will see a message once the connection is made.

7. That’s it. You can comfortably analyze your dataset from Sheets, even though it’s a large file since the data is not actually loaded into Sheets.

The sheet shows a preview of 500 rows, but charts, pivot tables, and functions you build on it use the whole table. To update the data, click Refresh options. You can refresh now or set a schedule. Only people with BigQuery access can refresh. Others you share the file with can still view and analyze the data.

To work with the rows themselves, click Extract. An extract copies up to 50,000 rows into a regular sheet, or up to 500,000 rows as long as it stays under 5 million cells.

Open a BigQuery table in Google Sheets from the BigQuery console

You can also start from BigQuery. In the Google Cloud console, open BigQuery and go to the Explorer pane. Expand your project and open the dataset. Next to the table name, click View actions, then select Open in > Connected Sheets. A new spreadsheet opens with the table connected.

Connect BigQuery to Google Sheets automatically using Sheetgo

Although the methods covered above are an effective way to connect your data, Sheetgo offers other features that provide more advantages.

These are just some benefits of using Sheetgo to connect BigQuery to Google Sheets:

1. Unlimited rows of data: Connected Sheets shows a preview of 500 rows, and an extract copies at most 500,000 rows into the sheet. Sheetgo allows you to import unlimited rows of data.
2. Current data for everyone you share with: In Connected Sheets, only people with BigQuery access can refresh the data or schedule a refresh. A scheduled transfer writes the data into a regular spreadsheet, so everyone who opens the file sees the latest numbers.
3. Experience spreadsheet behavior: Sheetgo’s functions and features lets you experience native spreadsheet behavior, unlike static results returned to your sheet by Google’s integration.
4. Build on your workflow: Instead of a one-off import, Sheetgo lets you continue building workflows with multiple automations using imported BigQuery data.

Now that we explored the benefits of using Sheetgo over Google’s in-built function, let’s look at the step-by-step instructions of how to connect BigQuery to Google Sheets automatically through Sheetgo.

Step 1: Sign up for Sheetgo

Sign up for Sheetgo using your Google account.

Step 2: Create workflow

Within Sheetgo, click New, then select Blank workflow.

Click Create an automation.

Step 3: Select your source

To choose your data source, scroll down to Databases and select BigQuery.

Click Grant permission so Sheetgo can connect to your BigQuery account.

Step 4: Find your BigQuery Project

Next, select your Bigquery Project, Dataset name, and Table or View. This will determine the data you are using.

Include a Query to pick the exact data you want to transfer.

Once you are happy with this, press Next step.

Step 5: Select a destination

It’s time to connect this dataset to Google Sheets. Select Google Sheets as your destination.

Name your new file.

When you’re done configuring your automation, press Finish and save.

Step 6: Automate your workflow

After saving your workflow, you can see a visual representation in the workflow view.

Under Triggers on the right sidebar, set up a schedule for your workflow to run on.

Sheetgo will automatically transfer data from BigQuery to Google Sheets at the frequency of your choice.

Your team then reads the results in Google Sheets instead of each person running the same query in BigQuery, which can help you cut BigQuery costs.

Going the other way: Google Sheets to BigQuery

This guide moves data from BigQuery to Google Sheets. To send spreadsheet data the other way, BigQuery can read a Google Sheets file directly:

1. In BigQuery, open the dataset that should hold the data, or create one from your project’s menu. Then click Create table.
2. Under Source, set Create table from to Drive. Paste the spreadsheet’s URL as the Drive URI and choose Google Sheet as the file format.
3. Under Destination, name the table and keep External table as the table type. Tick Auto detect to let BigQuery read the schema, or enter the columns yourself.
4. Click Create table. Each query on this table reads what’s in the spreadsheet at that moment.

An external table leaves the data in Google Drive, so anyone who queries it needs access to the spreadsheet as well as to BigQuery. To copy sheet data into BigQuery tables on a schedule instead, see how to build BigQuery pipelines in spreadsheets with Sheetgo, the AI-powered data automation platform, which can also append spreadsheet data to a BigQuery table.

Final thoughts

As you can see, Sheetgo is a great option for businesses to connect BigQuery to Google Sheets.

It has numerous advantages over the native feature allowing you to build automations.

In fact, Sheetgo can transfer data from BigQuery data to Excel, CSV, or TSV files. Read more about How to connect BigQuery data to Excel automatically