javascript - 谜语：为什么 onEdit 只触发在我显然从未定义过的范围内进行的编辑？还是我？

Question

TLDR：为什么我的示例中的最后一个“if”语句不起作用，而它之前/上方的“if”语句却起作用？

为什么只有在特定范围内进行编辑时才会触发 onEdit？它触发的范围奇怪地对应于我存储在 8 个数组中的一组值。

免责声明/道歉：全新的编码、谷歌应用脚​​本和表格

我的脚本以一堆全局变量开始，如下所示。特别注意“centre#Range”变量，它们是我稍后用于定义有用范围的数组。在我看来，正是这些变量似乎以某种方式定义了 onEdit 函数将触发的范围。其他任何地方的编辑，以及其他工作表上的编辑，似乎都不会触发 onEdit?? 如果我在明显由“centre#Range”变量定义的任何范围内进行编辑，onEdit 似乎才会触发。我在这里想念什么？

'''

var yieldManSSName = "yieldMan";
var optionsWsName = "options";
var yieldCalSSName = "yieldCal";
var resManSSName = "resMan";
var wsOptionsName = 'granary';

var optionsVillageFilterColumn = 0;
var optionsTownFilterColumn = 1;
var optionsTerrainColumn = 2;
var optionsFreshWaterColumn = 3;
var optionsVeinColumn = 4;
var optionsYielderColumn = 5;
var optionsWellColumn = 6;
var optionsWorkersColumn = 7;
var optionsDefaultYieldColumn = 8;
var optionsDefaultTypeColumn = 9;
var optionsYielderYieldColumn = 10;
var optionsYielderTypeColumn = 11;
var optionsWaterYieldColumn = 12;
var optionsWaterTypeColumn = 13;

var yieldManSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yieldManSSName);
var resManSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(resManSSName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow() - 1, 16).getValues();
var yieldCalSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yieldCalSSName);

var optionsBlankCell = wsOptions.getRange('O3');
var optionsYieldTokens = wsOptions.getRange('O4:O16');
var optionsTerrainsList = wsOptions.getRange(2, 3, wsOptions.getLastRow() - 1);

var yMCentre1Terrain1CellRow = 5;

//centre#Range = [top row, bottom row, left-most column, right-most column]; NOTE: top row = the row above the table
// these arrays seem to correspond with the ranges in which onEdit is triggering??
var centre1Range = [2, 10, 1, 9];
var centre2Range = [12, 20, 1, 9];
var centre3Range = [22, 30, 1, 9];
var centre4Range = [32, 40, 1, 9];
var centre5Range = [42, 50, 1, 9];
var centre6Range = [52, 60, 1, 9];
var centre7Range = [62, 70, 1, 9];
var centre8Range = [72, 80, 1, 9];

var centrePosition = [centre1Range, centre2Range, centre3Range, centre4Range, centre5Range, centre6Range, centre7Range, centre8Range];

var yMcentre1NameRow = 3;
var yMcentre2NameRow = 13;
var yMcentre3NameRow = 23;
var yMcentre4NameRow = 33;
var yMcentre5NameRow = 43;
var yMcentre6NameRow = 53;
var yMcentre7NameRow = 63;
var yMcentre8NameRow = 73;

function onEdit(e) {

  var editedCell = e.range;
  var editedCellVal = editedCell.getValue();
  var r = editedCell.getRow();
  var c = editedCell.getColumn();
  var editedCellWsName = editedCell.getSheet().getName();

  // this loop determines in which predefined table range (ranges defined above) an edit occurs and then delegates a corresponding reference cell for that table. "cTopRow" = centreTopRow; etc. 
  for (let i = 0; i < 8; i++) {
    if (r >= centrePosition[i][0] && r <= centrePosition[i][1] && c >= centrePosition[i][2] && c <= centrePosition[1][3]) {
      var cTopRow = centrePosition[i][0];
      var cBottomRow = centrePosition[i][1];
      var cLeftColumn = centrePosition[i][2];
      var cRightColumn = centrePosition[i][3];
    }
  }

  // "yMTokenC" stands for yieldManTokenColumn where yieldMan is the name of the sheet, short for Yield Manager; just so you know
  var yMTokenColumn = cLeftColumn;
  var yMCentreTypeCol = cLeftColumn + 1;
  var yMTerrainColumn = cLeftColumn + 1;
  var yMFreshWaterColumn = cLeftColumn + 2;
  var yMVeinColumn = cLeftColumn + 3;
  var yMYielderColumn = cLeftColumn + 4;
  var yMWellColumn = cLeftColumn + 5;
  var yMWorkersColumn = cLeftColumn + 6;
  var yMYieldColumn = cLeftColumn + 7;

  var yMCentreTypeRow = cTopRow + 1;
  var centreTypeCell = yieldManSS.getRange(yMCentreTypeRow, yMCentreTypeCol);
  var centreTypeVal = centreTypeCell.getValue();

  var line1 = cTopRow + 3;

  if (c === 9) { // notice 9 falls within the range of the arrays above
    yieldManSS.getRange('A1').setValue('This one works!');
  }
  if (c === 10) { // notice 10 falls beyond the range of the arrays above
    yieldManSS.getRange('A1').setValue('This one does not seem to even trigger onEdit?!');
}

'''

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

onEdit 触发器

每当用户在任何工作表的任何位置进行编辑时，都会触发 onEdit 触发器。如果您希望在某些范围内发生特殊情况，您可以使用条件逻辑来识别它并返回其他所有内容。如果您没有 onEdit() 函数，则触发器上不会发生任何事情。

如果您需要做一些需要权限的事情，那么您需要使用可安装的触发器。您可以通过触发器菜单或以编程方式创建它们ScriptApp.newTrigger()

onEdit 事件对象

如果您想查看更多 onEdit 事件对象。尝试这个：

function onEdit(e) {
  e.source.toast('onEdit trigger');
  Logger.log(JSON.stringify(e));
}

事件对象字符串化：

{"value":"\\","source":{},"range":{"columnEnd":9,"columnStart":9,"rowEnd":7,"rowStart":7},"authMode":"LIMITED","user":{"email":"","nickname":""}}

e.source.toast('flags');每当我调试它们时，我都会使用很多，而且我通常在它们在工作表上运行时调试它们。尽管您可以使用中间函数来调用它们，只要您提供如上所示的事件对象。