Using RegEx with AutoCat (Beta)

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?

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/

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.

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

1 Like

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.

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

1 Like

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

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

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

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
image

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.

1 Like

@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 :slight_smile:

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.

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.

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

1 Like

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…

1 Like

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.

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.

1 Like