Email Notifications Sheet with Script

Overview

Email and/or send SMS text message notifications customized to what you care about with your financial data.

Craft your own daily status email report with the content and format that works for you.

Get an alert for when a large transaction occurs or when a category goes over budget.

Send notifications to multiple recipients and/or vary recipients per notification.

Those are some examples of what this sample template sheet includes to get you started with some ideas.

This might be more suited to those who know their way around spreadsheets and are comfortable with crafting and modifying formulas. There are a few examples to work from and explore.

I wanted to automatically check for some of these things I was manually and visually looking for,
and I decided to make it a general configuration to more easily add/modify as things evolved.
And it gave me an excuse to play with script. :slight_smile:

Here are some example email notifications using sample data.

Tiller Daily Status:

Spending Limit Alert:

May Over Budget:

Text Message:

And the corresponding Notifications sheet configuration:

Note that this sheet uses Apps Script to send email as specified by the Notifications sheet.
See Notes Apps Script Scope

Installation

The installation steps include copying the Notifications sample data spreadsheet, it’s Apps Script, and Named function.

It’s important to copy the Apps Script and Named function into your Tiller spreadsheet first, before adding the Notifications sheet, as the Notifications sheet depends on them.

1. Make a copy of the Tiller Sample Data spreadsheet that includes the Notifications sheet and script.
Copy Tiller Sample Data - Notifications v1.0

There will be a warning message for Apps Script:
The attached Apps Script file and functionality will also be copied
- this is expected, and the script is needed to send email.
Copy Document message

At this point, you have a fully functioning sample data spreadsheet with Notifications sheet and script.
Feel free to poke around the Notifications sheet to get some hands-on experience with it.
Note that this sample data sheet is simulating new daily transactions/activity, so don’t attempt to “fix” things outside the Notifications sheet like uncategorized transactions or what appear to be blank rows on the Transactions sheet - as days progress, these resolve themselves. The primary goal is to focus on the Notifications sheet.

Optionally, to see it in action, add an email address to cell A2 and click the Send Email button in cell D1 to execute the script.
Upon first execution of the script, there will be Authorization required permissions to accept.
Click OK and select your Google account.
This will include Google hasn’t verified this app - because it isn’t released in the Google Add-ons system.
Click Advanced, click Go to Tiller Notifications Project (unsafe), click Allow.
After permissions have been granted, click the Send Email button again - now email will get sent according to the config.

Continue to Step 2 to install into your Tiller powered spreadsheet.

2. Copy/paste the Notifications Apps Script into your spreadsheet (do not copy the Notifications sheet into your spreadsheet yet)
From the Copy of Tiller Sample Data - Notifications v#.# spreadsheet, select Menu > Extensions > Apps Script
The Tiller Notifications Project script will be displayed - we’ll come back to this browser tab.
Open your Tiller powered spreadsheet you want to add Notifications to, and also select Menu > Extensions > Apps Script

There are a few different ways to copy script into Apps Script, and listed first is the single project case (recommended for beginner script users).

Choose one option.

Single Project Option

These are steps for adding the Notifications Apps Script in an existing project, so that your spreadsheet will have one project - Notifications script gets added to any pre-existing scripts.
If Apps Script has never been opened in this spreadsheet, an Untitled project with an empty myFunction script will be created.
Select and edit the Untitled project name at the top of the page to something you like, perhaps Tiller Project.
Click the + sign to the right of Files and select Script. Rename it Notifications.
Now go back to the sample data Apps Script browser tab, click in the Notifications.gs code, Ctrl+A to select all the code, Ctrl+C to copy.
Go back to your Tiller powered spreadsheet Apps Script browser tab, click in the Notifications.gs code, Ctrl+A to select all the code, Ctrl+V to paste.
Click the Save project disk icon to the right of Files Apps Script Save Project

Multiple Projects Option

These are steps for adding the Notifications Apps Script as a separate project, so that your spreadsheet will have multiple projects - one project for Notifications.
When there are multiple projects within one spreadsheet container, opening Apps Script will first present a page to select a project.
Apps Script Multiple Project Selection

If you already have multiple projects, click Create a new project and skip the copy and remove duplicates steps below.
Click Overview (circle-around-i icon) near upper-left corner.
Make a copy icon near upper-right corner of page, left of the trash can icon.
Select and edit the Copy of project name at the top of the page to something you like, perhaps Tiller Notifications Project.
- alternatively, just use the Untitled project default as a temporary copy, close Apps Script browser tab, re-open Apps Script and use the Create a new project option, then delete the Untitled project.
- Otherwise, Delete the duplicate Files/Libraries/Services and clean up appsscript.json (make visible via Settings cog), so you have a fresh project with no Files (e.g. delete Code.gs).
(If you are not sure what a fresh project looks like, create a new sheet and open Apps Script for a reference)

