I've been running into this situation more and more often where I need to gather user data on a website for things like a mailing list, an opt-in form, or a survey, but I don't have a marketing platform to store the data in. They all have different pricing and features and I don't have time to figure out which to use. I just wanted to append submissions from my front-end to a Google Sheet (mapping fields to column headers) and worry about marketing platforms later. But I couldn't find a good service to do that.

So I decided to build it myself. Couldn't be that hard, right?

Here's how I did it:

Tech Stack

As I've written before, I think the perfect tech stack for your startup is whatever you can use to get the job done fastest. For me that's a variation on the MERN stack with Serverless as the hosting framework.

If you've never built a Serverless app before and are looking for something to get you started, take a look at this boilerplate project I threw together. It's pretty basic but I use it for a lot projects to get things started.

The key considerations as I looked at the project were:

1. We needed to use the HTTP request to validate the form input and throw a user-visible error.
2. If everything looked good, that's when we needed to start talking to Google about updating a sheet. And because this was a third-party we needed to interact responsibly and limit our rates.

I've written about this in another article, but SQS FIFO queues are a great way to rate limit interactions with a third party api. So any interaction with Google needed to happen in a queue with a worker function. This is a perfect application for Serverless and FIFO.

Ultimately the basic architecture I had sketched out looked like this:

With this framework in place, I needed to get down to the specifics of each bit of logic.

Working with the Google Sheets API

My HTTP endpoint would be getting a POST payload like:

{
    "DOB": "6/20/1997"
    "Name": "Jane Doe",
    "Email": "jane@applemail.com",
}

I needed to convert that to a sheet like:

The only caveat is that I needed to order the data correctly so the values matched the columns in the sheet and then add it to the end of the sheet. Pretty simple.

Note: All these examples use the Google Sheets API, v4.

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

const { google } = require('googleapis');

class ExecuteSheetUpdateCommand {
  /**
   * @param {string} spreadsheetId ID of the Google Spreadsheet.
   * @param {Object} data Object that contains the data to add to the sheet as key-value pairs.
   * @param {google.auth.OAuth2} auth An Google OAuth client with a valid access token: https://github.com/googleapis/google-api-nodejs-client.
  */
  static async exec(spreadsheetId, data, auth) {
    const sheets = google.sheets({ version: 'v4', auth });

    const rows = [data.Name, data.Email, data.DOB];
    // Add our new data to the bottom of the sheet.
    await sheets.spreadsheets.values.append({
      spreadsheetId,
      range: 'A1',
      valueInputOption: 'RAW',
      insertDataOption: 'INSERT_ROWS',
      resource: {
        values: [rows],
      },
    });
  }
}

Viola! With one simple function we were automatically mapping form data to Google Sheets.

Now obviously this function isn't great. It's coupling the form headers to the sheet structure with this line: const rows = [data.Name, data.Email, data.DOB]; You really shouldn't do that. (For example, if I moved a column in my spreadsheet, this function would keep inserting data into the old location and my sheet would have incorrect data.) But it's a bit more complicated to automatically map form fields to the sheet headers and I'm leaving that part out for the sake of this example.

Adding an REST endpoint with an SQS worker

Ok, so we have a function that can send a JSON object to a Google Sheet but how do we do that with a HTML form? The answer is HTTP + SQS.

The HTTP part is pretty simple if you're familiar with Node and Express. (You could just as easily deploy this on another node-friendly environment but I'm going to show you how to do with Serverless and AWS.) I use the aws-serverless-express package to ship my express apps as Serverless Lambda functions. Combined with the serverless-api-cloudfront package, it's incredibly easy to spin up a scalable API.

Here's an express HTTP endpoint that begins the update to the Google Sheet:

const express = require('express');
const bodyParser = require('body-parser');

// An AWS SQS client
const sqsClient = require('./clients/SQSClient');

const app = express();

app.use(bodyParser.urlencoded({ extended: true }));

app.post('/form/:spreadsheetId', async (req, res, next) => {
  const { spreadsheetId } = req.params; // The Google Sheet ID
  const { body } = req; // The post body

  /* Note: You should run your own custom validation on the 
     * form before forwarding it on. In this example we just continue.
   *
     * At a minimum, make sure you have permission to update the 
   * sheet, otherwise this will break downstream.
     */
  const passedValidation = true;

  if(passedValidation) {
    // Send the data to our SQS queue for further processing
    await sqsClient.createEntry.sendMessage({
      spreadsheetId,
            body,
    });
  } else {
    throw new Error('Invalid form data');
  }

  res.status(200).send('Submitted your form');
});

And then here's the Lambda function that pulls the data off of the throttled SQS FIFO queue and processes it for Google:

const { google } = require('googleapis');
const ExecuteSheetUpdateCommand = require('../commands/ExecuteSheetUpdateCommand');

exports.handle = async (event, context, callback) => {
  const messages = event.record.body;

  // This little logic helps us throttle our API interactions
  messages.reduce(async (previousPromise, nextMessage) => {
    await previousPromise;
    const { spreadsheetId, body } = nextMessage;
    const accessToken = /* Load a valid access token for your Google user */;
    // Construct an oAuth client with your client information that you've securely stored in the environment
        const oAuth2Client = new google.auth.OAuth2(
      process.env.GOOGLE_CLIENT_ID, process.env.GOOGLE_CLIENT_SECRET, null,
    );
    oAuth2Client.setCredentials({
      access_token: accessToken,
    });
    await ExecuteSheetUpdateCommand.exec(spreadsheetId, body, oAuth2Client);
    return new Promise((resolve) => {
      setTimeout(resolve, 1000); // Throttle for the Google API
    });
  }, Promise.resolve());

  callback();
};

The reason we're using SQS with FIFO and not just executing this all in the HTTP endpoint is because it allows us to quickly respond to the user who is submitting the form, and update the Sheet as soon as we can while respecting API limits.

If we don't think about API limits, we could get ourselves into situations where the user is shown an error screen as soon as they submit a form. Not good. The Google Sheets API has a limit of "100 requests per 100 seconds per user", or 1 request / second is as fast as we can safely interact with it.

SQS FIFO queues allow us to put our sheet updates into a single line, grouped by user id, and we can then throttle those executions using that messages.reduce snippet above to make sure that we never go over our limit of 1 request / second / user. And we also get the added benefit of allowing AWS to do the hard work of throttling. The key is when your populating the FIFO queue, make sure the MessageGroupId is set to the Google user id that is making the OAuth request.

Wrapping it up

By using a combination of these techniques and functions, you should be in a place where you can write an HTML form like:

<form action="https://<my-express-endpoint>/form/<my-sheet-id>" method="post">
<input type="email" name="Email" placeholder="Enter your email" required />
<input type="name" name="Name" placeholder="Enter your name" required />
<input type="submit" value="Submit" />
</form>

and data will magically show up in your Google Sheet every time it's submitted:

Sheet Monkey

Ok, that was a lot more work than I thought. That's why I ended up turning this into a little indie product. If you need to send your HTML forms into a Google Sheet and don't want to go to the hassle of building your own solution, check out what I built at Sheet Monkey.