Using Google Apps Scripts to create an automatic email subsscription service

Maintaining an email list for email subscription service is a headache.  What I mean is a sincere service and not for spam email dissemination.

The steps will be as follows:

1. to collect email address
2. to confirm the email address is capable of receiving emails and the email address registration is intended (by sending a confirmation email and request acknowledgement)
3. to process the acknowledgement
4. to send email periodically based on the maintained email list

I encountered Google Apps Scripts last month and starts to think how I can make use of this to automate the above process.  There was already tutorial on how to handle step 4.  This blog will focus on steps (1) to (3).

I shall use Google Spreadsheet to maintain the email list.

For step 2, there can be two alternatives (i) requiring user to send email to a designated address; (ii) allowing user to input an email address on a web page.  Since I think some users may use receive-only email address, I decide to choose alternative (ii).

So, for step 1, the most straight forward method is to use Google form, as shown below:

Then I shall use Google Apps scripts to process the registration request.  Google supports many methods to invoke Apps scripts (link), one of which is the form-submit event handler.  Here I setup a method "send_confirmation" as follows:

I call this method send_confirmation because it will send an email to the inputted email address for confirmation purpose.  The source is as follows:

Before sending the email, I will assign a random number for a unique key (which will be included in the email subject).  This number will be recorded in the spreadsheet.  However, I find the range passed into the method (e.range) has only the fields populated by Google form.  Therefore I need to use the Spreadsheet object to do the update.  Moreover, there is a tutorial to use sheet.getLastRow to return the row number for update.  But I wonder whether there is any locking mechanism to prevent concurrent update.  Therefore I use e.range.getRowIndex instead.

I would expect the user to reply the email to confirm the email address is for an intended registration.

To process the email reply (step 4), I use another trigger "checkGmail".  I originally think whether there is any asynchronous event handler at Gmail for any event like emailReceived.  But I fail.  Therefore I use a periodic event (Time-driven) to periodically poll my Gmail inbox for an reply.

The source is as follows:

My email search criteria is 'in:inbox is:unread subject:"xxx"' to ensure that only new emails are processed.  Again I use the Spreadsheet object to search for the email record (using the unique random number).  If found, I will update the spreadsheet with a time-stamp to confirm the email address is geniune.

Finally I have included a method formatDate because I find there is not a dateFormatter in Javascript.  But it is easily copied from internet.