0

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());
            }
        }
    }
}
Chris Schaller
  • 13,704
  • 3
  • 43
  • 81
  • Added a tag for google-sheets, as that is what this is about, removed timestamp as a tag because it doesn't help OP solve the issue of how to reference sheets by Id. Changed the wording to frame the specific question and supporting evidence for why OP is not happy with the current solution. – Chris Schaller Nov 10 '19 at 04:54
  • Possible duplicate of [Get Google Sheet by ID?](https://stackoverflow.com/questions/26682269/get-google-sheet-by-id) – Tedinoz Nov 10 '19 at 11:03

0 Answers0