Google App Script unable to send notification only for specific sheet and previously empty cell-CodePudding

I'm new to Google Script and I'm trying to create this script that will send notification to the organisation's in-house messenger app when there is a new request filled into a specific tab and column.

So the tab's name is "Additional Requests" and only when a cell in column B "Request Number" is filled, it will trigger the notification. It shouldn't trigger notification when a filled cell is edited.

I have managed to make the trigger only happening to column B. However, the trigger also happens when other tabs' column B is edited even when I have already specified the tab "Additional Requests" in the script. Additionally, it also trigger notification when the filled cell in column B is edited. May I know what has gone wrong in the script? I have searched through a lot of similar questions to find answers but none worked.

Thank you in advance!

function API() {

  var cell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Additional Requests").getCurrentCell();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Additional Requests");
  var columnofCellEdited = cell.getColumn();
  if ((sheet.getSheetName() === "Additional Requests") && (columnofCellEdited === 2)) {
    if((cell.value !== null) && (cell.oldValue == null)) {
      var payload = JSON.stringify({
        "tag": "text",
        "text": {
          "content": "Hello team, there has been a request/change raised. Do help to review and action on it, thanks!",
          "mentioned_email_list": ["[email protected]"],
          "at_all": false
        }
      });
      
      
      var options = {
        'method': 'post',
        'payload': payload,
        'Content-Type': 'application/json',
      };
      
      var test = UrlFetchApp.fetch('messenger api link', options);
      console.log(test);
    }
 else {return;}   
}
}

CodePudding user response：

You need to use the event object and check the edited range:

Simple triggers and installable triggers let Apps Script run a function automatically if a certain event occurs. When a trigger fires, Apps Script passes the function an event object as an argument, typically called e. The event object contains information about the context that caused the trigger to fire.

You can then use the event object passed to the edit function to get infromation about the edited cell:

function API(e) {
  var cell = e.range
  var ss = e.source.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Additional Requests")
  var editedSheet = ss.getActiveSheet()
  var columnofCellEdited = cell.getColumn()

  if (editedSheet.getName() === "Additional Requests") {
    if (columnofCellEdited === 2) {
      if (cell.value !== null && cell.oldValue == null) {
        /*
         * Your onEdit() code goes here
         */
      }
    }
  }
}

The thing you're not doing is checking to see if the active sheet is called "Additional Requests", but if the sheet that you manually obtained is - which it always will be.

This also assumes already that your API() method runs on an installable onEdit trigger.