📬 Newsletter Subscription with Google Spreadsheet

Set up a simple newsletter subscription backend using Node.js, Google Spreadsheet, and Google reCAPTCHA.

---

✅ Prerequisites

1. Set Up Google reCAPTCHA

→ Go to Google reCAPTCHA Admin Panel

• Create a new site (choose reCAPTCHA v2 or v3 depending on your needs)
• Add the following domains: localhost and worker1.znote.io
• Copy your site key (public) and secret key (private)

---

2. Enable Google Sheets API

→ Go to Google Cloud Console

• Select or create a project
• Navigate to APIs & Services > Library
• Search for Google Sheets API, then click Enable

---

3. Create a Service Account

• Go to APIs & Services > Credentials
• Click CREATE CREDENTIALS → select Service Account
• Fill in name and description, click Create
• Skip permission settings → click Continue
• Click CREATE KEY → choose JSON, then download the file
• Locate the client_email field in your JSON key
• Share your spreadsheet with that email address (Editor access)

---

Useful Docs

• google-spreadsheet NPM package
• Official usage guide
• Google Sheets API Reference

---

⚙️ Installation

In your Znote environment:

1. Copy your JSON credentials file Name it google-spreadsheet-credentials.json and place it in your project folder:

open .

1. Install required packages

npm install -S google-spreadsheet@4.1.1 google-auth-library@9.6.3 express body-parser node-fetch@2.6.6

---

🧩 Newsletter Form (Frontend)

<script src="https://www.google.com/recaptcha/api.js"></script>
<form id="demo-form" action="http://localhost:4000/subscribe" method="POST">
  Email: <input type="email" name="email" required />
  <button class="g-recaptcha"
    data-sitekey="YOUR_PUBLIC_CAPTCHA_KEY"
    data-callback="onSubmit"
    data-action="submit">Subscribe</button>
</form>
<script>
function onSubmit(token) {
  document.getElementById("demo-form").submit();
}
</script>

---

🧠 Backend Logic

Step 1: Captcha Verification & Email Logging

//exec: node
//hide
const express = require('express');
const fetch = require('node-fetch');
const app = express();

app.use(express.urlencoded({ extended: true }));

app.post('/subscribe', async (req, res) => {
  const data = new URLSearchParams();
  data.append('secret', 'YOUR_PRIVATE_CAPTCHA_KEY');
  data.append('response', req.body["g-recaptcha-response"]);

  const result = await fetch("https://www.google.com/recaptcha/api/siteverify", {
    method: "POST",
    body: data,
  });

  const json = await result.json();
  if (json.success) {
    console.log(`New email to save: ${req.body.email}`);
    res.send("Captcha passed. Ready to save.");
  } else {
    res.status(403).send("Captcha verification failed.");
  }
});

app.listen(4000);

---

Step 2: Save Emails to Google Spreadsheet

//hide
const { GoogleSpreadsheet } = require('google-spreadsheet');
const { JWT } = require('google-auth-library');
const creds = require('./google-spreadsheet-credentials.json');

const jwt = new JWT({
  email: creds.client_email,
  key: creds.private_key,
  scopes: [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive.file',
  ],
});

const doc = new GoogleSpreadsheet('SPREADSHEET_ID', jwt);  
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];

const newEmail = "john@example.com";

// Check and insert
const rows = await sheet.getRows();
const exists = rows.some(row => row.email === newEmail);

if (!exists) {
  await sheet.addRow({ email: newEmail });
}

---

🔁 Final Znote Job: Newsletter API

//exec: node
//hide
const express = require('express');
const fetch = require('node-fetch');
const { JWT } = require('google-auth-library');
const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./google-spreadsheet-credentials.json');

const SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive.file',
];

const jwt = new JWT({
  email: creds.client_email,
  key: creds.private_key,
  scopes: SCOPES,
});

const app = express();
app.use(express.urlencoded({ extended: true }));

const doc = new GoogleSpreadsheet("SPREADSHEET_ID", jwt);
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];

app.get('/', (req, res) => res.send('OK'));

app.post('/subscribe', async (req, res) => {
  try {
    const captchaRes = new URLSearchParams();
    captchaRes.append('secret', 'YOUR_PRIVATE_CAPTCHA_KEY');
    captchaRes.append('response', req.body["g-recaptcha-response"]);

    const verify = await fetch("https://www.google.com/recaptcha/api/siteverify", {
      method: "POST",
      body: captchaRes
    });
    const result = await verify.json();

    if (result.success) {
      const email = req.body.email;
      const rows = await sheet.getRows();
      const exists = rows.some(r => r.email === email);
      if (!exists) {
        await sheet.addRow({ email });
        console.log(`✅ Added: ${email}`);
      }
      return res.send("Subscribed!");
    } else {
      return res.status(400).send("Captcha failed.");
    }
  } catch (err) {
    console.error(err);
    return res.status(500).send("Server error.");
  }
});

app.listen(4000);

---

🎉 You're Done!

You now have a working backend that validates reCAPTCHA and stores emails in a Google Sheet—perfect for collecting newsletter subscribers or waitlist signups.

Let me know if you want:

• A hosted version via Znote
• Multi-list support (e.g. tags or campaign sources)
• HTML + Tailwind form version
• Confirmation email or double opt-in flow

I can add those too.