What is the goal of your workflow? What problem does it solve, or how does it help you?
This workflow auto-renames transactions. It makes the whole Transactions sheet easier to read! It is a work in progress, and both my wife and I are pretty busy these days, so it you’re inspired to perfect this workflow, please don’t wait for us!
WARNING - This workflow can be destructive if you’re not careful with how you rename things. My wife and I have had the wind taken out of our sails with this more than once. More on the warning below.
How did you come up with the idea for your workflow?
It’s a function that I was looking for from Tiller since the start.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
- Add a sheet to your document and call it “AutoRename”
- Fill in the sheet like so…
- Make sure there are no empty cells at the bottom of the AutoRename sheet!!! (IMPORTANT!)
- Run the AutoRename Script! (it takes a while to work)
The AutoRename Script works by going through every transaction, row by row. If any part of any transaction matches an entry in the left column, the script will replace all of the contents in that transaction cell with the contents of the right column.
YOU REALLY NEED TO BE CAREFUL WITH HOW YOU RENAME THINGS OR YOU WILL UNDO WORK!!
For example, let’s say you have a transactions that looks like “Nordstromnyx7390275”, and you wanted them to just show up as "Nordstrom - ", so that you could add a description. You’d run the Auto-Rename script and be happy to see all your Nordstrom transactions formatted like so. If your style is to get more details, then maybe you go through all 30 Nordstrom transactions and add descriptions like “Nordstrom - Winter Coat”, “Nordstrom - Sweater”, and so on. However, the next time you run the script, you’d see that all 30 transactions are back to "Nordstrom - ". It’s very disheartening. That’s why we’ve tweaked how we rename things to something that’s still easy to look at but is different from the original transaction, like “Nordstro.m”. It’s a little janky, but oh well.
Anything else you’d like people to know?
We could use help, so dive in if you’re comfortable with Scripts (and RegEx). here’s the code:
function autorename_last50transaction() {
var sheet=SpreadsheetApp.getActive();
var desc_sh=sheet.getSheetByName('Transactions');
var range = sheet.getRange('C2:C50');
var list_sh=sheet.getSheetByName('AutoRename');
var list_txt=list_sh.getRange('B2:C');//replacement table [0,1]
var list_vals=list_txt.getValues();
var to_replace = ""
var replace_with = ""
for(var j=0;j<list_vals.length;j++){ // for each value in the list
to_replace = new RegExp(".*" + list_vals[j][0] + ".*");
replace_with = list_vals[j][1];
replaceInSheet(sheet, range, to_replace, replace_with);
}
}
function autorename_ALL() {
var sheet=SpreadsheetApp.getActive();
var desc_sh=sheet.getSheetByName('Transactions');
var range = sheet.getRange('C2:C');
var list_sh=sheet.getSheetByName('AutoRename');
var list_txt=list_sh.getRange('B2:C');//replacement table [0,1]
var list_vals=list_txt.getValues();
var to_replace = ""
var replace_with = ""
for(var j=0;j<list_vals.length;j++){ // for each value in the list
to_replace = new RegExp(".*" + list_vals[j][0] + ".*");
replace_with = list_vals[j][1];
replaceInSheet(sheet, range, to_replace, replace_with);
}
}
function replaceInSheet(sheet, range, to_replace, replace_with) {
var data = range.getValues();
var oldValue="";
var newValue="";
for (var row=0; row<data.length; row++) {
for (var item=0; item<data[row].length; item++) {
oldValue = data[row][item];
newValue = data[row][item].replace(to_replace, replace_with);
if (oldValue!=newValue)
{
data[row][item] = newValue;
}
}
}
range.setValues(data);
}
Is it ok for others to copy, use, and modify your workflow?
PLEASE DO!!!
