Create a useful Newsletter automation with Node and Google Spreadsheet

Hi all ,

Today I would show you how I recently switch from Mailchimp to an in-house solution for storing my emails with a Node API and a Google Spreadsheet.

We will see here how to use Recaptcha and interact with a Google Spreadsheet

For this tutorial, I internally use my sandbox application, Znote to prototype, deploy and capitalize all my work. You will find in conclusion the link to the copyable recipe directly in the app, otherwise no problem use your favorite tool

How it work?

We will make a Node backend with express and Recaptcha (to avoid spam). Received emails are sent from a HTML form and sent to a Google Spreadsheet.

Prerequisites

Create a Captcha

https://www.google.com/recaptcha/admin

• Create a new Site
• Add localhost and your website domain in Domains list
• Copy your keys (public and private)

Google Spreadsheet

Activate your Sheet API

Go to the Google Developers Console

• Select your project or create a new one (and then select it)
• Enable the Sheets API for your project
• In the sidebar on the left, select APIs & Services > Library
• Search for "sheets"
• Click on "Google Sheets API"
• click the blue "Enable" button

Setup Instructions for service account
Follow steps above to set up project and enable sheets API

1. Create a service account for your project
   • In the sidebar on the left, select APIs & Services > Credentials
   • Click blue "+ CREATE CREDENTIALS" and select "Service account" option
   • Enter name, description, click "CREATE"
   • You can skip permissions, click "CONTINUE"
2. Click "+ CREATE KEY" button
   • Select the "JSON" key type option
   • Click "Create" button
   • your JSON key file is generated and downloaded to your machine (it is the only copy!)
   • click "DONE"
   • note your service account's email address (also available in the JSON key file)
3. Share the doc (or docs) with your service account using the email noted above
   • Open the service_account.json file and find the client_email property.
   • On your Google Spreadsheet, click on the Share button in the top right, and add the email address of the service account as an editor.

Doc

NPM : google-spreadsheet
Doc: Google spreadsheet authentication
Google sheet API doc

Installation

Copy your Google service account json key in your dev folder

Install NPM dependency

npm i -S google-spreadsheetnpm i -S expressnpm i -S body-parsernpm i -S node-fetch@^2.6.6

Newsletter code

We are now ready to code our newsletter HTML 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="[email protected]" 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>

To use Recaptcha you need to serve on localhost your HTML file.
You could done this with http-server for Node

npm install --global http-serverhttp-server .

And Open http://localhost:8080

Server
Now the node backend to receive emails.

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)

Google Spreadsheet

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 folder

Google Spreadsheet code
Let's start to hack some code using Google Spreadsheet

const { GoogleSpreadsheet } = require('google-spreadsheet');const creds = require('./google-spreadsheet-key.json');// Initialize the sheet - doc ID is the long id in the sheets URLconst doc = new GoogleSpreadsheet('YOUR_SPREADSHEET_ID');await doc.useServiceAccountAuth(creds);await doc.loadInfo();const sheet = doc.sheetsByIndex[0];const newEmail = "[email protected]";// read rowsconst rows = await sheet.getRows();const isExists = rows.map(r => r.email).includes(newEmail);if (!isExists) {    await sheet.addRow({ email: newEmail });    }

Plug it all together

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 URLconst doc = new GoogleSpreadsheet('YOUR_SPREADSHEET_ID');await doc.useServiceAccountAuth(creds);await doc.loadInfo();const sheet = doc.sheetsByIndex[0];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)

Tada

To summarize

You now know how to use a captcha and how to connect and manipulate a Google spreadsheet to automate any similar process.

I hope this example will inspire you! Did you find this article useful? If so, I have plenty of other examples to share with you: Recipes

You will find below the ready-to-use recipe:
https://recipe.znote.io/recipes/make-newsletter-with-node-and-google-spreadsheet?td=dark

Go further

If you are looking for an efficient JS playground to continue prototyping, take a look at my app here (I will be happy to take comments)

Meanwhile, start turning your daily tasks into something easy and repeatable and your job will be more fun.

You can check my blog here

And don't forget to subscribe to my newsletter!