0

I have a Master sheet where my team and I make changes at least twice a day. The changes are recorded in the changelog sheet.

The changelog sheet currently only records onEdit events, i.e., hand edits. The problem is most of the changes are made using AppSheet and copy-pasting from many sources. Help me capture all changes made.

Here is the code I have been using.

function onEdit(e) {
  addchangelog(e);
}

function addchangelog(e) {
  // This script records changes to the spreadsheet on a "Changelog" sheet.
  // The changelog includes these columns:
  // "Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"
  // Version 1.1, written by --Hyde, 30 July 2014
  // See https://support.google.com/docs/forum/AAAABuH1jm07CaJ_nYfLnM/?hl=en&msgid=fBuBv7najJwJ&gpf=d/msg/docs/7CaJ_nYfLnM/fBuBv7najJwJ

  // 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 = ['Master', 'TODAY'];
  var changelogSheetName = "ChangeLog";
  var timestamp = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var sheetName = sheet.getName();
  if (sheetName == changelogSheetName) return;
  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) {
    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(["Row label", "Timestamp", "Sheet name", "Cell address", "Column label", "Value entered"]);
    changelogSheet.setFrozenRows(1);
  }
  changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
kiran kumar
  • 1
  • 1
  • The code you quote is from [this 2014 post](https://support.google.com/docs/forum/AAAABuH1jm07CaJ_nYfLnM/?hl=en&msgid=fBuBv7najJwJ&gpf=d/msg/docs/7CaJ_nYfLnM/fBuBv7najJwJ). Please add correct attribution: _Version 1.1, written by --Hyde, 30 July 2014_. – doubleunary Apr 23 '21 at 08:16
  • I have no idea of coding, nor am I a coder by profession, above code took me 2 days to search from this site, refer to videos from youtube and adapt for the need required for my sheet, the purpose of this changelog is to record COVID patients status of O2 and so on so that it will be helpful in determining which intervention works best and when to change intervention. – kiran kumar Apr 23 '21 at 08:24
  • Can you provide more details about what exactly is not working as expected and what is it that you want to achieve? @kirankumar – ale13 Apr 23 '21 at 09:57
  • Single-cell changes (typed and even copy-paste from different sheet after changing as suggested by doubleunary ) are being recorded in the Changelog sheet, Most of the time we change multiple rows of the same column and some times multiple adjacent columns at once too, I want all changes to be recorded, in same manner its recorded as changed one cell at a time. @ale13 – kiran kumar Apr 23 '21 at 17:16
  • And how exactly are you making these multiple-cell changes? – ale13 Apr 29 '21 at 10:07
  • will match using vlookup from different excel at additional columns, then copy-paste the same in required area @ale13 – kiran kumar May 05 '21 at 05:41

2 Answers2

1

A simple onEdit(e) trigger will only run when the spreadsheet is hand edited and not when it is changed programatically.

To catch modifications by your AppSheet application, remove the onEdit(e) function and create an installable on change trigger to run addchangelog(e).

The existing code may work as is, or it may require minor modifications. See event objects.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
0

As noted above as well, the onEdit trigger will run only when a user will make an edit to the spreadsheet. Therefore, apps and formulas are not taken into account in this situation.

The onChange won't solve this problem either unless you end up modifying the structure of the Changelog sheet.

A possible solution is to use a time-based trigger and eventually check for changes programmatically.

Reference

ale13
  • 5,679
  • 3
  • 10
  • 25