DEV Community

Julian Bustos
Julian Bustos

Posted on

Use Next.js 14 (App Router) to Store Subscriber Info in Google Sheets for FREE

Introduction

In this tutorial, we will learn how to store user input from a Next.js app into a Google Sheets spreadsheet.

Requirements

  • Google Sheets Setup
  • A running Next.js App

I will start with a clean project, but I assume you already have a Next.js app up and running. Below is what I have in my layout.tsx, globals.css, and home page files:

// app/layout.tsx

import type { Metadata } from "next";
import "./globals.css";

export const metadata: Metadata = {
  title: "Nextjs Google Sheets Integration",
};

export default function RootLayout({
  children,
}: Readonly<{
  children: React.ReactNode;
}>) {
  return (
    <html lang="en">
      <body
        className="antialiased"
      >
        {children}
      </body>
    </html>
  );
}
Enter fullscreen mode Exit fullscreen mode 
 /* app/global.css */

@tailwind base;
@tailwind components;
@tailwind utilities;
Enter fullscreen mode Exit fullscreen mode 
// app/page.tsx

export default function Home() {
  return (
    <main>
      <h1>Nextjs Google Sheets Integration</h1>
    </main>
  );
}
Enter fullscreen mode Exit fullscreen mode

Now let's setup google so we can use it as our backend for our subscribers.

Google Setup

We will first need to create a new Service Account to do so go here

Click on Start Free

Start Service Account

Read the terms of service and click Agree & Continue.

  1. Create your Payment Profile (you won't be charged).
  2. Select Individual Profile Type if you're not a business.
  3. Click Create.
  4. Now add a payment method.
  5. Click Start Free.
  6. Select your Cloud Platform options.

Great! Now that we have a Service Account set up, look for the menu on the left and click on APIs & Services.

APIs & Services

Enable APIs & Services

Enable APIs & Services

Search for Google Sheets API and select it

Google Sheets API

Click Enable

Great now we have the google sheets API enabled now let's create the credentials so we can programmatically connect to it.

Click on Create Credentials

Create Credentials

Select Application Data & click Done

Application Data

Now click on the IAM & Admin in the left menu and then click Service Accounts

Service Accounts

Now click on Create Service Account

Create Service Account

Give your account a name and a description and click Create And Continue

Select Owner as the Role and click Continue

We can click Done on the final step.

Now we can manage the keys for this account which will allow us to connect to Google Sheets.

Click on the 3 dots and select Manage Keys

Manage Keys

Click on Add Key and then on Create new key

Create New Key

Select the JSON option and click Create

Create JSON Keys

Save it somewhere safe and then open it in your code editor

Great all we really care about are the private_key and the client_email, these will allow us to establish the connection.

Go back to your Nextjs Project and in the .env.local (if you don't have a .env file yet create one in the root of your project) file let's create the environment variables we will need.

# .env.local

GOOGLE_SERVICE_ACCOUNT_EMAIL=
GOOGLE_PRIVATE_KEY=
Enter fullscreen mode Exit fullscreen mode

Paste your keys from the JSON file in here.

Awesome! Now we are ready to start writing some code finally!

Let's run our app

pnpm dev
Enter fullscreen mode Exit fullscreen mode

And open it in the browser.

We are going to create a button that opens a Dialog with the subscription form.

Lets start by adding the SubscribeDialog component.

Create Subscribe.tsx and import it in your page.tsx

We'll copy some basic styles for the buttons from tailwindcss.com

We will also use a dialog element so we can take advantage of some default behaviour that we'll see in action later, we have to give it the open property so we can see it on the screen, and some basic layout styles.

If we remove the open prop you will see the content change.

// app/Subscribe.tsx
const Subscribe = () => {
  return (
    <>
      <button className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent rounded">
        Subscribe
      </button>

      <dialog open className="border border-black py-5 px-3">
        <form className="flex flex-col gap-2">
          <label htmlFor="email">
            <span>Email: </span>
            <input type="email" id="email" name="email" className="border border-black" />
          </label>
          <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent rounded">Subscribe</button>
        </form>
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

Call it in page.tsx

// app/page.tsx

import Subscribe from "./Subscribe";

export default function Home() {
  return (
    <main className="flex flex-col items-center justify-center min-h-[100dvh]">
      <h1>Nextjs Google Sheets Integration</h1>
      <Subscribe />
    </main>
  );
}
Enter fullscreen mode Exit fullscreen mode

Now we can add the functionality to the button so we can open and close the dialog.

We will need to use the useRef hook which means we will need to make this component into a client component.

We will also write a toggleDialog function so we can open and close the dialog, and we will also add a close button to the dialog so we can close it. However because we are using the dialog and the showModal javascript function, it means we can use the ESC key to close the modal as well.

// app/Subscribe.tsx

"use client";

import { useRef } from "react";

const Subscribe = () => {

  const dialogRef = useRef<HTMLDialogElement>(null);
  const toggleDialog = () => {
    if (dialogRef.current?.open) {
      return dialogRef.current?.close();
    }
    dialogRef.current?.showModal();
  }

  return (
    <>
      <button onClick={toggleDialog} className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">
        Subscribe
      </button>

      <dialog ref={dialogRef} className="border border-black py-10 px-3 backdrop:bg-black backdrop:opacity-70">
        <form className="flex flex-col gap-2">
          <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
          <label htmlFor="email">
            <span>Email: </span>
            <input type="email" id="email" name="email" className="border border-black" />
          </label>
          <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">Subscribe</button>
        </form>
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

Nice! But we also want to capture more than the users email, however in my case I just want first name and last name.

// app.Subscribe.tsx

"use client";

import { useRef } from "react";

const Subscribe = () => {

  const dialogRef = useRef<HTMLDialogElement>(null);
  const toggleDialog = () => {
    if (dialogRef.current?.open) {
      return dialogRef.current?.close();
    }
    dialogRef.current?.showModal();
  }

  return (
    <>
      <button onClick={toggleDialog} className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">
        Subscribe
      </button>

      <dialog ref={dialogRef} className="border border-black pt-10 pb-5 px-3 backdrop:bg-black backdrop:opacity-70">
        <form className="flex flex-col gap-2">
          <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
          <label htmlFor="email">
            <span>Email: </span>
          </label>
          <input type="email" id="email" name="email" className="border border-black" />
          <label htmlFor="firstName">
            <span>First Name: </span>
          </label>
          <input type="firstName" id="firstName" name="firstName" className="border border-black" />
          <label htmlFor="lastName">
            <span>Last Name: </span>
          </label>
          <input type="lastName" id="lastName" name="lastName" className="border border-black" />
          <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">Subscribe</button>
        </form>
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

Great! Now that we have a front end let's write a form action for us to safely store the user's information on the backend. We will use valibot to validate the user data and the google API package to store the data.

Let's add the googleapis package first.

pnpm add googleapis
Enter fullscreen mode Exit fullscreen mode

Create the action file, I'm going to call it subscribeAction, which will receive our form data and store it, so let's start by receiving the form data. To make it safe we will use a "use server" directive at the top of the file.

// app/subscribeAction.ts

"use server";

export const subscribeAction = async (formData: FormData) => {
  const rawData = {
    email: formData.get("email"),
    firstName: formData.get("firstName"),
    lastName: formData.get("lastName"),
  }

  console.log({rawData})
};
Enter fullscreen mode Exit fullscreen mode

And we have to call our function, in our form like this:

// app/Subscribe.ts
import { subscribeAction } from "./subscribeAction";

// ... rest of your code
        <form className="flex flex-col gap-2" action={subscribeAction}>
// ... rest of your code
Enter fullscreen mode Exit fullscreen mode

Now you should be able to see the console log when you click the subscribe button.

Awesome let's add valibot now so we can validate the data. (valibot is a smaller zod which will help you add types and validations and errors to show) valibot uses a slightly different approach than zod, if we want to run different validations on a single piece of data we need to create pipes, and then we can add any validations we need. Feel free to go to their docs to learn more.

Let's do that first

pnpm add valibot
Enter fullscreen mode Exit fullscreen mode

It's best to write your validations in a separate file in case you need to call the types on the front-end. I'm going to call this file valibotSchemas.ts.

// app/valibotSchemas.ts
import { email, maxLength, nonEmpty, object, pipe, string } from "valibot";

export const subscribeDataSchema = object({
  email: pipe(
    string(),
    nonEmpty("Please enter your email."),
    email("The email is badly formatted."),
    maxLength(30, "Your email is too long.")
  ),
  firstName: pipe( 
    string(),
    nonEmpty("Please enter your first name."),
    maxLength(30, "Your first name is too long.")
  ),
  lastName: pipe( 
    string(),
    nonEmpty("Please enter your last name."),
    maxLength(30, "Your first name is too long.")
  ),
});
Enter fullscreen mode Exit fullscreen mode

Now we can import it so we can validate our data and get sweet type safety too.

// app/subscribeAction.ts
"use server";

import { parse, ValiError } from "valibot";
import { subscribeDataSchema } from "./valibotSchemas";

export const subscribeAction = async (formData: FormData) => {
  const rawData = {
    email: formData.get("email"),
    firstName: formData.get("firstName"),
    lastName: formData.get("lastName"),
  };
  try {
    const data = parse(subscribeDataSchema, rawData);
    console.log({ data });
  } catch (error: unknown) {
    if (error instanceof ValiError) {
      const issues = error.issues;
      const errorMessages = issues.map((issue) => issue.message);
      console.log({ errorMessages });
    } else {
      console.log({ error });
    }
  }
};
Enter fullscreen mode Exit fullscreen mode

Now if you an empty value on your form and click subscribe and look at your console you will see the error messages, we will use these to let the user know if there is an issue. To do this we will use React's useFormState hook.

We will add the useFOrmState and replace the action in our form in our Subscribe component.

"use client";

import { useRef } from "react";
import { subscribeAction } from "./subscribeAction";
import { useFormState } from "react-dom";

const Subscribe = () => {
  const [formState, formAction] = useFormState(subscribeAction, {
    success: false,
    errors: null,
  });

  const dialogRef = useRef<HTMLDialogElement>(null);
  const toggleDialog = () => {
    if (dialogRef.current?.open) {
      return dialogRef.current?.close();
    }
    dialogRef.current?.showModal();
  }

  return (
    <>
      <button onClick={toggleDialog} className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">
        Subscribe
      </button>

      <dialog ref={dialogRef} className="border border-black pt-10 pb-5 px-3 backdrop:bg-black backdrop:opacity-70">
        <form className="flex flex-col gap-2" action={formAction}>
          <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
          <label htmlFor="email">
            <span>Email: </span>
          </label>
          <input type="email" id="email" name="email" className="border border-black" />
          <label htmlFor="firstName">
            <span>First Name: </span>
          </label>
          <input type="firstName" id="firstName" name="firstName" className="border border-black" />
          <label htmlFor="lastName">
            <span>Last Name: </span>
          </label>
          <input type="lastName" id="lastName" name="lastName" className="border border-black" />
          <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">Subscribe</button>
        </form>
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

Now typescript is unhappy with out subscribe function because the action now needs to receive the formState, and also the returns of our function don't match the expected return defined in our form action ({ success: false, errors: null}) so let's add fix that.

Since we are not going to use we can just leave it empty.

// app/subscribeAction.ts
"use server";

import { parse, ValiError } from "valibot";
import { subscribeDataSchema } from "./valibotSchemas";

export const subscribeAction = async (state: {}, formData: FormData) => {
  const rawData = {
    email: formData.get("email"),
    firstName: formData.get("firstName"),
    lastName: formData.get("lastName"),
  };

  try {
    const data = parse(subscribeDataSchema, rawData);
    console.log({ data });

    return {
      success: true,
      errors: null,
    };
  } catch (error: unknown) {
    if (error instanceof ValiError) {
      const issues = error.issues;
      const errorMessages = issues.map((issue) => issue.message);

      console.log({ errorMessages });

      return {
        success: false,
        errors: errorMessages,
      };
    } else {
      console.log({ error });
      return {
        success: false,
        errors: ["An error occurred."],
      };
    }
  }
};
Enter fullscreen mode Exit fullscreen mode

And now on our Subscribe component let's render those messages if we have any.

// app/Subscribe.tsx
"use client";

import { useRef } from "react";
import { subscribeAction } from "./subscribeAction";
import { useFormState } from "react-dom";

const Subscribe = () => {
  const [formState, formAction] = useFormState(subscribeAction, {
    success: false,
    errors: null,
  });

  const dialogRef = useRef<HTMLDialogElement>(null);
  const toggleDialog = () => {
    if (dialogRef.current?.open) {
      return dialogRef.current?.close();
    }
    dialogRef.current?.showModal();
  }

  return (
    <>
      <button onClick={toggleDialog} className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">
        Subscribe
      </button>

      <dialog ref={dialogRef} className="border border-black pt-10 pb-5 px-3 backdrop:bg-black backdrop:opacity-70">
        <form className="flex flex-col gap-2" action={formAction}>
          <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
          <label htmlFor="email">
            <span>Email: </span>
          </label>
          <input type="email" id="email" name="email" className="border border-black" />
          <label htmlFor="firstName">
            <span>First Name: </span>
          </label>
          <input type="firstName" id="firstName" name="firstName" className="border border-black" />
          <label htmlFor="lastName">
            <span>Last Name: </span>
          </label>
          <input type="lastName" id="lastName" name="lastName" className="border border-black" />
          <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">Subscribe</button>
          {formState.errors?.map((error) => (
            <span className="text-red-700">
              *{error}
            </span>
          ))}
        </form>
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

Great! Now that we have dealt with the errors let's work on storing our data on a google spreadsheet. We have not yet created one so let's go ahead and do that. Go to Google Sheets and Create a new Blank Spreadsheet

Create New Spreadsheet

Let's add titles to the first row, in my case A will be email, B will be first name, and C will be last name.

On the spreadsheet for ease of reading later.

Spreadsheet titles

Lastly, we will add the google Service Account email to the users allowed to edit the document. Click on Share.

Click Share

Add the Service Email we added to the .env.local and give it editor access.

Editor Access

Click Send

We will need this spreadsheet's id in order to connect to it.

The id of the spreadsheet is in the URL, it is everything between /d/ and /edit rest of URL.

..../spreadsheets/d/<>/edit......

Spreadsheet id location

Copy it and let's head back to our .env.local and add it in there. I will call this environment variable GOOGLE_SPREADSHEET_ID. Remember to restart your server after adding anything to your .env.local.

Now we can create a function that will return the google auth so we can use it in other places too.

Let's create a new file for that, I will call it googleAuth.ts

// app/googleAuth.ts
import { google } from "googleapis";

export const googleAuth = async () => {
  const clientEmail = process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL;
  const privateKey = process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, "\n");

  if (!clientEmail || !privateKey) {
    throw new Error("Missing Google credentials.");
  }

  return new google.auth.GoogleAuth({
    credentials: {
      client_email: clientEmail,
      private_key: privateKey,
    },
    scopes: [
      "https://www.googleapis.com/auth/drive",
      "https://www.googleapis.com/auth/drive.file",
      "https://www.googleapis.com/auth/spreadsheets",
    ],
  });
};
Enter fullscreen mode Exit fullscreen mode

Great now let's update our action and finally store some data on our sheet.

// app/subscribeAction.ts
"use server";

import { parse, ValiError } from "valibot";
import { subscribeDataSchema } from "./valibotSchemas";
import { googleAuth } from "./googleSpreadsheets";
import { google } from "googleapis";

export const subscribeAction = async (state: {}, formData: FormData) => {

  const spreadsheetId = process.env.GOOGLE_SPREADSHEET_ID;

  if (!spreadsheetId) {
    return {
      success: false,
      errors: ["There was an error connecting to google."],
    };
  }

  const rawData = {
    email: formData.get("email"),
    firstName: formData.get("firstName"),
    lastName: formData.get("lastName"),
  };

  try {
    const data = parse(subscribeDataSchema, rawData);

    const sheets = await google.sheets({
      auth: await googleAuth(),
      version: "v4",
    });

    await sheets.spreadsheets.values.append({
      spreadsheetId,
      range: "A1:C1",
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: [[data.email, data.firstName, data.lastName]],
      },
    });

    return {
      success: true,
      errors: null,
    };
  } catch (error: unknown) {
    if (error instanceof ValiError) {
      const issues = error.issues;
      const errorMessages = issues.map((issue) => issue.message);

      return {
        success: false,
        errors: errorMessages,
      };
    } else {
      console.error(error);
      return {
        success: false,
        errors: ["An error occurred."],
      };
    }
  }
};
Enter fullscreen mode Exit fullscreen mode

Now check your spreadsheet and make sure you were able to add the information correctly. If you get any errors make sure your environment variables don't have any typos.

If you get an unauthorized message you might have missed the sharing the spreadsheet with the service account step. Go back to the Google Setup section.

Awesome, now that we are storing data, we want to make sure we don't add any repeats and that we get a success message if the information was added correctly.

Let's work on the no email repeats part first.

// app/subscribeAction.ts

"use server";

import { parse, ValiError } from "valibot";
import { subscribeDataSchema } from "./valibotSchemas";
import { googleAuth } from "./googleAuth";
import { google } from "googleapis";

export const subscribeAction = async (state: {}, formData: FormData) => {

  const spreadsheetId = process.env.GOOGLE_SPREADSHEET_ID;

  if (!spreadsheetId) {
    return {
      success: false,
      errors: ["There was an error connecting to google."],
    };
  }

  const rawData = {
    email: formData.get("email"),
    firstName: formData.get("firstName"),
    lastName: formData.get("lastName"),
  };

  try {
    const data = parse(subscribeDataSchema, rawData);

    const sheets = await google.sheets({
      auth: await googleAuth(),
      version: "v4",
    });

    // Select spreadsheet the range to read my emails are on column A
    const readRange = "A1:A";

    // Get the emails from the spreadsheet
    const emails = await sheets.spreadsheets.values.get({
      spreadsheetId: process.env.GOOGLE_SPREADSHEET_ID,
      range: readRange,
    });

    // Check if the email already exists
    const emailExists = emails.data.values?.flat().includes(data.email);

    if (emailExists) {
      return {
        success: false,
        errors: ["You're already subscribed!"],
      };
    };

    await sheets.spreadsheets.values.append({
      spreadsheetId,
      range: "A1:C1",
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: [[data.email, data.firstName, data.lastName]],
      },
    });

    return {
      success: true,
      errors: null,
    };

  } catch (error: unknown) {

    if (error instanceof ValiError) {

      const issues = error.issues;
      const errorMessages = issues.map((issue) => issue.message);

      return {
        success: false,
        errors: errorMessages,
      };

    } else {
      return {
        success: false,
        errors: ["An error occurred."],
      };
    }
  }
};
Enter fullscreen mode Exit fullscreen mode

Great now we should be able to see a message if the user is already in our spreadsheet.

Now let's add a small success message.

// app/Subscribe.tsx
"use client";

import { useRef } from "react";
import { subscribeAction } from "./subscribeAction";
import { useFormState } from "react-dom";

const Subscribe = () => {
  const [formState, formAction] = useFormState(subscribeAction, {
    success: false,
    errors: null,
  });

  const dialogRef = useRef<HTMLDialogElement>(null);
  const toggleDialog = () => {
    if (dialogRef.current?.open) {
      return dialogRef.current?.close();
    }
    dialogRef.current?.showModal();
  }

  return (
    <>
      <button onClick={toggleDialog} className="bg-transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">
        Subscribe
      </button>

      <dialog ref={dialogRef} className="border border-black pt-10 pb-5 px-3 backdrop:bg-black backdrop:opacity-70">
        {formState.success ? (
          <div>
            <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
            <h2>Thank you for subscribing!</h2>
          </div>
        ) : (
          <form className="flex flex-col gap-2" action={formAction}>
            <button onClick={toggleDialog} className="absolute right-2 top-2">X</button>
            <label htmlFor="email">
              <span>Email: </span>
            </label>
            <input type="email" id="email" name="email" className="border border-black" />
            <label htmlFor="firstName">
              <span>First Name: </span>
            </label>
            <input type="firstName" id="firstName" name="firstName" className="border border-black" />
            <label htmlFor="lastName">
              <span>Last Name: </span>
            </label>
            <input type="lastName" id="lastName" name="lastName" className="border border-black" />
            <button type="submit" className="transparent hover:bg-green-500 text-green-700 font-semibold hover:text-white py-2 px-4 border border-green-500 hover:border-transparent">Subscribe</button>
            {formState.errors?.map((error) => (
              <span className="text-red-700">
                *{error}
              </span>
            ))}
          </form>
        )}
      </dialog>
    </>
  );
}

export default Subscribe;
Enter fullscreen mode Exit fullscreen mode

And just like that, we're done! Now you know how to use Google Sheets to store data, which you could use as a lightweight CMS or even a simple database.

I hope you learned something valuable from this tutorial. Feel free to leave any comments or ask any questions!

Julian Bustos

Top comments (0)

Read next

zorian profile image

How to Leverage OpenAI for Advanced Text Analysis in Node.js

Zorian -

sharif_shariutullah profile image

Time complexity & Space complexity

Mohammad Shariful Islam -

mikeyoung44 profile image

Flexible Language Models Adapt with Memory and Amortization

Mike Young -

testifytech profile image

Visual Regression Testing Using Playwright

Steve Wortham -