Click the + sign to the right of Files and select Script. Rename it Notifications.
Now go back to the sample data Apps Script browser tab, click in the Notifications.gs code, Ctrl+A to select all the code, Ctrl+C to copy.
Go back to your Tiller powered spreadsheet Apps Script browser tab, click in the Notifications.gs code, Ctrl+A to select all the code, Ctrl+V to paste.
Click the Save project disk icon to the right of Files.

The two Apps Script browser tabs can be closed now.

3. Import the NOTIF_RANGE_LINK Named function
From your Tiller powered spreadsheet,
Menu > Data > Named functions > Import function > Recent >
Select Copy of Tiller Sample Data - Notifications v#.# > Insert > select NOTIF_RANGE_LINK check-box
There will be a red warning message:
Sheet Import NOTIF_RANGE_LINK Error
This is expected and due to the Named function using the HYPERLINK function to create links to ranges within the spreadsheet (nothing external).
Click Import
Click x next to Named functions in upper-right corner to close the sidebar.

4. Copy Notifications sheet into your Tiller powered spreadsheet
From the Copy of Tiller Sample Data - Notifications v#.# spreadsheet,
Right-click the Notifications tab > Copy to > Existing spreadsheet > Recent > select your Tiller powered spreadsheet > Insert > OK
From your Tiller powered spreadsheet,
Locate and Rename the Copy of Notifications tab to Notifications (this is required)

You may notice the Table Range column has some #NAME? errors indicating Unknown function: 'NOTIF_RANGE_LINK'.
Sheet Install NOTIF_RANGE_LINK NAME Error
That is expected, let’s fix those next.

5. Update Sheet GIDs corresponding to your spreadsheet
From your Tiller powered spreadsheet Notifications sheet,
Go to the Sheet GIDs middle grey titled section (Dates header section),


Select the five vertical cells with numbers with the light-green fill and delete the numbers.
Those were sheet GIDs from the sample data spreadsheet.
The cells to the left will now populate with the sheet GIDs for your spreadsheet.
Select those cells and Ctrl+C/Ctrl+Shift+V copy/paste-values-only into the cells that you just deleted.

6. Find/Replace #REF! with NOTIF_RANGE_LINK
Unfortunately, any use of the Named function NOTIF_RANGE_LINK gets replaced with #REF! when copying the sheet, these steps will restore that.
These steps assume that all #REF! are due to NOTIF_RANGE_LINK usage in the Table Range column and Tax Planning AGI amounts.
Although unexpected, if you visually notice any other errors in the sheet, investigate and resolve those first.
Ctrl+H (Edit > Find and replace)
Find: #REF!
Replace with: NOTIF_RANGE_LINK
Search: This sheet <— IMPORTANT, you do NOT want to change All sheets, which is the default
Check Match case
Check Also search within formulas
Click Replace all
There should be 19 occurrences replaced (17 in Table Range, 2 in Tax Planning AGI amounts), if not, Ctrl+Z Undo and investigate.
Click Done

The Table Range column #NAME? errors should now be fixed and replaced by working hyperlinks.

At this point, the Notifications sheet and script is installed and functional.

Setup

Here are some initial setup steps before diving into the usage.

1. Execute the script to accept the one-time permissions request (see Notes for more detail)
Add an email address to cell A2 and click the Send Email button in cell D1 to execute the script.
Upon first execution of the script, no email will be sent, and there will be Authorization required permissions to accept.
Click OK and select your Google account.
This will include Google hasn’t verified this app - because it isn’t released in the Google Add-ons system.
Click Advanced, click Go to Tiller Project (unsafe) - the name will be what you named your project, click Allow.

Optionally, clicking the Send Email button again will actually execute the script and send email per the Notifications sheet config.

2. Specify the number of Transactions sheet rows to use for custom tables/reports
Sheet Transactions Rows
Go to the Transactions middle grey titled section, and notice the middle-column value to the right with the light-green fill.
This is the last row of the range. e.g. ="200"
Limiting the range improves performance, when all are not needed. A value that gets comfortably two months worth works well.
For all transactions, use an empty string. e.g. =""

For quick reference, the date displayed in the cell to the right of this value is the date of the row specified.

Experiment with what works for your use case.

3. Optionally, consider adding your spreadsheet URL for email link references (the initial sheet template does not use it)
Go to the Sheet GIDs middle grey titled section, and notice the Spreadsheet URL blank cell with light-green fill.
This cell is assigned the NOTIF_URL named range for convenience.
Enter your spreadsheet’s URL from the browser’s address bar using this format:
https://docs.google.com/spreadsheets/d/<ID>/edit
Example HTML usage in Table Note:
="<br><a href=" & NOTIF_URL & "#gid=" & NOTIF_GID & "range=" & $O9 & "> Go to Table </a>"

