Time Off Request Form Script

The following was used to create a simple time off request system using a Google form.

A user would submit a time off request on the form, the response is recorded on a spreadsheet, the script would run and make the appropriate notifications to the brass. Brass would approve or deny as needed, and the script would send subsequent update notifications to the user.

Email Submitter

function onFormSubmit(e) {
  var values = e.namedValues;
  var htmlBody = '';
  htmlBody += '<strong>' + 'Date/Time Submitted' + ':</strong> '+ values['Timestamp'] + '<br>';
  htmlBody += '<strong>' + 'Submitty By' + ':</strong> '+ values['Email Address'] + '<br>'; 
  htmlBody += '<br>';
  htmlBody += '<strong>' + 'Your Name' + ':</strong> '+ values['Name'];  htmlBody += '<br>';
  htmlBody += '<strong>' + 'Start Date' + ':</strong> '+ values['Start Date and Time'];  htmlBody += '<br>';
  htmlBody += '<strong>' + 'End Date' + ':</strong> '+ values['End Date and Time'];  htmlBody += '<br>';
  htmlBody += '<strong>' + 'Type' + ':</strong> '+ values['Type'];  htmlBody += '<br>';
  htmlBody += '<strong>' + 'Comments' + ':</strong><br> '+ values['Comments'];  htmlBody += '<br>';
  htmlBody += '<strong>' + 'Approve or Deny the request here:' + '</strong> ' +  'https://linktothespreadsheet' + '<br>';
  GmailApp.sendEmail('[email protected]', 'Time Off Request for ' + values['Name'], '', {htmlBody:htmlBody})
}

Row Updater

var admin_email='[email protected]';
function triggerOnEdit(e)
{
  sendEmailOnApproval(e);
}
function checkStatusIsApproved(e)
{
  var range = e.range;
    if(range.getColumn() <= 11 && 
     range.getLastColumn() >=11 )
  {
    var edited_row = range.getRow();
        var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,11).getValue();
    if(status == 'Approved' || status == 'Denied')
    {
      return edited_row;
    }
  }
  return 0;
}
function sendEmailOnApproval(e)
{
  var approved_row = checkStatusIsApproved(e);
    if(approved_row <= 0)
  {
    return;
  }
    sendEmailByRow(approved_row);
}
function sendEmailByRow(row)
{
  var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,11).getValues();
  var row_values = values[0];
    var mail = composeApprovedEmail(row_values);
    MailApp.sendEmail(admin_email,mail.subject,mail.message);
    var candidate_email = composeCandidateEmail(row_values);
     MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}
function composeApprovedEmail(row_values)
{
  var timestamp = row_values[0];
  var email = row_values[1];
  var name = row_values[2];
  var start = row_values[3];
  var end = row_values[4];
  var type = row_values[5];
  var comments = row_values[6];
  var by = row_values[7];
  var date = row_values[8];
  var notes = row_values[9];
  var status = row_values[10];
  var message = "Time Off Request is "+status+" for "+name+"\n\n\n"+ 
    "Submitted: "+timestamp+"\n\n"+
    "Start Date: "+start+"\n\n"+
    "End Date: "+end+"\n\n"+
    "Type: "+type+"\n\n"+
    "Their Comments:\n "+comments+"\n\n\n"+
    "Status Updated By: "+by+"\n\n"+
    "Notes:\n "+notes+"\n\n\n"+
    "View Time Off Log here: https://changemetoo";            
  var subject = "Time Off "+status+" For: "+name+""                     
  return({message:message,subject:subject});
}
function composeCandidateEmail(row_values)
{
  var timestamp = row_values[0];
  var email = row_values[1];
  var name = row_values[2];
  var start = row_values[3];
  var end = row_values[4];
  var type = row_values[5];
  var comments = row_values[6];
  var by = row_values[7];
  var date = row_values[8];
  var notes = row_values[9];
  var status = row_values[10];
  var message = "Hello "+name+", your time Off Request is "+status+"\n\n\n"+ 
    "Submitted: "+timestamp+"\n\n"+
    "Start Date: "+start+"\n\n"+
    "End Date: "+end+"\n\n"+
    "Type: "+type+"\n\n"+
    "Your Comments:\n "+comments+"\n\n\n"+
    "Status Updated By: "+by+"\n\n"+
    "Request Notes Notes:\n "+notes+"\n\n";
  var subject = "Your Time Off Is "+status+""
  return({message:message,subject:subject, email:email });
}