I have a google sheet dataset with an Appsheet application for easy editing/updating the dataset. Utilizing google scripts, I was able to create a change log so I could determine how often particular information was being changed to and what it was changing from. It worked flawlessly when making changes in Google Sheets.
However, when I make changes in the AppSheet app, it doesn't track those edits. Anyone have other alternatives? as I understand onEdit doesn't work for this application.
Thanks
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";
var field = currentSheet.getRange(1, cell.getColumn()).getValue();
var id = currentSheet.getRange(cell.getRow(), 1).getValue();
var id2 = currentSheet.getRange(cell.getRow(), 2).getValue();
// if it is the changelog sheet that is being edited, do not record the change to avoid recursion
if (currentSheetName == changelogSheetName) return;
var changelogSheet = ss.getSheetByName(changelogSheetName);
if (changelogSheet == null) {
// 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(currentSheet);
changelogSheet.getRange('A1:I1').setBackground('#E0E0E0');
changelogSheet.appendRow(["Timestamp", "field", "id", "id2", "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, field, id, id2, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);
}