Usage

Quick Start

To simply get started with receiving automatic notifications based on the initial template config:
If you haven’t already,
Add an email address to cell A2
Click the Send Email button in cell D1 to execute the script and authorize permissions.

Add a Daily Time-driven Trigger

1. Confirm your spreadsheet’s Locale and Time zone in File > Settings is correct for you
2. Menu > Extensions > Apps Script
3. Click the left pane Project Settings gear icon > confirm the Time zone setting is correct for you
4. Click the left pane Triggers alarm clock icon
5. Click the + Add Trigger button in the lower-right corner
Choose which function to run > Notifications
Choose which deployment should run > Head
Select event source > Time-driven
Select type of time based trigger > Day timer
Select time of day > 5am to 6am (choose a time after your Auto Fill occurs and when you will not be using your spreadsheet)


Click Save
If asked, Choose an account
Close the Apps Script browser tab.

Now the Notifications script will automatically execute daily at the time you specified.

d. To enable Auto Fill, if it is not already enabled:
Extensions > Tiller Money Feeds > Settings
Click the Auto fill toggle to enable
“When Auto Fill is on Tiller Money Feeds will automatically fill your sheets with your latest available bank data. This happens once a day around 2AM.”

Since automatic Notifications are based on the data in the spreadsheet, Auto Fill enables a mostly fully automatic experience.

General

The general intent of this template is to have a framework for automating customized notifications when user specific conditions occur.
The template provides some examples that could be used as-is/modified/deleted. The user may want to start with a clean slate and create all their own.
Users may already have custom built tables/reports and they can be referenced/linked-to from the Notifications sheet (they do not need to be copied/moved).

Naming Convention

A NOTIF_ prefix is used for Named ranges, Named functions, and custom Apps Script functions.
These can be found when typing NOTIF_ in sheet formulas, Data > Named ranges, and Data > Named functions.
This naming convention is intended to indicate anything related to Notifications, for convenience, unique naming, organization, and functionality.

Color Scheme

The color scheme primarily follows the Tiller Color Palette.
Notably, the User Editable Green to indicate user editable fields and functions.
In addition, there is a yellow, orange, and red fill/font to visually highlight Send Email condition state - yellow is partially ready, orange is send email, red is stop sending email.

Sheet Layout

The sheet comprises of three main sections:
1. Config - left section with blue fill header (To, Subject, Message, etc.)
2. References - middle section with grey fill header (Dates, sheet references, etc.)
3. Helper Tables - right section with blue fill header (User Area for Tables and Helper Data)

See the Overview for a Config section image example.

References section (top portion):

Helper Tables section (upper-left portion):

The user will spend the most time editing the Config and Helper Tables sections.

Copy/Paste/Move Guidance

There is some brittleness to be mindful of with copy/paste/cut edits that depend on which section is being modified.
Consider - how will the change affect any formatting, Conditional formatting, and/or cell references?
Avoid full row operations (e.g. do not Insert, Delete, Clear, Hide entire rows)

1. To copy/paste/move within the email Config section
Avoid Edit>Cut (Ctrl+X), this will break formatting, including conditional formatting.
Use standard copy/paste/delete instead.
Expand column group by clicking + above column letters to reveal all columns in this section, to be sure to get all corresponding data on the row.

The Sent Data column is the exception and the above guidance still applies, but since there is no formatting in this column, Edit>Cut (Ctrl+X) makes sense for any cells in this column that are referenced in the Helper Tables so that the Helper Tables section updates correspondingly with the moving data.

2. To insert/delete rows within the grey References section
Note that this section has some Conditional formatting that could break/change with edits.
Select the right-most column of this section to see the Conditional formatting - check before/after edits.
This section is three columns wide, so select a row of cells across the three columns.
Right-click > + Insert cells > Insert cells and shift down

3. To copy/paste/move within the Helper Tables section
Use Edit>Cut (Ctrl+X) to move stuff around, as in this section we want any formatting to move as well.
Using Cut will also automatically update any references to the cells being moved.
After using Cut, select the old area (or the full sheet) and re-set the Roboto font.

Email Notifications Config

The Config section configures the email content and when email gets sent.
Most of the columns are inputs/outputs to/from the Notifications Apps Script.

Generally, fill out a row left-to-right like an email (To, Subject, Message, etc.), add the Send Email condition, select the ReSend frequency, and add a Table Range.

The second row defines the defaults and rows are processed top to bottom.
A default is used when the corresponding option is empty for that row.
Blank rows are essentially ignored whitespace, mainly for readability.

