Where is the “Apps Script and Named function” to copy first?
Simply continue following the instructions.
Amazing! I successfully got these cells added and copy/pasted into my grid above Sheet GIDs. All looks correct there.
The only thing I’m having an issue with figuring out is the array formula for my column AY grid I created to show the values. In the screenshot below you can see it’s saying “No Data” so I have something wrong. I copy/pasted from the monthly section above and updated the cells to look up from the yearly ones I added in. Here is the formula I currently have in case you have a quick solve!
=LET(cat, INDIRECT($AC$68), thisBud, INDIRECT($AC$70), thisAct, INDIRECT($AC$69), thisAva, INDIRECT($AC$62),
lastBud, IF($AC$63=“N/A”,ARRAYFORMULA(IF(cat=“”,“N/A”,“N/A”)),INDIRECT($AC$63)),
lastAct, IF($AC$64=“N/A”,ARRAYFORMULA(IF(cat=“”,“N/A”,“N/A”)),INDIRECT($AC$64)),
lastAva, IF($AC$65=“N/A”,ARRAYFORMULA(IF(cat=“”,“N/A”,“N/A”)),INDIRECT($AC$65)),
cCat, INDIRECT($AC$44), cType, INDIRECT($AC$46),
IFERROR(QUERY({cat,thisBud,thisAct,thisAva,lastBud,lastAct,lastAva,
ARRAYFORMULA(IFERROR(XLOOKUP(cat,cCat,cType)))},
"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7
WHERE Col8 = ‘Expense’
AND (Col1 = ‘AB Personal’ OR Col1 = ‘Groceries’ OR Col1 = ‘Restaurants’ OR Col1 = ‘Joint Entertainment’)
ORDER BY Col2 DESC
LIMIT “& $AX$2 &”
LABEL Col1 ‘Category’, Col2 ‘YTD Budget’, Col3 ‘YTD Actual’, Col4 ‘Favorability’
",0),“No Data”))
Say, could you please edit that code block to put it inside a Preformatted text block?
That keeps things like quotes from changing to curly quotes that won’t work in sheets.
1 Like
I just copy/edited the formula in the spreadsheet for how I might do it.
The last month stuff from the Yearly Budget can be removed and was probably causing your issue.
Cell AY60 formula:
=LET(cat, INDIRECT($AC$68), thisBud, INDIRECT($AC$70), thisAct, INDIRECT($AC$69), thisAva, INDIRECT($AC$71),
cCat, INDIRECT($AC$44), cType, INDIRECT($AC$46),
IFERROR(QUERY({cat,thisBud,thisAct,thisAva,
ARRAYFORMULA(IFERROR(XLOOKUP(cat,cCat,cType)))},
"SELECT Col1, Col2, Col3, Col4
WHERE Col5 = 'Expense'
AND (Col1 = 'Fun' OR Col1 = 'Clothes/Gear' OR Col1 = 'Hobbies')
ORDER BY Col4 ASC
LIMIT "& $AX$59 &"
LABEL Col1 'Category', Col2 'YTD Budget', Col3 'YTD Actual', Col4 'Favorability'
",0),"No Data"))
1 Like
Thank you, Mark! I have everything set up and operating how we’d hoped. Thanks again for the quick trouble shooting - hopefully others find this valuable as well.
2 Likes
I’ve run into a snag following your installation instructions. For sheet GIDs I get “‘Yearly Budget’ not found!”. Through my expert sleuthing skills I determined that it was because I didn’t have a “Yearly Burget” sheet. After copying one in from your “Copy of Tiller Sample Data - Notifications v1.1” I can not clear the error.
Yearly Budget is a standard Tiller template, I’d delete what you copied and install it from Tiller Money Feeds.
And then just temporarily change the Yearly Budget name in the Sheet GIDs section to something different and back again to Yearly Budget to get the script to re-run.
Great instructions for setting this up; thanks for the extra clarity. The pre-defined sample User Tables are useful. I especially like the “Over Budget” notification/table and related transactions.
I’m trying to customize the Large Transaction table and have trouble getting results, even in your sample sheet. Should that first WHERE condition be changed to Colt (Date Added) is “less than or equal to” NOTIF_TODAY?
WHERE Col6 >= date '"&TEXT(Notifications!NOTIF_TODAY,"yyyy-mm-dd")&"'
AND Col4 IS NOT NULL AND (Col4 <="&$AH$28&" OR Col4 >= "&$AI$28&")
AND Col7 IS NOT NULL AND NOT Col7 contains ':' AND NOT Col7 matches '"& $AE$32 &"'
1 Like
When I came back this morning, the error cleared and the GID populated. Everything appears to be working. Thank you.
1 Like
Yep, it’s just a matter of waiting for the script to re-run or forcing it.
Hey, thanks @brettanicus ! Over Budget is one of my favs, too.
The date condition is correct, as intended. The idea is that it’s a one-time alert to new large transactions, as opposed to a list of recent ones.
For example, auto-fill runs at say 6am hour, Notifications 7am hour, the new large transactions have TODAY Date Added (QUERY date condition is true). The helper table typically will be empty, as it only has new TODAY large transactions that haven’t been sent for about an hour window of time. Same thing with the New Transactions.
However, the new change to auto-fill four times per day changes things - auto-fill doesn’t just happen once per day in the hour preceding Notifications.
And so I have made some adjustments in my personal spreadsheet that I’ve been running for a bit to see what changes I might want to make to the shared template. It’s probably time for me incorporate those changes and I have a couple newer Notifications I’m considering including, as well, but I’m leaning to not add them, because I think there are enough examples already.
Here’s what the Large Transactions related cells might look like. It will include a datetime compare with the Large Transactions Sent Date, instead of always TODAY.
Cell AF30:
=LET(sentDate, $E$4, sentDatetime, IF(ISDATE(sentDate), sentDate, Notifications!NOTIF_TODAY-1),
IFERROR(QUERY({INDIRECT($AC$32),INDIRECT($AC$33),INDIRECT($AC$34),INDIRECT($AC$35),INDIRECT($AC$36),INDIRECT($AC$41),INDIRECT($AC$40)},
"SELECT Col1, Col2, Col3, Col4, Col5
WHERE Col6 >= datetime '"&TEXT(sentDatetime,"yyyy-mm-dd hh:mm:ss")&"'
AND Col4 IS NOT NULL AND (Col4 <="&$AH$28&" OR Col4 >= "&$AI$28&")
AND Col7 IS NOT NULL AND NOT Col7 contains ':' AND NOT Col7 matches '"& $AE$32 &"'
ORDER BY Col4 ASC
LIMIT " & $AE$29 & "
LABEL Col1 'Date', Col2 'Description', Col3 'Category', Col4 'Amount', Col5 'Account'
",0))
)
Cell AE31:
=LET(sentDate, $E$4, sentDatetime, IF(ISDATE(sentDate), sentDate, Notifications!NOTIF_TODAY-1),
IFERROR(TEXTJOIN("|",true,QUERY({INDIRECT($AC$32),INDIRECT($AC$33),INDIRECT($AC$34),INDIRECT($AC$35),INDIRECT($AC$36),INDIRECT($AC$41),INDIRECT($AC$40)},
"SELECT Col7
WHERE Col6 >= datetime '"&TEXT(sentDatetime,"yyyy-mm-dd hh:mm:ss")&"'
AND Col4 IS NOT NULL AND (Col4 <="&$AH$28&" OR Col4 >= "&$AI$28&")
AND Col7 IS NOT NULL AND NOT Col7 contains ':' AND NOT Col7 matches '"& $AE$32 &"'
ORDER BY Col4 ASC
LIMIT " & $AE$29 & "
LABEL Col7 ''
",0)),"none")
)
Cell M6:
=LET(sentDate, $E$4, sentDatetime, IF(ISDATE(sentDate), sentDate, Notifications!NOTIF_TODAY-1),
IFERROR(TEXTJOIN(" ;\n", TRUE, QUERY({INDIRECT($AC$32),INDIRECT($AC$33),INDIRECT($AC$34),INDIRECT($AC$35),
ARRAYFORMULA(TEXT(INDIRECT($AC$35),"$#,###") & " " & INDIRECT($AC$33) & ", " & INDIRECT($AC$36)),
INDIRECT($AC$41),INDIRECT($AC$40)},
"SELECT Col5
WHERE Col6 >= datetime '"&TEXT(sentDatetime,"yyyy-mm-dd hh:mm:ss")&"'
AND Col4 IS NOT NULL AND (Col4 <="&$AH$28&" OR Col4 >= "&$AI$28&")
AND Col7 IS NOT NULL AND NOT Col7 contains ':' AND NOT Col7 matches '"& $AE$32 &"'
ORDER BY Col4 ASC
LIMIT " & $AE$29 & "
LABEL Col5 ''
",0)),"none")
)
And I’ve simplified the corresponding count and sum cells.
Cell AE30:
=COUNTA(OFFSET(AF31, 0, 0, AE29))
Cell AI29:
=SUM(OFFSET(AI31, 0, 0, AE29))
1 Like