// Sends an email if Autocrat fails to send email.
// Note that this script checks the last column of the last row which should contain "Document successfully merged; Emails Sent to:..." 
// If this cell is empty, it means that Autocrat failed to run and the script will send an email to the address in the variable section below.
// In order to make this work, just paste this code into the sheet's Script Editor (Tools > Script Editor). Then set up the script trigger 
// (Resources > Current Project's Triggers). I set the trigger to run the script every 6 hours.
// Finally, don't miss the **** ATTENTION ****** notes below. There are a few variables that you'll need to update for your use.

function myFunction() { 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
//   ****************************     ATTENTION: YOU NEED TO READ THIS SECTION AND CHANGE THE 3-4 VARIABLES BELOW      ***********************   //
//
//        User variable section: These variables need to be completed for each form.
//        email = email address to send alerts to
//        spreadsheetURL: Just copy and paste the URL of the spreadsheet making sure it is enclosed in 'single quotes'
//        spreadsheetDescription: A brief description to let you know which Form spreadsheet is having the issue.
//        sheetName: This is usually "Form Responses 1" but change it if your sheet's name is different -     
//                   It is the sheet name (on the tab at the bottom of the page), NOT the title at the top of the page
//       
//        Also: This script will send out an email whether Autocrat is working or not. If you only want to receive alerts only when Autocrat
//              fails to run, just delete the rows between the dotted lines below.
//  
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////  
 var email = 'pdavidson@yourdomain.edu';
 var spreadsheetURL = 'https://docs.google.com/spreadsheets/d/17Tv4VhyTRiYe-dYwlur_T9OwM4-N3iZ_wWV8RYDro4/edit#gid=1692755134';
 var spreadsheetDescription = 'HS Leave Request Form spreadsheet';
 var sheetName = 'Form Responses 1'

 ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////  
 
 var activeSheet = ss.getSheetByName(sheetName);
 var lastColumn = activeSheet.getLastColumn();
 var lastRow = activeSheet.getLastRow();
 var lastCell = sheet.getRange(lastRow, lastColumn);
 
// If the last row/last column cell is empty, Autocrat has not populated it with the "Document successfully merged" message.
// In this case, we'll send an email letting us know that something's wrong.
if (lastCell.getValues() == '') {
    var bodyHTML = '<a href="' + spreadsheetURL + '">CLICK HERE</a> to go to the ' + spreadsheetDescription + '. <br> <br>' +
                 '<b>The last column of row #' + lastRow + ' is blank, indicating that Autocrat likely failed to run. Running the script manually usually resets it.<br><br>' + 
                 'To do this, open the <a href="' + spreadsheetURL + '">sheet</a>, click on Tools > Script editor... Then click the run button (right-facing triangle).</b>'
  
  MailApp.sendEmail({
     to: email,
     subject: 'Autocrat problem in ' + spreadsheetDescription,
     htmlBody: bodyHTML
   });
  }
  
//.....................................................................................................................................................................................................
// If Autocrat is working, this section will send a message to let you know everything is OK. If you only want notifications when something is wrong, delete everything between the dotted lines.
  else {
     var bodyHTML = '<a href="' + spreadsheetURL + '">CLICK HERE</a> to go to the ' + spreadsheetDescription + '. <br> <br>' +
                 'Autocrat seems to be working fine in the ' + spreadsheetDescription + '<br> <br> lastRow:' + lastRow + '<br> <br> Cell Value:' + lastCell.getValues()
  
  MailApp.sendEmail({
     to: email,
     subject: 'NO Problems in ' + spreadsheetDescription,
     htmlBody: bodyHTML
   });
  }
//.....................................................................................................................................................................................................  
}  //   ← DO NOT DELETE THIS BRACKET