Auto-Rename (In Progress...feel free to help!)

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?

  1. Add a sheet to your document and call it “AutoRename”
  2. Fill in the sheet like so…
  3. Make sure there are no empty cells at the bottom of the AutoRename sheet!!! (IMPORTANT!)
  4. 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!!!

I like this idea considering there is an Auto-Cat tool to auto-categorize transactions. This would be a great time saver! Unfortunately, I am not great at scripts or spreadsheet guru knowledge to assist, so this is a high-five to you and support the idea.

In the meantime, one workaround I use since I didn’t see a tool or create any tool/script: I use the the filter button to create a filter on the 1st row of headers. Then I can use the drop down menu above the Account column or Category column (or any column) and choose one account or category that has already been named how I want. It sorts the lines to show only that info then I can copy/paste the description. When done, either turn the filter off or go back to where I applied the filter and check the box to select all so it goes back to showing every single row.

I have to admit, while this IS time-consuming, it also provides an opportunity to be engaged with my finances. I definitely would prefer a tool to auto-rename so the time I spend in the budget is more focused on analysis and decision-making rather than data management.

1 Like

Is there a way to call a script from the sheet? Only thing I can figure out is to go to the script editor and run it, which is a bit of a pain.

I agree it’s a bit of a pain. You must be able to call a script from the sheet, as that’s what AutoCat does (I think). If any pros have tips out there, let us know!

IT pretty simple to add a button to the sheet that will run your script.
Here is one site that explains it.

This looks great. I have not used it but plan to. I have not worked with scripts before but hopefully just a copy/paste and button addition will do the trick. Thanks!

These should be helpful.

I love this idea and the community enthusiasm for it.

Spoiler: this feature is likely coming to a refreshed AutoCat this spring. :wink:

2 Likes

Nice! I was trying the same with a vlookup in another cell, but will give this a go.

@heather said: "Spoiler: this feature is likely coming to a refreshed AutoCat this spring. "

Excellent, hope it is as robust with multi conditions etc an per account naming rules, as in autocat

Thanks @brianvotoole for kicking this off