使用onChange检测音符变化 [英] Detect Note changes with onChange

问题描述

Google表格是否可以检测您何时使用onChange编辑便笺或插入新便笺?

Is it possible for Google Sheets to detect when you've edited your notes or inserted a new one using onChange?

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  var r = ss.getActiveCell();

  if(e.changeType == 'OTHER') {
    if ((r.getColumn() < 7 || r.getColumn() > 7) && (ss.getName()=="Completed") && (r.getRow() > 1)) { // 2. If Edit is done in any column before or after 6th Column (F) And sheet name is Sheet1 then:
      var celladdress ='G'+ r.getRowIndex() // Add time stamp to cell in Column F
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
      ss.getRange(r.getRow(), 17).setValue(s.getRange(row, 2).getNote());
    }
  }    

我尝试了changeType FORMAT，EDIT和OTHER，但它们均无效.如果无法通过onChange检测到注释编辑，是否有可以执行的功能?

I tried changeType FORMAT, EDIT, and OTHER and none of them works. If onChange is not possible to detect note editing, is there a function that does?

推荐答案

说明/问题:

我花了一个小时才弄清楚这一点，但实际上您的逻辑是正确的.

Explanation / Issue:

It took me an hour to figure this out, but actually your logic is correct.

• 编辑/插入便笺时， e.changeType 返回 OTHER ，因此这部分代码是正确的.

• When editing/inserting a note e.changeType returns OTHER, therefore this part of the code is correct.

我测试了您的代码，并尝试通过多种方法获取活动单元: getActiveCell()， getCurrentCell()和 getActiveRange()，因为 onChange 触发器的事件对象不支持 e.range .

I tested your code and I also tried to get the active cell with many different ways: getActiveCell(), getCurrentCell() and getActiveRange() since the event object of an onChange trigger does not support e.range.

• 但是每次 r.getColumn()和 r.getRow()返回 1 和 1 ，当我手动运行完全相同的代码时，我得到了正确的单元格.

• But every single time r.getColumn() and r.getRow() returned 1 and 1 and when I ran the exact same code manually, I was getting the correct cell.

我在线搜索，发现这种行为不仅被报告 ，而且被Google 接受作为错误，并且它与 onChange 触发器有关.您可以找到链接此处，并确保您在问题(左上角)中加注星标使其更受欢迎.

I searched online and I found that this behaviour has not only been reported but also accepted by google as a bug and it concerns the onChange trigger. You can find the link HERE and make sure you star (top-left corner) the issue to make it more popular.

由于 r.getRow()为 1 ，因此您的代码不执行任何操作，因为您具有 r.getRow()>1 作为条件，由于此错误，它总是评估为 false .

Since r.getRow() is 1 your code does not do anything because you have r.getRow() > 1 as a condition which always evaluates to false because of this bug.

作为此错误的证明，您可以改用此代码，您会看到，当您在工作表 Completed 中插入或修改注释时，时间戳将粘贴在单元格中G1 在同一张纸上.

As a proof of this bug, you can use this code instead and you will see that when you insert or modify a note in the sheet Completed, the timestamp will be pasted in cell G1 of the same sheet.

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");
  if(e.changeType == 'OTHER') {
    if (ss.getName()=="Completed") {
      var celladdress ='G1';
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
    }
  }
} 

尽管此答案不能回答您的问题，但可以证明您的逻辑是正确的，但是由于存在错误，您无法获得预期的结果.因此，我认为这对于您和其他将来的读者来说很有用.不幸的是，我没有提出解决方案，但我希望其他读者也可以.

While this answer does not answer your question, it proves that your logic is correct but due to a bug you are not getting the expected results. Therefore, I thought it would be useful for you and other future readers to be aware of the issue. Unfortunately, I didn't come up with a solution but I hope another reader would do.

对于上述所有情况，我假设您已经安装了 onChange 触发器因为它是可安装的触发器.

For all the above, I assume you have installed the onChange trigger since it is an installable trigger.

这篇关于使用onChange检测音符变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！