google-apps-script - 在 Google Script onedit(e) 中需要帮助来监控受限行/列或范围并在记录器 xls 中更新

Question

下面的代码正在工作并记录所有更改（来自整个工作表单元格值）并在记录器 xls 中更新。如何限制仅监视特定行/列或范围的更改

代码 ：

function onEdit() {
  // This script records changes to the spreadsheet on a "Changelog" sheet.
  // The changelog includes these columns:  
  // edit the following lines to suit your needs
  // changes are only recorded from sheets listed below
  // escape regular expression metacharacters as in \. \$ \+ \* \? \( \) \[ \]
  // see http://en.wikipedia.org/wiki/Regular_expression
  // use '.+' to include all sheets
  var sheetsToWatch = ['outcome overview', 'Sheet1', 'Another sheet'];
  // name of the sheet where the changelog is stored
  var changelogSheetName = "Changelog";

  var timestamp = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var sheetName = sheet.getName();

  // if it is the changelog sheet that is being edited, do not record the change
  if (sheetName == changelogSheetName) return;

  // if the sheet name does not appear in sheetsToWatch, do not record the change
  var matchFound = false;
  for (var i = 0; i < sheetsToWatch.length; i++) {
    if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
  if (!matchFound) return;

  var columnLabel = sheet.getRange(/* row 1 */ 1, cell.getColumn()).getValue();
  var rowLabel = sheet.getRange(cell.getRow(), /* column A */ 1).getValue();

  var changelogSheet = ss.getSheetByName(changelogSheetName);
  if (!changelogSheet) {
    // no changelog sheet found, create it as the last sheet in the spreadsheet
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
    // Utilities.sleep(2000); // give time for the new sheet to render before going back
    // ss.setActiveSheet(sheet);
    changelogSheet.appendRow(["Timestamp", "Sheet name", "Cell address", "Column label", "Rowlabel", "Value entered"]);
    changelogSheet.setFrozenRows(1);
  }
  changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}

标签： google-apps-scriptgoogle-sheets

Answer 1

有什么理由不使用事件对象吗？我认为这可以使一些东西更清洁。

针对您的具体问题，没有内置方法可以实际过滤onEdit(). if但是，当范围不在您的列表中时，您可以只使用一条语句来避免执行更改。

所以我将您的代码更改为：

function onEdit() {
  // This script records changes to the spreadsheet on a "Changelog" sheet.
  // The changelog includes these columns:  
  // edit the following lines to suit your needs
  // changes are only recorded from sheets listed below
  // escape regular expression metacharacters as in \. \$ \+ \* \? \( \) \[ \]
  // see http://en.wikipedia.org/wiki/Regular_expression
  // use '.+' to include all sheets
  var sheetsToWatch = ['outcome overview', 'Sheet1', 'Another sheet'];

  // Range to execute the changeLog
  var minRow = 1;
  var maxRow = 3;

  var minColumn = 1;
  var maxColumn = 3;

  // name of the sheet where the changelog is stored
  var changelogSheetName = "Changelog";

  var timestamp = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var sheetName = sheet.getName();

  // if it is the changelog sheet that is being edited, do not record the change
  if (sheetName == changelogSheetName) return;

  // if the sheet name does not appear in sheetsToWatch, do not record the change
  var matchFound = false;
  for (var i = 0; i < sheetsToWatch.length; i++) {
    if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
  if (!matchFound) return;

  if(minColumn > cell.getColumn() || cell.getColumn() > maxColumn 
     || minRow > cell.getRow() || cell.getRow() > maxRow){
    Logger.log("Change not done in the expected range, halting");
    return;
  }

  var columnLabel = sheet.getRange(/* row 1 */ 1, cell.getColumn()).getValue();
  var rowLabel = sheet.getRange(cell.getRow(), /* column A */ 1).getValue();

  var changelogSheet = ss.getSheetByName(changelogSheetName);
  if (!changelogSheet) {
    // no changelog sheet found, create it as the last sheet in the spreadsheet
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
    // Utilities.sleep(2000); // give time for the new sheet to render before going back
    // ss.setActiveSheet(sheet);
    changelogSheet.appendRow(["Timestamp", "Sheet name", "Cell address", "Column label", "Rowlabel", "Value entered"]);
    changelogSheet.setFrozenRows(1);
  }
  changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}

---

所以我刚刚修改了你的脚本，添加了变量来定义你想要插入的范围ChangeLog：

  // Range to execute the changeLog
  var minRow = 1;
  var maxRow = 3;

  var minColumn = 1;
  var maxColumn = 3;

之后，您只需与修改后的单元格进行比较，如果不在范围内，则停止执行：

  if(minColumn > cell.getColumn() || cell.getColumn() > maxColumn 
     || minRow > cell.getRow() || cell.getRow() > maxRow){
    Logger.log("Change not done in the expected range, halting");
    return;
  }