问题

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

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

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

答案1

得分: 0

我不确定，但如果你的范围由行和列组成，那么你将需要进行两层`map()`调用，以便循环遍历行，然后在每行中循环遍历单元格。

我尝试过这个方法，对我有效：

```javascript
let sheet = SpreadsheetApp.getActiveSheet();
let range = sheet.getRange('A2:D3');
range.setValues(range.getValues().map((row) => {
  return row.map((cell) => {
    return cell.replace(
      // 你的正则表达式。
      /(?:(?<lowercase_letter>\p{Ll})|(?<uppercase_letter>\p{Lu}))/gu,
      // 一个回调函数，第一个参数是匹配项，然后是所有捕获组，
      // 最后是偏移量、输入字符串和具有命名组的对象。
      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();
        }
      }
    );
  });
}));

在这个示例中，我使用了带有捕获组和回调函数的正则表达式，而不是替换字符串。这是为了展示如果需要的话可以进行相当复杂的替换。

<details>
<summary>英文:</summary>

I&#39;m not sure, but if your range is made of lines and columns, then you&#39;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:

```javascript
let sheet = SpreadsheetApp.getActiveSheet();
let range = sheet.getRange(&#39;A2:D3&#39;);
range.setValues(range.getValues().map((row) =&gt; {
  return row.map((cell) =&gt; {
    return cell.replace(
      // Your regex.
      /(?:(?&lt;lowercase_letter&gt;\p{Ll})|(?&lt;uppercase_letter&gt;\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.