A Node.js serverless implementation of appending an email to a Google Sheet via AWS API Gateway and AWS Lambda

handler.js

'use strict';

const { GoogleSpreadsheet } = require('google-spreadsheet');
const middy = require('middy');
const { cors } = require('middy/middlewares');
const { to } = require('await-to-js');
const moment = require('moment');

const data = require('./primarycast-sheets.json');

const Response = (obj) => ({
  statusCode: 200,
  body: JSON.stringify(obj),
});

const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_ID);

const appendSpreadsheet = (row) => {
  return new Promise(async (res) => {
    let err;

    // Authenticate as service account user
    [err] = await to(
      doc.useServiceAccountAuth({
        client_email: process.env.CLIENT_EMAIL,
        private_key: data.private_key,
      }),
    );
    if (err) {
      return res(
        Response({
          success: false,
          err,
        }),
      );
    }

    // Loads document properties and worksheets
    [err] = await to(doc.loadInfo());
    if (err) {
      return res(
        Response({
          success: false,
          err,
        }),
      );
    }

    // Append the row to the Google Sheet
    const sheet = doc.sheetsById[process.env.SHEET_ID];
    [err] = await to(sheet.addRow(row));
    if (err) {
      return res(
        Response({
          success: false,
          err,
        }),
      );
    }

    // Success
    res(
      Response({
        success: true,
      }),
    );
  });
};

const upload = async (event) => {
  return new Promise(async (res) => {
    // Upload the CSV file to S3 for record keeping and tracking
    const { email } = JSON.parse(event.body);
    return res(
      appendSpreadsheet({
        Email: email,
        Date: moment().format(),
      }),
    );
  });
};

// Let's "middyfy" our handler, then we will be able to attach middlewares to it
const handler = middy(upload).use(cors()); // Adds CORS headers to responses

module.exports.upload = handler;

primarycast-sheets.json

{
  "type": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "",
  "client_email": "",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": ""
}

serverless.yml

service: service-name

custom:
  appName: emailToGoogleSheets

provider:
  name: aws
  runtime: nodejs12.x
  stage: dev
  region: us-east-1
  environment:
    SPREADSHEET_ID: 0
    SHEET_ID: 0
    CLIENT_EMAIL: [email protected]

functions:
  emailUpload:
    handler: handler.upload
    memorySize: 128
    description: Append email address and date to Google Sheets
    events:
      - http:
          path: upload
          method: post
          cors:
            origins:
              - https://www.example.com