限制onEdit触发器以对特定工作表中的特定单元格进行的更改 [英] Restricting onEdit Trigger to Changes Made to a Specific Cell in a Specific Sheet

问题描述

我需要对特定工作表(选项卡)中的特定单元格进行任何编辑/更改，以生成onEdit触发器.

I need any edits/changes to a specific cell, in a specific sheet (tab) to generate an onEdit trigger.

我已经搜索并尝试了许多潜在的解决方案，但似乎无法使它们中的任何一个起作用.

I have searched, and tried, dozens of potential solutions, but can’t seem to make any of them work.

我特定的编辑"单元格是"H8"，它位于名为土地"的工作表(标签)上

My specific 'edit' cell is "H8" and it is located on a sheet (tab) called: "Land"

仅在此示例中，我希望触发器删除工作表"test"上单元格"A1"的内容

And just for this example, I want the trigger to delete the contents of Cell "A1" on sheet: "test"

尝试#1:基于此链接: https://stackoverflow.com/a/48964929/11317736

function onEdit(e) { 
  if (e.range.getA1Notation() === 'Land!H8') {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange('A1').clear();
  }
}

错误代码: TypeError:无法从未定义中读取属性"source".(第2行，文件代码")关闭

ERROR CODE: TypeError: Cannot read property "source" from undefined. (line 2, file "Code")Dismiss

尝试#2:基于许多不同的帖子

function onEdit(e) {
      var range = e.range;
      var rangeEdit = e.range.getA1Notation();
      if(rangeEdit == "Land!H8"){
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange('A1').clear();
          }
    }

错误代码: TypeError:无法从未定义中读取属性"range".

ERROR CODE: TypeError: Cannot read property "range" from undefined.

任何帮助将不胜感激

推荐答案

尝试一下:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(e.range.columnStart==8 && e.range.rowStart==8 && sh.getName()=="Land") {
    //put your code here
    e.source.toast('You enter a new value of ' + e.value);//remove this line
  }else{
    return;
  }
}

请注意，如果没有创建自己的事件对象，则无法从脚本编辑器运行此功能.另一种方法是将其复制到脚本编辑器中，然后编辑名为"Land"的工作表的单元格H8，以查看它是否对您有用.注意:进行修改需要您实际更改H8的值.

Please note this function cannot be run from your script editor without you creating you own event object. The alternative is to copy it into your script editor and edit cell H8 of a sheet named "Land" to see if it's working for you. Note: an edit requires that you actually change the value of H8.

我已经对其进行了测试，并且可以正常工作.我想我应该提到，仅将e放入onEdit(e)并不会创建事件对象.

I already tested it and it's working. I guess that I should mention that just putting an e in the onEdit(e) does not create an event object.

直接从范围文档页面:

• clear()清除内容，格式和数据验证规则的范围.
• clear(options)清除由给定高级选项指定的内容，格式，数据验证规则和/或注释的范围.
• clearContent()清除范围的内容，保留格式保持不变.
• clearDataValidations()清除以下项的数据验证规则:范围.
• clearFormat()清除此范围的格式.
• clearNote()清除给定单元格中的注释.

这篇关于限制onEdit触发器以对特定工作表中的特定单元格进行的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！