New technologies are emerging by the second, and we didn't even reach the singularity[1] yet - or did we?

Each new technology comes with a lot of promises, making it easy to get swept away by the hype. As a software engineer, staying ahead of the curve is a big part of the profession, however picking the right tool for the job is just as critical. Striking a balance between novelty and proven technology is one of the most impactful factors determining the success of a software project.

In this category of "Old but gold", one gift that keeps on giving is the Google Apps Script platform. Just keep using it for long enough and Google Workspace (Docs, Sheets, Drive ...) starts to feel like an extension for Apps Script rather than the other way around.

I've recently wanted to add a newsletter to my blog, notably because I started writing a series and I thought some readers might want to know when the next post is out.

I have used marketing automation and emailing platforms before, but that felt a bit overkill for this use case. All I want to do is send a periodic update containing the posts I made in that period. And maybe an occasional note alongside that.

Okay I'm an engineer, I can put this together quickly. You probably already see it coming, this will overshoot and take forever and I will end up in a rabbit hole debugging my emailing system instead of writing the actual posts. No. I'm not gonna fall for this one again. NIH[2]  syndrome is insidious.

Don't fall for it

I fell for it. It still took less than a day in total and I ended up with the following flow on the subscription side:

1. A user enters their name and their email in a box on my website
2. The user receives a confirmation email (to avoid people subscribing email addresses they don't own)
   
3. The confirmation email contains a link to open a confirmation page instead of confirming the subscription immediately on click (to avoid email apps opening the confirmation link accidentally)
   
4. A thank you page is displayed.
   

On the admin side, I have a spreadsheet with the list of subscribed emails, their subscription and confirmation dates, and any notes I might add if it's someone I know.

The script is automatically backing up this spreadsheet, itself, and deleting older backups.

On the newsletter side, each email is sent from my gmail address. It also contains an unsubscribe link that opens an unsubscription confirmation page with a button. Clicking on the button will delete the user from the list of emails. They will be completely deleted when the oldest backup containing their data expires.

That's it. A completely custom system with very little code in it, so not much to maintain.

As for actually sending the newsletter emails, I'm planning to send personalized emails until the number of subscribers becomes too unwieldy, if ever.

How did I build it?

If you're a Google user (Gmail, Sheets, Drive ..) and this is the first time you hear about Google Apps Script, oh boy are you in for a surprise. It's an automation platform allowing you to orchestrate Google services together programmatically, expose API endpoints (implemented in a serverless-like environment), and even serve HTML content, with zero hosting or deployment overhead.

The only constraint is that it's based around a programming language (almost identical to Javascript). It requires some coding, but once you get past that initial learning curve, some crazy possibilities are open to you. From this point below, I am assuming you have some familiarity with programming so you can follow along. If not, and you have an urgent need for a newsletter, you are better served by commercial providers or whatever your CRM provides.

Back to my newsletter. It's actually a pretty simple script, with Google services doing all the heavy lifting, and a holy spreadsheet connecting it all together.

This is the code for the subscription endpoint my website connects to. Notice how easy it is to read, write from Sheets and to send emails with Gmail:

/**
  * Subscribes a new user to Omar Kamali's newsletter.
  * @param {Object} subscriber - The subscriber object.
  * @param {string} subscriber.name - The name of the subscriber.
  * @param {string} subscriber.email - The email address of the subscriber.
  * @return {boolean} True if the subscription was successful, false otherwise.
  */
  
function subscribe({name, email}) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Subscriptions");

  // Check if the email is already in the spreadsheet (i.e. subscribed)
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][1] == email) {
      return false;
    }
  }

  // Store a new subscriber entry in the sheet
  const token = generateToken();
  sheet.appendRow([name, email, new Date(), token]);

  // Send a confirmation email
  const body = `Thank you for subscribing to my newsletter!

Please click the link below to confirm your subscription:

${ScriptApp.getService().getUrl()}?token=${token}&p=confirm-subscribe`;

  GmailApp.sendEmail(email, "Confirm subscribing to Omar Kamali's newsletter", body);

  // Send an update to my personal Discord server
  sendDiscordMessage("Subscription initiated", {name, email});

  return true;
}

This function subscribe is triggered when the user submits the form on my website. It saves a record in Sheets and then sends a confirmation email using Gmail.

More secret sauce

You might have noticed that the script is supposed to display multiple pages depending on the context. There are four pages in total:

1. Subscription page, which you get when you submit the newsletter form below this article.
2. Subscription confirmation page, which you get when you open the confirmation link sent via email.
3. Unsubscription confirmation page, which you get when you open the link to unsubscribe in any of my newsletter emails.
4. The famous 404, if someone somehow hits an unknown page.

In a regular web app context, you would have a router (like express for node.js), with which you can handle different routes such as /subscribe, /confirm, /unsubscribe. A Google Apps Script has only one function named doGet to handle GET requests, and one function named doPost to handle POST requests. So I implemented a sort of routing pattern:

function doGet(e) {
  let content = '';
  const path = e.parameter.p;
  const token = e.parameter.token;

  switch (path) {
    case "confirm-subscribe":
      content = render('confirm-subscribe', { token });
      break;
    case "confirm-unsubscribe":
      content = render('confirm-unsubscribe', { token });
      break;
    default:
      content = render('404');
  }

  return HtmlService.createHtmlOutput(content);
}

function doPost(e) {
  let content = '';
  const path = e.parameter.p;

  switch (path) {
    case "subscribe":
      content = render('subscribe', { email: e.parameter.email, success: subscribe(e.parameter) });
      break;
    default:
      content = render('404');
      break;
  }

  return HtmlService.createHtmlOutput(content);
}

As for the render function, it uses Google's own templating engine and some HTML template files to output the HTML for each route. This is what it looks like:

function render(templateName, data) {
  const html = HtmlService.createTemplateFromFile(templateName);

  html.data  = data;
  
  return html.evaluate();
}

And this is what some of the HTML looks like for the confirm-subscribe route:

<div id="message">
  Please confirm subscribing to my newsletter.
  <div>
    <button
      onclick="confirmSubscription('<?= data.token ?>')"
      class="bg-blue-500 rounded-lg px-4 py-1 text-white mt-2">
      Subscribe
    </button>
  </div>
</div>

Welp, that's it!

I hope this inspires you to automate some of your workflows on Google Workspace and create completely new functionality and experiences for your users using this pretty simple and powerful technology.

You can start your journey with Google Apps Script automation using the official documentation here.

-

By the way you can also subscribe to my blog using Monitoro. Then you can receive an alert on Telegram, Discord or any chat app. Or send my posts to Google Sheets, Airtable and trigger Zapier. More on this at https://monitoro.co.

-

[1] https://en.wikipedia.org/wiki/Technological_singularity

[2] https://en.wikipedia.org/wiki/Not_invented_here