I have never written in JavaScript before, and I'm sure it shows. This will likely make some of you cringe but its the best way I know how to parse and check the spreadsheet data. I need an email alert to go out when the date of calibration is less than a specified cutoff date. I've tried a few configurations, some send emails for every item regardless of the date comparison and some send nothing such as the following script. Please help me make this work! I'm sure its mostly syntax or lack of specification for certain data structures.
'''
function checkCal(){
// Fetch the equipment name, calibration date, and today's date
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1xKzfW5vX3-eDWKuKmc3R_xin4FMFKhDHaDAd7vdoaLE/edit#gid=0');
SpreadsheetApp.setActiveSpreadsheet(ss);
var calibrationDateRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("H2:H12").getValues();
var vendList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("C2:C12").getValues();
var modelList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("D2:D12").getValues();
var cutoffDate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails").getRange("D1").getValue();
var shipped = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("I2:I12").getValues();
var notes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("J2:J12").getValues();
var sn = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("E2:E12").getValues();
var loc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Equipment").getRange("A2:A12").getValues();
// Check date
for (var i = 0; i < calibrationDateRange.length; i++){
var calDate = Date(calibrationDateRange[i].getValue());
//console.log('comparing' + calDate + ' to ' + cutoffDate);
if (calDate.getTime() <= cutoffDate.getTime() + shipped[i] == false){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails").getRange("B2");
var emailAddress = emailRange.getValue();
// Send Alert Email.
var message = 'Calibration due date is approaching for ' + vendList[i] + ' ' + modelList[i] + ' S/N: ' + sn[i] + ', located at ' + loc[i] + ', on ' + calDate[i] + '. Please reference the equipment spreadsheet to verify this date and the serial number of the referenced equipment. Note: ' + notes[i]; // body of email using associated variables
var subject = 'Equipment Calibration Alert';
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
'''
Try it this way:
function checkCal() {
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1xKzfW5vX3-eDWKuKmc3R_xin4FMFKhDHaDAd7vdoaLE/edit#gid=0');
const msh = ss.getSheetByName("Emails");
const esh = ss.getSheetByName("Equipment");
const evs = esh.getRange(2, 1, esh.getLastRow() - 1, esh.getLastColumn()).getValues();
const codt = new Date(msh.getRange("D1").getValue());
const emailAddress = msh.getRange(msh.getRange("B2").getValue()).getValue();
evs.forEach((r, i) => {
let calDate = new Date(r[7]);
if (calDate.valueOf() <= codt.valueOf() && !r[8]) {
let message = 'Calibration due date is approaching for ' + r[2] + ' ' + modelList[i] + ' S/N: ' + r[4] + ', located at ' + r[0] + ', on ' + calDate + '. Please reference the equipment spreadsheet to verify this date and the serial number of the referenced equipment. Note: ' + r[9];
var subject = 'Equipment Calibration Alert';
MailApp.sendEmail(emailAddress, subject, message);
}
});
}