What are we trying to do?

We have a simple contact form and, we want to send that FormData to a Google spreadsheet.

1. We need to gain authorization to the spreadsheet
2. Since we need auth, we need to write a handler for this to run server-side
3. We need to append a row to the spreadsheet

1. Getting the authorization

This was the hardest part as we need to navigate a Google GUI that may not be familiar.

Thankfully Theo Ephraim wrote an incredible doc on this here. And if you find it helpful, consider buying him a beer.

Let's assume you've followed the above guide:

• started a new project in google dev console
• enabled the sheets api on that project
• created a service acount
• gotten the json key file download for that service

Since this is a secret, we want to only expose this to our code via an environment variable.

While working locally, it worked fine for me to copy and paste just the client_email and private_key from the json key file into my .env.local file.

const jwtFromEnv = new JWT({
  email: process.env.GOOGLE_SERVICE_CLIENT_EMAIL,
  key: process.env.GOOGLE_SERVICE_PRIVATE_KEY,
  scopes: SCOPES,
});

But this didn't work when I deployed to Vercel. I needed to base64 encode the entire json file and upload it to the Vercel env vars. I encoded it in the command line with base64 and then decoded in the javascript code like this:

const credential = JSON.parse( 
  Buffer.from(process.env.GOOGLE_SERVICE_KEY as string, 'base64').toString(), 
); 

const jwtFromEnv = new JWT({
  email: credential.client_email,
  key: credential.private_key,
  scopes: SCOPES,
});

2. Writing an API Endpoint

Backing Away from the Edge

Originally, I wanted to run this simple script on an edge runtime, but there was some overhead that I didn't want to take on that extra time for a task I was doing for a client (and the benefit was going to be minor given the UX of the project - aka this was a one-off call that didn't block anything else the user wanted to do next).

If I wanted to explore that again, I'd try to use this as a jumping-off point: https://sdorra.dev/posts/2023-08-03-google-auth-on-the-edge. You would still need to handle the API calls to Google since their node library googleapis doesn't support edge runtimes yet.

Actions

If you're using Next.js, you could leverage their Server Actions feature.

If you're using Remix, you could use their actions feature.

Etc for other frameworks.

We are just gonna make a simple API endpoint that takes a FormData and writes it to a Google spreadsheet and call that with fetch.

3. Writing the API Endpoint

We are writing a simple POST request endpoint. And using the great google-spreadsheet library to write to the spreadsheet.

Here is the entire endpoint:

import { JWT } from 'google-auth-library';
import { GoogleSpreadsheet } from 'google-spreadsheet';

const credential = JSON.parse(
  Buffer.from(process.env.GOOGLE_SERVICE_KEY as string, 'base64').toString(),
);

const GOOGLE_SPREADSHEET_ID = process.env.GOOGLE_SPREADSHEET_ID as string;

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

export async function POST(request: Request) {
  const { contactReason, name, email, description } = await request.json();

  const jwt = new JWT({
    email: credential.client_email,
    key: credential.private_key,
    scopes: SCOPES,
  });

  const doc = new GoogleSpreadsheet(GOOGLE_SPREADSHEET_ID, jwt);

  try {
    await doc.loadInfo();
    const sheet = doc.sheetsByIndex[0];

    const newRow = await sheet.addRow([
      contactReason,
      name,
      email,
      description,
      new Date().toISOString(),
    ]);

    return new Response(
      JSON.stringify({ contactReason, name, email, description }),
    );
  } catch (err) {
    console.error(err);
    return new Response('Unknown error', { status: 500 });
  }
}

Security

Currently the security on this API endpoint is that it only allows requests from the same-origin.

Someone could still write a bot to submit the form many times/with trash data and it would be submit successfully. If this is a threat, you'd want to mitigate that with some kind-of CAPTCHA or rate limiting. A really nice CAPTCHA for your users is Cloudflare's Turnstile that doesnt require your users to click all the images of the people wasting their time on with bad website UX.