A Cloud Function to automate Google Spreadsheet CSV import

Update: there is now a step-by-step codelab for this post (last updated May 2020).

Sure, you can paste or import CSV content to Google Sheets but wouldn’t it be nice to automate the process and thus be able to update a sheet with the content of a file as it’s uploaded to a bucket? This would ensure that you can analyze data (maybe produced by another team) in a spreadsheet as soon as it’s available :

In this post I’ll walk you through how to write a Cloud Function that reacts to a CSV file upload to a Cloud Storage bucket and then updates one of your spreadsheets with that CSV data using the Google Sheets API.

This is what the implementation looks like :

Just a bit of (security) setup

First, let’s create a new Sheets document. Once created, remember its identifier; it will be used as an environment variable for the function we’ll write :

From the GCP console, create a new project and enable the Google Sheets API (follow the link or go to the Console “APIs and Services” section) :

In the “IAM & admin” section, navigate to “Service accounts” and note the Email for the App Engine default service account. It should be of the form your-project-id@appspot.gserviceaccount.com. Of course you can also create your own service account dedicated to this action.

Now simply grant this service account edit privileges to your spreadsheet :

At this point a Cloud Function configured to use this service account will be able to request write access to this spreadsheet document.

Create the Cloud Function

We can now create a Cloud Function called csv2sheet that is triggered on file uploads to a specific Cloud Storage bucket. The code will be written in Node.js 8 with async functions.

Change the function body to use the Cloud Storage and Sheets APIs, mark thecsv2sheet function as async, and get a hold of the fileName from the Cloud Storage event metadata and derive a name for the new sheet that we'll create :

The use of async here is required to use await as we’ll see in a moment.

A couple of important options for this function include :

• the service account used (which should be the same as discussed above)
• an environment variable called SPREADSHEET_ID that should match the sheet document that you have created previously :

As a final setup step, here is the package.json content with the Cloud Storage and Google Sheet APIs as the two dependencies we’ll use :

Once you’ve configured everything as described, go ahead, click “Create Function” !

If you’d rather jump straight to the full code, it’s available here. Otherwise, follow along!

Auth

Before we code any further we need to block on the creation of a Google Client API with the proper Storage and Sheet scopes (remember, this is part of an async function) :

From there we can create a Sheets API client :

Create the empty sheet

With a Sheets API client we can create a simple new sheet in our document but before we go any further, here’s a quick note on vocabulary:

• a spreadsheet is the actual document and is referenced by its identifier (discussed above and visible in the document URL)
• a sheet is one of the tabs in the document and it can be referenced by its name (the tab name) or an identifier generated upon sheet creation

With this in mind, here’s a function using the Sheets API client to create an empty sheet at position 2 (typically after the default “Sheet1”), with 26 columns, 2000 rows, with the first row frozen :

Note how instead of hard-coding the reference to the spreadsheet we rely on the previously-created SPREADSHEET_ID environment variable.

We need to remember the sheetId for further requests made to this particular sheet. Also, the sheet name needs to be unique and the creation will fail if there is already a sheet called sheetName.

The batchUpdate function in the Sheets API is a common way to interact with documents and is described here.

Read data from a storage CSV file

Now that we have somewhere to dump our data, let’s use the Cloud Storage API to go and grab the actual data from the file that was just uploaded and store it in a string:

Populate newly created sheet

It’s now time to populate the sheet we’ve created using the same Sheet client API and the data we’ve just collected. We’ll take this opportunity to also add some styling to the sheet’s columns (changing the font size of the top row and making it bold) :

Note how both data and styling are combined as with multiple requests into a single Sheets API batchUpdate call. This makes for a more efficient and an atomic update.

Note also that we define an edit range that matches the size of the sheet we’ve created. This means that content that exceeds 26 columns will fail with this particular code.

If all goes well, at this point you can:

1. save the updated function
2. drop a CSV file into the bucket
3. see the corresponding data pop up in your spreadsheet!

Putting it all together and testing the flow

The calls to the functions we’ve just discussed can be made as follows in the csv2sheet function: block on the creation of the new sheet and then on the data retrieval from Cloud Storage. Then populate the sheet with the data and modify the sheet’s style:

Once everything is in place, simply upload a CSV file to the right bucket and watch your spreadsheet be updated with a new sheet with the file’s content. Here’s a sample CSV file if you don’t have one handy

Try uploading several files to the bucket to see what happens!

The complete function source code is available here.