Yes I think this is feasible, and it will likely just become a question of how convenient and usable it might be based upon your use case. I have a working script that I can share for Google Sheets, but before I include it, I thought maybe I’d just explain the concept, and then I can finish testing it out a bit more and pass it along in the next post. I can also spend a little more time to describe how to add the script/macro if you haven’t done so in Google Sheets before. What I did to test was to make a copy of the first 30 rows of the Transactions sheet and run it in a separate test tab first to prove it out before touching the prime data.
The concept is:
In Column A which is a blank column in my Transactions sheet, I’m defining a split flag that directly embeds the split percentages and categories. Your example would look like:
s-65:20:15|food:household:merchandise
This flag could be entered manually, or it could be set by AutoCat.
You might be thinking that it’s going to take just as long to type out the embedded instructions as to just manually do the split, but that’s where I think either utilizing AutoCat to set the flag or perhaps having preset entries as a cell validation (select from a dropdown) might save time. Or if there is one or a few dominant use cases, then those instructions could be hardcoded directly into the script and the flag could be replaced with just a single character.
Once the flag is set, you’d simply go to the menu path “Extensions–>Macros” and select the “Splitter” macro that you’ll create (or initiate it using a keyboard shortcut associated with that macro).
The script/macro will find any rows with a flag, parse the split instructions embedded in the flag, and create the split rows with amounts and categories according to those instructions. I also have it making a back-up copy of the original row before deleting, but I wonder if that may just slow things down. Do you care to see that row anymore either (a) in another sheet as a back-up, or (b) in the Transactions sheet so that it could be manually deleted after verification of the split?
The script/macro as a final step marks the split rows as “split-Done”. The script also edits the transaction id to indicate it is a split. So ColA could just as easily be cleared altogether.
The script avoids collision with “Group” or “Type” columns that some users add which fill down automatically from the top and does not break these. However, if you had other custom columns that cascade down via a formula, the split rows would interfere until making a script edit. The script also ensures that the sum total of split rows matches the original Amount to the penny.
Let me know if it sounds like something to try out or if you have any other thoughts and I can follow-up with another post.
