How can I use RegEx to find and replace over multiple columns in Google Sheets using App Script?

Question

I want a find/replace script in Google Sheets App script using RegEx to find and replace over multiple columns. I can only either find and replace using RegEx in one column or find and replace over multiple columns but don't seem to be able to use RegEx. Any ideas?

Here are those two scripts:

Finds and replaces using RegEx but will only work in 1 column:

var sheet = SpreadsheetApp.getActiveSpreadsheet()
var range = sheet.getRange("D:D"); 
range.setValues(range.getValues().map(function(row) {
return [row[0].replace(/\".*$/, "")];
}));

Finds and replaces plain text over multiple columns:

var to_replace = "example text"
var replace_with = "";

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('examplesheet');
var lastRow = sheet.getLastRow();
var ranges = ['E2:Z' + lastRow];
sheet.getRangeList(ranges).getRanges().forEach(r => 
r.createTextFinder(to_replace).replaceAllWith(replace_with)
);

Answer 1

I'm not sure, but if your range is made of lines and columns, then you'll have to do 2 levels of map() calls, in order to loop over rows and then loop over cells in each row.

I tried this and it worked for me:

let sheet = SpreadsheetApp.getActiveSheet();
let range = sheet.getRange('A2:D3');
range.setValues(range.getValues().map((row) => {
  return row.map((cell) => {
    return cell.replace(
      // Your regex.
      /(?:(?<lowercase_letter>\p{Ll})|(?<uppercase_letter>\p{Lu}))/gu,
      // A callback function with the match as first parameter,
      // then followed by all the capturing groups,
      // and finally by the offset, input string and an object with the named groups.
      function (match, lowercase_letter, uppercase_letter, offset, input, groups) {
        if (lowercase_letter !== undefined) {
          return lowercase_letter.toUpperCase();
        }
        else if (uppercase_letter !== undefined) {
          return uppercase_letter.toLowerCase();
        }
      }
    );
  });
}));

In this example, I used a regex with capturing groups and with a callback function instead of a replacement string. This was to show that one can do quite complex replacements if needed.