Ideas for Custom Builder Functions Embedded in the Add-ons

Google Sheets Show & Tell
1

At this week’s Builder’s Roundtable, builder @shankarimohan7 suggested creating high-reuse, custom helper functions to ease template design. It’s an interesting idea! When building a template, so many essential ingredients are complex-but-vetted building blocks laboriously borrowed from other projects. What if there were a Tiller library of these modular building blocks to ease the process?

This is indeed possible within the Sheets ecosystem. Apps Script code blocks can be built into add-ons and called from in-cell formulas. The cool part about this functionality is that they load at runtime and can be maintained and improved by the add-on publisher.

Truth is that we tried this eons ago. You can still find some obtuse references to these helper functions in this community. At the time, we found that the architecture wasn’t incredibly performant and also that the templates were often non-functional when the add-on was not present (e.g. in a shared environment).

That said, I’m curious if anyone has ideas on helper functions that would be valuable in this context. What kinds of common operations are impossibly hard in in-cell functions but doable in a scripting environment?

2

For me the ability to copy values to other area and clear values are the main two functions I use in my scripts

3

That’s an interesting one, @richl, and I agree that would be powerful— it’s the only scripts required for our Savings Budget template… but I’m not sure an in-cell formula COULD trigger a write. It’s not quite a function call, so how would it stop calling the change once it had executed?

4

You could always give us an onopen() or onedit() access. But even without that , we can still call it with a an if statement or lambda formula .