Google-sheets – Search & Replace Script in Google Sheets

google sheetsgoogle-apps-script

I am scrapping info from a webpage into Google Sheets using scripts. The information contains addresses similar to 123 Test St, MKE

I am seeking help with a script that will take 'MKE' at the end, and convert it to 'Milwaukee, WI'

I have looked into regex replace functions but cannot solve this problem.

I have also come across code that assists, but does not complete what I am looking for.

function doTest() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for ( var i = 0 ; i<sheets.length ; i++) {
    var sheet = sheets[i];
    sheet.getRange('C:C').setValue('Milwaukee');
}
}

Any further suggestions?

Best Answer

The regular expression that matches "MKE" at the end of a string is MKE$ where $ anchors the match to the end of the string. It's not necessary to use a script to perform such replacement: the regexreplace function is available, and it can be applied to arrays. For example: 

=arrayformula(regexreplace(C:C, "MKE$", "Milwaukee, WI"))

If using a script to do the same, the logic would be

var sheet = ... ; // whatever sheet you work with
var range = sheet.getRange("C:C");   // or another range 
range.setValues(range.getValues().map(function(row) {
  return [row[0].replace(/MKE$/, "Milwaukee, WI")];
}));