Email gets sent when the Send Email condition is yes AND either Sent Date is not a date OR today’s date is later than ReSend Date.
The easiest way to get an email to send is to set Send Email to yes and delete the Sent Date cell contents.
An orange conditional formatted yes means all conditions are met to send email. Yellow means one condition is met.

Config Column Descriptions

To, Subject, and Message are required options.

To
Email addresses, comma separated. Use mobile addresses alone and not mixed with other addresses.

Subject
Email Subject string. Build a string formula with variable references, or simply enter plain text.
e.g. =NOTIF_MONTH_NAME & " Over Budget for " &$M15& " Categories"

Message
Email Message string. HTML supported/optional.
e.g. spent over has bold HTML tags in:
="You have <b>spent over</b> " & TEXT($N12,"0%") & " of your monthly budget"

Send Email
Clicking the Send Email button in cell D1 will execute the Notifications Apps Script function and send email based on spreadsheet conditions and configuration.
The Send Email button is a convenience for testing and executing the Notifications Apps Script via a daily time-driven trigger is considered the primary use case.

Write conditions in this column to control when the notification should be sent.
These are the recognized values:
yes = send email, depending on ReSend Date conditions
no = do not send email
combine = combine other tables in the same email, use in rows directly after the main yes row. Multiple combine rows can be used and the combine chain is stopped with the first non-combine entry.
stop = stop sending email, stop processing rows (conditional format red). Use ahead of work-in-progress config rows, or simply to stop processing blank rows at the bottom of the sheet for performance.
An orange conditional formatted yes means all conditions are met to send email. Yellow means one condition is met.

The “defaults” row only has meaning when in row 2.
Specify default values to use when the corresponding column in the notification row is empty.
It only applies to columns read by the Notifications Apps Script (and not dates or helper data).

Sent Date
The Sent Date column is written by the Notifications Apps Script function with the date and time the email was sent.
Any errors will also be written here.
Manually deleting the date is an easy way to send another email without waiting for ReSend Date conditions.

ReSend Date
The ReSend Date column is automatically calculated, with a row 2 formula, based on the last Sent Date and the other ReSend column selections.
Use to control how often the notification should be re-sent. A blank ReSend Date means a notification will not be re-sent.
Note that the Send Email condition must also be yes for a notification to be sent.
A yellow conditional formatted date means the ReSend Date condition is met to send email.

ReSend Every
The ReSend frequency selections are used in the ReSend Date calculation to select how often the notification should be sent.
Data validation for the ReSend columns guide the selections.
These columns are not read by the Notifications Apps Script.

ReSend Custom
The ReSend Custom column can be used to write a custom formula with a date result that will override all other ReSend frequency selections.
ReSend Date = ReSend Custom, when defined.

Twice a Month Example Formula

Modify the dayOne and dayTwo values to your case.

=LET(dayOne, 10, dayTwo, 25,
sentDate, INDIRECT(LET(ltr, CHAR(64 + XMATCH("Sent Date", INDIRECT("A1:Z1"))),ltr&"2:"&ltr)),
validDate, IF((sentDate="")+(ISERROR(DATEVALUE(TEXT(sentDate, "MM/DD/YYYY")))), NOTIF_TODAY, sentDate),
day_, DAY(validDate),
month_, MONTH(validDate),
year_, YEAR(validDate),
dateOne, DATE(year_, month_, dayOne),
dateTwo, DATE(year_, month_, dayTwo),
dateOneNextMonth, DATE(year_, month_ + 1, dayOne),
IF(day_ < dayOne, dateOne, IF(day_ < dayTwo, dateTwo, dateOneNextMonth))
)

Sent Data
The Sent Data column is written by the Notifications Apps Script with the data in the Save Data column at the time a notification is sent.
Use in comparison with Save Data in Send Email conditions and Helper Tables.

Save Data
The Save Data column is used for saving data at the time a notification is sent - it will get written to the Sent Data column.
It can be used just for informational purposes, but the primary intended use is for comparing the current data with last sent data in the Send Email condition.
e.g. if a notification is sent for an over budget category, only re-send a notification when there is a change/new/different status.

Helper1
This is simply an optional helper column to help with constructing the row’s conditions and content.
Feel free to add more Helper# columns to meet your needs.
These columns are not read by the Notifications Apps Script.

Table Range
The Table Range in string format for sending a table in the email notification.
This is optional and a notification could have a Message without tables/reports.
There are a few other Table-prefixed options to support the table’s formatting.

NOTIF_RANGE_LINK Named Function

Use the NOTIF_RANGE_LINK named function to create a link to the range that can be dynamically sized to match the data and will auto-adjust if/when tables are cut/pasted moved around.
The link makes it easy to go to the respective table and visually check the table and range selection is as expected.
Use F4 key to make the parameters absolute references (e.g. $A$1).

