My code appends a row for every edit made on a spreadsheet, including the following details:
Date, Time, Sheet Name, Cell Location, User
The original code is as follows:
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var timestamp = new Date();
var sheetName = sheet.getName();
sheet.appendRow([Utilities.formatDate(timestamp, "GMT", "dd-MMM-yyyy"),
Utilities.formatDate(timestamp, "GMT", "E"),
Utilities.formatDate(timestamp, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "HH:mm:ss"),
sheetName,
sheet.getActiveCell().getA1Notation(),
Session.getActiveUser().getEmail()]);
}
My goal is to hyperlink the 'Cell Location' part to the original location using this function:
sheet.getRange(1,1)
.setValue('=hyperlink("https://docs.google.com/spreadsheets/d/'+ss.getId()
+'/edit#gid='
+ss.getActiveSheet().getSheetId()
+'&range='
+sheet.getActiveCell().getA1Notation()+'")');
I attempted to modify the code as such:
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var timestamp = new Date();
var sheetName = sheet.getName();
sheet.appendRow([Utilities.formatDate(timestamp, "GMT", "dd-MMM-yyyy"),
Utilities.formatDate(timestamp, "GMT", "E"),
Utilities.formatDate(timestamp, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "HH:mm:ss"),
sheetName,
.setValue('=hyperlink("https://docs.google.com/spreadsheets/d/'+ss.getId()
+'/edit#gid='
+ss.getActiveSheet().getSheetId()
+'&range='
+sheet.getActiveCell().getA1Notation()+'")'),
Session.getActiveUser().getEmail()]);
}
Unfortunately, this modification did not work. Any suggestions or advice would be greatly appreciated.
I suspect the issue may be due to starting the line with .setValue(), but I'm unsure.
Thank you.