sei-uno-zero-nove

Let’s say you have a dentist appointment next Tuesday at 3 PM, so you configure the software to send you a reminder email at 2:30.

Then, if you’re anything like me, on the day of the appointment the reminder note arrives in your inbox and you dismiss it almost thoughtlessly, saying to yourself “Oh yeah. The dentist. I’ll get up and go in a second, as soon as I finish debugging this one function.”

The next time you blink your eyes, it’s eight o’clock in the evening. Not only have you missed your dentist appointment, but also your dinner.

When I’m in the zone, it’s hard to snap out of that reverie, so those online appointment reminder services don’t work very well for me.

But what if I could tell the software to send me a barrage of notifications? [...] That would probably get my attention, and I’d probably make it to my dentist appointment.

I know there are a zillion reminder services out there. But what if there was another one, capable of ridiculous, incessant pestering?

(from http://benjismith.net/index.php/2006/07/12/biz-idea-16-incessant-pestering/)

Important: Here I do not speak of simple reminders. I speak of pestering (or nagging, or snooze) reminders. It’s different! Don’t think that you can use Google Calendar or Remember the Milk to have the same feature!

Pestering: a killer feature

I am a nostalgic fan of the (imho) wonderful PingMe reminder web service, which unfortunately has been discontinued by Zemetic company last spring, due to overwhelming difficulties outlined in this blog post.

After PingMe shut down I had to look for a valid alternative among the many other free web reminder services. Apparently, this did seem a simple task, even because I was interested only in email reminders and I hadn’t other very sophisticated needs. Still I really missed a feature I can’t live without and that I have found only in PingMe service: the support for pestering reminder, which is a reminder repeatedly sent at given intervals until it is explicitily turn off (in PingMe this could be done in a very simple way by replying “done” or “ok” to the pestering email reminder).
For me pestering support is absolutely useful, since I am a very absent-minded person: I tend to forgive every to-do note just after few minutes I read it and therefore I really need nagging advices to stop only after having done the to-do job.

Why a DIY reminder?

Since, as I have already pointed out, I was not able to find any reminder service that supports pestering alarms, I have decided to build it by myself. My initial wish was to have a fully email-driven reminder system which would have allowed to create, edit and delete reminder entries directly by email message send as in PingMe and other reminder web services. On the other hand, after bailing out some other ideas, I have realized that the great facility of Google docs, even not allowing identical operating ways, has all I need to easily make a convenient personal email reminder system with pestering support and email integration:

• a database to put and organize all reminder tasks (by a spreadsheet);
• a way to interact with the database to stop pestering (by a form);
• a procedure to check reminder deadlines and handle stop pestering commands (by some script triggers).

Eventually, I have set up a minimalist but fully working reminder app. Its logic is very simple. The user can create, modify and delete his reminders in a sheet. A script checks automatically at regular intervals all the reminders, sending an email for each one whose deadline has arrived and then updating its status in the sheet. In the case of a pestering reminder, the email allows to click a button which activates another script turning off the reminder pestering option in the sheet.

Don’t think it is too hard to be replicated. I have already done all the work, and the reminder app is available right away as a Google worksheet at https://spreadsheets.google.com/ccc?key=t_HlNtR4xyDktL7hS_EwiBQ&authkey=CPyK-YMP#gid=0.
So, if you are eager to start using the app, you can jump directly to section Ready to start?. If on the other hand you want to understand the app structure, you can continue to read next sections where I explain its components: the spreadsheet, the form and the scripts.

The Spreadsheet

The spreadsheet contains four sheets:

1. 

   alarms contains the reminders table-list; note down that fields:

   • ID must be manually input as an unique row identifier;
   • repeat and pester require values yes or no;
   • repeat time interval accepts one of these values: years, months, weeks, days or hours;
   • pester time interval accepts one of these values: days or hours;
   • next scheduled time and next pester time are calculated by two formulae, the first one appearing on the formula bar in the screenshot, based on date and time of deadline or its last occurrence in the case of a recurrent reminder;

   Note: You can freely change field names, for example translating them in your language.

   

2. 

   template contains an empty template row to copy and paste in the alarms sheet if the user want to add a reminder having the formulae automatically set up. Obviously, the two formulae needed for a new reminder can be copied from the previous row in alarms sheet, so that template sheet is not really essential.

   

3. 

   input is dedicated to receive the form data submitted by the user to stop pestering reminders. The screenshot simulated the submission of a form, even if the user does not need to interact with this sheet at all.

   

4. 

   parameters, besides containing some cells which the scripts use to check and update reminders, attends the user to personalise the app; in particular:

   • email to: address is the recipient address where the email reminders are sent;
   • message subject template and message text template define subject and body content of the email messages to send for normal and pestering reminders, following exactly the same conventions used in the Google tutorial https://code.google.com/intl/it-IT/googleapps/appsscript/articles/mail_merge.html#section2.

   

The Form

The form serves the purpose to record the identifier of the pestering alarm to be stopped in the worksheet.

Indeed, the parametrization of pestering reminder email content as it appears in parameters sheet screenshot allows to build the message body inserting the link of the pre-filled form to submit and then to write the current alarm ID in input sheet.

The scripts

My JavaScript skill level is nearly zero. Luckily, after reading some Google documentation I have realized that almost all the code I needed is explained in the Simple Mail Merge Tutorial published by Google team and it was not so hard to write down the remains. This is so even because some tasks (mainly condition tests and time calculations) are performed by formulae in alarms and parameters sheets.
The full source code of my app is available at page http://www.antoniorinaldi.it/wp/wp-content/uploads/2010/my_calendar.js.google. Here I present only the two core functions I have written (a bit) or recycled (for the most part) and explain how they run automatically:

• checkAlarms uses named ranges in parameters sheet to initialize and assign the necessary variables, read all the populated rows in alarms sheet and scan all the reminder deadlines, sending the proper email message and updating the status for each fall due reminder. It is a slight modification of sendEmails function in the cited Google tutorial, to which I refer the interested reader.

// Read the reminder data to process every due item
// sending an email reminder and updating its status.
function checkAlarms() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("alarms");

  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, dataSheet.getMaxColumns());

 
  // Assign parameter values stored in parameters sheet to proper variables.
  var parametersSheet = ss.getSheetByName("parameters");
  var currentTime = ss.getRangeByName("currentTime").getValue();

  var addressTo = ss.getRangeByName("addressTo").getValue();
  var emailSubjectTemplate=[];

  emailSubjectTemplate[0] = ss.getRangeByName("emailReminderSubjectTemplate").getValue();
  emailSubjectTemplate[1] = ss.getRangeByName("emailpestersubjecttemplate").getValue();

  var emailBodyTemplate=[];
  emailBodyTemplate[0] = ss.getRangeByName("emailreminderbodytemplate").getValue();

  emailBodyTemplate[1] = ss.getRangeByName("emailpesterbodytemplate").getValue();
 
  // Create one JavaScript object per row of data.

  objects = getRowsData(dataSheet, dataRange);
 
  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.

  // Cycle starts from 1 because index 0 is used to retrieve field column position.
  // See also comment in getObjects function.    
  for (var i = 1; i < objects.length; ++i) {

    // Get a row object
    var rowData = objects[i];
 
    // Check reminder deadline.

    var passDate = (rowData[NEXTSCHEDULEDDATE] < currentTime && !rowData[NEXTSCHEDULEDDATE] == "");

    if (passDate || (rowData[NEXTSCHEDULEDPESTER] < currentTime && !rowData[NEXTSCHEDULEDPESTER] == "")) {

      var fPester = 0;
 
      if (rowData[PESTER] == "yes") {

        fPester=1;
      }
 
      // Allow alarm date to be properly used in the email template.
      rowData[LASTDONEDATE] = rowData[LASTDONEDATE] || rowData[NEXTSCHEDULEDDATE];

 
      // Generate a personalized email.
      // Given a template string, replace markers (for instance ${"First Name"}) with
      // the corresponding value in a row object (for instance rowData.firstName).
      var emailBody = fillInTemplateFromObject(emailBodyTemplate[fPester], rowData);

      var emailSubject = fillInTemplateFromObject(emailSubjectTemplate[fPester], rowData);
      MailApp.sendEmail(addressTo, emailSubject, emailBody);

 
      // Update data.
      if (fPester == 1) {
        dataRange.offset(i-1,objects[0][LASTDONEPESTER],1,1).setValue(rowData[NEXTSCHEDULEDPESTER]);

      }
      if (passDate) {
        dataRange.offset(i-1,objects[0][LASTDONEDATE],1,1).setValue(rowData[NEXTSCHEDULEDDATE]);

      }
    }
  }
  // Update last execution time.
  ss.getRangeByName("lastExecutionTime").setValue(currentTime);

}

