“onEdit”脚本依赖于同一工作表中另一个标签页中的单元格。

huangapple go评论57阅读模式
英文:

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"

“onEdit”脚本依赖于同一工作表中另一个标签页中的单元格。

This will run the script with further permissions and will probably work!

PS: see Documentation

huangapple
  • 本文由 发表于 2023年7月6日 20:12:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76628702.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定