英文:
onEdit script depending on a cell from another tab in the same sheet
问题
我正在尝试创建一个使用"onEdit"功能的脚本,以便在特定单元格被修改时每次都会触发。
问题在于这个特定单元格不在脚本需要运行的同一标签页中,所以到目前为止我的脚本没有起作用。
以下是我确切需要的内容:
每当名为"Filter"的标签页中的单元格K4(其中包含一个复选框)被选中或取消选中时,我希望在"Overview"标签页中的K:K中更新现有的筛选器。
当手动运行时,筛选器会完美更新,但当我基于"Filter"标签页添加"onEdit"功能时,它不起作用,所以我认为问题出在那里。
我非常感谢您的帮助。
提前致谢!
英文:
I am trying to create a script using an onEdit feature so that it activates every time a specific cell is altered.
The problem is that this specific cell is not in the same tab where the script needs to run, so what I have so far is not working.
Here exactly what I need:
Every time cell K4 (which contains a checkbox) in tab named "Filter" is checked or unchecked, I want the existing filter in K:K in Overview tab to be updated.
When run manually, the filter updates perfectly, but when I add the onEdit feature based on tab "Filter", it does not work, so I assume the problem is there.
I would really appreciate your help.
Thanks in advance!
function ResetFilter() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Overview");
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['Archived', 'Cancelled', 'Excluded'])
.build();
var filter = sheet.getFilter() || sheet.getRange("K:K").createFilter();
filter.setColumnFilterCriteria(11, criteria);
}
function onEdit(e) {
var sheet = SpreadsheetApp.getActive().getSheetByName("Filter")
if(e.range.getA1Notation() == "K4"){
ResetFilter();
}
}
答案1
得分: 0
问题不在于在另一个标签页中编辑,那不会是一个问题。我的建议是将onEdit(e)的名称更改为其他名称 - 比如onEditing(e)、whenEdit(e)或任何您喜欢的名称,并设置一个可安装的触发器。有一些教程可以参考,但简单的步骤如下:
- 点击图像左侧的钟表(用黄色标出)
- 然后点击左下角的“添加触发器”
- 选择您函数的名称(位于顶部)
- 在底部更改选项为“在编辑时”
这将以进一步的权限运行脚本,可能会起作用!
PS:查看文档。
英文:
The problem is not in the edition in another tab, that wouldn't be a problem. What I suggest is you change the name of onEdit(e) to another thing - onEditing(e), whenEdit(e) or whatever you want, and set an installable trigger. There are several tutorials on that, but as little instructions:
- Click on the clock on the left of the image (highlighted in yellow)
- Then "Add Trigger" on the left bottom
- Choose the name of your function (on the top)
- Change the option at the bottom to "On edit"
This will run the script with further permissions and will probably work!
PS: see Documentation
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论