如何使用onEdit事件返回已编辑单元格的范围? [英] How can I return the range of an edited cell using an onEdit event?

问题描述

这似乎很容易做，但是我似乎无法使用我的任何小测试实现.最终目标是创建一个仅在特定范围内的单元格被编辑时才运行的函数.为此，我创建了一个函数，该函数可在每次编辑时运行，但首先要检查所编辑的单元格的范围是否在某个范围内，并且只有在这是正确的情况下才执行其余代码，否则，它将执行该函数.马上结束.

This seems like it should be easy as pie, but I can't seem to get any of my little test implementations to work. The ultimate goal is to create a function that runs only when cells in a certain range are edited. To accomplish this, I am creating a function that runs on every edit but starts by checking to see if the range of the edited cell is within a certain range, and only executing the rest of the code if this is true, and otherwise, it just ends immediately.

在我看来，执行此操作的第一步是找出如何返回已编辑单元格的范围.我已经尝试过各种测试.

The first step in doing this, it seems to me, is to find out how to return the range of the edited cell. I've tried various tests.

这是当前版本，我通过编辑电子表格对其进行测试:

Here is the current version, which I test by editing the spreadsheet:

function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var eRange = e.range;
var message = eRange.getA1Notation();
ss.toast(message);
}

(输出:不显示吐司面包/什么也没发生)

(output: toast does not appear/nothing happens)

我在这里发布了大约10个其他版本，但是我意识到这没有帮助.

I had about 10 other versions posted here, but I realized it was not helpful.

我已经设法在我的Toast消息中返回了[object Object]和[undefined]，所以我知道我没有在我的Toast函数中犯一些愚蠢的错误.

I've managed to return [object Object] and [undefined] in my toast message, so I know I'm not making some silly error with my toast function.

e.range不返回范围吗?那是我出问题了吗?如果它返回一个范围，我是否应该能够使用该范围内的任何方法，就像我曾经使用过类似"ss.getRange("A1").someMethod()之类的方法一样?还是有些我不理解的差异?

Doesn't e.range return a range? Is that where I go wrong? If it returns a range, shouldn't I be able to use any of the methods in range, just as if I'd used something like "ss.getRange("A1").someMethod()"? Or is there some difference that I don't understand?

好的，我知道我在这里问了几个不同的问题-我只是想对我的思维过程有一个更清晰的了解，以防它可以帮助您纠正我错了的地方.一个简单的问题是:我可以使用哪种命令或方法来返回已编辑单元格的范围?

Okay, I realize I've asked a couple of different questions here-- I'm just trying to give a clearer picture of my thought process in case it helps you to correct me where I'm wrong. The simple question is this: what sort of command or method can I use to return the range of an edited cell?

很抱歉提出这样一个基本问题.我真的很害怕有人会输入正确的搜索词组合，而答案将恰好位于结果的顶部……但是我似乎无法自己找到它，所以我就在这里.我已经尝试了StackOverflow和旧版Google网上论坛支持论坛中的许多代码段，也尝试了很多.我看到人们在这里对我的最终目标提出了很多类似的问题-仅在编辑一定范围内的单元格时才运行onEdit函数-但是答案都更加复杂并且似乎是针对每种情况而量身定制的，而它们并没有适合我的.我敢肯定，答案就在那里，但我一直在连续三个晚上进行搜索，发现有很多类似的问题要问，但似乎无法让我自己实施这些建议.感谢您对我的耐心，也非常感谢您提供的任何答案！

I'm so sorry to ask such a basic question. I'm really afraid that someone will enter the right combination of search terms and the answer will be right on the top of the results... but I just can't seem to find it on my own, so here I am. I've tried lots of snippets of code from both StackOverflow and the old Google Groups support forum, as well as lots of my own attempts. I see people have asked lots of similar questions regarding my ultimate goal here-- only running the onEdit function if cells in a certain range are edited-- but the answers are all more complex and seem tailored to each individual situation, and they don't fit mine. I'm certain the answer is out there, but I've been searching for three straight evenings now and found many similar questions asked, and just can't seem to get my own implementation of those suggestions to work. Thanks for being patient with me, and thank you so much for any answers you can offer!

推荐答案

这是所采用(并稍加修改)的代码来自文档:

here is the code taken (and slightly modified) from the doc :

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  r.setComment("Last modified: " + (new Date())+' by '+Session.getActiveUser());
  ss.toast('Last cell modified = '+r.getA1Notation()+' by '+Session.getActiveUser());
}

这篇关于如何使用onEdit事件返回已编辑单元格的范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！