• stopPestering also uses a parameters sheet cell, whose formula refers to the form data submitted in input sheet, to retrieve the row index of the pestering reminder in alarms sheet to turn off and update.

// Read submitted form data and process reminder to stop pestering.
function stopPestering() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dataSheet = ss.getSheetByName("alarms");
  var dataRange = dataSheet.getRange(2, 1, 1, dataSheet.getMaxColumns());

  // Read first line and structure of alarms data
  objects = getRowsData(dataSheet, dataRange);
 
  var inputSheet = ss.getSheetByName("input");

 
  // Loop through submitted forms in input sheet.
  while (!inputSheet.getRange("A2").getValue() == "") {

    // Retrieve reminder row position in alarms sheet.
    var errorStop = ss.getRangeByName("currentPesterRow").offset(0,1).getValue();

    if (!errorStop) {
        var currentStopAlarmRow = ss.getRangeByName("currentPesterRow").getValue();

        // Update reminder data.
        dataRange.offset(currentStopAlarmRow-2,objects[0][LASTDONEPESTER],1,1).setValue("");

    }
    // Clear space in input sheet.
    inputSheet.deleteRow(2);
    inputSheet.insertRowAfter(inputSheet.getMaxRows())

  }
}

Now, the key point. To have the app scripts running in unattended mode, Google allows to set up trigger events. From the spreadsheet menu bar, select Tools > Scripts > Script editor > Trigger > All the triggers.

