Overview
Are you confused with Account names and Balances in the daily email summary that you get from Tiller?
You’ve reached the right place.
Note: This only works for the users who are using Tiller with Google Sheets
What is the goal of your workflow? What problem does it solve?
Get an email notification with the Account balances in your own format. The choice is up to you.
But, here are some of the possibilities that I believe might be useful for everyone. So, I’ve created the template for achieving these.
- Include your own custom alias names for your accounts
- Display the positive or negative sign next to the balances and highlight the rows with color code to easily differentiate assets from liabilities
Solution inspired from one of the top voted feature Use customized account names in daily e-mail instead of the auto-generated ones
Installation
What steps are required to add it to a user’s personal spreadsheet?
Install the Google Sheets add-on Sheet Automation - Automate Google Sheets from Google Workspace Marketplace.
After installing this add-on, you should be able to see “Sheet Automation - Automate Google Sheets” in the Extensions menu.
If you don’t see it yet, refresh your Tiller Spreadsheet on your browser.
Here’s the sample sheet needed for the setup.
Are there dependent sheets?
Yes, this workflow relies on the Accounts tab, which is part of the Tiller Foundation Template
Setup
Setup Balance Notification Sheet
- Copy the “Balance Notification” sheet from the Sample sheet linked above into your actual Tiller Spreadsheet
To do that, follow the below steps:- Open the sample sheet
- Right click on the “Balance Notification” sheet, select “Copy to” and then choose “Existing spreadsheet”.
- Switch to the Recent tab and choose your actual Tiller Spreadsheet
- Open your actual Tiller Spreadsheet and you should see the new tab created with the name as “Copy of Balance Notification” at the end. Rename it as “Balance Notification”
- Adjust the width of the columns to see all the values clearly
Here’s how the sample data would look like.
Setup a workflow to send email daily
-
Navigate to Extensions->Sheet Automation - Automate Google Sheets and select “Open…”
-
Once the App is loaded, click on “+ Rule” button and give the name.
Ex: “Account Balance Notification - Tiller” -
Click on the grey box under the Trigger field and choose the trigger named “Sheet is scheduled”
-
On the next screen, select the dropdown for Sheet field and choose the “Balance Notification” sheet
-
Update the Schedule from “Hourly” to “Daily” and choose the Time at which you want to receive the daily email notification. Hit the Save button, once done.
-
On the next screen, click on the button named “+ Action” and choose “Send Email” action
-
In the Send Email screen:
- Enter your email address in the “To” field
If you want to send this email notification to both yourself and your spouse, you can enter both your email addresses and separate them with a comma - Enter the subject as “Account Balance Notification - Tiller”
- Enter the Body as “Daily email notification with the latest account balances from Tiller sheet”
- Click on “Attachment” link in the bottom and choose “Export sheet” from the dropdown.
- Enter your email address in the “To” field
-
On the last screen, hit the “Save” button
-
To test the email notification, place your cursor on the Rule Name and click on the Ellipsis (…) icon on the right and select “Run”.
-
You should be receiving an email with a PDF attachment named as “export.pdf”. Open it to view the data and make sure the alignment is fine. If not, adjust your “Balance Notification” sheet accordingly.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Absolutely… Feel free to try it out and let me know if you face any issues.
Notes
-
Please note that whatever formatting you apply to the columns A:E in the “Balance Notification” sheet will be reflecting in your email notification.
-
If you don’t want to see the Manual Accounts from showing up in the list, update the below formula in cell A1
=QUERY({Accounts!L:L, Accounts!H:H, Accounts!I:I,
ARRAYFORMULA(IF(Accounts!O:O="Liability", Accounts!J:J * -1, Accounts!J:J)),
ARRAYFORMULA(if(isblank(Accounts!H:H), "",if(row(Accounts!H:H)=1,"Last Update",TODAY()-Accounts!K:K))),
ARRAYFORMULA(if(isblank(Accounts!H:H), "",if(row(Accounts!H:H)=1,"Account Id",LEFT(Accounts!G:G,6))))},
"select Col1, Col2, Col3, Col4, Col5 where Col6 <> 'manual' and Col1 IS NOT NULL order by Col1, Col2")
-
Just wanted to highlight one more use case where this might be helpful.
- If you are using “Bill Payment Tracker” template, then you can copy the “Bills Notification” sheet from the Sample sheet shared above and setup one more rule “Bills Due Notification - Tiller” in the add-on “Sheet Automation - Automate Google Sheets” for the “Bills Notification” sheet.
But, as we might not have the bills due in the next week or past due all the time, we can skip sending email on those days.
To do that, open the Rule and add a Condition. Choose the Range Condition, enter the range as “A2” and select the Condition from the dropdown as “is not empty”.
- If you are using “Bill Payment Tracker” template, then you can copy the “Bills Notification” sheet from the Sample sheet shared above and setup one more rule “Bills Due Notification - Tiller” in the add-on “Sheet Automation - Automate Google Sheets” for the “Bills Notification” sheet.
-
The add-on “Sheet Automation - Automate Google Sheets” is free to use with a limited quota of 300 rules triggers per month.
