Sheet name is not updating with the script

Question

In my google spreadsheet i created a script (below) which give me the function/formula to get the name of the sheet/tab which i am currently in.

The problem is when i change the sheet name to a new name then this cell is not updating with the new name even after several refresh.

I have set triggers to run the script as well as i have used SpreadsheetApp.flush() to forcefully applies all pending Spreadsheet changes, but still this does not work.

This is my script below:

function sheetname() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = ss.getActiveRange().getSheet();
   return s.getName();
}

And this is with the flush :

function sheetname() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveRange().getSheet();
   SpreadsheetApp.flush();
   return s.getName();
 }

Please let me know what changes i need to make so that this can update as and when the sheet name is changed.

The only way to get code to run from changing a sheet tab name would be to install a trigger that uses the "On Change" event. I think that "On Change" will detect a sheet tab being renamed, but I'm not sure. You'll need to test it yourself. — Alan Wells, Jun 16 '17 at 14:41
Thanks for your response. No, its not working with 'on change' trigger either. — viv227295, Jun 16 '17 at 15:31
Possible duplicate of [Refresh data retrieved by a custom function in Google Sheet](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) — Rubén, May 02 '19 at 05:18

Cooper · Answer 1 · 2017-06-17T17:08:37.373

This function works when connected to an onChange trigger and the Sheet Name is changed. I think you were adding it to the cell as "=sheetName()". I have to admit I don't know much about custom functions because I always do all of my calculations in arrays because it's much faster. But if you hook this up to an onChange event and set the load value of the sheet name into a cell it works.

function sheetName()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var name=sht.getName();
  sht.getRange('A5').setValue(name);
}

Sheet name is not updating with the script

1 Answers1