I have a script that is based on hardcoding the Name of a sheet in Google Sheets. It works but this means that the script needs to be updated if the name of the Sheet changes. Using the Id will mean that the sheet does not have to be protected, I would like the user to be able to re-name the sheet.
Can the below script be changed to use the ID of a sheet, instead of the Name?
This script assumes a gid=0 and updates an audit timestamp in the columns to the right of Column F for a row that has changed.
function onEdit(event) {
var name = event.range.getSheet().getName();
switch (name) {
case "AA":
update(event)
break;
}
}
function update(event) {
var ColF = 6; // Column Number of "F"
var changedRange = SpreadsheetApp.getActiveRange();
if (changedRange.getColumn() == ColF) {
if (changedRange.getRowIndex() >=7 ) {
// An edit has occurred in Column F
var state = changedRange.getValue();
var adjacentDate = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColF+4);
var adjacentDate2 = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColF+2);
var adjacentLDAP = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColF+3);
var adjacentLDAP2 = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColF+1);
var timestamp = new Date(); // Get the current time
adjacentDate2.setValue(timestamp);
adjacentLDAP2.setValue(Session.getEffectiveUser().getEmail());
if (adjacentDate.getValue() == "") {
// Write timestamp into adjacent cell
adjacentDate.setValue(timestamp);
adjacentLDAP.setValue(Session.getEffectiveUser().getEmail());
}
}
}
}