公式更改单元格值时触发脚本 [英] 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?
当前已设置
下面是我的脚本部分,其中涉及有问题的列/单元格。
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:
- 如果它是另一个手动输入的单元格,然后使用该单元格通过
onEdit
- 触发更改,如果该公式从外部源导入数据,请使用随机或时间函数,最好使用 onTime触发器
- 如果公式使用
importrange
,然后转到您要导入的范围并查看原始范围,请返回步骤1→2。 ..
- if it is another cell, entered manually, then use those cell to trigger it's changes with
onEdit
- if the formula imports data from external source, use random or time functions, you'd better use onTime trigger.
- if the formula uses
importrange
then go to the range you import and see the original range, return to step 1 → 2...
这篇关于公式更改单元格值时触发脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!