Sending Slack message onEdit trigger from Google Spreadsheet, if the edited columns value matches specific text like Send or Yes

Question

I have the below App-script which I am using to send Slack messages via Google Spreadsheet on Edit Trigger. I am totally new to coding, but have a fair bit of understanding, so, I researched and came up with this code. This script is working and I am receiving the slack messages but the glitch is that I am not able to store row values in an array and pass them into Slack payload.

My requirement is if I edit a specific column let's say I choose or type 'Send' or 'Yes' from the drop-down, I want to pass on all or some of the column's value of that edited row into my Slack message.

I know there is something I am missing in the below code and as per my understanding, I don't need the i++ thing. But, I am not able to fix it. Also, if we can put in something that resets the edited cell value to 'No' or 'Refresh' once the message is sent to Slack. It's my humble request to you guys if you can help me with this code.

Just to make myself more clear - I want to know how to send row data of edited cell onedit to slack. i.e. how we can post row data on particular cell edit from the same row. I hope this clarifies.

function sendApplicationDetails() {
  // select the range from the Summary sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Database");
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2,1,lastRow-1,61).getValues();

  
  // loop over range and send communication if "Yes" option chosen
  for (var i = 0; i < range.length; i++) {
    if (range[i][59] == "Yes") {
      
          // post message to slack
          sendToSlack(range[i]);
          break;

      }

    };
}
  

// function to send message to Slack
function sendToSlack(range) {

  var timestamp = new Date();

  var url = "Slack Webhook";
  
  var payload = {

    "text": "Hi " + range[31] +
      "\n Here are the details. Let us know if you have any questions! \n" +
      "\n Student Name: " + range[15] +
      "\n Nationality: " + range[16] +
      "\n Chosen Country: " + range[21] +
      "\n Preferred Course: " + range[22] +
      "\n Date: " + timestamp 
    };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  return UrlFetchApp.fetch(url,options);
}

Answer 1

Try this and delete the trigger you created manually please.

function onEdit (e) {
  var sheetToWatch = 'Database';
  var colToWatch = 60;

  if (e.range.getSheet().getName() == sheetToWatch 
      && e.range.getColumn() == colToWatch) {
    sendApplicationDetails()
  }


}
function sendApplicationDetails() {
  // select the range from the Summary sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Database");
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2,1,lastRow-1,61);
  var values = range.getValues();

  
  // loop over range and send communication if "Yes" option chosen
  for (var i = 0; i < values.length; i++) {
    if (values[i][59] == "Yes") {
      
      // post message to slack
      sendToSlack(values[i]);
      sheet.getRange(i+2,60).setValue('No')
    }
  }
}
  

// function to send message to Slack
function sendToSlack(range) {

  var timestamp = new Date();

  var url = "Slack Webhook";
  
  var payload = {

    "text": "Hi " + range[31] +
      "\n Here are the details. Let us know if you have any questions! \n" +
      "\n Student Name: " + range[15] +
      "\n Nationality: " + range[16] +
      "\n Chosen Country: " + range[21] +
      "\n Preferred Course: " + range[22] +
      "\n Date: " + timestamp 
    };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  return UrlFetchApp.fetch(url,options);
}