Allow users to update their contact details via a periodic emailed reminder

I am a big fan of Romain Vialard's scripts and I have built on a couple of his published tricks to create the following tool.

Our requirement was to replace an existing SharePoint based emergency contacts list, and to hopefully include a workflow style function to remind users to periodically update their details.

I had thought that unlike SharePoint, Google did not have an editform capability for previously submitted forms, but after a bit of searching I was happy to be proved wrong. In this solution, I adapted some of Romain's scripts used for his Awesome table and Events booking tools. To start I created a form and linked sheet to collect staff emergency contact details, and added two new column headers "editurl" and "Reminder Date". For my sheet, these were columns 10 and 11, which I then referenced in the script.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var formURL = 'https://docs.google.com/a/nihr.ac.uk/forms/d/0123456789abcdsefghijklmnop/edit'; // enter the edit url for the form
var sheetName = 'Form Responses';
var columnIndex = 10;  // this stores the edit url for each form
var columnNewTime = 11;  // this stores the date for the next emailed reminder

I then created function getEditResponseUrls. This identifies the edit form URL from each submitted response via the timestamp and writes this to the editURL field. It also takes the current date, and then adds 6 months to it, writing it to the reminder column.

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 1; i < data.length; i++) {
    if(data[i][0] != '' && (data[i][columnIndex-1] == '' || !data[i][columnIndex-1])) {
      var timestamp = data[i][0];
      var now = new Date().getTime();
      var newtimestamp  = new Date(now);
 newtimestamp .setDate(newtimestamp .getDate()+182);
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
     var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl); 
       sheet.getRange(i+1, columnNewTime).setValue(newtimestamp ); 
    }
  }
}

 I set the trigger for this to be on form submit, so that each time a user enters a form, it sets the reminder date and appropriate edit link.

The next function deals with the emailed reminders  This function checks to see if the next reminder date is in the past, and if so, emails the user with a link to their edit form, then sets the next reminder date 6 months in the future.

function sendUpdateEmail() {
  var dataSheet = ss.getSheetByName("Form Responses");
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var headers = dataSheet.getRange(1, 1, 1, lastColumn).getValues()[0];
 var reminderColumn = headers.indexOf('Reminder Date') + 1;
  var dataRange = dataSheet.getRange(2, 1, lastRow, lastColumn);
  var objects = getRowsData_(dataSheet, dataRange);
  for (var i = 0; i < objects.length; ++i) {
    try {
      var rowData = objects[i];
      var now = new Date().getTime();
   var reminderDate = new Date(rowData.reminderDate).getTime();
 var nextReminderNew  = new Date(reminderDate);
 nextReminderNew .setDate(nextReminderNew .getDate()+182);
if((reminderDate - now) < 0){
        var emailAddresses = rowData.yourEmail;
        var editURL = rowData.editurl;
        var emailSubject = "Please review your Emergency Contact details";
       var emailText = "Please can you review your Emergency Contact details and update if required by <a href=" + editURL + ">following this link</a>";
        MailApp.sendEmail(emailAddresses, emailSubject, emailText, {
          htmlBody: emailText
        });
        dataSheet.getRange(i + 2, reminderColumn).setValue(nextReminderNew);
      }
    }
    catch(e){
      Logger.log(e.message);
    }
  }
}

I then added a timed trigger for this function, so the sheet checks each day whether any emails need to be sent out

For this all to work correctly however there are a few references in the above scripts to some useful functions that Romain had included in his 'Utilities' script on his event booking template. These functions include a very useful one that grabs column headings and allows these to be referenced in the main scripts. I included the appropriate functions here verbatim.

//////////////////////////////////////////////////////////////////////////////////////////
//
// 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
function getObjects_(data, keys) {
  var objects = [];
  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 < headers.length; ++i) {
    var key = normalizeHeader_(headers[i]);
    if (key.length > 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 < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 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();
    }
  }
  return key;
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit_(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit_(char) {
  return char >= '0' && char <= '9';
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty_(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

Hope this is useful - any comments or code improvements welcomed.