C4L16 Google Form URL/Email generation script

urlemailgeneration.js

// This is function taken from Luke's google doc, except a trigger for email form is added to end, to call confirmationEmail function
function genEditUrls() {
  // Use data collected from dialog to manipulate the spreadsheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getActiveSheet();
  var formUrl = ss.getFormUrl();
  var form = FormApp.openByUrl(formUrl);

//Change the sheet name as appropriate
  var data = currentSheet.getDataRange().getValues();
  var urlCol = currentSheet.getLastColumn();  
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];
  
  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {

    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  currentSheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
  
  confirmationEmail();
}
// Need to have column in sheet for storing this variable. 
var EMAIL_SENT = "EMAIL_SENT";

// Function to send a confirmation email. No trigger needed, as it is called upon end of the genEditUrls() function above.
function confirmationEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getActiveSheet();
  var startRow = 2;
  var numRows = currentSheet.getLastRow()-startRow+1;
  var dataRange = currentSheet.getRange(startRow,1,numRows,75);
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailSent = row[21];
    if (emailSent != EMAIL_SENT) {  // Prevents sending same email twice, column is second to last in responses sheet
      var emailAddress = row[5]; 
      Logger.log(emailAddress);
      var message = "Thank you " + row[3] + " for your Code4Lib 2016 pre-conference proposal: " + row [1] + ". \n\n Until the Code4Lib 2016 Preconference proposal closing date of November 16th, 2015 (midnight PST), you are able to go back and edit that proposal here: " + row[22] + ". \n\n We will email " + emailAddress + " the first week of December to alert you to the decisions. If you have any issues, you can respond to this email (to Christina at [email protected]). \n\n For more information about Code4Lib 2016, check out the conference website: http://2016.code4lib.org/ \n\n Thank you again for your submission; we are looking forward to seeing you in Philly!";
      var subject = "Code4Lib 2016 Preconference Proposal";
      MailApp.sendEmail(emailAddress, subject, message); // Sends from Christina's email address currently; can switch form ownership.
      currentSheet.getRange(+startRow + +i, 22).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}