CODEWITHSUNDEEP
google-sheetsnamed-ranges
valadil
valadil

Reputation: 1703

How do I make changes to a named range in google sheets propagate immediately?

I'm using Google Sheets to calculate my goals for weight lifting. Different workouts are based on different percentages of my high score for each lift.

I had been calculating each day's plan with something like "=CEILING((0.8*A3/5), 1)*5" to represent 80% of whatever the hell was in A3, rounded to something divisible by five. This was not maintainable.

What I'm working on now is using the script editor to provide custom functions. "=FIVES('bench')" gives me my bench press progression for five reps.

That works great, except for when I change values. I put a named range on each of my max lifts so I could call getRangeByName get the value by name instead of by cell coordinates. But the functions I made don't get called again so I keep seeing stale values, even after refreshing the page.

I found another post suggesting that getRangeByName had a second arg which could be set to now() to refresh the cache now. Tried that and it looks like that was out of date. I haven't found any other info on a named range being cached.

How can I tell my script that it needs to re-run when a named range gets updated? Is there some sort of observer/listener/watcher in sheets? If not, I'm open to techniques - named ranges were just the first thing I bumped into for naming my variables.

Upvotes: 1

Views: 110

Answers (1)

valadil
valadil

Reputation: 1703

Google's script caching was never going to allow that to work without shenanigans. The expectation is that foo('bar') will be deterministic, always returning the same thing. I'd been passing the named range's name, hoping I could use getRangeByName and using the name as a label.

Instead I'm just using the range as an arg and passing a second string as a label: foo(bar, "Bar label"). It's a little more verbose, but much less so than the stuff I was trying to trick the script server with. Don't fight the framework, folks.

Upvotes: 1

Related Questions