I am still working with the many wys on how Google sheets triggers a slack notification.
I have managed to make a script trigger a notification to a webhook.

I could not seem to convert my script to send dm’s based off of a list with an onEdit trigger.

Sorry for my very limited knowledge of how this works. I was hoping for help and have a tweak on my script as below.

Here is my sample sheet: https://docs.google.com/spreadsheets/d/1TxrhVeQeN5n1ezz4stQxT206cGDWljHdx_-KWIwRcbc/edit#gid=1896280612

and the script I have tried so hard to tweak. But it just would not work. 🙁

var POST_MESSAGE_ENDPOINT = "https://slack.com/api/chat.postMessage";
var TOKEN = "my_token_";
var message = ":heavy_dollar_sign: *Approved* a *refund* request" + "nEmail:" + "ncustmer:"

var sh = e.source.getActiveSheet();
var rng = e.source.getActiveRange();
var row = rng.getRow();
var col = 5
var custmer = e.source.getActiveSheet().getRange(row, 4, 1, 1).getValue(); 
var user = e.source.getActiveSheet().getRange(row, 9, 1, 1).getValue().split('.')[0]; 
  
function onSpeEdit(e) {  // refund approval notif - 

if (rng.getColumn() == col && sh.getName() === "Customer_Refund_Requests" 
 //&& e.user != "[email protected]"
 && e.user != "[email protected]" )

 postToSlack(message);
}

function postLoop () {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input")
 var rangeValues = sheet.getDataRange().getValues();

  for (i=1;i<rangeValues.length;i++){
    var channel = ''+rangeValues[i];
    postToSlack(channel)
  }
}

function postToSlack(channel) {

  var payload = {
    "text": message,
    "channel": channel
  };
  var params = {
             "method": "post",
             "payload": payload, 
             "headers": {
                        "Authorization": "Bearer " + TOKEN
                        }
           };
  var response = UrlFetchApp.fetch(POST_MESSAGE_ENDPOINT, params);
}; 

Thank you in advanced!

Jennifer

I have preferred that my Slack bot work without the other users seeing the notification and also not having “myself” on every conversation.

I do have a working script but runs and triggers on a webhook. This is my attempt to make my Slack Bot – send direct messages to users from a list triggered by OnEdit on Google sheets. I tried to combine 2 of my previously used scripts.

features I need it to do are:

1. Get the users list based on the Users column (Column I) – which is the email address
2. Match the Column I email address to the users List (Input Tab)
3. Send a slack DM to that user when Column 5 (Column E) is ticked with an onEdit trigger.