Move Data from Google Sheets to BigQuery

How do you link BigQuery with Google Sheets?

Importing a table from Google BigQuery to Google Sheets is quite easy. In fact, you can connect BigQuery tables directly to the Google Sheets spreadsheet with a couple of clicks using ​​Connected Sheets, the new Google BigQuery data connector (check the full instructions here).

But what if you want to move data the other way around? That is, how do you send Google Sheets data to your Google BigQuery data warehouse?

Now your life becomes a bit more complicated.

But you can simplify it in two ways: You can use Keboola to streamline the entire data operation, or move data manually (more time-consuming).

Advice for the busy person on a tight schedule: both methods have pros and cons. 

• Manual import is best if you need to import a single Google Spreadsheet only once. 
• Keboola is your best option if you need to import the same spreadsheet multiple times, import multiple spreadsheets at the same time, or import other data sources (not just Google Sheets) into BigQuery. 

It doesn't matter what option you choose -  we will guide you through steps for both methods in this blog:

• Using Keboola to move data from Google Sheet to BigQuery (set it once, use it forever)
• Manual data transfer (set it once, use it once)

Before you start

To move data from Google Sheets to BigQuery you need to have:

1. A Google Drive account. You can make one for free. This is where your Google Sheets spreadsheets are made.
2. A BigQuery project. You can get one for free on the Google Cloud Platform (GCP). GCP offers 300 free processing minutes to every new account, so you can try and test it. Simply create a Google Cloud Project within the GCP platform and it will create a Google BigQuery warehouse for you.

Method 1: Streamlined connection with Keboola

Keboola is a data platform as a service that automates your data operations. One of these operations is moving data from Google Spreadsheets to BigQuery. 

Let’s see how easy it is.

Step 1: Log into Keboola

Go to https://connection.north-europe.azure.keboola.com/wizard and log into your Keboola account. No account? No problem! You can create one free of charge and without swiping your credit card. 

Step 2: Create a new data pipelin

We’ll use the Visual Flow Builder to create a data pipeline that extracts data from Google Sheets and sends it to Google BigQuery.

In Keboola, navigate to Flows > Flows > New Flow.

A window will pop up to name and describe your new flow.

Once you click “Create flow”, you’ll be redirected to the Visual Flow Builder, a drag-and-drop interface to set up your data pipelines. Time to configure our data pipeline.

Step 3: Configure the data pipeline

Start by clicking “Select first step” and select “Google Drive” as your incoming data component.

The wizard will guide you through two simple configuration steps: (1) authorization (aka, let Keboola access your Google Drive account) and (2) table selection (aka, pick the spreadsheet you want to import). The final result will look like this:

Now we return to the visual flow builder and we add a data destination - Google BigQuery.

The wizard will guide us through configuring BigQuery, so Keboola can write data to it. You’ll create a service account and link it to Keboola and create a new dataset, where your data can be imported (as we did below, “wine_dataset”), or choose an existing one. 

Step 4: Run the data pipeline

Click “Run flow” to send the data from Google Sheets to BigQuery. Or select “set schedule” to run the data pipeline repeatedly on a schedule.

Is Keboola the right choice for importing Google Sheets data into BigQuery

Pros:

• Faster setup. Compared to the manual method, Keboola involves less clicking around and is much more streamlined.
• Intuitive and user-friendly. The visual drag-and-drop editor is much more understandable than the manual clicking around BigQuery. A wider scope of users can self-serve their data integration needs. 
• Scalable. Keboola can easily scale your operations. You can add multiple Google Spreadsheet tables to a single data import, and the integration can scale with higher data volumes and can be scheduled.
• Extendable. Google Spreadsheets and BigQuery are just two options for the nodes of the data integration. You can extend the connectivity to multiple sources (e.g. Facebook Ads data, Google Analytics data, CSV imports, …) and to other data destinations (AWS Redshift, Postgres, Azure, …). Keboola offers over 250+ data sources and destinations that you can connect with a couple of clicks.
• Free. Keboola offers a free account with 300 free minutes every month. So you can service your data integration at no additional cost to you.
• Automated. With Keboola, you can run your integrations on a schedule. Simply set the data pipeline and forget about it, Keboola takes care of the heavy lifting for you.

Cons:

