遍历数组，在单元格中查找子字符串，替换Google表格中的文本

Question

我想在Google表格中创建一个函数，该函数循环遍历数组，找到一个字符串，并用新值替换字符串的文本。 我有有效的代码，但它仅在单元格仅包含字符串时才替换文本(即搜索"办公室"替换为"办公室"仅在单元格仅包含"办公室"时才有效(。 我希望能够将"办公室"替换为"办公室"，即使单元格包含"Office 867"。 下面是有效的代码。另外，如何让函数搜索 A 列、第 1 行到任何内容？ 截至目前，该函数仅在值在 B2 中时查找并替换任何值(即不搜索第一列 A，不搜索每隔一行的第一行(。函数适用于 B2 的任何内容，但不适用于 B1、C1、D1 等。 我知道这是因为 searchRange 变量，但我无法弄清楚如何设置参数以便它搜索整个工作表(即所有列和所有行(。 所以这是我到目前为止的代码。 我的问题在代码中作为注释：

// This code works but ONLY if entire cell matches the string being searched for
// I want to search for all cells that contain the string even if other text is present
// So if cell contains "office 98" I want to change that to "Office 98"
// If cell contains "blg 78" I want to change that to "Building 78"
// If cell contains "space 9876" I want to change that to "Space 9876"
// Also, how do I change the var searchRange so that the range is the entire sheet?
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);
function findReplace() {
var rangeValues = searchRange.getValues();
for ( i = 0; i < lastColumn - 1; i++){
for ( j = 0 ; j < lastRow - 1; j++){
if(rangeValues[j][i] === "office"){
sheet.getRange(j+2,i+2).setValue("Office");
}else if (rangeValues[j][i] === "blg"){
sheet.getRange(j+2,i+2).setValue("Building");
}else if (rangeValues[j][i] === "space"){
sheet.getRange(j+2,i+2).setValue("Space");
}; 
};
};
};

Answer 1

您可以使用getDataRange函数 [1] 来获取工作表中有数据的整个范围。若要避免匹配整个字符串，请使用方法 [2]indexOf检查搜索的字符串是否在单元格字符串内。要仅替换搜索的字符串，请对单元格值使用replace函数 [3]。我还放置了一个字符串验证，因此将忽略任何其他类型的单元格值(数字、日期、布尔值(：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var searchRange = sheet.getDataRange();
var lastColumn = searchRange.getLastColumn();
var lastRow = searchRange.getLastRow();
function findReplace() {
var rangeValues = searchRange.getValues();
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
var value = rangeValues[j][i];
if(typeof value != "string") { continue; }
if(value.indexOf("office") > -1){
var newValue = value.replace("office", "Office")
sheet.getRange(j+1,i+1).setValue(newValue);
}
else if (value.indexOf("blg") > -1){
var newValue = value.replace("blg", "Building")
sheet.getRange(j+1,i+1).setValue(newValue);
}
else if (value.indexOf("space") > -1){
var newValue = value.replace("space", "Space")
sheet.getRange(j+1,i+1).setValue(newValue);
}; 
};
};
};

[1] https://developers.google.com/apps-script/reference/spreadsheet/sheet#getdatarange

[2] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/indexOf

[3] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace

Answer 2

function findReplace() {
const ui=SpreadsheetApp.getUi();
const ss=SpreadsheetApp.getActive();
const srchA=['office','blg','space'];
const vA=['Office','Building','Space'];
const sh=ss.getActiveSheet();
const rg=sh.getRange(2,2,sh.getLastRow()-1,sh.getLastColumn()-1);
const v=rg.getValues();
v.forEach(function(r,i){
r.forEach(function(c,j){
srchA.forEach(function(s,k){
if(v[i][j].toString().includes(s)) {
sh.getRange(i+2,j+2).setValue(vA[k]);
}
});
});
});
}

或者也许是这个：

function findReplace() {
const ui=SpreadsheetApp.getUi();
const ss=SpreadsheetApp.getActive();
const srchA=['office','blg','space'];
const vA=['Office','Building','Space'];
const sh=ss.getActiveSheet();
const rg=sh.getRange(2,2,sh.getLastRow()-1,sh.getLastColumn()-1);
srchA.forEach((s,k) => {
rg.createTextFinder(s).matchEntireCell(false).findAll().forEach(rg => {
rg.setValue(vA[k]);
});
}
}