Typically, the NOTIF_RANGE_LINK range parameter is the table header rows and then the offsets are used to expand the range based on table helper cell size results.

NOTIF_RANGE_LINK Usage

NOTIF_RANGE_LINK(range, gid, row_offset, col_offset)
EXAMPLE
NOTIF_RANGE_LINK(W3:Y8, 123456789, 3, 0)
ABOUT
Creates a hyperlink to upper-left cell of table range, with link name matching table range A1 notation.
range
Table range using A1 notation (required)
gid
Sheet gid value, can be found in Sheet GIDs reference section (default: NOTIF_GID)
row_offset
Number of rows to expand range (default: 0)
col_offset
Number of columns to expand range (default: 0)

Table Note
A note below the table. HTML supported/optional, e.g. <b>bold text</b>

Category and account Last Update example:
=NOTIF_CAT_STATUS & "<br>" & NOTIF_LAST_UPDATE_STATUS

URL example:
="<br><a href=" & NOTIF_URL & "#gid=" & NOTIF_GID & "range=" & $O9 & "> Go to Table </a>"

Table Border Size
Size of the table border (optional, default 1), use zero to hide borders for reports like Monthly Budget.

Table Cell Padding
Padding for each cell (optional, default 5)

Table Font Family
Font family, comma separated priority font list (optional, default Roboto)
General sans-serif is always included as the last option in the list.
Roboto is the default, since it is commonly used in Tiller spreadsheets.
Other common safe sans-serif options are:
Arial, Helvetica, Verdana
e.g. specifying Roboto,Verdana will result in Roboto,Verdana,sans-serif attempted in left-to-right order, depending on what the email client supports.

Table Font Size
Font size in points (optional, default are the sheet’s values)
Common values are 10, 11, or 12

Table Font IncDec
Font size increment/decrement in points (optional, default 0)
You may want all fonts to be smaller/bigger than that you’re seeing, independent of knowing the actual point size.
1 makes a 10pt font 11pt
-1 makes a 12pt font 11pt

Table Width
Table width in either percentage or pixels, ="<n>%" or ="<n>px" (optional string, default "")
e.g. for Monthly Budget sheet, try ="800px"

From Name
The email will show it is sent From this name.

Cc
Cc email addresses, comma separated

Bcc
Bcc email addresses, comma separated

Reply To
Email address to use when recipient replies to a notification.
This option is supported, but not included as a separate column by default, just in an effort to reduce the number of columns a bit. Feel free to add it either by inserting another config section column or perhaps renaming a column you don’t plan to use, e.g. rename Bcc to Reply To.

References

The middle grey section contains references to Dates and sheet data for convenience and efficiency.
e.g. use NOTIF_TODAY in formulas, instead of TODAY()
(the TODAY function gets executed once, it’s result referenced many times).

Named Ranges

This section contains the following Named ranges for readability and easy reference.
This list can be viewed in the Name box (Ctrl+J) to the left of the formula bar fx.
From the Name box, select or type a named range to activate the corresponding cell.

Notifications Prefix Note

Note that the Notifications! prefix gets added to the Named ranges when copying the Notifications sheet.
The names can be referenced in formulas without using that prefix, but the sheet will automatically add the prefix anyhow.
If you prefer to see/use the shorter name without the prefix, you could go through the following exercise:
a. change the named reference back to it’s standard A1 notation
(e.g. use AC2, instead of $AC$2 to keep it unique for replacing with Named range, since absolute references with both $ is the standard usage)
b. delete the named range
c. add the named range without prefix
d. Find and replace (Ctrl+H) This sheet within formulas the A1 notation with your new name
Be mindful for unintended matches - like $AC$2, where $AC$2 could also match $AC$21, etc.
e. NOTIF_GID is the only required named range, as it’s used in the NOTIF_RANGE_LINK Named function

NOTIF_CAT_STATUS
Status string for uncategorized/invalid category counts

NOTIF_GID
GID value for Notifications sheet, used as the default GID in NOTIF_RANGE_LINK Named function

NOTIF_INVALIDS
Count of invalid categories found on the Transactions sheet

NOTIF_LAST_UPDATE_CNT
Count of linked accounts last updated over 3 days ago

NOTIF_LAST_UPDATE_STATUS
Status string for linked accounts last updated over 3 days ago

NOTIF_MONTH_NAME
Full name of the current month

NOTIF_TODAY
Today’s date, i.e. use NOTIF_TODAY, instead of TODAY()

NOTIF_UNCATEGORIZED
Count of uncategorized transactions

NOTIF_URL
Base URL for the spreadsheet to optionally include URL links in notifications.
Replace <ID> with your spreadsheet’s value:
https://docs.google.com/spreadsheets/d/<ID>/edit

