0

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?

1

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")];
}));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.