How to make a newsletter with Node, Recaptcha and Google Spreadsheet
https://www.google.com/recaptcha/admin
👉 Activate your Sheet API 👈
Go to the Google Developers Console
Setup Instructions for service account Follow steps above to set up project and enable sheets API
NPM : https://www.npmjs.com/package/google-spreadsheet
Doc: https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication
Google sheet API doc: https://developers.google.com/sheets/api/quickstart/js?hl=fr
Open zenv and copy your Google service account json key in folder
open .
Install NPM dependency
npm i -S google-spreadsheet
npm i -S express
npm i -S body-parser
npm i -S node-fetch@^2.6.6
We are now ready to code our newsletter form and backend
Newsletter form Create a simple newsletter form with Google recaptcha
<script src="https://www.google.com/recaptcha/api.js"></script>
<form id="demo-form" action="http://localhost:4000/subscribe" method="POST">
Email: <input type="text" value="name@domain.com" name="email" id="email" required>
<button class="g-recaptcha"
data-sitekey="YOUR_PUBLIC_CAPTCHA_KEY"
data-callback='onSubmit'
data-action='submit'>Submit</button>
</form>
<script>
function onSubmit(token) {
document.getElementById("demo-form").submit();
}
</script>
Server Now the node backend to receive emails.
//exec: node
//hide
const express = require('express')
const bodyParser = require('body-parser');
const fetch = require('node-fetch');
const fs = require('node:fs');
const app = express()
app.post('/subscribe',
express.urlencoded({extended: true}), async (request, response) => {
const data = new URLSearchParams();
data.append('secret', 'YOUR_PRIVATE_CAPTCHA_KEY'); // private key
data.append('response', request.body["g-recaptcha-response"]);
const result = await fetch("https://www.google.com/recaptcha/api/siteverify", {
body: data,
method: "post"
});
const json = await result.json();
if (json.success) {
// captcha succeeded
print(`email to add: ${request.body.email}`)
return;
}
})
app.listen(4000)
At this point, you are now able to publish emails to your node API. We now want to upload emails to a Google Spreadsheet. You previously activated your Google Spreadsheet API and copied your json key file into your znote env
Google Spreadsheet code Let's start to hack some code using Google Spreadsheet
//hide
const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./google-spreadsheet-key.json');
// Initialize the sheet - doc ID is the long id in the sheets URL
const doc = new GoogleSpreadsheet('YOUR_SPREADSHEET_ID');
await doc.useServiceAccountAuth(creds);
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];
const newEmail = "tony3@gmail.com";
// read rows
const rows = await sheet.getRows();
const isExists = rows.map(r => r.email).includes(newEmail);
if (!isExists) {
await sheet.addRow({ email: newEmail });
}
//exec: node
//hide
const express = require('express')
const bodyParser = require('body-parser');
const fetch = require('node-fetch');
const fs = require('node:fs');
const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./google-spreadsheet-key.json');
const app = express()
// Initialize the sheet - doc ID is the long id in the sheets URL
const doc = new GoogleSpreadsheet('YOUR_SPREADSHEET_ID');
await doc.useServiceAccountAuth(creds);
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];
app.get('/', function (req, res) {
res.send('ok')
})
app.post('/subscribe',
express.urlencoded({extended: true}), async (request, response) => {
try {
const data = new URLSearchParams();
data.append('secret', 'YOUR_PRIVATE_CAPTCHA_KEY'); // private key
data.append('response', request.body["g-recaptcha-response"]);
const result = await fetch("https://www.google.com/recaptcha/api/siteverify", {
body: data,
method: "post"
});
const json = await result.json();
if (json.success) {
const newEmail = request.body.email;
// publish if email does not exist
const rows = await sheet.getRows();
const isExists = rows.map(r => r.email).includes(newEmail);
if (!isExists) {
await sheet.addRow({ email: newEmail });
// captcha succeeded
print(`email to add: ${newEmail}`)
}
return response.send("done");
}
} catch(err) {
return response.send("error");
}
})
app.listen(4000)