I am currently working on a code that checks if the checkboxes for CEO_box and Acc_box are checked. If they are, an email is sent to the corresponding email address in the column, and the inform_cell is set as 'informed'. However, I keep encountering an error that seems to be related to the var courseCol = data[i][7] line, but I can't identify any issues with it.
function sendEmailver2() {
try{
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1")
var lr = sheet.getLastRow();
var data = sheet.getDataRange().getValues();
//msg body sheet
const templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("template").getRange(1,1).getValue();
for(var i=2; i <= lr; i++) {
var CEO_box = sheet.getRange(i, 25);
var Acc_box = sheet.getRange(i, 26);
var courseCol = data[i][7]
var receiverName = data[i][6]
var receiverEmail = data[i][18]
var inform_cell = data[i][27]
var totalAmount = data[i][12]
if(Acc_box.isChecked() && CEO_box.isChecked() && inform_cell.getValue() =="uninformed") {
var subject = "please check email"
var messageBody = templateText.replace("{name}",receiverName).replace("{courseName}",courseCol).replace("{amountVal}", totalAmount)
console.log("send Email to:"+ receiverEmail)
MailApp.sendEmail(receiverEmail, subject, messageBody);
inform_cell.setValue("informed");
}
}
} catch(e) {
SpreadsheetApp.getUi().alert(e)
}
}