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 .

5

I would love to see Tiller add “triggers” somehow. From what I understand, Google Sheets already has triggers for running scripts when cells are added/edited/etc, but those triggers don’t run when rows are added programmatically (via the addon), so it’s very difficult to perform an action when the addon syncs new transactions/balances or when cells are updated by AutoCat, etc.

The reason I want to have something like this is because Google Sheets is nowhere near as performant as a database for complex queries. Because of this, I really want to sync my data with a database somehow so I can more easily perform queries to build graphs and visuals of my data. This and many other powerful workflows would be possible if Tiller gave us a way to run a script of our choosing via a “trigger” when transactions or balances were added/changed by the Tiller add-on.

6

I am wondering if the onChange() trigger would do the trick for this issue @jarrettsorensen. I haven’t used it recently, but I think it will fire even if the spreadsheet is being changed by the addon.