问题描述
我有一个Google电子表格,我想在每次使用Zapier自动化添加新行时自动更新一个字段.
I have a Google spreadsheet where I would like to automatically update one field every time a new row is added by a Zapier automation.
每次我通过添加一行或对现有行进行编辑来手动测试脚本时,脚本都会按预期触发.但是,当Zapier向电子表格中添加新行时,绝对不会发生任何事情.
Every time I test the script manually by adding a row or making edits on existing rows, the script fires as expected. However, when Zapier adds a new row to the spreadsheet absolutely nothing happens.
有人知道为什么以及如何解决这个问题吗?
Any idea why and how I can remedy this problem?
这是我要使用的脚本:
function onEdit(e) {
var row = e.range.getRow();
SpreadsheetApp.getActiveSheet().
getRange(row, 10).setValue('My_New_Value');
}
推荐答案
我能够复制您在脚本中看到的行为.我相信,当您在活动数据范围之外添加一行时,它不会触发编辑事件.为了捕捉这些事件,您还需要捕捉香槟事件.
I was able to replicate the behavior you are seeing in your script. I believe that when you add a row outside of the active data range, it does not trigger an edit event. In order to catch those events, you will also need to catch chagne events.
https://developers.google.com /apps-script/reference/script/spreadsheet-trigger-builder#onchange
function onOpen ()
{
var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger("myFunctionx")
.forSpreadsheet(sheet)
.onChange()
.create();
}
通过查看更改事件的文档,您可以检测到已插入一行,但是未返回该行号.
Looking at the documentation for a change event, you can detect that a row was inserted, but the row number is not returned.
https://developers.google.com/apps-script /guides/triggers/events#change
function myFunctionx(e) {
Logger.log("Change3");
Logger.log(e.changeType);
}
通过查看活动范围的最后一行,您也许可以找到插入的行.
You might be able to find the row that was inserted by looking at the last row in the active range.
function myFunctionx(e) {
Logger.log("Change3");
Logger.log(e.changeType);
Logger.log(SpreadsheetApp.getActiveRange().getLastRow());
}
如果复制并粘贴到多个单元格中,则更改可能会影响多行.您应该遍历范围内的所有行.
It is possible for a change to affect more than one row if you copy and paste into multiple cells. You should loop through all of the rows in your range.
function onEdit(e) {
for(var i=e.range.getRow(); i<= e.range.getLastRow(); i++) {
SpreadsheetApp.getActiveSheet().
getRange(i, 10).setValue(e.range.getA1Notation());
}
}
这篇关于Zapier更新工作表时运行onEdit触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!