Sheet GIDs

Add any personal sheet GIDs you’d like to reference here.
Select the Accounts three cells and Insert cells and shift down - this will add it to the conditional formatting.
Type your sheet name in the first cell and copy down the formula in the second cell.
Ctrl+C/Ctrl+Shift+V copy/paste-values-only from second cell into third cell.
Use this GID value in the NOTIF_RANGE_LINK Named function.

Transactions

Repeated from Setup.
Notice the value to the right with the light-green fill.
This is the last row of range. e.g. ="200"
Limiting the range improves performance, when all are not needed. A value that gets comfortably two months worth works well.
For all transactions, use an empty string. e.g. =""

For quick reference, the date displayed in the cell to the right of this value is the date of the row specified.

Experiment with what works for your use case.

User Area for Tables and Helper Data

This area is a sandbox for the user to completely modify to meet their needs for custom reports and helper data.
It is intended as a convenience and helps to organize Notifications related data.
Using this area is optional and NOTIF_RANGE_LINK can be used to link to any sheet ranges within your spreadsheet.

The QUERY function works great for creating custom tables and there are many examples in the starter template. Craft custom tables/reports and use this area however you like.

Note that table cells/rows/columns are auto-sized in email to fit the content,
so there’s no need to spend time adjusting them for the sake of email.
Plus, varying all these would be awkward in the Notifications sheet.
The Table Width option is the best way to handle email table sizing, and Table Cell Padding is another option.

Example Usage

This Discretionary expense table is an example of how the template uses this area.

Note that these QUERY formulas expect a category group named Discretionary - change/repurpose this for your use case.

There is the main table that will get sent in an email and some helper cells around it for use in formulas.
The x above the table is just a visual mark to show where above table limit ends and next one can begin (formulas do not depend on this mark, it’s a visual organizational aide).
The 10 value to the left of Discretionary is a manually entered value to limit the QUERY output rows.
The 10 value to the left of Date is a formula that calculates the number of output rows (the image doesn’t show all 10 result rows).
The cell Date and the cell above Amount are QUERY formulas to produce the table.
The colors and bold font are user formats that will get reflected in email.

The corresponding Table Range cell uses the following NOTIF_RANGE_LINK formula:
=NOTIF_RANGE_LINK($AF$15:$AJ$16,,$AE$16,)
where $AF$15:$AJ$16 is the range of the two header rows,
and $AE$16 is the Result Count helper cell (10), which is the row offset to expand the range to AJ26.
The NOTIF_RANGE_LINK result is a hyperlink named AF15:AJ26, which is the full range of the table result to email (initial range + offsets).

For reference, here is the QUERY string portion of the Date cell formula:

"SELECT Col1, Col2, Col3, Col4, Col5
WHERE Col1 >= date '"&TEXT($AC$14,"yyyy-mm-dd")&"'
  AND Col4 IS NOT NULL AND Col4 <0
  AND Col6 = 'Expense'
  AND Col7 = 'Discretionary'
ORDER BY Col4 ASC 
LIMIT " & $AE$15 & "
LABEL Col1 'Date', Col2 'Description', Col3 'Category', Col4 'Amount', Col5 'Account'"

Supported Formats

These formats will be read from the sheet and seen in email:
Number format
Font italic, bold, color, size
Background color
Horizontal alignments
Merged Ranges

Not supported:
Charts, sparklines, and images
Cell dimensions (too performance hungry, in my testing)

Extra Tidbits

Here are some extra tidbits of possible interest that are not needed to use the template.

Extra Tidbits

Send Email Never
The Send Email defaults cell in row two can also optionally be set to never.
The intent of never is to never send email or change the sheet without having to delete/modify/re-add the trigger.
This could be used in a case where the user just wants to shut off Notifications - maybe a vacation, maybe for making config changes.
The trigger keeps executing the script, but nothing is done.
This is very close to the same as using stop instead.
One difference between never and stop is that never will not set the location and size of the Send Email button, that still happens when using stop.
The defaults row still functions the same as a defaults row even when set to never, although it doesn’t really matter, since nothing is done.
Conditional formatting is unaffected.

Send Email Always
The Send Email defaults cell in row two can also optionally be set to always.
The intent of always is to always send email for yes and no conditions, independent of date status.
It is intended to be used interactively as a debug aide to view email content of all the notification configs for anything amiss.
Keep in mind that some results may not look complete or quite right because the condition has not actually been met in some cases.
The stop keyword is still respected, so nothing beyond stop will be processed.
Remember to change back to the defaults keyword and possibly delete some Sent Date and Sent Data cells for when conditions actually occur.
The defaults row still functions the same as a defaults row even when set to always.
Conditional formatting is unaffected.

