Find Replace with RegEx failing for string ending in ? Google script

Question

I have a script in Google sheets

I am trying to find and replace headers on a sheet from a table of values on a different sheet

It is mostly working as desired but the replace is not working for any string that ends in ?

I do not know in advance when a ? will be present

I am using this:

const regex = new RegExp("(?<![^|])(?:" + search_for.join("|") + ")(?![^|])", "g");

I have tried to figure out how to correct my Regex but not getting it

Thanks in advance for your assistance with this

I have in a sheet:

search_for	replace_with
ABC Joe	MNQ
XYZ car	NNN XXX
DDD foo?	Bob bar

I have for Headers on a different sheet:

Label	Id	ABC Joe	XYZ car	DDD foo?

after running the replacement I want for headers:

Label	Id	MNQ	NNN XXX	Bob bar

what I get is:

Label	Id	MNQ	NNN XXX	DDD foo?

var data = range.getValues();


 search_for.forEach(function(item, i) {
    pair[item] = replace_with[i];
  });

  const regex = new RegExp("(?<![^|])(?:" + search_for.join("|") + ")(?![^|])", "g");

  //Update Header row
  //replace(/^\s+|\s+$|\s+(?=\s)/g, "") - Remove all multiple white-spaces and replaces with a single WS & trim
    for(var m = 0; m<= data[0].length - 1; m++){
            data[0][m] = data[0][m].replace(/^\s+|\s+$|\s+(?=\s)/g, "").replace(regex,(m) => pair[m])
    }

You need to escape the `search_for` keys that you join with `|` using `.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&')` — Wiktor Stribiżew, Mar 23 '21 at 16:31
Wiktor, thank you for your response. Alas I can not figure out where to add your code snippet — xyz333, Mar 23 '21 at 16:41
When you want to replace the text in the cells on Google Spreadsheet, for example, how about using TextFinder? [Ref](https://developers.google.com/apps-script/reference/spreadsheet/text-finder) And also, I think that the batchUpdate method of Sheets API can be used for this situation. But, I'm not sure whether this is the same direction of your goal. I apologize for this. — Tanaike, Mar 23 '21 at 16:48

score 5 · Accepted Answer · answered Mar 23 '21 at 16:41

A word of warning: what you're doing is scaring me a bit. I hope you know this is a brittle approach and it can go wrong.

You're not quoting the dynamic parts of the regex. The ? is a special character in regular expressions. I've written a solution to your problem below. Don't rely on my solution in production.

//var data = range.getValues();

var data = [
    ['Label', 'Id', 'ABC Joe', 'XYZ car', 'DDD foo?']
];
var search_for = [
    'ABC Joe',
    'XYZ car',
    'DDD foo?'
];
var replace_with = [
    'MNQ',
    'NNN XXX',
    'Bob bar'
];
var pair = {};


search_for.forEach(function(item, i) {
    pair[item] = replace_with[i];
});

const regex = new RegExp("(?<![^|])(?:" + search_for.map((it) => quote(it)).join("|") + ")(?![^|])", "g");
for (var m = 0; m <= data[0].length - 1; m++) {
    data[0][m] = data[0][m]
        .replace(/^\s+|\s+$|\s+(?=\s)/g, "")
        .replace(regex, (m) => pair[m]);
}

// see https://stackoverflow.com/a/3614500/11451
function quote(s) {
    var regexpSpecialChars = /([\[\]\^\$\|\(\)\\\+\*\?\{\}\=\!])/gi;
    return s.replace(regexpSpecialChars, '\\$1');
}

Kristof, thank you. Ok, I hear that loud and clear!!! Can you see a better approach for this? — xyz333, Mar 23 '21 at 16:49

score 2 · Answer 2 · answered Mar 29 '21 at 14:10

2

Can you not do something really simple like escaping all non-alphanumeric characters which would work with the example data you gave above and this seems trustworthy

function quote(s) {
    var regexpSpecialChars = /((?=\W))/gi;
    return s.replace(regexpSpecialChars, '\\');
}

answered Mar 29 '21 at 14:10

Einarr

214
2
14

Thanks, this seems a simple solution to my problem and as you said seems trustworthy/non-brittle unless someone with greater knowledge will weighes in and say otherwise – xyz333 Mar 29 '21 at 14:31

Find Replace with RegEx failing for string ending in ? Google script

2 Answers2