Named Ranges do not update when Tiller fills in data

Spreadsheets Google Sheets
,
1

I’m experimenting with using the Google Sheets API to pull some data out of my Tiller sheet and visualize it elsewhere. (I’m deeply appreciative that this is even possible — good luck trying to do this with most of Tiller’s competitors.)

I’m using Google Sheets Named Ranges to make it possible to reference certain important cells using meaningful names. For example, I might apply the name Spending to 'Monthly Budget'!G24. Then, when making my API call, instead of asking specifically for G24, I can ask for Spending, and get the same data.

One advantage to Named Ranges it that they are supposed to maintain their reference even if a cell moves. For example, imagine I change my categories and what was previously cell G24 is now cell G27. The Named Range Spending is supposed to be updated to reference cell G27.

You can see this if you create a new, blank Google Sheet, fill something into cell A1, name it (Data > Named ranges), and then insert a new row above. The name will now reference A2.

However, I’ve noticed that when Tiller inserts or deletes rows, the Named Ranges do not update. Using my prior example, even if the contents of G24 have moved to G27, the name Spending will still refer to G24, which might be blank or have some other number in it.

Is this something Tiller can address? I realize this might be something on Google’s end that cannot / will not be fixed, but I still wanted to ask about it.

2

Hi @petero can you share a screenshot of the definition of your named range, the range in its original place and after movement and what it shows in the named range afterwards? You can share here if you are able to mask your information otherwise please share with me via a message and I’ll see how I can help!

3

Hey, absolutely.

This is a somewhat-contrived example meant to protect my privacy. I’m demonstrating using the row headers. In my real use case, I’m using this technique against amounts rather than headers.

This shows an example named range:

2025-06-17 Before
2025-06-17 Before986×768 57.2 KB

You can see I have tagged cell B39, which contains the word “Living”, with the literal name TheWordLiving.

Then, let’s pretend I add another category to my sheet. In this case, I’ve called it “Another category.”

2025-06-17 After
2025-06-17 After1002×790 58.7 KB

You can see the new category in cell B38. It has bumped the word Living down to B40. However, you can see the named range still references B39, when I would expect it to reference B40.

Does that make sense?

4

It’s because the cell isn’t moving, only the value of the cell is changing, due to the ARRAYFORMULA.
A Named Range might not make sense in this case.

2 Likes
5

Ah, that makes sense.

I can work around it with a bit more API work.

Thanks.