How to collect data from your leads with NodeJs and Google spreadsheet
Just imagine — you work on a new MVP project and decided to start it with a small landing page which describe main information, benefits of your products or service.
Landing page is ready (for example with next.js), but you want collect forms data from this page. How to do it? Of course, if you have CRM system the best way would be to use API of this system to collect form data. But if you do not have any CRM solution you can use free and simple tool — Google spreadsheet. It has simple API and amazing UI to operate with any data from your clients. So, let’s do simple and fast solution.
First of all you should have google account and create Service Account at this page https://console.cloud.google.com/. How to do it you can read at this support page https://developers.google.com/identity/protocols/oauth2/service-account?hl=en. It is not difficult, but after all actions you will get JSON file with all credentials.
Second step — download and install nodejs library https://www.npmjs.com/package/google-spreadsheet. This is good wrapper to work with spreadsheet and also it has well-formed documentation. If you use Typescript in your project do not forget to install `@types/google-spreadsheet`.
And finally we can write some code to collect form data from landing page. Have a look on example below from Next.js framework on Typescript.
import { GoogleSpreadsheet } from 'google-spreadsheet';
import type { NextApiRequest, NextApiResponse } from 'next';
import { FieldSchemaValues } from '../../blocks/index/form';
import creds from '../../config/nodejs.json';// You can get it from the address of document page
const doc = new GoogleSpreadsheet('<ID of your Google spreadsheet document>');export default async function handler(
req: NextApiRequest,
res: NextApiResponse
) {
if (req.method !== 'POST') {
return res.status(405).json({
error: 'Incorrect form request'
});
} // Get data from the request
const {
name,
email,
instagram,
tiktok,
youtube
}: FieldSchemaValues = req.body; // Validate data
if (!name || !email) {
return res.status(400).json({
error: 'Please provide name & email'
});
} // Make authoziation of our client
await doc.useServiceAccountAuth(creds);
await doc.loadInfo(); const date = new Date().toUTCString();
const sheet = await doc.sheetsByIndex[0]; // Add headers
await sheet.setHeaderRow([
'Date',
'Name',
'Email',
'Instagram',
'TikTok',
'Youtube',
]); // Write data to file
await sheet.addRow({
Date: date,
Name: name,
Email: email,
Instagram: String(instagram),
TikTok: String(tiktok),
Youtube: String(youtube),
}); return res.status(200).json({});
}
Also do not forget to provide edit access to you created Service Account. In your JSON file you can find email. Use it when you provide access to your google document.
And that is it. You do not need to create any other Service Account for your new projects, you can use created one and with this account you can write to any Google spreadsheets documents (if someone provide access to you Service account).
