0

I use this script to get the current sheet name :

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

then use =sheetName() in the cell to load the current sheet name. It works but if I rename the sheet, the script won't load the new name. In the setting, I already set the calculation to 'on change'. I tried to force refreshing using Google Apps Script by setting the value of the cell to "" (blank) then re-set the cell with the same previous formula but it won't update immediately:

function refreshtest() {
  var Sheet = SpreadsheetApp.getActiveSheet()
  var quot_cell = "L11" // cell location where the formula is used to grab the sheet name
  var formula = Sheet.getRange(quot_cell).getFormula()
  Sheet.getRange(quot_cell).setValue("")
  Sheet.getRange(quot_cell).setValue(formula)

}

But it will load properly if I manually delete the formula and set it back.

0

1 Answer 1

0

That is a memoization side-effect. Use a dummy parameter as demonstrated by my SheetName() custom function. Make the dummy parameter point to some cell or range that changes often.

Not the answer you're looking for? Browse other questions tagged or ask your own question.