Hello,
I have a google scripts code for creating a changelog on my dataset. Every time I make a change in Sheets, it tracks the change. But when I use Appsheet it doesn't track changes. I believe it is something to do with the 'getActiveSpreadsheet' code. But I am unsure which code to change.
Thanks,
Tom
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]);
}
LinkedIn
Twitter
