getActiveSpreadsheet().getActiveSheet().getName() not updating with new sheet name

Question

I'm trying to create a generic timesheet for my school project. I created a simple function to get the name of the active sheet.

function SetSheetName() {
 var Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  return Sheet.getName();
}

It works just fine the first time I use it. But if I change the name of the sheet and re-call the function, it stills fetch the old name of the sheet.

For example, if the name of the sheet was "Sheet1" and I run the function in a cell, the cell value will be Sheet1. But if I change the name of the same sheet and re-call the function, I will still get Sheet1 no matter what.

I guess it's the way google docs functions, but I am not aware how to fix that problem.

How are you changing the name of the sheet? I just changed the name of the sheet manually, and it seemed to work for me. — Phil Bozak, Feb 12 '13 at 15:26
Also, you can directly call `SpreadsheetApp.getActiveSheet()`. =) — Phil Bozak, Feb 12 '13 at 15:26
And how do you know it's not fetching the new name of the sheet? Are you using Logger? or Browser.msgBox? — Phil Bozak, Feb 12 '13 at 16:01
If you are running as a custom function, i.e. in the spreadsheet cell, then the only way I know to refresh the custom function is to force it to recalculate (which, apparently, is not achieved by changing the sheet name). One (ugly) way that might work for you is to force a recalculation with the function that recalculates itself every minute, i.e. GOOGLECLOCK. So you would run your function such like this: =SetSheetName(GOOGLECLOCK()) ... but remember it is not instant since the output recalcs every 1 minute only. — David Tew, Feb 13 '13 at 00:40
This issue also exists when you have multiple sheets. For example, add a second sheet (Sheet2) and the custom function (`SpreadsheetApp.getActiveSheet().getSheetName()`) on Sheet2 will still output "Sheet1". — thdoan, Nov 24 '14 at 07:30
@10basetom - That behavior was [Issue 3522](https://code.google.com/p/google-apps-script-issues/issues/detail?id=3522), and has since been fixed. — Mogsdad, Jun 12 '15 at 02:21

score 1 · Answer 1 · edited May 23 '17 at 10:30

Henrique provides a good explanation of why custom functions like this don't update in Script to summarise data not updating.

It comes down to an optimization decision in Google Sheets; a custom function will only be re-evaluated if its parameters have changed. Your function has no parameters - so it will never be re-evaluated while the spreadsheet is open.

The GOOGLECLOCK() function David Tew mentions provided a once-per-minute changing parameter that caused re-evaluation of functions, but is not available in "New Sheets". So you're sheet out of luck.

getActiveSpreadsheet().getActiveSheet().getName() not updating with new sheet name

1 Answers1