Wednesday, April 23, 2014

Newsletter service on Google Apps Script

This blog post was inspired by great ebook Google Apps Script for Beginners.

Google Apps Script (GAS) is tool for manipulating Google Documents, SpreadSheets, Calendar, Gmail, Forms, Sites, …  and automating your workflow. GAS offers nice and powerful API, detailed documentation. In this post I am going to describe how to create Newsletter service on GAS.

Newsletter service - as I called it - uses Spreadsheet as storage for subscriber emails, MailApp for sending newsletter, HtmlTemplate for output. And it is deployed as web app.

Use cases:
  • Subscribe new email
  • Unsubscribe
  • Send newsletter

The benefit of using Newsletter service is that you can connect it for many of your projects. Every project uses own sheet.  All is in one place - in your Google Drive. It is not need to recreate newsletter service on every of your webpages.

Data source

  • isSend - is set to 1 when newsletter has been send.
  • isUnsubscribe - is set to 1 when user clicks to unsubscribe link
  • hasError - is set to 1 when an exception occurred during sending email. Google does not find "Delivery Failure" during sending emails. I saw an obscure way how to someone solved it. :)

Send newsletter

It uses HtmlTemplate. HTML output on GAS is pain. GAS uses Caja Compiler. There are lots of restrictions.

For quality output use email templates:

By default Caja removes HTTP headers from your template, but fortunately this does not apply when you send an email.

It is known that Google has defined daily email quota.


New registration of subscriber is designed as HTTP POST request to Newsletter service. The form must contain the required parameters. Request creates a new record in the sheet.


Unsubscribe url is generated automatically at the end of newsletter template.

Final evaluation

This solution has some advantages but disadvantages and limitations too. It is not probably satisfy for big company who wants to send thousands of emails per day.

It might interest you


I was inspired by a great book Google Apps Script for Beginners

The book contains:
  • Spreadsheet automation
  • Manipulate Forms
  • Managing Email Account
  • Script in text document
  • Standalone Web application

No comments:

Post a Comment