Accounts sheet update killed all my account links, i think

The Lab showed that the accounts sheet had an up date so I updated. And that netted me an accounts tab that was fairly blank.

I clicked on the triangles, one by one and built my accounts back up. I tried copying from the archived sheet made in the update. No joy. I reupdated, same thing.

According to the Insights tab, I have no ‘linked’ accounts. All the other sheets seem to be working fine. The Balances sheet, of course, no longer has groupings (because I have not restored them) so it’s clearly getting the info from the blank accounts sheet.

I’ve tried turning the accounts on and off again at the console and with the Feed add-on. Neither effort made a difference.

Still Insight says " Tiller has added 380 transactions for 0 linked accounts since Jan 1, 2020. " The accounts are all there under the triangles but how do I get them linked back up again?

And, also… I decided to try and restore the last working version (3 versions ago), that netted me a ‘could not restore’ error which now makes me really twitchy.

Help? Thanks

The Tiller Labs add-on restore & upgrade workflows work pretty well when pulling data from upstream sheets (e.g. updating the Monthly Budget which pulls from Transactions), but we have noticed they sometimes don’t update reliably when pushing data to downstream sheets (e.g. updating Accounts which pushes to Balances). The issue is just that the inter-sheet links can be brittle when performing a restore on an upstream sheet.

(Last week, I found a bug in the Accounts sheet that caused the last-balance query to fail when the Balance History sheet was not sorted properly. This is what the new version of the Accounts sheet addresses.)

A few shortcuts & notes for next time:

  1. There should be an option to archive the existing version of the sheet during an upgrade. Next time, if you do this, you can just copy the old Accounts overrides into the new sheet, then delete the archived sheet. (Sounds like you tried this.)
  2. The Accounts sheet only requires entries when you wish to apply an override— i.e. Class Override, Group, or Hide. If you are satisfied with the defaults for an account, a row in the Accounts sheet is not required; the account will be included in other sheets (like Balances).
  3. If you wish to populate column A with a list of all accounts, one shortcut I use is to copy the values in column F (hidden) then “Paste special / paste value only” into column A.

I’m not sure I understand why the copy from your archived sheet didn’t work. Did you paste as values? Were the new values you pasted rejected somehow? The change shouldn’t affect the way the account names are validated in the new vs. old sheet. Could you try unhiding the right hand side of the Accounts sheet and see if an accurate list of accounts appears in column F?

The update definitely should not have broken anything about your linked accounts. Essentially, the Accounts sheet consolidates information about your accounts and is referenced by other “downstream” sheets. If the data isn’t properly consolidated in the Accounts sheet, then other sheets may (temporarily) show missing account data.

Once we fix your Accounts sheet, I believe the problem in your Insights sheet will resolve… though you may need to restore that one too.

I’m sorry for all the hassle, @susandennis.
I hope this helps. Let me know if more help is needed.

Randy

P.S. As for the “could not restore” error, I have seen that too and it makes me twitchy :grimacing: as well. I believe it is a Google issue and I’m hoping they resolve it soon.

Oh Randy, Gold Stars for you!!

I did, indeed, find the hidden stuff and will admit to NOT using values only pasting before. So that was that problem. And the fix to the accounts page which, by the way, between the documentation and your reply, I now understand much better so double thanks.

Then on to the Insights page which was still badly broken. But, as you suggested, a restoration fixed that right up.

All is fixed and I am smarter. So win win!

S.

p.s. Thanks also for the confirmation that the “could not restore” error might not be user error. That makes me feel better :slight_smile:

1 Like

Now there are new issues that may be coincidental but might be connected to that account sheet update…

My Insights sheet and my Balance sheet - are no longer reflecting the latest update.

Both transactions and balances updated this morning. I redid the update and now Update Sheets says no new transactions/no new balances.

The Insight sheet shows yesterday’s numbers and the Balance sheet even has yesterday’s date. (All other sheets are fine and reflect today’s numbers.) I restored both Insight and Balance and both remain a day behind.

So, now what have I broken? And how can I fix it?

Thanks, Susan

@susandennis,

I don’t think that it’s broken, there is probably just a one day delay. You may need to visit the Tiller Console to re-authenticate accounts to pull in the latest data to make it available for the add-on to be able to update the sheet with today’s numbers.

I’d recommend these steps:

  1. Log in to your Tiller Console at https://sheets.tillerhq.com/auth/google
  2. Click Account Summary
  3. Wait for the refresh process to stop (indicated by little spinning wheel).
  4. Click the “refresh” link next to the account if you see it or click gear icon for the account and choose “update login or fix account error” if there is no “refresh” link.
  5. Enter credentials and/or security codes/answers.
  6. Wait for a successful connection.
  7. Open the Tiller Money Feeds add-on and click “update sheets”

