Solved my issue - needed to give permissions/access to the sheet for it to be added.
I am running into the same error of: Unable to Add Sheet
@abhiroopb: you said you were able to solve this problem. Can you explain the steps you took to be able to add this sheet? ThanksâŚ
Unable to Add Sheet
The add-on could add the requested sheet or its dependencies.
The process failed with message: âService Spreadsheets failed while accessing document with id 1NZz1fvT-NDUj5-mz1uo1Vw7lQrC93rTvEJSvAdMjSxY.â
Resolve the issue and then try again
UPDATE I re-ran this a second time and now it worked!
The solution I used:
- Click on âShareâ in your Google Sheet
- Click where it says âRestrictedâ and change to âAnyone with the linkâ.
- Click where it says âViewerâ and change to âEditorâ
- Click âDoneâ
Note this gives your sheet access to anyone on the Web.
Once youâve done this, try installing the sheet. Once the sheet is installed revert the changes above (and block access).
1 Like
But wait, other solutions donât need this work-around, right?
Hi @abhiroopb, thanks for writing back.
I tried those 4 steps, and still no luck after trying to add the Transaction Tracker (same error message about Unable to Add Sheet).
But thanks for sharing your steps; maybe it will help someone else.
@Mark.S They donât, Iâm unsure why this sheet does. Should definitely be fixed.
@patking02 Did you refresh the sheet? Maybe re-load Tiller App Script completely (restart browser, etc)
Hello - I just added this sheet and I am having an issue. The transactions populate correctly when the sheet is 100% fresh, but if I select a single filter (e.g., a Group), the sheet immediately breaks unless I ctrl+Z back to the original. Strangely, not even unselecting the filter works.
I have not modified the base template sheets (transactions, categories), so I am unsure what would cause this behavior. Is anyone else having a similar problem and/or know of a solution? This sheet seems extremely useful, so I would love to get it working.
Try adding the Group column to your Transactions sheet. Seems to have fixed it for me.
Insert column after Account # column and put this equation in that columnâs row 1 cell:
={"Group";ARRAYFORMULA(IFERROR(XLOOKUP(INDIRECT("$D2:$D"),INDIRECT("Categories!$A$2:$A"),INDIRECT("Categories!$B$2:$B"))))}
1 Like
That fixed it! Thank you Mark!
@yossiea Great solution!
However, my Transactions sheet doesnât have a Group column, which seems like a common-enough situation that this sheet could handle more gracefully. I edited the formula in A12 to fall back on an XLOOKUP if a Group column isnât found on the Transactions sheet (the change is in line 3, below):
=QUERY(
{INDIRECT(V14),INDIRECT(V15),
if(isna(T19),arrayformula(XLOOKUP(INDIRECT(V18),INDIRECT(V16),INDIRECT(V17),"")),INDIRECT(V19)),
INDIRECT(V18),INDIRECT(V20),INDIRECT(V21),INDIRECT(V19),INDIRECT(V22),INDIRECT(V24),INDIRECT(V26)},
"select Col1, Col2, Col3, Col4, Col10, Col9, Col5, Col6, Col8
where " &
if(D6="","","upper(Col2) contains """&UPPER(D6)&""" and ") &
if(D5="","","Col9 ="""&D5&""" and ") &
if(D4="","","Col10 = '"&D4&"' and ") &
if(H4="","","Col9 != """&H4&""" and ") &
if(H5="","","Col4 != '"&H5&"' and ") &
if(B5="","","Col3 = '"&B5&"' and ") &
if(B6="","","Col4 = '"&B6&"' and ") &
if(F6="","","Col6 contains '"&F6&"' and ") &
"Col1 >= date '"&TEXT(AC13,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(AC14,"yyyy-mm-dd") &
if(H11=FALSE,"'","' and NOT Col4 matches '" & TEXTJOIN("|",true,AA13:AA) & "'") &
if(F4=FALSE,""," and Col8 <> '' ") &
if(F5="",""," and upper(Col8) contains '" & UPPER(F5) & "' ") &
if(D7 ="Negative","and Col5 <0 ","") &
if(D7 ="Positive","and Col5 >0 ","") &
if(H8 ="","","and Col5 <= "&H8&"") &
if(H7 ="","","and Col5 >= "&H7&"") &
if(F7 ="","","and Col5 = "&F7&"") &
"Order by Col1 "&H6&", Col9 asc
Label Col1 'Date', Col2 'Description', Col3 'Group', Col4 'Category', Col5 'Amount', Col6 'Tags', Col8 'Note', Col9 'Account', Col10 'Institution'")
1 Like
@yossiea
Another suggestion:
The sheet creates unnecessary rows of 0s in columns Q and R because the range P13:P includes empty cells. To fix, in cells Q13 and R13 change each reference from P13:P to
filter(P13:P,P13:P<>"")
So the formula in Q13 becomes:
=arrayformula(if(L5="Group",COUNTIF(C13:C,filter(P13:P,P13:P<>"")),if(L5="Account", countif(F13:F,filter(P13:P,P13:P<>"")),if(L5="Category",countif(D13:D,filter(P13:P,P13:P<>"")),COUNTIF(B13:B,filter(P13:P,P13:P<>""))))))
And the formula in R13 becomes:
=arrayformula(if(L5="Group",sumif(C13:C,filter(P13:P,P13:P<>""),G13:G),if(L5="Account",sumif(F13:F,filter(P13:P,P13:P<>""),G13:G),if(L5="Category",sumif(D13:D,filter(P13:P,P13:P<>""),G13:G),sumif(B13:B,filter(P13:P,P13:P<>""),G13:G)))))
1 Like
I just noticed your question about filtering on more than one account. We had to do this because our bank accounts were hacked forcing us to retired the hacked and get new account numbers. So, for part of the year, our joint spending was in one account. The rest of the year it was in a different account. I added a column (AB) to the Transactions sheet named âAccount Groupâ. I used an array formula to assign a label to each transaction based on the acct number that is downloaded to Col K in my Transaction sheet.
Basically, it works like this. If the value in Col K contains either [12345] or [67890], enter âJointâ. If it contains [2 other strings] enter ['my wifeâs name].
This allows me to use the Account Group column to search for transactions from the old, hacked account or the replacement.
I think a similar approach could be used in your situation. But, youâd also have to modify the Transaction Tracker template to refer to the new Account Group column instead of the Account column as it does today. It would take some fiddling but I think it would do what you want if you still want to do it. I know itâs been a year since you posted the issue.
3 Likes
Thanks for those tips. For the Group column, I find itâs easier to add to the Transaction table and then it can be used for many functions.
1 Like
It might be useful to add a Group column to the Transactions sheet, but I donât think the instructions mention it, and it doesnât seem easier than just having the sheet handle it.
This is a great sheet â Iâd bet that almost every Tiller user will find a use for it sooner or later â so why not make it as robust as possible?
1 Like
I understand your viewpoint, but weâre using Group in the filter and output. Itâs an important filter. If someone doesnât have it on their transaction sheet, I presume theyâd rather put it in then let my sheet error handle it.
I think this formula is much easier to put on the Transaction sheet, than redo the tracker to check for errors: =ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))
1 Like
Ah, I think I wasnât clear about how my suggested change handles the error â the change I posted for the formula in A12 doesnât generate an error message. Rather, if it doesnât find a Group column on the Transactions sheet, it looks up the group assignment for each transaction. So itâs transparent to the user and allows filtering by group exactly as intended.
More detail: In A12, my change simply replaces the reference to the Group column on the Transactions sheet â INDIRECT(V19) â with an IF statement that checks for an NA error in T19. If thereâs an error, thereâs no Group column on the Transactions sheet, so we use XLOOKUP to create an array of group values that correspond to the values in the Transaction sheet category column. If thereâs no error, the Group column exists, so we use it:
if(isna(T19),arrayformula(XLOOKUP(INDIRECT(V18),INDIRECT(V16),INDIRECT(V17),"")),INDIRECT(V19))
Hope that makes sense. (Iâm not suggesting the sheet should check for every error, but this seems to be a common one; the Group column isnât included on the Transactions sheet in the standard template; and the instructions for this sheet donât mention adding it.)
This is super cool. Thoughts about adding a Clear Filter button?
This is amazing, thank you! I also love the sheet and was unsure why groups were not appearing to populate. The link to the solution to add the column was so easy to follow! Great work!
Sorry for the late reply, but Iâm not sure a clear filter option would be possible without using script code.