Setting fields as in the screenshot, stopPestering is invoked automatically at every form submission, while checkAlarms run every hour. Notice that in this way an email reminder could be delivered one hour after its deadline time. If, for example, checkAlarms run o’clock every hour, and the user set a reminder for, say, 7:01 AM, the first script execution that processes such reminder will be at 8:00 AM. It’s not anything wrong for me. I have some (probably groundless) scruples on efforts that Google servers must carry, and above all I don’t need a more accurate delivery time, but obviously you can try a different trigger time specification.

Ready to start?

To start using the app, you have to complete the following steps:

1. Open a Google account, if you haven’t already one.
2. Do a copy of the worksheet https://spreadsheets.google.com/ccc?key=t_HlNtR4xyDktL7hS_EwiBQ&authkey=CPyK-YMP#gid=0 in your Google docs page, and open it.
3. Click Form > Go to live form, then copy the url of the opening window and paste it over the link inside the text of cell C12 in parameters sheet if they are different.
4. Define one or more short-time reminders in alarms sheet checking that cells in columns M and O contain the prepared formulae.
5. Do not forget to replace the predefined recipient email address in parameters sheet with yours.
6. Verify that scripts have the authorization to run by clicking Tools > Script > Manage from the spreadsheet menu bar and then clicking on Run button after having selected checkAlarms and stopPestering scripts. If the following window appears,
   

   click on Authorize button.

Done! Wait for the first reminder message arriving in your mailbox. It should look as the following one:

Important: The app is provided as is, without warranty of any kind. Especially, I am not responsible if you misuse of the Google docs Send Email capability.
Remember also that I have developed it mainly as a daily reminder. In such capacity, it does the dirty work I expect. If you need a more time refined reminder, you can shorten the timer interval trigger for checkAlarms till 5 minutes, but in this case I don’t assure that all the due email reminders will be sent.

Edit: After some insistence I have tried to set trigger time interval for stopPestering script to 5 minutes, without noticing any reminder delivery problem in my gmail mailbox. Therefore, I am confident that you can successfully do the same with your copy of my Google doc.

Please let me know what you think about my work leaving a comment. I will be happy to read any opinion, suggestion or, most important, any bug report.

Possible enhancements

As I have stated at the beginning, I am eager only in email reminder system. If someone is interested, I think that he could get sms reminder support by subscribing some mail-to-sms gateway service and setting a proper forward filter in his mailbox.
Apart such opportunity, possible enhancements of my simple app are virtually endless.
Just let me do a couple of very simple and very small examples.

• Use a more elegant form. Somebody could criticize the form layout I have used showing the ID text box which the user have to not interact. There are several ways to remedy. The two ones which require the minimum effort consist of copy and paste the form html code on a web page you have full access and then: add some css lines to set the visibility property of the text box element to hidden, or change the type attribute of the text box from text to hidden.
• Hang a pestering reminder. As PingMe allowed to temporarily suspend a pestering reminder besides turn it off, it should be not difficult to modify the form by adding a radio button and then to add a few code script lines to let the user choose between stop and hang up the reminder.

These ones and many other enhancements should make the most of Google form customization, which allows to change the appearance and the behaviour of a form when it is moved from its original Google hosting page.

Credits

Portions of the code shown in this page are modifications based on work created and by Google and used according to terms described in the Commons 3.0 Attribution License.