Tuesday, 25 February 2014

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.

Thursday, 13 February 2014

Google Sheets script to automatically fill down formulas to last row

Update 10.03.2014 - since writing this script, I came across this post, which, by using arrays, is a much simpler and more elegant solution than a script. The script method could still have its uses, so am keeping this here as a reference

In Excel, I frequently use a VB script to fill down specific formulas in columns to the same length as in other columns. However, when using Google Sheets, I could not find a script or method that would allow me to do this - so I created the following script.  This also has the advantage of running on edit.

A quick demo of this in action.

video


The complete code is at the bottom of this page if you just want to copy and paste.  Ill explain the methodology though here first by breaking this down.
First, I added a new on-open menu item to allow the user to set this up

//Add the new menu on Open 
function onOpen() {
  var menuEntries = [{
    name: "Autofill Columns on Edit",
    functionName: "onEditTrigger"
  }];
  ss.addMenu("Auto Fill Menu", menuEntries);
}

This trigger function makes the function "onEditAutoFill" run on edit.
I also set some properties. This is used to define the columns and rows that the user will choose to do the fill on.  (I initially tried to declare global and local variables, but found that any subsequent changes did not persist, so discovered this method did the trick instead)

function onEditTrigger() {
  ScriptApp.newTrigger("onEditAutoFill").forSpreadsheet(ss).onEdit().create();
   ScriptProperties.setProperties({  //set zero properties, for use later to check if user has defined the fill down range already
   "start"     : "0",
     "end"     : "0",
     "frows"   : "0",
 });
  onEditAutoFill();   // let's just run this straight away to fill down any data
}


If the user has already defined the columns and rows already, then this part of the script takes the property values for those columns and rows, and finds the last row

var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = ss1.getLastRow();
  var checker = ScriptProperties.getProperty('start'); 
  if (checker != "0") {   //Determine whether user has defined anything already
  Logger.log(checker);
  var startcolumn = ScriptProperties.getProperty('start');   //As this is already defined, take the User entered values
    var endcolumn = ScriptProperties.getProperty('end');  //As this is already defined, take the User entered values
    var numberrows = ScriptProperties.getProperty('frows'); //As this is already defined, take the User entered values

The script then gets the formulae from the first row, sets this as an array, and then pushes that array to the rows and columns it needs to fill down

  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}   
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array
}

However, if the properties are still zero, meaning the user has not already defined the columns and rows to use, this bit runs instead, collecting the user values and writing them to the properties, before the remainder of the script fires and uses those values



