How to utilize NEXT.JS and REACT.JS for saving data in Google Sheets.
By Jaam Asad
12 Sep 2023
In this article, we'll walk you through the process of creating a basic contact form using Next.js and demonstrate how to store the form data in Google Sheets. The form will consist of four fields: Full Name, Email, Topic, and Description.
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 Select a Project and Click on New Project.
9. Click on Select a Project and Click on New Project.
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
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 - sheetLet’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
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;
},
};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, '
'),
});
// 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);
}
};Thanks for Reading
Regards
Jaam Asad