Loading Google Sheets content in 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.

My first contribution

The problem: At work we have access to a Google Sheets spreadsheet with data that is refreshed on a daily basis. Unfortunately, we are not in control of the spreadsheet. So if we want to safe each day’s content we must copy it to another sheet. However, if you do this on a daily basis, 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 load a CSV file into Big Query using Google Apps Scripts. There are also several blogs about loading BigQuery results in a Google Sheets spreadsheet. Blogs and examples about loading the content of a Google Sheets spreadsheet into Google BigQuery are more sparse though. That’s why I wrote something based on two examples that sort-of do what I was looking for, but not exactly.

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.