javascript - Google Sheets Changelog onEdit() 脚本不返回数据，只重复标题

Question

我对脚本非常陌生，并试图充分利用 onEdit() 函数来跟踪对协作电子表格所做的更改。

该脚本似乎运行良好，因为每次任何用户在工作表上进行更改时都会在 Changelog 工作表中添加一行。但是，它有时只注册我正在寻找的数据，并且大多数时候注册一行是标题的副本：请参阅此处

我这里的授权有什么问题吗？或者是别的什么？

非常感谢，非常感谢！

--

以下是脚本的详细信息：

    function onEdit(e) { 

    var changelogSheetName = "Changelog";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var cell = SpreadsheetApp.getActiveRange();
    var timestamp = new Date();
     var currentSheet = ss.getActiveSheet();
    var currentSheetName = currentSheet.getName();
    var previousValue = e.oldValue;
    var newValue = cell.getValue(); 
    var typeChange = "Edit";
    if (currentSheetName == changelogSheetName) return;
    var changelogSheet = ss.getSheetByName(changelogSheetName);
    if (changelogSheet == null) {changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());}
    changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');  
    changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);  
    changelogSheet.deleteColumns(8,19);
    changelogSheet.setFrozenRows(1);
    changelogSheet.setColumnWidth(1, 170);
    changelogSheet.setColumnWidth(7, 170);
    changelogSheet.protect();
    var user = Session.getEffectiveUser().getEmail();
    if (previousValue == null){typeChange = "Add";} else if (newValue == "") {typeChange = "Remove";}
    changelogSheet.appendRow([timestamp, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);

    }

标签： javascriptgoogle-sheetschangelog

Answer 1

这部分代码应该在您的语句中，因为您只希望在工作表ChangeLog不存在if (changelogSheet == null)时执行这部分。这也会导致脚本在每次发生事件时写入标头。

changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');  
changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);  
changelogSheet.deleteColumns(8,19);
changelogSheet.setFrozenRows(1);
changelogSheet.setColumnWidth(1, 170);
changelogSheet.setColumnWidth(7, 170);
changelogSheet.protect();

在这里，我编辑了您的代码，修复了一些逻辑并利用事件对象（e）：

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var editedSheet = ss.getSheetName();
  var changelogSheetName = "Changelog";
  if (editedSheet == changelogSheetName) return;

  var changelogSheet = e.source.getSheetByName(changelogSheetName);
  if (!changelogSheet) {
    changelogSheet = e.source.insertSheet(changelogSheetName, e.source.getNumSheets());
    changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);
    changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');
    changelogSheet.deleteColumns(8, 19);
    changelogSheet.setFrozenRows(1);
    changelogSheet.setColumnWidth(1, 170);
    changelogSheet.setColumnWidth(7, 170);
    changelogSheet.protect();
  }
  
  var timestamp = new Date();  
  var previousValue = e.oldValue;
  var newValue = e.value;
  var typeChange = "Edit";
  var cell = e.range.getA1Notation();

  var user = Session.getEffectiveUser().getEmail();
  if (previousValue == null) {
    typeChange = "Add";
  } else if (newValue == "") {
    typeChange = "Remove";
  }
  changelogSheet.appendRow([timestamp, editedSheet, cell, typeChange, previousValue, newValue, user]);
}

编辑表：

输出：

参考：

• 事件对象