Writing to a Google Sheet using serverless
Tags: serverless, javascript, node, spreadsheets
Published: 2020-07-24 18:09:58

---
title: Writing to a Google Sheet using serverless
published: true
description: Steps to automate writing into Google Sheet
tags: serverless, javascript, nodejs, spreadsheets
cover_image: https://imgur.com/Jf69qRG.png
---

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](https://www.serverless.com/) 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](#1-create-a-new-project)
2. [Set up authentication](#2-set-up-authentication)
  * [Creating your service account](#2a-creating-your-service-account)
  * [Share the document](#2b-share-the-document)
  * [Set up your environment variables](#2-c-set-up-your-environment-variables)
3. [Implement the write function](#3-implement-the-write-function)
4. [Add a read function](#4-add-a-read-function)
5. [Deploy it](#5-deploy-it)
6. [Utils and room for improvements](#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:
```diff
functions:
-  
+   write:
+    handler: handler.write
+    events:
+      - http:
+          path: /write
+          method: POST
```
Also add to the `handler.js` file:
```js
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](https://www.npmjs.com/package/googleapis) 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](https://www.npmjs.com/package/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](https://console.cloud.google.com/iam-admin/serviceaccounts). You can choose an existing project or create a new one. Click on "Create Service Account" and enter a name for your new account.
![Screenshot from GCP's IAM panel](https://dev-to-uploads.s3.amazonaws.com/i/znqu4xq2g8st6rh1z9sv.png)
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".
![Screenshot from GCP while adding a new key](https://dev-to-uploads.s3.amazonaws.com/i/v2fu038xgv0i670hi0xe.png)
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. 
![Screenshot from the "Share" menu in Google Sheets](https://dev-to-uploads.s3.amazonaws.com/i/ewxbal8lslxe369iwx36.png)

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](#2a-creating-your-service-account):

```yaml
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:
```diff
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:
```js
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.
```js
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:
```yaml
  read:
    handler: handler.read
    events:
      - http:
          path: /read
          method: GET
```
and add to our `handler.js` file the next method:
```js
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:

```bash
$ 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:

```bash
$ curl --location --request GET 'https://your.own.endpoint.com/dev/read?cells=A1,A2'
```
{% asciinema 350189 %}

## 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:
```js
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](https://github.com/a0viedo/spreadsheet-example) 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](https://github.com/a0viedo/dolar-historico)
- [weather-daily](https://github.com/a0viedo/sls-weather-daily)

{% github a0viedo/dolar-historico %}

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.