Google Sheets Script to refresh the script to get the name of the spreadsheet

Question

I use a script that creates a formula to get the name of the spreadsheet.

But this formula never refreshes, neither when I change the spreadsheet name nor when I reopen the spreadsheet.

I would like if it is possible that it refreshes when I change the name of the spreadsheet. If not possible, at least when I close and reopen the spreadsheet.

Here is the script:

    function SPREADSHEETNAME() {
  let ss = SpreadsheetApp.getActive();
  return ss.getName();
}

I execute this function with this formula:

=SPREADSHEETNAME()

Thanks in advance.

If it doesn't have a parameter that it looks for changes in, like a cell, then it will never change. — Cooper, Jul 28 '22 at 15:54
You mean to tell me that it's not possible or that I have to change the script completely to get the desired result? — Mozart75, Jul 28 '22 at 16:06
I thnk Mike's answer covers all of the bases. But basic it won't work automatically in all situations. — Cooper, Jul 28 '22 at 17:11

score 2 · Accepted Answer · answered Jul 28 '22 at 16:18

2 solutions

onOpen : execute the script with a slight modification, thsi script will give the address where the name will be put
add a dummy parameter as a checkbox, for instance in A1 and put =SPREADSHEETNAME(A1)

the last possibility is, when opening the spreadsheet, change the value of A1

however, if you change the name manually when the spreadsheet is open you will have to change the value of the checkbox to refresh

Google Sheets Script to refresh the script to get the name of the spreadsheet

1 Answers1