Using RegEx with AutoCat

[wizavi]

Hello - when using RegEx with AutoCAT - I am seeing issues.

My bank savings account transaction has entries name like for income entries

MYCOMPANY EDIPAYMENT PPD ID: 1212121212
Mycompany Edipaymentppd Id: x33322

To cover both of these I was am trying to use AutoCAT RegEx entry

Category Description Contains Account Contains
Paycheck MYCOMPANY.*Edipayment Chase CHECKING

While the regex works in a text editor - I don’t see any transactions getting updated by AutoCat.

What am I doing wrong?

[warren]

You should be able to add a column name and append the regex suffix to it to build rules using Regex. E.g. Description Regex or Account Regex

Examples of terms here: https://cs.lmu.edu/~ray/notes/regex/

[wizavi]

I have used RegEx before. Also, what I am trying to do with RegEx is documented on StackOverflow as well. To verify that my RegEx statement is good, I copied some of my transactions data to Notepad++ which has RegEx search functionality, and I see that my RegEx string works as expected. So I am confident that the RegEx string is good.

But when I use it in Tiller AutoCAT, it isn’t working.

[randy]

Hey @wizavi,

I’m the developer for the updated AutoCat implementation. I’m glad to hear you’re trying the Regex feature. I’m sorry to hear that your rules aren’t processing as expected.

Honestly, our focus has been on the more core/commonly-used aspects of AutoCat. The Regex capability was added as a late Easter egg with limited testing and documentation (thus far).

I think the issue is that the version of regex implemented in Google Apps Script doesn’t always adhere to the standard. An easy way to try regex within the context of how our add-on makes the calls is to open the Script Editor, manually run a function like the one below and then click View / Logs to see what processed.

function regexTest() {
  Logger.log('Best   buy'.match(/(b|B)est\s*(b|B)uy.*/)!== null? 'match':'no match'); 
  Logger.log('Testing'.match(/ing$/)!== null? 'match':'no match'); 
}

These are the same calls we are making in our code. I know the process is iterative and clumsy, but you should be able to get where you need quickly until we can build out the regex tool’s documentation.

I hope this helps.
Randy

[wizavi]

Thanks Randy for responding back - love it when devs are able to take time to interact with customers using their work.

“I think the issue is that the version of regex implemented in Google Apps Script doesn’t always adhere to the standard.” - this feels scary to me and being a product guy I can imagine how it can impact larger audience. AutoCAT rules is something users would like to set and forget - so it is paramount that consistency over time is maintained. Maintaining consistency with non-standard stuff is next to impossible especially when you don’t own it.

If folks have to learn a special version of RegEx that is different from the standard - after a little while I will forget the quirks I learnt and any future issue will mean relearning and long term maintenance pain. Might be worth it to see if you can not depend on Google’s underlying functionality and use a standard library so that you own your destiny and it will also get you consistent interoperability between Google Sheets and Excel.

RegEx support is a very powerful feature - some might say how many users will really use it - they are right - but what they would be missing is that this is more important for template creators, and richer template library is the main reason I am evaluating Tiller over substitutes. With Money for Excel expanding, rich templates and active community is one way Tiller can differentiate your self and give higher value to customers.

Just my 2cents.

Thank you once again.

[randy]

I agree that we have work to do to make regex easier.

I think better documentation with some examples will go a long way… and perhaps we can even add a regex tester into the rule builder interface where the user enters a rule and confirms that it triggers on a string.

Thanks for your feedback. With time, this feature will be stronger and easier to use.

Randy

[wizavi]

Thanks @randy for the detailed suggestion of using Script Editor.

Is there anyway to add RegEx modifiers when entering text in “Description Contains” cell?

[randy]

No. You’d need to choose a Regex filter for that.

[wizavi]

Hi @randy - I just tried it out using Script Editor and I see successful matches - but when I use in Tiller sheets I am seeing “No Transactions updated”

 var str1 = 'MYCOMPANY        EDIPAYMENT                 PPD ID: 2562526265'
 var str2 = 'Mycompany Edipaymentppd Id: x4442'

 Logger.log(str1.match(/(m|M)(y|Y)(c|C)(o|O)(m|M)(p|P)(a|A)(n|N)(y|Y).*(e|E)(d|D)(i|I)(p|P)(a|A)(y|Y)(m|M)(e|E)(n|N)(t|T)/)!== null? '1: Yes match':'no match');
 Logger.log(str2.match(/(m|M)(y|Y)(c|C)(o|O)(m|M)(p|P)(a|A)(n|N)(y|Y).*(e|E)(d|D)(i|I)(p|P)(a|A)(y|Y)(m|M)(e|E)(n|N)(t|T)/)!== null? '2: Yes match':'no match');

Here is screenshot of how I have it in my Transactions sheet

Here is screenshot of how I have it in my AutoCAT entry (as you can see I put this rule at the top to not clash with any previous ones)

But still seeing

[randy]

Try changing the column header in AutoCat from “Description Contains” to “Description Regex”, @wizavi. That will set your filter to a) operate on the “Description” column and b) use a “Regex” filter type.

