CODEWITHSUNDEEP
regexgoogle-apps-scriptgoogle-sheets
xyz333
xyz333

Reputation: 768

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

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

Upvotes: 2

Views: 305

Answers (2)

Einarr
Einarr

Reputation: 332

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, '\\');
}

Upvotes: 2

Kristof Neirynck
Kristof Neirynck

Reputation: 4162

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');
}

Upvotes: 5

Related Questions