build your own pestering email reminder using Google docs

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.
    ss2.png
  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.

    ss4.png
  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.

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

    ss8.png

The Form

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

ss9.png

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 https://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.

ss10.png

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,
    ss12.png

    click on Authorize button.

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

ss11.png

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.

32 thoughts on “build your own pestering email reminder using Google docs

  1. Hello,

    This pester function is really athing which 1/2% of these service purposes :-( you must warn before than google calendar only pest every day or more, not for short-time event as “take the dog out if it’s nice out”

  2. Pingback: sei-uno-zero-nove » Blog Archive » promemoria con ripetizione

  3. Great work.
    I am getting a error when I run the “stop pestering” as follows. “It looks like someone else already deleted the cell.” please rectify.

    Appreciate if there is an option for adding different email address for each reminder activity. I want to timely message to all my friends such as birth day,wedding day etc. please help

  4. I think that you have tried to run “stop pestering” from the script console (Tools > Script manager). Instead, you don’t need at all to run such a script, which is automatically executed when you submit a form. Haven’t you tried to submit a form?
    Surely, it is possible to allow different email addresses. I use the app as a personal to-do (pestering) reminder, so I’m sorry to be not interested in adding such a feature, but you can do it by yourself adding a column in alarms sheet and then making some little changes to check_alarms script (see rows 56 and 92).

  5. Thank you very much for your reply. Regarding Stop pestering.I have
    already tried submitting the form and and it does not stops getting
    reminders.(it keeps sending emails)

    I am happy to hear that it is possible to add differnet email addresses.
    But please tell me what changes exactly to be added in rows56 and 92
    after inserting a column named, Email address in the Alarm sheet.

    Please help me.

  6. Are you sure to have done step 3 listed under “Ready to start?”? Open the sheet. Go to Form > Go to live form. Compare the page url with the one you find in cell C12 of sheet parameters: are they equal?
    I’m sorry but I am not inclined to make all the work to allow different email recipients. As I have already said, this is a personal reminder app and the change you ask for can be used for spamming purposes. Furthermore, I see no usefulness in pestering reminders for friend birthdays. If you want, you can ask for support on google apps script forum. Good luck.

  7. yes. I have done the step 3. When I Keep submitting the form again and again say 3 times it drops the value say 12 one below one in the ID Column as 3 rows in “input sheet”. Also B4value in Parameters sheet is 13 and C4 says False.
    I love to stop getting reminders using form. Now I had to stop manually by opening the Alarm Sheet and enter “No” in column G(repeat).
    Please help.

  8. Oh, you are right. There is a name which points to the wrong cell. Please do Insert > Named range > Manage ranges, and set B4 for currentpesterrow. So it should work. I’ve already corrected the shared spreadsheet.
    I apologize for the inconvenience and thank you for the debug.
    Please tell me if you have any other problem.

  9. I have followed all the instructions and after I click “Run” on “checkalarms” in the script manager screen, I do receive emails for all scheduled reminders that are past due.

    However, I don’t receive reminders scheduled in the future until I login and run the script again.

    How can I get the script to run in the background when I am not logged into My Drive?

  10. Fred, check in Tools > Script Editor > Resources > Current Script’s Triggers as I have described in the post. Do you see stopPestering and checkAlarms?

  11. I have configured the triggers. Just to test the system I set it to minute, every minute. Then i set 3 reminders at #:00, #:10, #:20 with repeats 0.5 hours.

    The first reminders come in perfectly spaced as scheduled. Then 0.5 hours later the next reminder comes in as scheduled.

    But after the 2nd reminder, they are sent every minute, non-stop.

    What did I do wrong?

  12. Fred, what have you set in pester/pester every/pester time interval columns for your reminders?
    Moreover, have you clicked on the link in the reminder messages and submitted the corresponding forms?

  13. Fred,
    Furthermore I suggest you to change the time interval for your triggers from 1 minutes to at least 5 minutes.
    Google set a limit of 500 emails/day (I remember so when I did work on this app), and you risk to reach such a limit after few hours.

  14. Pester is set to “no” because I only wanted to test the reminder.

    Pester every & interval are both blank since they aren’t needed.

    I’m not receiving any pester emails, just reminders.

    I’ve stopped the reminders until I can test again with new settings. But thanks for letting me know about the 500 emails a day limit.

  15. Fred, thanks for your feedback. Could you share your sheet with me, after removing any personal data, so I can check which is the exact source of your problem?

  16. Fred, I checked the sheet you sent me privately. I had no problem.
    “repeat” is set to “no” for every alarm. So it is for “pester”.
    I have blanked “last done date” column, waited for the reminders (that are sent since a blank cell in “last done date” column is interpreted as “no reminder has been sent so far”), and then checked that “last done date” was regularly updated and (most of all) “next scheduled date” was still empty.
    Remember that after a reminder message is processed “next scheduled date” column will contain the time of the new reminder.

  17. Fred,
    I hope eventually to have understood the busillis. The formula in column M don’t work properly when the repeating period is less than one day, so I’ve updated the “next scheduled date” formula. You should open my original sheet, copy cell M2 in template sheet and paste it in cell M2 of your template sheet and in column M of your alarms sheet (new readers don’t need to do anything since I’ve updated my shared sheet). This should solve the problem.
    Anyway, remember a thing. Suppose that today, now, it is Aug 20, 17:55PM and that you set an alarm at Aug 19, 7:00AM (that is, in the past) to repeat every 30 minutes (or 0.5 hours).
    When the first reminder is sent, the next reminder time is updated to Aug 19, 7:00AM + 0.5h, that is to Aug 19, 7:30AM. When the second reminder is sent, the next time is updated to Aug 19, 7:00AM+1h = Aug 19, 8:00AM and so on.
    So, you will receive a reminder every 30 minutes until the next reminder time is greater than the actual time.

  18. Hi,

    I basically copied and did everything you have instructed us to do in here. However, when I tried to save the script, it is giving me an error “Syntax error. (line 13, file “Code”) pointing to line ” var ss = SpreadsheetApp.getActiveSpreadsheet();
    “. Please advise why, I am a novice btw

    • Don’t copy/paste and save anything! You don’t need it! Just open the worksheet whose link is below the “Ready to start” title and follow the successive instructions.

  19. Hi,

    I would be very very very very grateful if you can spare me a time to discuss…I am desperate to have this script running. I am a novice to this kind of stuff —- I am working in this company for 3 months, and I wanna impress my CEO with this kind of task. This would surely boost my confidence. Please help

  20. Btw, this is the script I copied. Sorry for posting it here. I hope it is legal:

    // Following constants have to match the corresponding
    // space-trimmed and lower-case field names in alarms sheet
    // Change them if you change titles in the first row of alarms sheet.
    const LASTDONEDATE = “lastDoneDate”;
    const LASTDONEPESTER = “lastDonePester”;
    const NEXTSCHEDULEDDATE = “nextScheduledDate”;
    const NEXTSCHEDULEDPESTER = “nextScheduledPester”;
    const PESTER = “pester”;

    // Read submitted form data and process the 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())
    }
    }

    // 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 lastReminderColumn = ss.getRangeByName(“lastReminderColumn”).getValue();
    var lastPesterColumn = ss.getRangeByName(“lastPesterColumn”).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);
    }

    // Replaces markers in a template string with values define in a JavaScript data object.
    // Arguments:
    // – template: string containing markers, for instance ${"Column name"}
    // – data: JavaScript object with values to that will replace markers. For instance
    // data.columnName will replace marker ${"Column name"}
    // Returns a string without markers. If no data is found to replace a marker, it is
    // simply removed.
    function fillInTemplateFromObject(template, data) {
    var email = template;
    // Search for all the variables to be replaced, for instance ${"Column name"}
    var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

    // Replace variables from the template with the actual values from the data object.
    // If no value is available, replace with the empty string.
    for (var i = 0; i < templateVars.length; ++i) {
    // normalizeHeader ignores ${"} so we can call it directly here.
    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || "");
    }

    return email;
    }

    //////////////////////////////////////////////////////////////////////////////////////////
    //
    // The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
    // tutorial.
    //
    //////////////////////////////////////////////////////////////////////////////////////////

    // getRowsData iterates row by row in the input range and returns an array of objects.
    // Each object contains all the data for a given row, indexed by its normalized column name.
    // Arguments:
    // – sheet: the sheet object that contains the data to be processed
    // – range: the exact range of cells where the data is stored
    // – columnHeadersRowIndex: specifies the row number where the column names are stored.
    // This argument is optional and it defaults to the row immediately above range;
    // Returns an Array of objects.
    function getRowsData(sheet, range, columnHeadersRowIndex) {
    columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() – 1;
    var numColumns = range.getEndColumn() – range.getColumn() + 1;
    var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
    var headers = headersRange.getValues()[0];
    return getObjects(range.getValues(), normalizeHeaders(headers));
    }

    // For every row of data in data, generates an object that contains the data. Names of
    // object fields are defined in keys.
    // Arguments:
    // – data: JavaScript 2d array
    // – keys: Array of Strings that define the property names for the objects to create
    // **********
    // Modified to have name column positions in the first object.
    // **********
    function getObjects(data, keys) {
    var objects = [];

    var object = {};
    for (var j = 0; j < data[0].length; ++j) {
    object[keys[j]] = j;
    }
    objects.push(object);

    for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
    var cellData = data[i][j];
    if (isCellEmpty(cellData)) {
    continue;
    }
    object[keys[j]] = cellData;
    hasData = true;
    }
    if (hasData) {
    objects.push(object);
    }
    }
    return objects;
    }

    // Returns an Array of normalized Strings.
    // Arguments:
    // – headers: Array of Strings to normalize
    function normalizeHeaders(headers) {
    var keys = [];
    for (var i = 0; i 0) {
    keys.push(key);
    }
    }
    return keys;
    }

    // Normalizes a string, by removing all alphanumeric characters and using mixed case
    // to separate words. The output will always start with a lower case letter.
    // This function is designed to produce JavaScript object property names.
    // Arguments:
    // – header: string to normalize
    // Examples:
    // “First Name” -> “firstName”
    // “Market Cap (millions) -> “marketCapMillions
    // “1 number at the beginning is ignored” -> “numberAtTheBeginningIsIgnored”
    function normalizeHeader(header) {
    var key = “”;
    var upperCase = false;
    for (var i = 0; i 0) {
    upperCase = true;
    continue;
    }
    if (!isAlnum(letter)) {
    continue;
    }
    if (key.length == 0 && isDigit(letter)) {
    continue; // first character must be a letter
    }
    if (upperCase) {
    upperCase = false;
    key += letter.toUpperCase();
    } else {
    key += letter.toLowerCase();
    }
    }
    // Browser.msgBox(key);
    return key;
    }

    // Returns true if the cell where cellData was read from is empty.
    // Arguments:
    // – cellData: string
    function isCellEmpty(cellData) {
    return typeof(cellData) == “string” && cellData == “”;
    }

    // Returns true if the character char is alphabetical, false otherwise.
    function isAlnum(char) {
    return char >= ‘A’ && char = ‘a’ && char = ‘0’ && char <= '9';
    }

  21. I think its time to give up when its time go give up. sad..Im getting the same error when I run the script

    • It seems to me that you have changed the structure of the “alarms” sheet. In my original one, you can choose a snooze alarm in the “Pester” column.

  22. Hi,

    I changed the spreadsheet settings to the UK locale and timezone. Now when the emails are sent, they are sent on time however it is adding a GMT offset of GMT +0200 to the last done time making the email message incorrect.

    How do i resolve this please?

    • Open the spreadsheet and check the value in sheet “parameters”, range B1. What time is indicated?
      The script simply checks if alarm date and time is older than the current time.

  23. Hey Antonio, as I said love this form you have created but I need some help.
    I cant get pestering to stop.
    a) I keep getting email even after I submit the form
    b) Form places entry on line 5 on input sheet

    • Before you submit the form, check your alarm sheet, cell at line 5, columns titled “last done pester”: what does it contain?
      After you submit the form, check your alarm sheet, cell at line 5, columns titled “last done pester”: has it been blanked?
      I send this message to your mailbox too, so if you have any problem yet, you can share your sheet indicating my email.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.