I've been working on personal projects that leverages Google Sheets for some time now and for multiple reasons: spreadsheets are super easy to create, view and edit for anyone. You can share them to the public and anyone would be able to see what you built or what you're working on.

My experience browsing the internet for resources on how to use Google Sheets programmatically was not what I expected though. Most of the examples I could find are focused on reading from spreadsheets, not writing onto them.

In this article I'll explain how you can create an AWS Lambda function using serverless from scratch that writes into a Google Sheet and will cover some of the pain points I've found.

Table of contents

1. Create a new project
2. Set up authentication
   • Creating your service account
   • Share the document
   • Set up your environment variables
3. Implement the write function
4. Add a read function
5. Deploy it
6. Utils and room for improvements

Prerequisites

• You will need a Node.js version installed (v12.x is recommended)
• Serverless framework installed (npm i serverless -g)

1. Create a new project

We will use the CLI from serverless to bootstrap a new project. Run the following command to create a spreadsheet-example directory in the current folder:

serverless create --template aws-nodejs --path spreadsheet-example

Next we will add a new function called write with a POST endpoint as trigger in the serverless.yml that the CLI just created for us:

functions:
-  
+   write:
+    handler: handler.write
+    events:
+      - http:
+          path: /write
+          method: POST

Also add to the handler.js file:

module.exports.write = async event => {
  console.log('Starting write function');
}

A practical way to check we're on the good path is to execute sls invoke local -f write and verify the log is printing the correct message.

2. Set up authentication

I've read the documentation around Google Sheets and their google-apis library but still got frustrated from the lack of clarity on authentication mechanisms.
After some troubleshooting, I've found that using a Service Account is enough to get write permissions. For the sake of simplicity in these code examples I'll switch into using google-spreadsheet which abstracts a lot of the nitty-gritty details from Google's library.

2.a Creating your service account

Go to the Service Accounts page. You can choose an existing project or create a new one. Click on "Create Service Account" and enter a name for your new account.

You won't need to change anything for steps 2 and 3. Once it's created you should be able to see your new account on the list. Click on it and select "ADD KEY > Create new key".

After doing it a JSON file should be automatically downloaded, we will use it in the following steps.

2.b Share the document

After creating the service account you will also need to share the spreadsheet with said account.

You will have to do this for every spreadsheet you want to have access from the service account (an ideal world would have one service account per spreadsheet, but who am I to judge you?).

2.c Set up your environment variables

Now that your service account is fully configured you will want to use two fields from the credentials file: client_email and private_key. Create a new .env.yml replacing these two fields from the JSON file that was downloaded in the step 2.a:

GOOGLE_SERVICE_ACCOUNT_EMAIL: spreadsheet-example@yourexample.iam.gserviceaccount.com
GOOGLE_PRIVATE_KEY: "-----BEGIN PRIVATE KEY-----top-secret-do-not-share-----END PRIVATE KEY-----\n"

and include it in your serverless.yml like this:

provider:
  name: aws
  runtime: nodejs12.x
-
+  environment: ${file(.env.yml):}

We will abstract the usage of these variables from our handlers with the spreadsheetAuth function:

function spreadsheetAuth(document) {
  return document.useServiceAccountAuth({
    client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
    private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/gm, '\n'),
  });
}

3. Implement the write function

At this point we should be able to run our serverless function and also have authentication sorted out. In the next handler we're adding a few checks for the input of the function and we will always write to sheet with the index 0, but it could also be sent as parameter.

module.exports.write = async event => {
  console.log('Starting write function');

  if(!event.body) {
    return formatResponse(400, { message: 'body is missing' });
  }
  const body = JSON.parse(event.body);

  if(!body.cells || !Array.isArray(body.cells)) {
    return formatResponse(400, { message: '"cells" should be an array' })
  }

  // load up everything that's necessary to work with cells
  await spreadsheetAuth(doc);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0];
  await sheet.loadCells();
  for(const { identifier, content } of body.cells) {
    const cell = sheet.getCellByA1(identifier);
    cell.value = content;
  }
  await sheet.saveUpdatedCells();
  return formatResponse(200, { message: 'Cells saved successfully'});
};

4. Add a read function

Since we're already writing, why not offer the ability to read cells from the spreadsheet too? Modify the serverless.yml file and append at the end of the file the following:

  read:
    handler: handler.read
    events:
      - http:
          path: /read
          method: GET

and add to our handler.js file the next method:

module.exports.read = async event => {
  console.log('Starting read function');
  if(!event || !event.queryStringParameters || !event.queryStringParameters.cells) {
    return formatResponse(400, { message: 'Invalid parameters' });
  }

  const cells = event.queryStringParameters.cells;

  await spreadsheetAuth(doc);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0];
  await sheet.loadCells();
  const contents = cells.split(',').map(cell => sheet.getCellByA1(cell).value);
  return formatResponse(200, { contents });
};

5. Deploy it

In order to test it you can deploy it with sls deploy. If the deployment was successful you should be getting two endpoints within the terminal output. You can use curl to test it out:

$ curl --location --request POST 'https://your.own.endpoint.com/write' \
--header 'Content-Type: application/json' \
--data-raw '{
    "cells": [{"identifier": "A1", "content": "hello"}, {"identifier": "A2", "content": "world"}]
}'

If you want to both test the read function and verify the write went as expected you could run:

$ curl --location --request GET 'https://your.own.endpoint.com/dev/read?cells=A1,A2'

6. Utils and room for improvements

To avoid duplicating a few lines of code in the functions we discussed here I abstracted the response parsing into the formatResponse function. Here's how it looks like:

function formatResponse(statusCode, payload) {
  return {
    statusCode: statusCode,
    body: JSON.stringify(
      payload,
      null,
      2
    ),
  };
}

There's still parts that could be abstracted too into reusable components and for a real-world scenario you could also consider moving the read and write handlers into different files.

I've covered a very small example and there are many ways on how it could be improved. There are a few cases for error handling I've intentionally left out the code examples for simplicity like JSON parsing errors. I pushed these files to a repository if you want to check it out. For more complicated examples on how to edit the formatting of the cells, adding borders and removing columns you could check out these other projects I've pushed on GitHub:

• dolar-historico
• weather-daily

Finally, I'm convinced a lot of the documentation on this topic makes it seem harder than it actually is. If you're using spreadsheets as data-store or you worked on a side project that integrated with spreadsheets on your own I would love to hear about it in the comments.