else{     //We need to get the User Values
  var startcolumn = Browser.inputBox('Enter Start Column Letter', Browser.Buttons.OK_CANCEL);
    var endcolumn = Browser.inputBox('Enter End Column Letter', Browser.Buttons.OK_CANCEL);
    var numberrows = Browser.inputBox('Which Number row do you want to start your fill from', Browser.Buttons.OK_CANCEL);
    ScriptProperties.setProperty('start', startcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('end', endcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('frows', numberrows);  //Set the properties to the User entered values
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
    Logger.log(rngVal);
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array to the defined range
  }
}


The complete script is here

var ss = SpreadsheetApp.getActiveSpreadsheet()

//Add the new menu on Open 
function onOpen() {
  var menuEntries = [{
    name: "Autofill Columns on Edit",
    functionName: "onEditTrigger"
  }];
  ss.addMenu("Auto Fill Menu", menuEntries);
}
  
function onEditTrigger() {
  ScriptApp.newTrigger("onEditAutoFill").forSpreadsheet(ss).onEdit().create();
   ScriptProperties.setProperties({  //set zero properties, for use later to check if user has defined the fill down range already
   "start"     : "0",
     "end"     : "0",
     "frows"   : "0",
 });
  onEditAutoFill();   // let's just run this straight away to fill down any data
}

function onEditAutoFill() {

var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = ss1.getLastRow();
  var checker = ScriptProperties.getProperty('start'); 
  if (checker != "0") {   //Determine whether user has defined anything already
  Logger.log(checker);
  var startcolumn = ScriptProperties.getProperty('start');   //As this is already defined, take the User entered values
    var endcolumn = ScriptProperties.getProperty('end');  //As this is already defined, take the User entered values
    var numberrows = ScriptProperties.getProperty('frows'); //As this is already defined, take the User entered values
    
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}   
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array 
}
  else{     //We need to get the User Values
  var startcolumn = Browser.inputBox('Enter Start Column Letter', Browser.Buttons.OK_CANCEL);
    var endcolumn = Browser.inputBox('Enter End Column Letter', Browser.Buttons.OK_CANCEL);
    var numberrows = Browser.inputBox('Which Number row do you want to start your fill from', Browser.Buttons.OK_CANCEL);
    ScriptProperties.setProperty('start', startcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('end', endcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('frows', numberrows);  //Set the properties to the User entered values
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
    Logger.log(rngVal);
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array to the defined range
  }
}
I am still learning my way with Google Scripts, so if anyone has any improvements to the code, or an easier way to do the same functionality, I would be very happy to know.

Monday, 10 February 2014

Auto Hyperlinks with a Google Sheet

Auto Hyperlinks with a Google Sheet


Thanks to a post found on Stack Exchange - I created a quick script to automatically link to studies on the NIHR Portfolio when entering a study ID into a Google Sheet.

The script is quick and easy to implement, in this example the script looks in the first column for values, ignoring the top row.

function onEdit(e) {
  var activeRange = e.source.getActiveRange();
// ID is going in Column 1 and we don't want to inlcude the first row
  if(activeRange.getColumn() == 1 && activeRange.getRow() != 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://public.ukcrn.org.uk/search/StudyDetail.aspx?StudyID='+e.value+'","'+e.value+'")');
    }
  }
}
video

Friday, 7 February 2014

Enhance Event Booking template further with a date and time picker

Thanks to a post made by Serge Gabet I was able to enhance further the Google Sheets booking template described in my earlier post

With the new script in place, a new menu item is added to the Sheet, allowing users to pick a date and time for the event, using a generated picker.  Apart from ease of use, this also has an important benefit, as it ensures that dates entered into the sheet are correctly formatted before the event is imported into the Google Calendar.



















My onOpen script now looks like this, with the new function "listBoxVersion" added to the menu.

function onOpen() {
  var menuEntries = [{
    name: "Import Events in Calendar",
    functionName: "importIntoCalendar"
  },null, {
    name: "Notification Setup (on form submit)",
    functionName: "notificationSetup"
  }, {
    name: "Reminder Setup (24hrs before the event)",
    functionName: "reminderSetup"
  }, {
    name: "Instructor Feedback Surveys (sent after event)",
    functionName: "InstructFeedbackSetup"
  },{
    name: "Attendee Feedback Surveys (sent after event)",
    functionName: "FeedbackSetup"
  }, {
    name: "Alert Setup (if not enough participants)",
    functionName: "alertSetup"
  },null, {
    name: "Report an issue",
    functionName: "reportIssue"
  },{
    name: "insert date/time", 
     functionName: "listBoxVersion"
    }];
  ss.addMenu("Event booking", menuEntries);
}

The remaining script was just appended to the end of the main Code.gs script

function listBoxVersion() {
  var app = UiApp.createApplication().setTitle('Time Picker');
  var main = app.createGrid(2, 4);
  var date = app.createDateBox().setName('date');
  var hour = app.createListBox().setName('hour').setWidth('100');
  var min = app.createListBox().setName('min').setWidth('100');
 
  for (h=0;h<24;++h){
  if(h<10){var hourstr='0'+h}else{var hourstr=h.toString()}
  hour.addItem(hourstr)
  }
  for (m=0;m<60;++m){
  if(m<10){var minstr='0'+m}else{var minstr=m.toString()}
  min.addItem(minstr)
  }
  var button = app.createButton('validate')
  main.setWidget(0,0,app.createLabel('Choose Date')).setWidget(0,1,app.createLabel('Choose Hours')).setWidget(0,2,app.createLabel('Choose minutes'))
  main.setWidget(1,0,date).setWidget(1,1,hour).setWidget(1,2,min)
  main.setWidget(1,3,button)
  var handler = app.createServerHandler('show').addCallbackElement(main)
  button.addClickHandler(handler)
  app.add(main)
  ss=SpreadsheetApp.getActive()
  ss.show(app)
}
 
function show(e){
  var cel1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getA1Notation();
  ss=SpreadsheetApp.getActive()
  //ss.getRange(cel1).setValue(Utilities.formatDate(e.parameter.date,'GMT+02:00','MMM-dd-yyyy')+'  @ '+e.parameter.hour+':'+e.parameter.min)
  var date = new Date(e.parameter.date);
  date.setHours(e.parameter.hour,e.parameter.min,0)
  ss.getRange(cel1).setValue(date)
  }





Wednesday, 5 February 2014

Enhancing Events Booking template in Google+

The task: To create a booking system to organise CPMS training. The system should be able to replace the existing SharePoint booking system, automatically collecting feedback from attendees and Instructors. Most of the work was already done, by downloading the excellent template described here

 

 The system is a Google Sheets solution, containing scripts which updates a Google calendar, sends emails, and collects registrations. Here are some of the tweaks made to this template made by delving into the code and reworking some elements to fit

Making this work with Outlook 

I noticed that the .ics invitation file which is sent out when attendees register did not work in Outlook, though works fine in a Google calendar. The problem appeared to be that the ics file did not specify an Organiser, so Outlook assumed that the recipient was the organiser and the event was already in the calendar. The solution to this was to add an organiser/trainer column (organiser) + email address column (orgmail) to the Google sheet, and bring this information into the .ics file

ics += "ORGANIZER;CN=" + eventRowData.organiser + ":mailto:" + eventRowData.orgmail + "\r\n";


These new columns also gave me the data required to send out feedback forms to the Trainer when the course was complete.

Getting Attendee Feedback

The template does not include feedback surveys, so some recoding was necessary to get this functionality. Only only one form is allowed to be directly linked to a Google Sheet - for this reason, once I had created the forms for attendee feedback and Instructor feedback, I had to bring the results into the template, using a very useful Google sheets formula - IMPORTRANGE

IMPORTRANGE("<spreadsheet key>","<sheet>!<range>")
e.g. 
=ImportRange("0AisVSZMkrof5dEw5M01Kb1RMb2cybjB2aGlLX00tNXc","Form Responses!d:e")

The information from the separate feedback response sheets are now in a sheet the master template, and automatically refreshed with new data as forms are submitted.

Adding new menu items to the Google Sheet

Two new scripts to enable feedback surveys to be sent out were created - via  New -> Script file from the coding screen.
AttendFeedback and InstructFeedback

A nice trick from the Template was the ability to add triggers for these to the Sheets menu, allowing you to activate or disable the functionality with only a couple of clicks.  I therefore used this trick for the new scripts.

Similar to when you add a Private Sub Workbook Open() event in excel, Google sheets allows you to use the function onOpen() which is automatically executed every time a Spreadsheet is loaded. With the code below, the new menu items are added for the toggle triggers. (NB. Code has been trimmed here to exclude the other menu items for ease of viewing, though in the spreadsheet, the other menu triggers have been kept)


function onOpen() {
  var menuEntries = [{
    name: "Instructor Feedback Surveys (sent after event)",
    functionName: "InstructFeedbackSetup"
  },{
    name: "Attendee Feedback Surveys (sent after event)",
    functionName: "FeedbackSetup"
  }];
  ss.addMenu("Event booking", menuEntries);
}

Emailing links to feedback forms, prefilling course ID and Title

Google forms allow you to prefill certain information into fields, using a query string in the URL. I wanted attendees to be able to provide feedback through a new button on the calendar event and also to get sent an email when the course finished with a link to that form - with the course ID and Title prefilled.

The button was easy, by adapting the existing script which pushes event details to the calendar,
On the templates sheet, you can enter the required HTML in a cell, and get the script to push this to the calendar.  I therefore created HTML code for the button

<div id="feedback_button" style="text-align:right; padding:5px"><a href="https://docs.google.com/*******/forms/d/14snn4VMc0rtsy6mSPsl_sGJaC3eclU7JbU2Q2VYaWOw/viewform?entry.13782825=${"Event Id"}&entry.1644682605=${"Event Title"}" style="-moz-box-shadow:inset 0px 1px 0px 0px #cae3fc;        -webkit-box-shadow:inset 0px 1px 0px 0px #cae3fc;        box-shadow:inset 0px 1px 0px 0px #cae3fc;        background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #79bbff), color-stop(1, #4197ee) );        background:-moz-linear-gradient( center top, #79bbff 5%, #4197ee 100% );        filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#79bbff', endColorstr='#4197ee');        background-color:#79bbff;        -webkit-border-top-left-radius:0px;        -moz-border-radius-topleft:0px;        border-top-left-radius:0px;        -webkit-border-top-right-radius:0px;        -moz-border-radius-topright:0px;        border-top-right-radius:0px;        -webkit-border-bottom-right-radius:0px;        -moz-border-radius-bottomright:0px;        border-bottom-right-radius:0px;        -webkit-border-bottom-left-radius:0px;        -moz-border-radius-bottomleft:0px;        border-bottom-left-radius:0px;        text-indent:0;        border:1px solid #469df5;        display:inline-block;        color:#ffffff;        font-family:Arial;        font-size:11px;        font-weight:bold;        font-style:normal;        height:28px;        line-height:30px;        width:90px;        text-decoration:none;        text-align:center;        text-shadow:1px 1px 0px #287ace" target="_blank">FEEDBACK</a></div>

Extracting the
${"Event Title"}

for example is made possible by a utility already included on the template which grabs applicable row data for an event.

NB The html renders in a published or embedded view of the calendar, so embedding this in a Google site works fine. As you can see there is now a new feedback button which passes the prefill values to the form




Google needs a plus symbol to replace spaces.... this caused a challenge when coding a link to be sent by email to the same form, as simply grabbing the value ignores any words after the first space.
The solution to this was a a combination of a cell value in the sheet translated with a regular expression
On the templates sheet, I put in a cell which grabs the appropriate event title   ${"Event Title"}

This was then referred to in the code, and any spaces converted to plus symbols

Here's the (abridged) code
// Let's grab the Event title for use later
var reminderSubjectTemplate = templateSheet.getRange("E7").getValue();

..............
    var TitletoEncode =  fillInTemplateFromObject_(FeedbackFormTitleFixed, rowData);
        // Lets replace the spaces with pluses
  var EncodeTitle = encodeURIComponent(TitletoEncode).replace(/%20/g, '+');

This can then be used in an email (sample here for the instructor feedback)

var emailText = "As an instructor, please could you provide your feedback on " + rowData.eventTitle + " by completing the survey <a href=" + feedbackURL + "?entry_355127635=" + event.getId() + "&entry_1189378848=" + EncodeTitle + ">on this link</a>";

NB. To get the IDs of the form fields (the &entry_********) you can either do a test prefill from the normal form interface and grab those from there, or use Firebug or (F12)IE Dev tools or a Chrome inspect element to expose these instead.

Scheduling emails

When a script has fired, a cell on the spreadsheet updates to confirm this.  This also prevents duplicate emails being sent out, as in the script you can just tell the script not to fire depending on the value of that cell

if(rowData.status != undefined && rowData.status != "INST_FEEDBACK_SENT"

I wanted the attendee feedback reports to be sent out, and then the instructor feedback to be sent out after a delay, to allow the sheet to update the cell values in the interim and prevent duplicate emails. The solution to this is to tell Google exactly when to send them via the script e.g.

The attendee survey emails will be triggered every hour
ScriptApp.newTrigger('sendAttFeedback').timeBased().everyHours(1).create();
but will only actually be sent if the course has finished
// check to see if event has finished
      if(rowData.status != undefined && rowData.status != "ATT_FEEDBACK_SENT" && rowData.status != "INST_FEEDBACK_SENT" && (endDate - now) < 0){


 The Instructor survey emails will be triggered in the first hour of the next day
ScriptApp.newTrigger('sendInstructFeedback').timeBased().everyDays(1).atHour(1).create();
 but will again only be sent if the course has finished using the same method
 if(rowData.status != undefined && rowData.status != "INST_FEEDBACK_SENT" && (endDate - now) < 0){


Google Sheets appear to be a really versatile and powerful tool. Hope this has been of interest! Any comments or queries welcomed!