Can I protect the name of a Google sheet? Or reference the sheet by position rather than name?

Question

I manage a Google spreadsheet that has multiple sheets. I use scripts to automate some processes on the first sheet. Recently, a user accidentally renamed the first sheet -so the script stopped working until I found out and corrected the name of the sheet.

My question is: how can I protect against this happening again? Is there a way to protect the sheet name from being changed by users other than myself? Or, in the script, is there a different way to reference the first sheet, regardless of its name? I.e., instead of sheet.getSheetName is there something like sheet.getFirst that returns the first sheet, regardless of its name?

Here's a sample of one script that is being affected.

function onOpen(){
  //Sheet where this script should run
  var SHEETNAME = "Scan IN Check OUT" 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  if (sheet.getSheetName() == SHEETNAME ) { //Check we are on the correct sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  //Find the last cell with data in that specific column (A in this case)
  var lastCell = sheet.getRange('A1').getNextDataCell(SpreadsheetApp.Direction.DOWN);
  //Activate the next cell
  lastCell.offset(1, 0).activate();
} 
}

Under an earlier post, I found the suggestion to use a time based trigger to rename the sheet every 5 minutes or so (Zig Mandel). I'll give that a try. — TucsonTerry
I would determine what the ID number is of the sheet tab that you want, and then use the ID number to determine which sheet tab to get. Unfortunately, there isn't a method to get a sheet tab by ID number, so you need to get all sheet tabs, loop through them and compare the ID number, then stop the loop when it finds the sheet tab with the correct ID number. And when that code runs, you could also check if the name is correct, and if not, then change it. Doing it that way would avoid the need to have the trigger run on a time interval. — Alan Wells

Cooper Cooper · Accepted Answer · 2019-12-29T20:40:12

SpreadsheetApp.getActive().getSheets()[0]; will always be the first sheet on the left which may not always be the same sheet since users can change their position. You can use the first function below to see how the indexes change as you move sheets around. WARNING: don't do this in a spreadsheet that contains important information because it first clears the sheet and then writes the name, the id's and the indexes for all sheets.
So you can also use the first function to get the id of the sheet that you want not to change.

The second function will get the sheet by id. If you will select the id of your sheet and replace the string 'My Sheet ID' with it and also put the correct name of your sheet where it says my sheet name, then when you runTwo() it will move you sheets all the way to left (ie index 1) and rename to whatever you want.

function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var sht=ss.getSheets();
  sh.clear();
  sh.appendRow(['Name','id','index']);
  sht.forEach(function(s){
    sh.appendRow([s.getName(),s.getSheetId(),s.getIndex()]);
  });
}

function getSheetById(id) {
  var id=id||'My Sheet ID';
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    if(shts[i].getSheetId()==id) {
      return shts[i];
    }
  }
}

function runTwo() {
  var ss=SpreadsheetApp.getActive();
  var sh=getSheetById().activate();
  ss.moveActiveSheet(1);
  sh.setName('My Sheet Name');
}

Of course the real problem here is that if the user can change the name of your sheet they can also go in and change your script.

Can I protect the name of a Google sheet? Or reference the sheet by position rather than name?

1 Answers