Hi I am trying to send message to slack from google sheets using app script. I have got it to work to post a message to Slack when a change is made to the column but I want to be able to send a message to slack when the status of a cell in that colum changes from Pending to In Progress. I have tried below but this does not seem to be working

function sendSlackMessage(e) {
  if (e.range.getColumn() == 4 || e.value ){
    var source = e.source;
    var row = e.range.getRow();
    var emAddress = source.getActiveSheet().getRange(row, 1, 1, 1).getValue();
    var status = e.value;
    var url = "https://hooks.slack.com/services/orgid/channelid";
    var payload = {
      text: "Hey <!here>, we have a request from "   emAddress   ". \n<@slackid>, can you please look into this"
    }

    var headers = {
      'Content-type': 'application/jason'
    }
    var options = {
      headers: headers,
      method: "POST",
      payload: JSON.stringify(payload)
    }

    UrlFetchApp.fetch(url, options)
  } else {
    return;
  }
}

CodePudding user response：

function sendSlackMessage(e) {
  const sh = e.range.getSheet();
  if (e.range.columnStart == 4 || e.value == "In Progress") {
    var emAddress = sh.getRange(e.range.rowStart, 1).getValue();
    var url = "https://hooks.slack.com/services/orgid/channelid";
    var payload = {
      text: "Hey <!here>, we have a request from "   emAddress   ". \n<@slackid>, can you please look into this"
    }
    var headers = { 'Content-type': 'application/jason' }
    var options = { headers: headers, method: "POST", payload: JSON.stringify(payload) }
    UrlFetchApp.fetch(url, options)
  }
}

From the looks of this code I would guess that you have setup an installable onEdit trigger. However, perhaps you do not realize that onEdit trigger only respond to user edits and not changes that originate from other scripts, custom functions or cell formulas.