2

I'm trying to write a Google Apps Script to find acronyms and abbreviations within Google sheets using regular expressions. I have dozens of acronyms that need to be replaced over thousands of rows. I have found some great bits of code from stack overflow that help find and replace strings, but nothing for bulk finding regex and replacing with strings.

With trying to find and replace acronyms and abbreviations I found I needed to use regex with the boundary flag to prevent it replacing 3 letter matches within bigger words. In the example below, I'm looking for the acronym "xyz" and to replace it with "X Y Z". But don't want it do match on the word "abcxyzdef".

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetName");
  var values = sheet.getDataRange().getValues();  

  // Replace Acronyms
  replaceInSheet(values, '\bxyz\b', 'X Y Z');

  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

function replaceInSheet(values, to_replace, replace_with) {
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
}

From my little understanding it's to do with '.replace' only working with Strings and not Regular expressions. I've tried escaping the '\b', using double quotes.

Any help would be appreciated.

1 Answer 1

2

Use a regular expression instead of a string:

replaceInSheet(values, /\bxyz\b/g, 'X Y Z');
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you so much. I can't believe the answer was that simple. I won't tell you how long I spent trying to solve that!
No problem @Pommelhorse, always glad to help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.