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
– Wiktor Stribiżew, Commented Mar 23, 2021 at 16:31
Wiktor, thank you for your response. Alas I can not figure out where to add your code snippet — xyz333
– xyz333, Commented Mar 23, 2021 at 16:41
When you want to replace the text in the cells on Google Spreadsheet, for example, how about using TextFinder? Ref 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
– Tanaike, Commented Mar 23, 2021 at 16:48

Kristof Neirynck · Accepted Answer · 2021-03-23 16:41:44Z

5

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

answered Mar 23, 2021 at 16:41

Kristof Neirynck

4,2132 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xyz333 Over a year ago

Kristof, thank you. Ok, I hear that loud and clear!!! Can you see a better approach for this?

Einarr · Accepted Answer · 2021-03-29 14:10:41Z

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, 2021 at 14:10

Einarr

3503 silver badges21 bronze badges

1 Comment

xyz333 Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related