Posts

Posted on

Pushing Google Sheets content to BigQuery

I’ve decided to dust off my blog. After several years of neglect, I thought it would be nice to try to do something useful with it again. For my little coding projects, I combine and use lots of code from others. I guess the least I can do is try to share some of my code and ideas in return.

But first I should probably warn you: I’m not an educated developer. Most of my coding skills are self-taught through websites such as CodeCademy, Udemy, and DataCamp, among others. So if you have any remarks, improvements or tips, please feel free to reach out.

How to push data from Google Sheets to Google BigQuery

The problem: At work, we have access to a Google Sheets spreadsheet with data that is refreshed daily (our agency Google Ads HI Score spreadsheet for those who are familiar with it). Unfortunately, we are not in control of the spreadsheet. So if we want to save each day’s content we must copy it to another sheet. However, if you do this daily, you quickly reach limits such as the maximum number of cells a spreadsheet can contain. That’s where Google BigQuery comes in.

The solution: With Google Apps Scripts we can access the BigQuery API to push data to BigQuery. We can also use Google Apps Scripts to access the content of Google Sheets. By combining these two it’s possible to push data from Google Sheets directly to Google BigQuery.

There are several examples to be found on how to push a CSV file to Big Query using Google Apps Scripts. There are also several blogs about downloading BigQuery results to a Google Sheets spreadsheet. We want to do the opposite though, and blogs and examples about pushing the content of a Google Sheets spreadsheet to Google BigQuery are more sparse. At least, I didn’t find any (free) useful examples. That’s why I wrote something that is based on two examples that sort-of do what I was looking for, but I adapted them to my needs.

I used these examples:

This script can be used as a Google Apps Script. The easiest way is to open the Google Sheet and go to the script editor through the menu bar.

In the script editor, you must enable the BigQuery API through the Advanced Google Services. You can then set a schedule for the Google Apps Script to fetch the content of the Spreadsheet and push it to Google BigQuery.

You can find the code below or check the repository at GitHub.