• Some technical expertise is needed. To set up the connection, the user needs to configure a service account in Google BigQuery. Though this requirement is much easier than all the technical prerequisites of integrating data manually, this step can still pose a challenge and require technical assistance from a more technically-savvy user.

Method 1: Manual data transfer

Let’s run through the entire process and then evaluate the pros and cons of moving data manually.

Step 1: Navigate to your BigQuery account

Go to https://console.cloud.google.com/bigquery and select the project where you’d like your Google Sheets data to be imported. In our case, the project is named “My Project 41325”, a random default naming assigned by GCP.

Step 2: Connect Google Drive with BigQuery

Click “Add data” and select “Google Drive” under additional sources. 

Step 3: Configure Google Drive as the data source

Once you start connecting your Google Drive, you will have to manually configure a lot of fields:

1. Keep “Drive” as the default for the “Create table from” field.
2. Copy and paste your Google Sheets spreadsheet URI (that’s the web address in your browser when you open the sheet).
3. Change file format to “Google Sheet”
4. Optimally: indicate which sheet from the Google Sheet document should be imported or what range of cells should be imported.

Step 4: Configure BigQuery as the data destination

BigQuery does not automatically generate a new table when you import data. First, you have to manually create an empty new table, then BigQuery inserts the new dataset into the table.

Start by selecting “Create new dataset” under the “Dataset” field.

A new window will pop up asking you how to configure your dataset:

1. Create a unique name for this dataset. In our example, it’s called “Ideal_Customer_Profiles_Google_Spreadsheet_Import”. Make sure you remember this name because you will need it in future steps.
2. Choose the data location. Preferably, your data will be located where the other data in your BigQuery account is located to lower costs and keep compliance high. a)Optionally, you can enable table expiration. Specify in how many days the data should be deleted by BigQuery.
3. When satisfied, click the “create dataset” button.

Finish up:

1. Name the table that will hold your new dataset in the “Table” field. We used the same name as for the dataset specified in the previous section to keep things tight.
2. Specify the type of table to “External table” (selected by default).
3. Make sure to check the box next to schema autodetection. Schema refers to the data types found in the columns of your dataset (string, numbers, …). BigQuery uses its own data types to speed up SQL queries and apply background tests. So we need to specify a mapping between Google Sheets data types and BigQuery data types. Luckily BigQuery’s schema auto-detect feature works great at inferring the underlying schema.
4. Lastly, click the “create table” button.

Step 5: Check your data import

BigQuery creates a table in the project you specified beforehand. You can now access the table and perform data analysis or cleaning (for example, pivot tables).

Is manual import the right choice for importing Google Sheets data into BigQuery

Importing data from Google Spreadsheets to BigQuery has advantages and disadvantages. 

Pros:

• (Almost) free of charge. You incur no additional costs (licensing fees, surcharge, …) by importing this manually, other than the data ingestion costs by BigQuery.
• Native integration. Both Google Spreadsheets and BigQuery are Google’s products and part of Google’s infrastructure. So the connection is natively supported. 

Cons:

• Extremely time-consuming. The entire operation is a clickathlon. We summed it up in 5 neat steps, but as you can see, each step has multiple substeps.
• Does not scale. When you have 1 spreadsheet to import, the manual method can be your friend. But if you are importing tens or hundreds of spreadsheets, the system does not keep up. You will spend some lonely afternoons clicking around.
• Governance challenges. Permissions for imports need to be set on a per-sheet basis. This can be good, as it lowers your security concerns. Or it can be an additional step in the time-consuming import.
• Not user-friendly for non-technical users. The import assumes the user has access to BigQuery and is familiar with its architecture (projects > BigQuery > tables) as well as its configuration (location of data storage). This can be challenging for non-technical users. Ultimately, this means the method is appropriate for tech-savvy users only, or that non-technically gifted people need to depend on the company’s IT resources.

There is a faster, more scalable, and much more user-friendly way of moving data from Google Sheets spreadsheets to BigQuery. Use Keboola.

Connect your Google Sheets to BigQuery today, for free

You can start moving data between Google Sheets and BigQuery today. With Keboola, the entire setup takes minutes, is user-friendly, and scales with your needs.

Did we mention you can do it for free? 

Keboola offers an always free account with 300 free processing minutes every month. So you can move data from Google Sheets to BigQuery without breaking your piggy bank.

Try Keboola today.