单元格值更改时运行一个函数(onEdit函数) [英] Run a function when a cell value changes (onEdit Function)

问题描述

在下表中:

https://docs.google.com/spreadsheets/d/1F22Z19gi9WRaYbsrvlDyuiJX5VXfppAlCPzB5bjdMN8/edit?usp = sharing

在脚本编辑器中，添加了以下代码:

From the script editor, I added the below code:

function onEdit(e) {
  var sheetName = 'Sheet2'
  var cellName = 'C2'

  var sortSheet = e.range.getSheet()

  if (sortSheet.getName() === sheetName) {
    if (e.range.getValue().length === cellName) {
      var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
      var e = sh.getRange(2, 3).getValues();
      var m = 'Test Message';
      var subject = 'Test Subject';
      MailApp.sendEmail(e, subject, m);
    }
  }
}

目的是当在sheet2中更改"C2"单元格值时触发MailApp功能

The purpose is to trigger the MailApp function when a "C2" cell value is changed in sheet2

我测试了相同的脚本，但是在sheet1上(我在其中添加了单元格值)测试了它，但是它起作用了，但是，当我尝试在sheet2上应用(其中值是从sheet1复制的)时，脚本不起作用.任何帮助，谢谢！

I tested the same script but on sheet1 (where I add the cell values) and it worked but, however, when I try to apply on sheet2 (where the values are copied from sheet1), the script does not work. Any help, Thanks in advance

请注意，表格是可编辑的，因此非常欢迎您编辑脚本

推荐答案

聊天之后，我们决定将邮件合并到已经可用的doPost()函数中.

After chatting, we decided to incorporate the mailing inside the already available doPost() function.

function doPost (e) {


.... 

var doc = SpreadsheetApp.getActive();
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
  return header === 'Timestamp' ? new Date() : e.parameter[header]
})

sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
var email = newRow[1];
var subject = 'XXXXXXXXX';
var body = 'XXXXXXX';
MailApp.sendEmail(email, subject, body);

...... //rest of the code

}

这篇关于单元格值更改时运行一个函数(onEdit函数)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！