If that still doesn’t work, please reach out directly to support@tillerhq.com

Heather

Sorry, I was not clear. Your steps are exactly what I did before posting here. Twice. No change. The two sheets still don’t update. They may note be broken but they are sure not fixed.

I’m glad you were able to get your Accounts sheet back up and running, @susandennis. Replacing a interlinked/referenced sheet without breaking links is not well supported by the API and we are doing our best… but the solution is imperfect. I’m sorry that created more work for you.

As for the Could Not Restore error… that is definitely not a user problem. It’s a platform problem. I’m crossing my fingers that Google prioritizes a solve at some point.

Cheers,
Randy

Can you clarify, @susandennis: you have new transactions and balance history rows in your spreadsheet that are not represented in the Balances and budget solutions? I.e. there is data in your spreadsheet that is not being incorporated into the downstream analysis sheets? (Or are you saying that Tiller Money Feeds hasn’t fed the latest data into the spreadsheet?)

Randy

No, sorry. Feeds are fine. Data is there. I have new transactions and balances today.

Balance History is correct. Net worth is correct. Transactions is correct. Monthly Analysis is correct. (And, by correct, I mean showing the data that the feeds downloaded this morning.)

Insights is not correct - today’s data is not showing on that sheet.
Balances is not correct - today’s data is not showing on that sheet and the date is yesterday’s date.

So yes, there is data in my spreadsheet that is not being incorporated into those two sheets.

Let’s start with the Accounts sheet, since it is the nerve center… if you unhide the area at right, does the hidden data reflect the new balances?

ha! You nailed it in one. (And I should have figured out to look after what you taught me yesterday.)

The hidden section of the Accounts sheet reveals that it has no new data since yesterday. Even the date reflects that. Yesterday.

Two things…

  1. I just checked out my own personal updated Accounts sheet and I’m noticing a bug in how the data validation changes when one archives the old version of a restoring solution. The data validation points to the archived version rather than the new one. You may see this in your Accounts sheet A2:A. Data validation should be $F$2:$F, not 'Archived Accounts'!$F$2:$F. I recommend making this change manually.’
  2. I’m confused as to why your account balance info in Accounts cols J & K aren’t updating. Does the formula in H2 look like this:
    =iferror(if(isblank(G2),iferror(1/0),query(INDIRECT("'Balance History'!$A$2:$AZ"),"select "&T$3&", "&T$6&", "&T$4&", "&$T$5&", "&T$8&", "&T$9&", "&T$10&" where "&T$7&" = '"&G2&"' ORDER BY "&$T$5&" DESC LIMIT 1")))

Randy

Your number 1 confuses me. I don’t know how/where to look. A2:A is only the copy of the list of accounts. No formula at all. So I’m thinking I’m looking in the wrong place… I’m interpreting A2:A to be column A from cell 2 on down.

BUT

I did discover that I had manually hosed the Accounts sheet with yesterday’s fix. So. Today, I deleted Accounts. Renamed Archive Accounts to Accounts. And ran the update/restore again. All the data was fixed and H2 looks exactly as you say it should. I then restored Balances and Insights and they both got up to date perfectly - with today’s date and data.

Then I went to one of my accounts and moved money from checking to savings (same account) to create both new balances and new transactions for testing.

I did the full data refresh from the console and then sheet update. The Accounts sheet (hidden columns) updated perfectly. The Quick Insights sheet did not. I restored it and now it’s up to speed.

I’m tentatively optimistic. If tomorrow morning’s run goes without a hitch, I’ll declare victory :slight_smile:

Thank you for your patient assistance.

Susan,

Regarding #1, select cells A2:A in the Accounts sheet and then go to Data / Data validation in the menu bar. The “List from range” should not include a sheet name reference just F2:F.

Sounds like you found a good way to work through this.
Sorry about the extra work.

Randy

Oh! Thank you for the explain on Data Validation. I was not even close :slight_smile: I found it now and List from range says: Accounts!F2:F102

I’m retired and am enjoying the learning experience of this extra work so no problem!

Sounds like the data validation is in good shape. Glad we checked.

I like your can-do spirit, @susandennis.

1 Like

This morning, I did my usual update starting with the console and ending with the ‘update sheet’ and everything worked exactly as expected. All sheets have the current, fresh data in the right place with the right date.

VICTORY!!!

Thanks again for your help.

1 Like

Great news & perseverance, @susandennis.