Apps Script Debug
The Apps Script page has an Executions tab that provides script message logging for debug.
The top of the Notifications function code has a debug variable that is false by default.
Change it to true to enable more logging messages.
var debug = true;
It is false by default for performance and more concise message logging.

NOTIF_GET_SHEET_ID Custom Function
This is the Apps Script function that gets the sheet GIDs in the Sheet GIDs grey reference section.
The GIDs are used in the HYPERLINK function, which is called from the NOTIF_RANGE_LINK Named function.
Input the sheet name and output is the GID of that sheet.
It was intentionally setup to just execute one time to copy/paste the result and reference the fixed value.
This is to keep it from unnecessarily executing all the time, and there is a load time that can be unpredictable for triggers.

NOTIF_HTML Custom Function
This is the Apps Script function that creates the email message body/tables in html format.
It is also a custom function that is useable in a sheet formula, just select a sheet cell and type =NOTIF_HTML and you’ll find it with usage available.
The parameters of this custom function are the same as some of those used in the Notifications sheet config header.
Remember, the range must be in string format, e.g. "Q3:S5" and not Q3:S5. range is the only required parameter.
I use it for occasional debug, or as a way to just see the html without sending email, or to get starter HTML strings for use in Message and/or Table Note fields.
I don’t recommend using it directly in Message and Table Note fields, as the loading time can make it unreliable to use with a Notifications Trigger.
Feel free to play with it to see some of what’s happening under the hood.

As an example, let’s say we want to change the the Table Note category and last update status to have “conditional formatting”.

Use the helper area as temporary scratch space and put each format category in a separate cell, making a 2x2 table. Merge the bottom row two cells into one cell. Then add color formatting you want.
Something like this:
Apps Script NOTIF_HTML Example Helper

Then, reference that table in a separate cell, using NOTIF_HTML with a borderSize of zero to remove table borders.
=NOTIF_HTML("AY6:AZ7",,,0)

Which will return the corresponding HTML string:

"<table border=""0"" cellpadding=""5"" style=""border-collapse: collapse; font-family:Roboto,sans-serif; font-size: 10pt;"">
<tr><td style=""color: #ee5253;"">1 uncategorized</td><td style=""color: #ee5253;"">2 invalid categories</td></tr>
<tr><td colspan=""2"" style=""color: #ee5253;"">3 linked accounts last updated over 3 days ago</td></tr>
</table>"

That can be used as a template for crafting your own parameterized formula to use in the Table Note field (keeping in mind the result needs to be a string):

