Email/text-msg Alerting Based on a Transaction (Google Sheets)

Google Sheets Show & Tell
1

Sharing my use-case and solution here. What brought me to Tiller was a need to send myself an alert (text message, email, etc.) if/when a transaction appears on any account which matches a search string.

Use Case

I cancel Peacok TV service, but they keep charging me. I can either:
A) Set a reminder to keep searching for “peacock” in my spreadsheet.
B) Automate that search and send myself a text message if “peacock” ever appears.

I like B.

Solution

Step 1

Create an Autocat rule so that “peacock” (or anything else you want to alert on) gets auto-categorized into the “Alert” Category.

Step 2

Add an Apps Script with the following code:

function CheckTransactions() {
  // Fetch the Transaction categories
  var TransactionsRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transactions").getRange("D:D"); 
  var Transactions = TransactionsRange.getValues();
  // Check if Alert category exists for any transaction (Use AutoCat rule to set an alert)
  for(var i in Transactions){
    if(Transactions[i][0].match("Alert")!=null){  //Column contains a transaction categorized as "Alert"...send email.
      var emailAddress = 'your_email@whatever.com'
      // Send Alert Email.
      var message = 'A transaction got categorized as Alert' ; // Email body
      var subject = 'Credit Card Transaction Alert!';
      MailApp.sendEmail(emailAddress, subject, message);
      }
    }
}

Then, enable this to automatically run each day/hour/minute (your preference) by going to “Triggers” ==> “Add Trigger”, and select CheckTransactions in “Function to Run”.

If email is enough for you, you’re done.

Step 3 (Optional)

Forward this email to your phones text msg emal address. For me (gmail user) I simply add the following filter:

Matches: from:(your_email@whatever.com) transaction got categorized as Alert
Do this: Forward to my_phone_number@msg.fi.google.com

Note: I’m a Google Fi customer, so for me it’s phone # @ msg.fi.google.com (look up what it is for you, based on your carrier).

2

I like what you’ve done here, @albano.mike, and I can see how Tiller’s integration with tools like Apps Script allows you do go further customizing automated workflows to suit your needs. I also like how you have repurposed Autocat as the real-time transaction flagger— clever!

One minor callout… you scan the range Transactions!D:D for the “Alert” category. This won’t work in all users’ sheets as some have added or moved columns. I’d recommend either calling out in the instructions that that call in the script should be customized to match the user’s Transactions sheet or adding a few lines of code to first find the Category column (e.g. fetch and find a match on Transactions!1:1, then use that index to fetch the correct column).

I’d love to offer you a reward with your contribution but script-based solutions are currently not eligible for our Builder Rewards Program.

Thanks so much for sharing this solution. It’s powerful but elegant in its simplicity.

3

You might be able to also incorporate the actual transaction that caused the alert to make it more actionable. I seem to recall doing this ages ago with Zapier and my budget sheet but I kept getting too many alerts so I shut it down, but I like your solution since it keeps it in house.

4

@yossiea Yep, including the offending transaction should be easy enough with something like:
var cell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetTab).getRange("M"+row); assuming a header, and defining row as the matching row, and then something like
var cellValue = cell.getValue();

In any case, totally doable. Same goes for dynamically looking up the transactions column to make it a bit more user friendly for those that alter the columns from the default template.

5

I tried to create a second column with a AutoCat on excel( not Google sheet), no category (or connect it to category), (on column D),separately - sub category… is any shortcut easy way to do so?

Thanks a lot.

6

Sorry, I don’t use Excel.