[wizavi]

@randy Yup - that did it.

As I had other rules which needed “Description Contains”, instead of changing the column header I created a new column with header “Description Regex” and moved the expression into that - worked just as expected.

While I read the doc of AutCAT, I didn’t completely internalize the section Filter Criteria Suffixes of https://help.tillerhq.com/en/articles/3792984-how-to-use-autocat-beta-for-tiller-money-feeds-sheets

Thanks for clarifying. Might be helpful to have an example in the doc for a slowmind like me

[wizavi]

I am seeing many places where credit cards companies are NOT consistent with case sensitiveness of their text

Costco by Instacart Httpsinstacarca
COSTCO BY INSTACART HTTPSINSTACARCA

Www Costco Com x-x-2292 WA
WWW COSTCO COM 800-955-2292 WA

It might be really helpful to provide a way for users to provide RegEx modifiers - folks are already accustomed with the format of /pattern/modifiers - why not just allow that notation?

Otherwise the regex strings will look horrendously bad and super hard to debug (see above) OR we have to create multiple rules to cover one for full upper case and one for full lower case, which will break if the descriptions ends with partial casing.

[randy]

We have shared a beta of an AutoCat rule builder with early adopters. It doesn’t help much with regex but does layer on a UX for more intuitively creating AutoCat rule columns without having to understand the filter-column/filter-type paradigm.

[randy]

The Contains/Starts With/Ends With/Equals filter types are case insensitive. That is probably a more efficient approach in most cases than regex.

[randy]

I just changed the regex call in the add-on to function in a case insensitive mode. Right now, that change is pushed to a dev build that should be published broadly sometime next week. Thanks for flagging this issue. I think making the functionality case insensitive is the right solution…

[wizavi]

About AutoCat rule builder : How can I opt in for it?

Also - here is something I found to address the issue https://sites.google.com/view/googlappsscript/hot-off-the-presses/regex-search-using-textfinder

Case insensitive parsing makes sense - thanks for making the change so quickly - love it.

[randy]

My colleague, @tom, added you to the early adopter program, @wizavi. You should see a gray link at the bottom of the Tiller Money Feeds AutoCat sidebar pane. Let me know what you think.

[ScottC]

Randy:

I have the same issue mentioned by wizavi in his July 2020 post. For some reason, some of the transactions downloaded from Wells Fargo are upper case and some are lower. When I imported them , my AutoCat rules didn’t process.

Your post back to Wizavi in July 2020 says that the Contains/Starts With/Equals filter are case INSENSITIVE. Does this only apply to the Advanced AutoCat feature? if so, I missed that.

Also, regarding the use of Regex …

Another person in the Forum suggested using Regex by adding a column to AutoCat, “Description Regex.”

I’ve tested this in two ways:

Assuming this is the value in A2 being searched:

PURCHASE RETURN AUTHORIZED ON 08/29 PAYPAL *DOLCE VITA 718-308-2314 NY

Method 1. Including the match value as a literal string in the Regex Expression

Formula: =regexmatch(a3,“(?i)(?:Paypal)”) which finds Paypal regardless of case but it requires adding the match string as a literal for every rule. I’d like to avoid this if possible.

Method 2. Referencing the Match Value from another cell - C9 in the example below. This evaluates as TRUE which is correct but, unfortunately, it also evaluates as True if C$9 is empty.

Formula: =regexmatch(A9,“(?i)(?:”& c$9 & “)”) where cell c$9 contains “dolce | vita | Paypal” (without quotes)

Can you tell me how to change the expression so it evaluates as FALSE if the reference cell is empty?

I suppose the real question is this. What is the best way to use AutoCat if the values in the Description field of the csv import file are a mixture of upper and lower case strings?

Thank for any all help.

ScottC
Saluda, NC

[jpfieber]

Ahh, I think I see the problem. You don’t need to put a formula in the cell, just the Regex expression:
(?i)(?:Paypal)
Not sure if this fixes the issue you’re having, but I don’t think that column was intended to have formulas in it. @Randy will be authority on this, and likely knows a better way than I…

[ScottC]

Joseph:

This is FYI. No need to reply but I wanted to let you know that, with your help, I think I figured this out but still testing.

The whole point of this exercise is to create a rule that allows multiple values and that ignores the case. (UPPER, lower, etc.)

As you pointed out, I was trying to create a Regex formula when I didn’t need to if I used AutoCat’s Advanced Rule builder and selected Regex from the top Filter Criteria. Then, I entered multiple values separated by the pipe character ( | ) into a single match criteria.

Tiller added the “Description Regex” column in the AutoCat tab and added the example values shown below. (I hope you can get screenshots).

This seemed to work but I haven’t tested thoroughly.

I hope to connect with Randy and / or find more written docs about using Regex because I think it would greatly simplify the Categorization process. Tiller is already way better than others that I’ve tried but this would make it even better.

Thanks very much for your help.

ScottC
Saluda, NC