=LET(fontColor, "color: #ee5253;",
tableStyle, "style=""border-collapse: collapse; font-family:" & $S2 & ",sans-serif; font-size: 10pt;""",
uncatStyle, IF(NOTIF_UNCATEGORIZED>0," style="""&fontColor&"""",""),
invalStyle, IF(NOTIF_INVALIDS>0," style="""&fontColor&"""",""),
lastUpStyle, IF(NOTIF_LAST_UPDATE_CNT>0," style="""&fontColor&"""",""),
"<table border=""0"" "&tableStyle&">
<tr><td"&uncatStyle&">" & NOTIF_UNCATEGORIZED & " uncategorized</td><td"&invalStyle&">" & NOTIF_INVALIDS & " invalid categories</td></tr>
<tr><td colspan=""2"" "&lastUpStyle&">" & NOTIF_LAST_UPDATE_STATUS & "</td></tr>
</table>"
)

Producing a result in email similar to this:
Apps Script NOTIF_HTML Example Email

Remember to delete or change the =NOTIF_HTML to text '=NOTIF_HTML, so it isn’t executing unnecessarily and causing unwanted row height changes across the Notifications sheet.
The scratch table could also be deleted, as it’s no longer needed, but remember to restore formatting for those cells.

Permissions

Feel free to copy, use, and modify to meet your needs.

Notes

Apps Script Permission

The Apps Script will request permission for the following Scopes:
View and manage spreadsheets that this application has been installed in
Send email as you
Apps Script Permissions

These are the Notifications Apps Script functions:
Email is sent using MailApp with these quotas.
Email is sent only to email addresses specified by the user in the Notifications sheet.
Email is sent from the Google account of the user’s spreadsheet.
Email content is only what the user specifies in the Notifications sheet and a date/version footer.
Reads the Notifications sheet and based on that config could read other sheets within the same spreadsheet.
Writes two columns in the Notifications sheet (Sent Date, Sent Data).

Disclaimer

A word of caution that you are solely responsible for your data, including and not limited to what gets sent in email/text. Use at your own discretion and risk.

FAQ

How do I send a text message notification to my cell phone?

Mobile carriers have email addresses in the format number@domain that can be used to send email to SMS text message.
Here are some examples:
AT&T: number@txt.att.net
T-Mobile: number@tmomail.net
Verizon: number@vtext.com

Why haven’t I received a text message, even though the sheet indicates it’s been sent?

This may depend on your mobile carrier and their anti-spam measures.
Here are some possible reasons:

  • Testing a mobile notification multiple times within a short amount of time could delay the text message many hours or even into the next day or two
  • Sending a text message with a table/html code could make it bounce and you’ll receive bounced email notices in the coming days (use plain text SMS messages)
  • Sending a text message with multiple recipients could make it bounce and you’ll receive bounced email notices in the coming days (only use one recipient for text messages)
  • Check your Sent Items folder to confirm the email was actually sent and the email address is correct

How often does the script run?

The script only runs when the user wants it to run, either interactively on-demand or via a user setup trigger. A daily trigger and some occasional on-demand Send Email button executions would be considered typical usage. The script is not running continuously in the background.

How long does it take the script to run?

This depends on the size of the table ranges, more data takes more time.
In my experience, 1-3 email notifications containing 2-4 tables each is typically less than 20 seconds via a Time-Driven trigger, less than 10 sections running interactively.
For reference, the Google Apps Script timeout limit is 360 seconds (6 minutes).
The script is designed with performance in mind, using available batch operations.

What’s the best way to learn the QUERY function?

Google Sheets QUERY function is really powerful for creating tables/reports and there are a few examples in the template.
For more, Ben Collins has an intro, including a template.
Google also has documentation with examples.

Will there be an Excel version?

There are currently no plans to pursue an Excel version.

I accidentally moved the button, how do I restore it?

Undo Ctrl+Z, if possible.
Or, just wait for the next time the script is run, and it will get restored at that time.
Or, to run the script now without possibly sending unwanted email, temporarily change Send Email defaults to stop, and click the button to execute script, change stop back to defaults.
Note that the button will still work regardless of it’s location/size.

I accidentally deleted the button and it’s too late to undo that action - how do I restore it?

These are steps to copy the button from the template spreadsheet to your spreadsheet:

  1. Open your spreadsheet to the Notifications tab > select the Send Email cell > Insert > Drawing (we’ll come back to this sheet)
  2. Open the template spreadsheet and right-click the button
  3. left-click the three vertical dots in the upper-right corner of the button > Edit
  4. left-click-hold a selection box around the drawing > Ctrl+C to copy
  5. Go back to your spreadsheet and Ctrl+V paste the drawing > Save and close
  6. right-click the new button > left-click the three vertical dots in the upper-right corner of the button > Assign script > Notifications > OK
  7. right-click the new button to resize via handles to fit the cell, or simply left-click the button to run the script to restore the location/size (use stop to prevent email from being sent)

Is it okay to duplicate the Notifications sheet in the same spreadsheet?

No, this will cause unwanted cross referencing between sheets and possibly other issues.
Instead, use File > Make a copy to maintain working versions.

How do I update my existing Notifications sheet to a new template?

First, use File > Make a copy to save off a backup, working version of your spreadsheet with Notifications for easy reference.
Open the copy to verify the Notifications sheet appears in working condition.
From your original, File > Version history > Name current version > Notifications Backup > Save
This will make it easy to go back to that prevision version, if needed.
Assess the changes and copy/paste changes of interest using the Installation steps as a guide.

How do I remove Notifications sheet with script?

First, use File > Make a copy to save off a backup, working version of your spreadsheet with Notifications for easy reference.
Open the copy to verify the Notifications sheet appears in working condition.
From your original, File > Version history > Name current version > Notifications Backup > Save
This will make it easy to go back to that prevision version, if needed.
Right-click the Notifications tab along the bottom and select Delete > OK (this will also remove the Named ranges)
Menu > Data > Named functions > Click the 3 vertical dots to right of NOTIF_RANGE_LINK > Remove > Click x to close the sidebar
Menu > Extensions > Apps Script

Depending on how the script was installed (single vs. multiple projects):

  • single project
    Click 3 vertical dots next to Notifications.gs > Delete > Delete (this will also remove the Trigger)

  • multiple projects
    Select the project you want to delete > Overview (circle-around-i icon) > trashcan icon (Delete project forever)

Great idea and work @Mark.S !

I really like it. It’s kind of like the “have it your way” version of the Tiller Hello Money daily email with some great features.

Clint

1 Like

Thanks @Clint.C ! Yeah, the customization is really nice - automatically check any conditions you care about and send notifications with the data you want to see :sunglasses:

1 Like

Wow, this is a great project. Looking forward to digging in when I have some time.

1 Like

Thanks @rhowell , I’ve been enjoying it, looking forward to hear how it goes for you :slight_smile: