Add columns to accounts sheet?

Google Sheets
1

I found another thread from 2019 asking about this, but it has been ‘solved’ by not adding new columns to the accounts tab.

In my case I’d like to add a closed column so I can filter out closed accounts in the Balances sheet. I’ve seen other suggestions (like set the Hide column to ‘Closed’ and reference that), but that breaks other things (like the Tiller Dashboard addon).

If I add a column to the Accounts sheet it currently breaks. I did some debugging and was able to fix the Accounts sheet itself by removing a bunch of seemingly unnecessary INDIRECT function calls.

The Unique Account Identifier column had this:

={"Unique Account Identifier";arrayformula(if(isblank(INDIRECT("G2:G")),iferror(1/0),INDIRECT("J2:J")&" - "&INDIRECT("K2:K")&" ("&upper(right(INDIRECT("G2:G"),4))&")"))}

I’m not really certain why it is using INDIRECT to directly reference a column instead of just directly referencing the columns? Either way, removing these doesn’t seem to break anything and it allows the sheet to automatically update the reference (e.g. G => H) when a new column is added (instead of continuing to reference G, when it is now H).

={"Unique Account Identifier";arrayformula(if(isblank(G2:G),iferror(1/0),J2:J&" - "&K2:K&" ("&upper(right(G2:G,4))&")"))}

I had to replace INDIRECT(“G2:G”) with G2:G in the ‘Account Id’ and ‘Last Balance’ columns as well. Not going to include the full script here because it gets a bit long.

The “Balances” and “Insights” sheets seem to work fine after this, but unfortunately the ‘Net Worth’ sheet needs more work. The ‘Sheet References’ (Net Worth - AX) doesn’t seem to pick up the change, haven’t dug into why on that yet. The ‘Sheet References’ section in the ‘Balances’ sheet works fine, so it should be easy to fix at least.

I don’t have the ‘Debt Progress’ or ‘Business Dashboard’ sheets referenced in the other post from 2019.

Can we get this working? It seems relatively straightforward so far but I’m worried that the rabbit hole is deeper than I realize and that there are more issues I am missing. Has anyone dug into this before?

Edit: I added the ‘Debt Progress’ sheet and it seems to work fine. I don’t see the ‘Business Dashboard’ sheet.

2

@royalpeasantry Hi Josh,

Have you seen the Builder’s Guide for Google Sheets?

It does address the INDIRECT usage. I don’t know if it answers your question and need. It is a good reference for Sheets developers if you haven’t seen it.

1 Like
3

Hi @royalpeasantry - I’ve added 5 columns between columns D and E without adverse consequences. The purpose for these was to affect the sort order of the accounts in response to the following post.

https://community.tillerhq.com/t/alphabetize-the-dropdown-list-of-accounts/25602

In the process I learned that there are at least two versions of the Accounts sheet. I would suspect you have the newer one where the formulas for F through Q are in row 1. The older version is what we were looking at initially in that post and has slightly different formulas starting in row 2 for ColsF-Q. In any case I think the more important thing is where to add the columns. My take on it is that it’s probably better to focus any added columns either just after D or after Q. But you may still need to make some adjustments afterwards. Hopefully that helps. I’d have to look into the Net Worth sheet if you’re saying it had some impact as I didn’t experience that.

1 Like
7

@royalpeasantry Hi Josh,

Have you seen the Builder’s Guide for Google Sheets?

It does address the INDIRECT usage. I don’t know if it answers your question and need. It is a good reference for Sheets developers if you haven’t seen it.

Thanks! That page looks like it is going to be very helpful.

It does explain why INDIRECT are used in some places (namely when looking up columns in other sheets), but in this case I was replacing INDIRECT(“G2:G”) with G2:G. I can’t see a reason that would be better? I suspect copy-pasta or some kind of reformatting leftovers shrug.

Unfortunately it also has a section that might immediately kill any simple plan to try and fix it.

Due to the duplicative use of the Account, Group and Hide header names, column lookups into the Accounts sheet are slightly different, starting in column G instead of A . Refer to Balances!X:Y for an example.

If I understand correctly that paragraph encourages builders to look for columns in the Accounts page starting at column G. So… if I add a custom column to the page and then add a new solution that is reading from the accounts page… the new sheet will be searching from G since it wasn’t there when I added the new column.

The real fix is probably to break accounts up into two sheets… something like an Accounts sheet and an Account Properties sheet.

This… poses some problems from a backwards compatibility standpoint. I think it might be resolvable by moving columns A-D to the new Account Properties sheet and either leaving A-D blank or pointing them at the new sheet.

1 Like
8

Sorry for the deleted post spam - I was failing so hard there.

1 Like
9

Using the INDIRECT("G2:G") convention would prevent G2:G from incrementing to G3:G if a row were inserted above row 2. More important for the Transactions sheet than Accounts sheet, since it’s generally understood not to insert rows on the Accounts sheet. The formulas should really probably define a LET variable to auto-find the G column and wrap INDIRECT around that so it could update with any new added columns.

I agree with @KyleT 's comment here. If you did that, I’m thinking other sheets will auto-update to the new columns.

1 Like
10

Ah - that is one I did not think of. That being said - this is in the header row, I don’t think someone adding a row above there is very likely, and if they do then G3:G seems more likely to be correct than not since they probably aren’t adding an account above the header (not on purpose at least…).

Two issues here -

  1. The accounts sheet as it is currently breaks if you add any columns before G due to the INDIRECT(“G2:G”) (and similar) references. Even after fixing those some other sheets break if you add a column before G (Net Worth at least).
  2. Adding it after G… might work, though it is going to be super confusing because the existing columns after row E (F-Q) are a different dataset than the columns before E. (F-Q is the full accounts data set, A-D is basically just user-defined overrides for specific accounts in the full account dataset). I don’t think you can add user-defined content directly to the full account dataset because the order can change (I think, not 100% certain) as data is added to the balances sheet - you need to add them to the overrides dataset and then write a query to bring them into the full account dataset. So… you’re going to have a column for the overrides dataset off in column S when the rest of the dataset is in columns A-D, and the full account dataset is going to be sitting between them. At least the new column can be in R near the rest of the full account dataset.
    • Ah… columns S/T are actually a third dataset with references to the balances page, so it gets even weirder
11

Inserted above row 2, not above the header row.

Understood, the changes you’ve made to the Accounts sheet are still needed. I was more thinking other sheets might be more likely to handle it, but no guarantees :slight_smile:

I took a look at a couple sheets and there are differences with the Accounts sheet column reference formulas.

Net Worth cell AX13 will break with Accounts columns inserted before F, because of the hard-coded code("F"), but the Accounts!$F$1:$Q$1 range would update okay:

=SUBSTITUTE(ADDRESS(1,MATCH($AW13, Accounts!$F$1:$Q$1,0)+code("F")-code("A"),4),1,"")

Balances cell Y3 will work with Accounts columns inserted before F, because the Accounts ranges would update okay:

=iferror(char(65+small(arrayformula((IF(X3=Accounts!$G$1:$1,COLUMN(Accounts!$G$1:$1)-1))),1)))

Note that char would only work up to column Z.

So, inserting columns before F, looks problematic, but perhaps not after Q :thinking:

If your Balances sheet references columns after Q, I’m thinking it’s due to inserting columns in the Accounts sheet.

Here’s the Balances sheet references without columns inserted in the Accounts sheet:

image
image235×473 12.5 KB

12

Ah - you are correct - that does change it to G3:G and that is much more likely to happen. Bleh.

Ah thanks - I hadn’t tracked that down yet. Wasn’t sure it was worth fixing it since it looked like other things might be likely to break and I wasn’t sure I wanted to actually try and modify the Accounts sheet.

Balances was working fine once the Accounts sheet was “unbroken” by removing the INDIRECT(“G2:G”) references. Though that will make it break if someone tries to add a new row 2 so I’m not certain that is a great fix :frowning_face:. I could use it myself that way, but it would have been nice if the changes could be easily pushed into the foundation so it doesn’t diverge :confused:. Not sure that is possible without breaking backwards compatibility anyway so… bah.

If I feel like spending the time I can duplicate Accounts to an Accounts2 sheet and fix the column names to be unique in Accounts2. I’ll then just set up a formula in Accounts to map the columns from Accounts2 into Accounts (rewriting header names as necessary and only cloning the columns that are expected to be in Accounts).

That will keep Accounts in the expected state and should be relatively easy to keep in sync with any tiller foundation updates.

I can then point Balances at Accounts2 by just updating the one cell in the Sheet Reference dataset and making it start at A instead of G. Add a new reference to my new Closed header and filter out stuff using it. Net worth and other sheets will keep working because they still see what they expect in Accounts.

But… thats a fair amount of work to just clean up my balances sheet a bit. Might not bother for now… but eventually it will probably bug me enough to make it worth it.

I also don’t really think this is a solution for the tiller foundation sheet… too much confusion for anyone who isn’t a power-user. Not sure there is a solution that doesn’t involve possibly breaking changes for community solutions.

1 Like