GS Named Function Not Accepting Named Function Output as Input

Question

Introduction

I'm trying to create custom Google Sheets functions for tabletop rolls and rules, and I ran into what appears to be a technical limitation on part of Google Sheets new Named Functions feature, but I'm hoping to be wrong and hope the community here can recognize a mistake of mine or recommend a sensible workaround¹.

For whatever reason, it appears that Google Sheets' new Named Functions do not accept output from other named functions as input.

I have made an example sheet with a demonstration as well as a testing zone that the community is free to mess around with.

The Problem

I am working with two named functions that I have created:

FUNCTION `=ROLL_D10(count)`

count: The number of dice rolls you want to roll. Default: 1
=ARRAYFORMULA(ROUNDUP(RANDARRAY(IF(AND(ISNUMBER(count),count=INT(count),count>=1),count,1),1)*10,0))

FUNCTION `=ROLL_ADVANTAGE(rolls)`

rolls: A singular cell or array of cells consisting of numerical rolls.
=IFERROR(LARGE(rolls,1),NA())

The goal is to be able to nest the named functions like so: =ROLL_ADVANTAGE(ROLL_D10(3)) The result should be to retrieve the highest value from a 3d10 roll. To those familiar with tabletop, this would be a double advantage roll.

What appears to be happening is that once created, the nested function will not recalculate unless the cell that contains the nested formula is changed. For example, if I change =ROLL_ADVANTAGE(ROLL_D10(3)) to =ROLL_ADVANTAGE(ROLL_D10(3))&"A", it will recalculate the ROLL_D10 function. It will not recalculate if any other On Change or Every Minute/Every Hour event is triggered.

Tests Performed

I have tested the following methods and achieved the following results. Again I invite users to see the example sheet for live examples.

r = Non-named ROLL_D10 function; R = Named ROLL_D10 function.
a = Non-named ADVANTAGE function; A = Named ADVANTAGE function.
:S = Output posted to the sheet prior to being passed as input.
:ƒ = Output passed directly as formula input.
.n = Non-named function; .N = Named function.
‘⇒’ Output passed from ROLL_D10 to ADVANTAGE function.

Tested Operation	Result
`r:S` …Raw formula	Success
`R:S` …Raw formula	Success
`a:S` …Raw formula	Success
`A:S` …Raw formula	Success
`r:ƒ` …Raw formula	Success
`R:ƒ` …Raw formula	Possibly does not work with `ƒ.N ⇒ f.N`
`a:ƒ` …Raw formula	Success
`A:ƒ` …Raw formula	Success
`r:S ⇒ a`	Success
`r:S ⇒ A`	Success
`R:S ⇒ a`	Success
`R:S ⇒ A`	Success
`r:ƒ ⇒ a`	Success
`r:ƒ ⇒ A`	Success
`R:ƒ ⇒ a`	Success
`R:ƒ ⇒ A`	FAILURE

Annotations

I consider a sensible workaround to be avoiding integrating advantage/disadvantage/neutral arguments into the roll functions as this increases complexity and therefore makes it less user-friendly: I do hope to make these functions available generally. If this is the only reasonable option, that is fine, but I would prefer to explore other options first.

score 0 · Answer 1 · answered Nov 30 '22 at 09:39

In the formula =ROLL_ADVANTAGE(ROLL_D10(3)), the inner function ROLL_D10(3) has a single static parameter 3 that never changes. The function will not recalculate as long as the parameter remains the same. Since ROLL_D10(3) will always return the same result, the outer ROLL_ADVANTAGE() function will also return its cached result instead of recalculating.

This behavior is a side effect of the memoization used by Google Sheets to cache the return values of functions. Its purpose is to improve spreadsheet performance. As long as a function's parameters remain the same, it will return the cached result without recalculating it.

The standard recipe to make the function recalculate is to add a dummy parameter that does get changed. The signature becomes ROLL_D10(count, dummy). Then modify the formula to make the dummy parameter refer to a value that gets updated with new values every now and then, such as cell A1, like this:

=ROLL_ADVANTAGE(ROLL_D10(3, A1))

Your named function does not need to actually use the dummy parameter — its mere presence is enough to avoid the memoization effect. The named function will get called every time the value in cell A1 changes, which is not a perfect solution, but it is usually good enough for most needs. You can increment the value in cell A1 manually, or use =now() there to easily force recalculation from time to time.