Make a newsletter with Node and Google Spreadsheet

Prerequisites

Create a Captcha

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

• Create a new Site
• Add localhost and worker1.znote.io in Domains list
• Copy your keys (public and private)
• Save Save your public and private keys

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 : 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

Installation

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

Newsletter code

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)

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 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 });    
}

Create the final Job to publish

//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)