Save Form data in Google Sheets with Next JS

In this post, we are going to make a simple contact form with Next JS and save Form data in Google Sheets. This form will have four files – Full Name, Email, Topic & Description.

Also Read: Next JS Project Ideas to Boost Your Portfolio

App Register for Google Sheet

Here are the detailed step-by-step instructions to register an app in Google console and save Form data in Google Sheets with screenshots:

1. First, go to Google Developers Console and log in with your Google account
2. Click on Select a Project and Click on New Project.

3. Give Your Project a name and click on create

4. After that click on Credentials from the left side menu and click on manage service accounts

5. Click on create service account to create

6. Give your service account a name, and a description and then click on Create and Continue. The second option is optional you can skip it. And now click on Done.

7. On your service account list click on three-dot on the right side and click on manage keys from options.

8. Click on Create New Key from add key button options

9. You will get a popup with 2 options. Select the JSON option and then click on create.

10. A JSON file will be downloaded with Private Key,email address and API Keys. These will be needed for form submits and post requests.

{
  "type": "service_account",
  "private_key": "-----BEGIN PRIVATE KEY----- -----END PRIVATE KEY----",
  "client_email": "some@email",
}

Google Spreadsheet create

11. Create a new Google Sheet from the same Google account that was used for creating the Google Console Project.

12. Give your sheet a name and save the SPREADSHEET_ID and SHEET_ID from the google sheet URL

13. Share the Google Sheet with the email provided in the JSON file as Editor. This is important.

14. One more important step in Google Console. Click on the 3 bar on the top left side and find APIs and Services. From there Click on Library.

15. Search Sheet from the search bar and you’ll get Google Sheet on the first result. Click on it.

16. Enable Google Sheet API. This is an important step.

17. Important! Add your field names as heading in the Google Sheet

Now we are done with Google Console and Google Sheet Part

Also Read: How to Build PWA with Next JS?

Save Form data in Google Sheets

First, create a Next JS App with create-next-app command. We will create the app with a template. Use the following command to create the app.

npx create-next-app next-js-contact-from-google-sheet
#or
yarn create-next-app next-js-contact-from-google-sheet

This command will create a template Next JS application. We will make the form on the homepage. Next JS has its own folder-based routing system so in the pages folder create a file name index.js.

Our form will have four fields –

• Full Name
• Email
• Topic
• Description

Let’s create the form first. We are using Tailwind CSS here for styling. The form value is handled with React `useState`. The form fields are shown below in the index.js file.

// index.js
import React, { useState } from 'react';

const ContactForm = () => {
  const [form, setForm] = useState({
    name: '',
    email: '',
    topic: '',
    description: '',
  });

  const submitForm = (e) => {
    e.preventDefault();
  };

  const handleChange = (e) => {
    setForm({
      ...form,
      [e.target.name]: e.target.value,
    });
  };

  return (
    <form className="space-y-3 max-w-lg mx-auto p-5" onSubmit={submitForm}>
      <p className="font-semibold text-2xl text-center">Contact Form</p>
      <label className="block">
        <span className="text-gray-700 font-semibold">Full Name</span>
        <input
          name="name"
          type="text"
          className="form-input form-field-contact"
          placeholder="Full Name"
          onChange={handleChange}
        />
      </label>
      <label className="block">
        <span className="text-gray-700 font-semibold">Email</span>
        <input
          name="email"
          type="email"
          className="form-input form-field-contact"
          placeholder="Email"
          onChange={handleChange}
        />
      </label>
      <label className="block">
        <span className="text-gray-700 font-semibold">Topic</span>
        <input
          name="topic"
          type="text"
          className="form-input form-field-contact"
          placeholder="Topic"
          onChange={handleChange}
        />
      </label>
      <label className="block">
        <span className="text-gray-700 font-semibold">Description</span>
        <textarea
          name="description"
          className="form-textarea form-field-contact"
          rows="3"
          placeholder="Description"
          onChange={handleChange}
        />
      </label>

      <button
        className="bg-green-200 px-3 py-1 font-semibold shadow-md rounded-md"
        type="submit"
      >
        Send Message
      </button>
    </form>
  );
};

export default ContactForm;

Setting up Google Sheets in Next JS

To communicate with the Google Sheets API we need to install a package named google-spreadsheet. So let’s install the package.

npm i google-spreadsheet --save 
# or 
yarn add google-spreadsheet

As we are using Next JS to build the website, we need to add some extra configurations in our next.config.js. The configurations are for Webpack as we are using SSG.

// next.config.js
module.exports = {
  webpack: (config, { isServer }) => {
    if (!isServer) {
      config.resolve.fallback.fs = false;
      config.resolve.fallback.tls = false;
      config.resolve.fallback.net = false;
      config.resolve.fallback.child_process = false;
    }

    return config;
  },
};

Also Read: How to add Google AdSense in Next JS

We need to add our API key, Client Email, Spreadsheet ID and Key to env.local file. Next JS reads env variables like this. Add your details in env file

// .env.local
NEXT_PUBLIC_SPREADSHEET_ID = 
NEXT_PUBLIC_SHEET_ID = 
NEXT_PUBLIC_GOOGLE_CLIENT_EMAIL = 
GOOGLE_SERVICE_PRIVATE_KEY = 

The magical keyword NEXT_PUBLIC_ at the beginning of NEXT_PUBLIC_API_KEY denotes that this environment variable is not private and can be used by anyone.

But, GOOGLE_SERVICE_PRIVATE_KEY does not begin by NEXT_PUBLIC_ and will not be available to the general public. For this we have to add the variable in next.config.js like this

// next.config.js
module.exports = {
  env: {
    GOOGLE_SERVICE_PRIVATE_KEY: process.env.GOOGLE_SERVICE_PRIVATE_KEY,
  },
};

Now we will make the form submit handler

import { GoogleSpreadsheet } from 'google-spreadsheet';

// Config variables
const SPREADSHEET_ID = process.env.NEXT_PUBLIC_SPREADSHEET_ID;
const SHEET_ID = process.env.NEXT_PUBLIC_SHEET_ID;
const GOOGLE_CLIENT_EMAIL = process.env.NEXT_PUBLIC_GOOGLE_CLIENT_EMAIL;
const GOOGLE_SERVICE_PRIVATE_KEY =
  process.env.GOOGLE_SERVICE_PRIVATE_KEY;

// GoogleSpreadsheet Initialize
const doc = new GoogleSpreadsheet(SPREADSHEET_ID);

// Append Function
const appendSpreadsheet = async (row) => {
  try {
    await doc.useServiceAccountAuth({
      client_email: GOOGLE_CLIENT_EMAIL,
      private_key: GOOGLE_SERVICE_PRIVATE_KEY.replace(/\\n/g, '\n'),
    });
    // loads document properties and worksheets
    await doc.loadInfo();

    const sheet = doc.sheetsById[SHEET_ID];
    await sheet.addRow(row);
  } catch (e) {
    console.error('Error: ', e);
  }
};

const submitForm = (e) => {
  e.preventDefault();

  if (
    form.name !== '' &&
    form.email !== '' &&
    form.topic !== '' &&
    form.description !== ''
  ) {
    // Data add for append
    const newRow = {
      FullName: form.name,
      Email: form.email,
      Topic: form.topic,
      Description: form.description,
    };

    appendSpreadsheet(newRow);
  }
};

Tips on common issues

• Do not remove ----BEGIN PRIVATE KEY---- or ----END PRIVATE KEY---- or anything else from your private key.
• Enable Google Sheet API from the google console.
• Do this to your private key before using 

process.env.GOOGLE_SERVICE_PRIVATE_KEY.replace(/\\n/g, '\n')

This is it. Now after the form submit you will get the data the Google SpreadSheet.

You will get the full code of this on Github. Find more details on this Save Form data in Google Sheets with Next JS