Google Sheets Changelog onEdit() script does not return data but only repeats heading

Question

I'm very new to scripts and trying to get the best out of and onEdit() function to track changes made on a collaborative spreadsheet.

The script seems to run well given that a line is added to the Changelog sheet every time a change is made by any user at all on the sheet. However, it only sometimes registers the data i'm seeking, and most of the times registers a line which is a copy of the heading: see here

Do I have something wrong with authorizations here? Or is it something else?

Thank you so, so much!

--

Here is the detail of the script:

    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]);

    }

Answer 1

This part of your code should be inside your if (changelogSheet == null) statement since you only want this part to be executed if the sheet ChangeLog does not exist. This also causes the script to write headers every time an event occurs.

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();

Here I edited your code, fixed some logic and utilize to use of event object(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]);
}

Edited Sheet:

Output:

Reference:

• Event Objects