The goal of this codelab is for you to understand how to write a Cloud Function to react to a CSV file upload to Cloud Storage, to read its content and use it to update a Google Sheet using the Sheets API.

This can be seen as the automation of an otherwise manual "import as CSV" step. This would ensure that you can analyze data (maybe produced by another team) in a spreadsheet as soon as it's available.

This is what the implementation looks like :

Self-paced environment setup

If you don't already have a Google Account (Gmail or Google Apps), you must create one. Sign-in to Google Cloud Platform console (console.cloud.google.com) and create a new project:

Remember the project ID, a unique name across all Google Cloud projects (the name above has already been taken and will not work for you, sorry!). It will be referred to later in this codelab as PROJECT_ID.

Next, you'll need to enable billing in the Cloud Console in order to use Google Cloud resources.

Running through this codelab shouldn't cost you more than a few dollars, but it could be more if you decide to use more resources or if you leave them running (see "cleanup" section at the end of this document).

New users of Google Cloud Platform are eligible for a $300 free trial.

First, let's create a new Sheets document (this sheet can belong to any user). 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.

Finally, simply grant this service account edit privileges to your spreadsheet using the "Share"
button :

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

Let's create the bucket which our cloud function will monitor for CSV files. In the console, use the left-hand menu to navigate to "Storage"... :

... and create a new bucket called function2sheet-postfix (replace the postfix by something unique) with all other settings set to their default values :

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

Make sure to select the Trigger to "Cloud Storage" and to adjust the bucket name to the one you've created in the previous step.

Now change the function body to use the Cloud Storage and Sheets APIs and mark the function as async:

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.function2sheet = async (req, res) => {
  // TODO!
};

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

A couple of important options while creating this function include (click the "More" link at the bottom of the screen) :

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 :

{
    name: "function2sheet",
    version: "0.0.42",
    dependencies: {
        googleapis: "^35.0.0",
        @google-cloud/storage: "^2.3.1"
    }
}

Once you've configured everything as described, go ahead, click "Create" ! After a short minute your function should be created and deployed.

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). Click on "EDIT" and add the following code to the body of your function :

const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

From there we can create a Sheets API client :

const sheetsAPI = google.sheets({version: 'v4', auth});

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:

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 :

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

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.

Now that we have somewhere to dump our data, let's go and grab the actual data from the file that was just uploaded and store it in an array. Here we assume that the content is indeed comma-separated which means that undefined things will happen if it's not :

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    let rows = [];
    storage
      .bucket(file.bucket)
      .file(file.name)
      .createReadStream()
      .on("error", function(err) {
        reject("The Storage API returned an error: " + err);
      })
      .on("data", function(chunk) {
        fileContents = Buffer.concat([fileContents, chunk]);
      })
      .on("end", function() {
        let content = fileContents.toString("utf8");
        let lines = content.split(/\r\n|\r|\n/);
        lines.forEach(function(line) {
          rows.push(line.split(","));
        });
        console.log("CSV content read (" + rows.length + " rows) from " + file.name);
        resolve(rows);
      });
  });
}

This is a pretty basic implementation of parsing CSV data. You are free to implement other formats and store the resulting data in an array of rows.

It's now time to populate the sheet we've created using the same Sheet client API and the data we've just collected :

function populateSheet(sheetsAPI, theData, sheetName) {
  return new Promise((resolve, reject) => {
    const editRange = sheetName + "!A1:Z";
    const initTrixHeadersParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: editRange,
      resource: {
        range: editRange,
        majorDimension: "ROWS",
        values: theData
      },
      valueInputOption: "RAW"
    };
    sheetsAPI.spreadsheets.values.update(initTrixHeadersParams, function( err, response ) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetName + " sheet populated with " + theData.length + " rows");
        resolve();
      }
    });
  });
}

Note 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:

If we assume that the first row is made up of headers we can add some formatting to the process, such as changing the font size of that row and making it bold :

function setColumnStyle(sheetsAPI, sheetId) {
  return new Promise((resolve, reject) => {
    const setStyleParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate(setStyleParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          console.log("Colums style set for sheetId: " + sheetId);
          resolve();
        }
      }
    );
  });
}

We could similarly resize the columns in the same batchUpdate call, an exercise left to the reader !

The calls to the functions we've just discussed can be made as follows in the function2sheet 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:

const sheetId = await addEmptySheet(sheetsAPI, sheetName);
const theData = await readCSVContent(sheetsAPI, data, sheetName);
await Promise.all( [
  populateSheet(sheetsAPI, theData, sheetName),
  setColumnStyle(sheetsAPI, sheetId)
]);

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.

Just kidding, there is no infrastructure to tear down, this was all done serverless-ly !

You can, if you'd like, delete the cloud function and the bucket that you've created.

This concludes this codelab walking you through steps to listen to uploads to a Cloud Storage bucket in a Cloud Function to update a Google Sheet using the appropriate API.

Here are some follow-up steps :

/