I have been using AutoKitten for a week now. Love it!
I turned off AutoCat, and emptied the autocat sheet, and I don’t think I am going back.
I do weird stuff with dating my paychecks, and it was pretty easy to add another case in the Actions to change a date to the first day of the next month.
I must have had a lot of complicated/non-standard autocat rules, because I tried importing once and many of them were not correct or got multiple rows. It probably works better for more mainstream rules. This was not a big deal, and it was nice when doing them manually to clean out unused rules anyway. (And add new ones!)
Really like your example sheet, and having the ability to do ‘comment’ rows is great for organizing.
Edit: From my testing, Google Sheets’ regex engine (RE2) and JavaScript’s RegExp object in Google Apps Script are different. So I had to change some of my RegEx from AutoCat rules to work properly in AutoKitten. Might be worth pointing that out in your first post, so people can find a RegEx helper that will give them correct format. (Or they are a savant and do RegEx manually. )
Terrific, thanks @m00 . You’re right that the importer is not thoroughly tested - I generated it with ChatGPT and tested it only on my own rules, which were not super general.
I’ve added a note regarding regexen.
Thanks again and please ping back if you make improvements!
I replaced the existing matches regexcase in the checkCondition() function with:
case "matches regex":
try {
let regex = new RegExp(conditionValue, "i"); // Case insensitive match
return regex.test(String(value));
} catch (e) {
throw new Error(`Invalid regex: "${conditionValue}"`);
}
Worked better at matching for me, and also gives errors if you mess up the regex.
Date to last day of month
This may be me-specific, but I added another case for the performAction() function. It takes a date field and changes it to be the first of the next month if it is at the end of the month. This makes it easier for me to budget income and makes my paycheck and my wife’s paycheck virtually pay us on the same day.
case "adjust date to first of next month":
const oldDate = new Date(rowData[column]); // Get the existing date
const lastDayOfMonth = new Date(oldDate.getFullYear(), oldDate.getMonth() + 1, 0).getDate();
if (oldDate.getDate() > lastDayOfMonth - 5) {
// If within the last 5 days, set to the 1st of next month
const newDate = new Date(oldDate.getFullYear(), oldDate.getMonth() + 1, 1);
yap(`Adjusting date for row ${rowNum} to ${newDate.toISOString().slice(0, 10)}`);
r.setValue(newDate);
}
break;
I just leave the ‘Action Value’ blank when that is selected as the ‘Action.’
Hmmm, looks like (some of) the calculated values in column A got replaced by actual values. It happens to me if I copy entire lines around; to create new rules, use “insert row” or copy only from column B onwards.
To solve this you may want to select values A1:A and then press Delete.
@m00 quesstion about your "matches regex" code. The extant AutoKitten code transforms the condition into a regex early (while loading rules in the autoKitten function). Presumably that’s a little faster because the code converts each regex only once, as opposed to creating a regex upon each check (though most time is spent in setValue, so I’m not too worried). In both cases, the error messages should be the same - what’s the advantage of deferring creation of the regex to checkCondition?
This is fun, thanks for doing it. I discovered one wrinkle which is probably categorizable as either a bug or a feature or both in Autocat. I somehow had created a useless rule in Autocat which assigned the category “Restaurant” but had nothing in any other column. In Autocat, this apparently was having no effect (as I’ve had this rule for months or years), but when imported to AutoKitten, it did the (right?) thing and matched transactions with no other matching rules to that category. Fortunately, I had only a half dozen or so blanks from previous years that ended up being “Restaurant”(s), and they were easy to track down as I had copied my whole Tiller sheet first.
I guess whether Autocat or Autokitten is correct here depends on whether anyone has a legit reason to want to categorize EVERYTHING which doesn’t match a rule to some default value (e.g. Miscellaneous).
One more wrinkle - Autocat allows rules such that: if the Category column on the Autocat sheet is “Stuff” and the Description Contains column in the Autocat sheet (for example) contains:
“Foobar1”, “Foobar2”
then a transaction with a description of either “Foobar1” or “Foobar2” will be categorized as “Stuff”.
The AutoKitten import from Autocat seems to copy the text in the Description column exactly, but the rule no longer matches.
This is basically an “OR” command. I had a lot of these when I started using Autocat a long time ago, but have gradually been cleaning them out as I ran into cases where I wanted more precise behavior anyway (i.e. depending on whether it was Foobar1 or Foobar2). So no biggie, but just a heads up. I’m sure there’s an equivalent if I really want an OR, but I haven’t figured that out yet. Of course the obvious fix is just to use two rules.
Yup, you are right. I was just modifying fast. Definitely better to keep everything in autoKitten(). For me at least, I still would want to make sure everything was converted to a string. Also, I had trouble getting the regex to work in the sheet when I didn’t force case-insensitive in the code; so I will leave that on for mine.
Updated in autoKitten():
if (comparison === "matches regex") {
try {
conditionValue = new RegExp(String(conditionValue), "i");
} catch (e) {
throw new Error(`Invalid regex "${conditionValue}" in AutoKitten, Column: ${conditionColumn}`);
}
}
@michael303 indeed if no condition is present it is assumed to be true. That makes multiline rules work. If you have an “orphan” rule with no condition (no previous line ending with “and next” or “else next”) that’s legal but suspect - that’s why there’s a conditonal formatting rule that makes that line yellow. All told I think it’s good to allow catch-all rules - just move them to the end.
I’d be remiss if I didn’t mention there’s a way to hook everything at the beginning, e.g. to color all negative amounts in red: put a catch-all rule at the top with “continue” in the next field.
@m00 - thanks for clarifying. I, too, find it annoying that I couldn’t take advantage of all regex flags so I finally sat down and figured out a simple solution: have the AutoKitten rules use the Javascript literal syntax in between slashes. Here’s the code - it verifies that the string is a correct regular expression and then it evaluates it:
if (comparison === "matches regex") {
const regexLiteralPattern = /^\/((?:\\.|[^\\/])+?)\/([a-zA-Z]*)$/;
if (!conditionValue.match(regexLiteralPattern)) {
throw new Error(`Invalid regex "${conditionValue}" in AutoKitten, column: ${conditionColumn}.`
+ " Use the Javascript literal syntax between slashes /like this/.");
}
conditionValue = eval(conditionValue);
}
So now instead of writing ^Spank .* Collective you’d write /^Spank .* Collective/i.
I modified the AutoKitten template doc accordingly, including an example.
I wish I was more techno savvy but I cannot get this to work for me. I was able to copy the sheet into my tiller worksheet as a separate tab on the bottom.
This is great, thank you for sharing. I am trying to figure out if I can use AutoKitten to set the value of description to be equal to the value of Merchant Name if the Merchant Name value is not blank. I can do the check for Merchant Name, however, when I set an action to set the value for Description I cannot figure out how to set it to be equal to Merchant Name.
@chachalamerachacha a belated hello - I missed your note. The answer is simple (and perhaps applies to other similar extensions) - you don’t need to keep the Script window open, feel free to close it. To run AutoKitten, take a look at the top-level menu in your Google Sheet: you’ll see a new entry called “Actions” and under that you’ll see “AutoKitten”.
AutoKitten is so useful that I have been sucked into understanding and fixing issues when I find them (not that there have been many). (I retired as a software developer after 30 years a few years ago, so this is now fun rather than work!)
A follow-on subtle bug? As discussed back in Feb, I set things up so that there was a catch-all rule at the end which colored red the description of anything that couldn’t be categorized. After a while I noticed that some cases were getting the colorizing treatment even though they had been correctly categorized. All these cases involved a three-rule row where the second rule contained an “else next”. (I hope I am getting the distinction between “row” and “rule” correct as used in the script comments). So I went in and debugged the code. There is a loop through the rules which sets the “test_succeeded” variable to begin as “false” on each iteration. But if the middle rule in the case I describe succeeded, this overrides that success, and that in turn means that this subsequent code:
switch (rule.next) {
case "": // Next row on success, next rule on failure
skip_this_rule = false; // in all cases we're done skipping
if (test_succeeded) break each_rule; // <= this is the break which doesn't happen
continue;
...
fails to break out of the each_rule loop. So we continue through all the rows, eventually ending up at my catch-all rule.
I have tentatively fixed this by changing the code which sets test_succeeded to false at the beginning of each iteration to make that conditional on the state of the skip_this_rule variable. I suspect that this may change the behavior of other complicated cases, but I will cross that bridge when I come to it. Or when I just have some free time to play around.
)
