I've got a sheet comprised of lastName, firstName pairs in one column, which I'm trying to convert into firstName Lastname.

When I use the following code, nothing happens on the sheet:

  SpreadsheetApp.getActive()
    .createTextFinder('(\w ),\s(\w ).*?')
    .useRegularExpression(true)
    .matchEntireCell(false)
    .matchFormulaText(false)
    .replaceAllWith('$2 $1');

If I swap the regex for an actual string, it works fine. Been testing my regex on regexr.com and it matches everything I throw at it, so what am I doing wrong?

CodePudding user response：

The createTextFinder() method takes a text string rather than an actual regex. Internally, the function will use RE2 to match text.

In JavaScript, you can use escapes to give a specific meaning to certain special characters. For example, to specify a newline character, you can use '\n'. That sequence will give a control character rather than a backslash and an 'n'. To get a backslash and an 'n', you would have to escape the escape, as in '\\n'. That will will give two characters, a backslash and an 'n'.

To make your code work, use double escapes, like this:

    .createTextFinder('(\\w ),\\s(\\w ).*?')

You can improve the expression a bit, like this:

    .createTextFinder('^[^\\w]*(\\w ),\\s*(\\w )[\\s\\S]*')

CodePudding user response：

Another approach

function funko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let o = vs.map((r, i) => {
    let m = r[0].match(/^(\w ),\s (\w )$/);
    return [m[1], m[2]];

  });
  sh.getRange(2,2,o.length,o[0].length).setValues(o);
}

Data:

Output: