Apps Script to record copy-paste and programmatic changes through AppSheet in Google Sheets

Question

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

Answer 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.

Answer 2

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

• Apps Script Triggers.