1

I'm using a custom function to read the sheet name (shown in the tab) and then return the name to the cell in the summary page. The script works, however it doesn't update when I change the name of the tab. It updates seemingly randomly. I'd really like to know how to force it to update or have it update immediately.

function SheetName(sheetID) {
  var ASS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = getSheetById(ASS, sheetID);
  var sheetName = sheet.getName();
  return sheetName;
}

Where getSheetById is another function that finds a sheet based on its sheetID.

2

Contrary as occurs with built-in functions like =NOW() that return an new value every time that the spreadsheet is recalculated, custom functions are recalculated only when their arguments change. So if we have a sheet which ID is 503917557, then the resulting formula is

=SheetName("503917557")

if the name of the sheet with ID = 503917557 is changed, the argument of the above formula is still the same, so it will not be recalculated.

It's worthy to note that if we overwrite a cell with exactly the same formula, the formula will not be recalculated either, because the optimization algorithm of Google Sheets see this action as "nothing happened".

On previous versions of Google Sheets Google Apps Script allowed the use of functions like NOW(), TODAY() and other deterministic built-in functions as custom function arguments, but nowadays they aren't allowed.

The current workarounds are

  1. Use the web browser refresh feature as custom functions are recalculated on opening the spreadsheet

  2. Add a dummy argument, like adding a number that is incremente every time that we want the formula to be recalculated, example:

    =SheetName("503917557",1)
    
  3. Update the cell value by using a script able to be called from an installable trigger, custom menu, button, etc.


Below is my original answer.

From my answer to Custom Functions and Recalculation

The proper way to make that a custom function to recalculate is to change a parameter of it. Regarding the use of NOW() and other similar built-in functions as paremeters of custom functions, from Custom functions in Google Sheets

Custom function arguments must be deterministic. That is, built-in spreadsheet functions that return a different result each time they calculate — such as NOW() or RAND() — are not allowed as arguments to a custom function. If a custom function tries to return a value based on one of these volatile built-in function, it will display Loading... indefinitely.

From a comment by Mogsdad to this answer:

In fact, rather than "Loading...", this will display an #ERROR!, This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()

1
  • None of those workarounds are working for me, but I did find one of my own: edit the script and save it. The spreadsheet recalculates about the time you switch back to the tab. Why? Who knows! I give up at this point. This has been a rabbit hole of stupidity. From the broken typeof operator telling me that an http response is an object when actually no, it really is a string, to the cells not updating themselves to begin with, this has been dreadful. – Braden Best Jan 4 at 5:36
0

I assume you are using this as a function in a cell in the spreadsheet. Presumably, you would like it to update immediately upon changing the name of the tab. I don't know of any way to do this.

That said, if you go to File -> Spreadsheet Settings -> Calculation -> Recalculation, you can change that to On change and every minute.

This might update it within a minute of the change, although the description claims it works only with time-based functions.

1
  • Yeah I've tried this, it doesn't recalculate the cell. Thanks though! – Trevor Aug 28 '17 at 18:21
0

You create a tick box, for example at cell C1, and then set your target cell as:

=if(C1=true, SheetName("123"),)

When you untoggle and re-toggle the tick box, the function will be called again and the result updates. Still manual, but probably better than refreshing the page.

-1

The Problem:-

A custom script on a cell of a spreadsheet will only execute itself when the parameters defined on a function defined on the cell has changed or manipulated. If they remain same, the earlier result is driven from the cache.

Possible Solutions:-

A)

Obviously you may use Now() on any cell and then pass that cell to your function parameter. Make sure to change the spreadsheet settings -> calculation -> onchange every minute. (This will change the parameter of the function every minute and thus the script will be executed)

Latest updated on Spreadsheet doesn't seem to be allowing Now() or Rand() or such functions anymore to be passed indirectly/directly to the custom function.

B)

Add a 'Refresh' menu item and maybe go to your script and define a function which changes the value of a cell when you click the 'Refresh' menu. Pass the value as a parameter to your function where you are executing your script.

C)

Go to your script and make a function. Let's say updateCellValue(). Now define the same logic here which is aimed at changing the value of a cell.

Example: SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());

Now go to Edit->Current Project's trigger and create a trigger that will trigger your function(updateCellValue) after every custom-defined time limit. This will do the same thing as solution B but you won't have to click the Refresh button of menu.

Seems like a bit of hack but probably I feel that's the only way as of now to update the spreadsheet for custom-defined functions like fetching data dynamically from a server.

2
  • The solution A doesn't work since a long time ago. – Rubén Sep 9 '19 at 23:28
  • @Rubén yes you re right, but it used to work in the earlier versions of the spreadsheet. so I've just added that in case anyone wonders as to why that solution isn't working in their spreadsheet. – Parth Choksi Sep 11 '19 at 4:54
-1

I've been fighting with this all day, and I've finally come up with a workaround that in my opinion is just about as stupid as the original problem: edit and save the script where the custom function is being loaded from.

I don't know how reliable this is, but I tested it with a couple strawpolls I had in a spreadsheet, and sure enough, the vote count changed to reflect the current total after I edited and saved the script, and tabbed back to the sheet. Honestly, I'm not even sure if editing the function is entirely necessary. I just tabbed back to my spreadsheet and watched a countdown timer update itself.

Note: This is a workaround, not a solution. Sadly, I do not believe it is possible to get a spreadsheet to update cells using custom functions automatically, let alone immediately.

1
  • I do not believe the downvote is warranted. I answered the question given what is possible and what information is available. You can't fault me for being unable to solve an unsolvable problem. Sometimes, workarounds are all there are. Get used to it. – Braden Best Jan 5 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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