当公式更改单元格值时触发脚本 [英] Trigger a script when a formula changes a cell value

问题描述

我正在使用 Google 脚本，然后在 Google 工作表中的某个列发生更改时发送电子邮件.单元格中的信息要么手动输入，要么根据其他单元格中的信息使用公式完成.

I'm using a Google script then sends out an email when a certain column in a Google sheet is changed. The information in the cell is either inputted manually, or completes using a formula based on information in other cells.

脚本在手动输入信息时工作正常，但在公式运行时则不然.我已经阅读了它并意识到公式计算不算作编辑，那么如何让脚本运行?

The script works fine when information is manually entered, but not when the formula runs. I've read up on it and realise that a formula calculation doesn't count as an edit, so how do I get the script to run?

当前设置为在进行编辑时从电子表格触发.

It's currently set up to trigger from the spreadsheet when there's an edit.

以下是我的脚本中涵盖相关列/单元格的部分.

Below is the part of my script that covers the column/cell in question.

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();

   if(sheet.getActiveRange().getColumn() == 12)

脚本中包含更多内容，因此我没有将所有内容复制到此处.非常感谢.

There's a lot more included in the script so I haven't copied everything onto here. Many thanks in advance.

推荐答案

没有可以在公式更改时运行的触发器.

There is no trigger that can run when a formula changes.

试试看，你的公式取决于什么条件:

Try figure out, what conditions is your formula depends on:

1. 如果是另一个单元格，手动输入，然后使用这些单元格通过 onEdit
触发它的更改
2. 如果公式从外部导入数据，使用随机或时间函数，最好使用onTime 触发器.
3. 如果公式使用了importrange，则转到您导入的范围并查看原始范围，返回步骤 1 → 2...

1. if it is another cell, entered manually, then use those cell to trigger it's changes with onEdit
2. if the formula imports data from external source, use random or time functions, you'd better use onTime trigger.
3. if the formula uses importrange then go to the range you import and see the original range, return to step 1 → 2...

这篇关于当公式更改单元格值时触发脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！