Introducing AutoKitten, an alternative to AutoCat

Spreadsheets gSheets Templates
,
41

I’ve stubbed out changes in the performAction function that will update the action field with regex substitutions. It took some Innerweb hunting to pin down the proper use of regex group and the substitution format ( some docs said \1,\2 etc others $1,$2 (which is working)

AutoCat sheet entries

image
image1181×171 41.9 KB

Line 91: says to match any digit followed by a letter and put a space inbetween
Line 92: (probably redundant):says look for a digit jammed next to ‘at’

Also specific to my situation, I get Descriptions with words jammed together, so I added another action type – replace. This action uses the string replace method to modify the target field. Unfortunately, the list I work against is rather long so the load and execution is a bit sluggish.

Modified code for performAction

 switch (rule.action) {
    case "set":
    //console.log(rule)
      //console.log(rowData)
      yap(`${rule.actionColumn}[${rowNum}]="${rule.actionValue}"`);
    //  console.log("Comparison:", rule.comparison)
      switch(rule.comparison) {
        case "matches regex":
        //const have = ${rule.actionColumn}[${rowNum}]
        //const replace = r.value.replace("/".${rule})
     //   console.log(rule);
      //console.log(rowData);
        const val=rowData[column]
        const nval=val.replace(rule.conditionValue,rule.actionValue)
        console.log(nval)
        r.setValue(rowData[column] = nval)
        break;
      default:
        r.setValue(rowData[column] = rule.actionValue);
      }
      case "replace":
        const val=rowData[column];
        const nval=val.replace(rule.conditionValue,rule.actionValue);
        console.log(nval)
        r.setValue(rowData[column] = nval);
        break;
//            yap(`${rule.actionColumn}[${rowNum}]="${rule.actionValue}"`);
//      if (rule.comparison == "match regex") {
//        rule.actionColumn.replace(rule.comparison,rule.actionValue)
//        r.setValue(rowData[column] = rule.actionColumn)
//      }
//      else {
//        r.setValue(rowData[column] = rule.actionValue);
//      }
      break;

Probably not optimal use of the data structures defined in the code, but I’ve yet to tease out the design.

1 Like
42

Excellent, thanks! Inspired by @ramerkw 's code, I updated the template with a new action called “replace”. It works the following way:

  • The action column must be the same as the condition column (makes sense)
  • The comparison must be one of ‘equals’, ‘matches regex’, ‘equals (ignore case)’, ‘contains’, and ‘contains (ignore case)’
  • For those cases, the replacement is carried accordingly (either string match or regex match) with case sensitivity as appropriate.

Thanks!

1 Like
43

@falcon8664 if you could post a sheet showing the problem, I’d be glad to look into it. Thanks!

44

@andrei, thanks for the offer. See attached

Copy of Tiller Foundation Template FOR SHARING

1 Like
45

Thanks, sent a request for access.

46

Hey @falcon8664, I fixed the issue. It had to do with the rule 12 “Amount equals (ignore case) $1,188.35” on row 22. The code was attempting to “ignore case” on a number, which got it confused. I fixed the code in your shared sheet and on my AutoKitten template. Give it a try!

47

quick question about how to set up the rules in AutoKitten… What is the preferred way to match on multiple strings for the same rule? In AutoCat, I can type in “BJ’S”,”BJS” to find a match on either “BJ’s” OR “BJS” but I’m not seeing that option in AutoKitten. Also (and perhaps related), it would be helpful to have some general guidance on how to set up some more complex rules. The sample sheet is WONDERFUL but perhaps if it included a larger variety of rules that might help illustrate more of the functionality. For instance, I’m still not exactly sure how/when to use “continue” vs “and next” (and I was expecting to see “or next” there which prompted my question here).

Thanks for sharing this!

1 Like
48

To match one of multiple strings, you’d want to use a “matches regex” comparison. For your example, in the “Value” cell you’d put:

/BJS|BJ’S/

That would match any string that contains BJS or BJ’S. To match regardless of case:

/BJS|BJ’S/i

You can arrange regular expressions for exact match, match at start, match at end, etc. Your favorite AI can tip you on that kind of stuff. I’ll add more examples to the template when I get time. Cheers.

1 Like
49

I’m taking a look at your code, thank you for sharing this template.

I took a look at your code, looks pretty good. I’m a Google Developer Expert for Google Workspace and I enjoy a good Apps Script solution.

Users:

To add this to your current template, I agree to make a copy of your spreadsheet and try it out on a copy of your spreadsheet. If you want to make it official, add it on your original.

You will need to have the AutoKitten sheet in your spreadsheet and it will need to be named “AutoKitten”. If you copy the sheet over (click on the arrow on the tab to copy to…) it will say “Copy of.” Rename the tab to only say “AutoKitten.”

The code is in the Extensions menu under “Apps Script.” You can copy and paste the code into the Apps Script IDE in your spreadsheet. You’ll need to save and run. It will give you an authorization screen, however since it is code in your own spreadsheet you are only authorizing yourself. It might say “Return to safety” but it is safe, I reviewed the code. Click on advanced and the “unsafe” link (again, it is safe).

The onOpen menu might be problematic if you already have a script in your Tiller spreadsheet with an onOpen function. If this is the case, you can paste the .addItem lines into the onOpen function you already have and delete the new onOpen.

If anyone has questions about Apps Script and how it pertains to you running this template I am happy to help.

-Alice
Tiller Evangelist

Bluesky, Instagram, Facebook, LinkedIn

1 Like
50

Thanks for your kind appreciation, @TillerAlice !