发送Slack消息onEdit触发从谷歌电子表格，如果编辑的列值匹配特定的文本，如发送或是

Question

我有下面的应用程序脚本，我用它来发送Slack消息通过谷歌电子表格上的编辑触发。我对编码完全陌生，但有相当多的理解，所以，我研究并想出了这个代码。这个脚本正在工作，我正在接收slack消息，但故障是我无法将行值存储在数组中并将它们传递到slack有效载荷。

我的要求是，如果我编辑一个特定的列，假设我从下拉菜单中选择或键入"发送"或"是"，我想将该编辑行的全部或部分列的值传递到我的Slack消息中。

我知道在下面的代码中我缺少了一些东西，根据我的理解，我不需要i++的东西。但是，我无法修复它。此外，如果我们可以在消息发送到Slack时将编辑的单元格值重置为"No"或"Refresh"。这是我卑微的请求，如果你能帮助我与这个代码。

只是为了让自己更清楚-我想知道如何发送编辑单元格onedit的行数据到slack。即，我们如何从同一行发布特定单元格编辑的行数据。我希望这能澄清。

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

请尝试删除手动创建的触发器。

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);
}

Answer 2

尝试使用此代码，我将循环for替换为条件，并使用答案"Yes"，然后我更改range(一个二维数组)的参数range[I]，最后我添加代码以更改单元格"Yes";No"

function sendApplicationDetails() {
  // select the range from the Summary sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Database");
  var column = sheet.getActiveRange().getColumn();
  
  if (column == 59){
    var row = sheet.getActiveRange().getRow();
    var cell = sheet.getRange(row, column);
    if (cell.getValue() == "Yes"){
      var range = sheet.getRange(row, 1, row, 61);
      // post message to slack
      sendToSlack(range.getValues());
      cell.setValue("No");
    }
  }    
}
// function to send message to Slack
function sendToSlack(range) {
  var timestamp = new Date();
    
  var url = "Slack Webhook";
  
  var payload = {
    "text": "Hi " + range[0][31] +
    "n Here are the details. Let us know if you have any questions! n" +
    "n Student Name: " + range[0][15] +
    "n Nationality: " + range[0][16] +
    "n Chosen Country: " + range[0][21] +
    "n Preferred Course: " + range[0][22] +
    "n Date: " + timestamp 
  };
  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  return UrlFetchApp.fetch(url,options);
}

可以在此条件下添加&& (cell == "Choose")接受

if (cell == "Yes"){
  var range = sheet.getRange(row, 1, row, 61);
  // post message to slack
  sendToSlack(range);
  cell.setValue("No");
    
}