Issue with sending message to Slack when cell automatically update

Question

I am new to coding and app script. I am trying to send a message to Slack when the cell is updated, in this can I want the message to send to slack when the checkbox value changes.

I am not sure if I can change the var availability to something else that would show the value of the formula.

function sendSlackMessage(e) {

if(e.range.getColumn() == 2) {
  var source = e.source;
  var row = e.range.getRow();
  var column = e.range.getColumn();
  var stall = source.getActiveSheet().getRange(row, 1, 1, 1).getValue();
  var date = source.getActiveSheet().getRange(row, 3, 1, 1).getValue();
  var availability = e.value;


  var url = "";
  var payload = {
    text: "Stall #" +" "+ stall + " " +  "is" + " " +   availability
  }

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

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

}

I have tried to setValue, and getDisplayValue but those didnt work.

Answer 1

The reason your code only runs when you edit the sheet manually using the checkbox is because your script is inside a condition if (e.range.getColumn() == 2) which means it is only running if you edit on column B. So if you make edits on other columns it just returns since your else statement catches it.

}else {
return;
}

How about removing this condition and add the following instead, so if you edit other columns it will run and the availability you will get is still the value from column B whether you edit Column B, Column C, or Column D:

  if (e.range.getColumn() == 2) {
    var availability = e.value;
  } if (e.range.getColumn() == 3) {
    var availability = source.getActiveSheet().getRange(row, column - 1, 1, 1).getValue();
  } else if (e.range.getColumn() == 4) {
    var availability = source.getActiveSheet().getRange(row, column - 2, 1, 1).getValue();
  }

Full revised code:

Try:

function sendSlackMessage(e) {

  var source = e.source;
  var row = e.range.getRow();
  var column = e.range.getColumn();
  var stall = source.getActiveSheet().getRange(row, 1, 1, 1).getValue();
  var date = source.getActiveSheet().getRange(row, 3, 1, 1).getValue();

  //Get Availability
  if (e.range.getColumn() == 2) {
    var availability = e.value;
  } if (e.range.getColumn() == 3) {
    var availability = source.getActiveSheet().getRange(row, column - 1, 1, 1).getValue();
  } else if (e.range.getColumn() == 4) {
    var availability = source.getActiveSheet().getRange(row, column - 2, 1, 1).getValue();
  }

  var url = "https://hooks.slack.com";
  var payload = {
    text: "Stall #" + " " + stall + " " + "is" + " " + availability
  }

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

  UrlFetchApp.fetch(url, options);

}