Script to send a chat message based on an IF condition in Google Sheets-CodePudding

I'm trying to edit a script that was intended to send an email if column D is checked (true) but instead of sending an email, I now want it to send a chat. I've created a webhook for the chat group, and it is sort of working, but it's sending a message on EVERY edit made to the sheet.

I'm just a high school teacher way out of my depth here. If anyone can help me figure out how to fix the script, I'd be quite grateful.

When I associate my function with the on edit trigger it sends a chat for every action. If I use the on open trigger, it does nothing.

Screenshot of code

function myFunction() {

    var WebWhooklink = "link to my group chat goes here"
    
    var message = { text: "Student has returned"};
    var payload = JSON.stringify(message);
    var options = {
            method: 'POST',
            contentType: 'application/json',
            payload: payload
    };
  
    var response =  UrlFetchApp.fetch(WebWhooklink, options ).getContentText();
}
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Electronic Hall Pass" ) { //checks that we're on Sheet1 or not
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?
nextCell.setValue(new Date());
}
}
}
function onEdit2(e) {
        if(e.value != "TRUE" ) return;
    e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart 2).setValue(new Date());
}

CodePudding user response：

You may try something like this that checks if the edit was made in Column D (the fourth column):

function onEdit(e){
  if(e.range.getColumn()==4){
    --> Put your function here
  }
}

CodePudding user response：

Thanks so much Martin! That fixed it!

function sendMailEdit(e){
  if (e.range.columnStart != 4 || e.value != "TRUE") return;
  let sName = e.source.getActiveSheet().getRange(e.range.rowStart,1).getValue();
    var WebWhooklink = "webhook link to chat goes here"
  
    var message = { text: "Student has returned"};
    var payload = JSON.stringify(message);
    var options = {
            method: 'POST',
            contentType: 'application/json',
            payload: payload
    };
  
    var response =  UrlFetchApp.fetch(WebWhooklink, options ).getContentText();
}