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:
- https://www.lunametrics.com/blog/2017/07/26/connect-google-analytics-data-tools-via-bigquery/
- https://developers.google.com/apps-script/advanced/bigquery
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.
hey mate,
this is awesome work! Thank you. There is one issue i encountered when i used our code.
If there is a cell in the spreadsheet where commas exists, then the construction of csv would make additional columns, which will be an issue when appending.
Here is my solution:
var file = SpreadsheetApp.openByUrl(url).getSheetByName(sheetName);
// new methods, first cell from “A15”
var rowsLast = file.getLastRow().toString();
var colsLast = String.fromCharCode(file.getLastColumn() + 64);
var range = file.getRange(“A15:”.concat(colsLast, rowsLast));
// replace all commas to dot
var textFinder = range.createTextFinder(“,”);
var occurance = textFinder.replaceAllWith(“.”);
Logger.log(occurance);
var rows = range.getValues();
var rowsCSV = rows.join(“\n”);
var blob = Utilities.newBlob(rowsCSV, “text/csv”);
var data = blob.setContentType(‘application/octet-stream’);
But apart from that, your work is amazing. Thank you!
Hello,
Thank you for sharing this script! It run successfully for me and creates the table, however, there’s no data in the BQ table. I’m using the same writedisposition and I don’t receive any error messages. Do you have any idea what might be wrong?
Hi Mike,
It’s nice to see that this blog from 2018 is still helpful! One thing I can think of that might cause the issue you’re describing is that the data in your spreadsheet does not start in cell A1? My code example uses
.getDataRange()to get all the data from the sheet and this method assumes that the data starts cell A1. See https://developers.google.com/apps-script/reference/spreadsheet/sheet#getdatarange for more info about this. I hope this helps!Hi Wouter,
Thanks for very helpful tutorial for passing data from GS to GBQ. I got the following issue when using DATE type field with Google Cost data despite the fact that the type of the field is really date (here is the list of data passing to GBQ https://www.dropbox.com/s/ste2xbspx7cwupi/Screenshot%202023-04-12%20at%2018.43.43.png?dl=0)
“Error while reading data, error message: Could not parse ‘Wed Nov 09 2022 00:00:00 GMT+0100 (Central European Standard Time)’ as DATE for field ga_date (position 0) starting at location 77 with message ‘Unable to parse'”,”reason”:”invalid”
I ll be grateful for any thoughts / hints
Hi Andrew,
Sorry for the late reply. The screenshot is from GBQ, right? But the error is from the Google Sheets script. Perhaps the date in the Sheet is not accepted as a date? It might also help to set the GS ‘Locale’ setting to ‘United States’. I hope this helps!