I am trying to create a small laptop rental system in the Google Sheets which sends email depending on checkbox which is selected (checked).

Two emails which are sent are: 1. Approved and 2. Overdue.

Problem that I have with my attempt is that multiple emails are sent even to the users which already have received the approval email when approved checkbox is selected and that no user is receiving any overdue email when overdue checkbox is selected. Also I have a Trigger set up with On edit. Its been wrecking my head for a while now, an help, pointers are greatly appreciated.

function onCheckboxEdit(e) {
var source = e.source;
var sheet = source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
console.log("column:: ", column);
    
var targetRange = sheet.getRange(row, 1, 1, 17);
var targetValues = targetRange.getValues();
console.log("targetRange:: ", targetValues);
var student = targetValues[0][2];
var recipient = targetValues[0][1];
var checkboxValue = targetValues[0][9];
var checkboxValue2 = targetValues[0][17];    
var subject = ("Laptop Loan Approved");
var body = ("Hello "  student  ", \n\nWe are happy to confirm that your laptop loan application has been approved.");
var subject2 = ("Laptop Loan");
var body2 = ("Hello "  student  ", \n\nPlease disregard last e-mail sent by us, it was mistakenly sent.");
var subject3 = ("Overdue Laptop Loan");
var body3 = ("Hello "  student  ", \n\nThe laptop you have been loaned is due to be returned.");
    
if(column = 10 && checkboxValue == true) {
 console.log("chekbox marked true")
    MailApp.sendEmail(recipient, subject, body)    
} else if (column = 10 && checkboxValue == false) {
 console.log("chekbox marked false")
    MailApp.sendEmail(recipient, subject2, body2)   
} else {
 console.log("No clue")
} 

if(column = 18 && checkboxValue2 == true) {
 console.log("chekbox marked true")
    MailApp.sendEmail(recipient, subject3, body3)
} else if (column = 18 && checkboxValue2 == false) {
 console.log("chekbox marked false")
    MailApp.sendEmail(recipient, subject3, body2)    
} else {
 console.log("No clue")
}
    }

CodePudding user response：

if(column = 10 .........)

should be ...

if(column == 10 .........)

or even ...

if(column === 10 .........)

CodePudding user response：

Thanks to JtrM we have an answer!

rrays are zero indexed. Rows and columns are not. var targetRange = sheet.getRange(row, 1, 1, 17); Change the 17 to 18 – JtrM

Also adding extra (=) eg: Before column = 10 After column == 10 etc...

 function onCheckboxEdit(e) {
    var source = e.source;
    var sheet = source.getActiveSheet();
    var range = e.range;
    var row = range.getRow();
    var column = range.getColumn();
    console.log("column:: ", column);
        
    var targetRange = sheet.getRange(row, 1, 1, 18); // <- 17 changed to 18
    var targetValues = targetRange.getValues();
    console.log("targetRange:: ", targetValues);
    var student = targetValues[0][2];
    var recipient = targetValues[0][1];
    var checkboxValue = targetValues[0][9];
    var checkboxValue2 = targetValues[0][17];    
    var subject = ("Laptop Loan Approved");
    var body = ("Hello "  student  ", \n\nWe are happy to confirm that your laptop loan application has been approved.");
    var subject2 = ("Laptop Loan");
    var body2 = ("Hello "  student  ", \n\nPlease disregard last e-mail sent by us, it was mistakenly sent.");
    var subject3 = ("Overdue Laptop Loan");
    var body3 = ("Hello "  student  ", \n\nThe laptop you have been loaned is due to be returned.");
        
    if(column == 10 && checkboxValue == true) {
     console.log("chekbox marked true")
        MailApp.sendEmail(recipient, subject, body)    
    } else if (column == 10 && checkboxValue == false) {
     console.log("chekbox marked false")
        MailApp.sendEmail(recipient, subject2, body2)   
    } else if(column == 18 && checkboxValue2 == true) {
     console.log("chekbox marked true")
        MailApp.sendEmail(recipient, subject3, body3)
    } else if (column == 18 && checkboxValue2 == false) {
     console.log("chekbox marked false")
        MailApp.sendEmail(recipient, subject3, body2)    
    } else {
     console.log("No clue")
    }
}