loop through array, find sub-string in cell, replace text in Google Sheets

Question

I want to create a function in Google Sheets that loops through an array, finds a string, and replaces the text of the string with a new value. I have code that works but it only replaces the text if the cell ONLY contains the string (ie search for "office" replace with "Office" only works if the cell only contains "office). I want to be able to replace "office" with "Office" even if the cell contains "office 867". Below is the code that works. Also, how can I get the function to search column A, row 1 to whatever? As of now, the function only finds and replaces if the values are in B2 to whatever (ie the first column A is not searched and the first row of every other row is not searched. Function works on B2 to whatever but won't work on B1, C1, D1, etc. I know it is because of the searchRange variable but I can't figure out how to set the parameters so that it searches the entire sheet (ie all columns and all rows). So here's the code I have so far. The questions I have are in the code as comments:

// 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");
      }; 
    };
  };
};

I believe I could do this with a formula, rather than a script, if that's something you're interested in. — MattKing, Feb 13 '20 at 19:45

Cooper · Answer 1 · 2022-10-26T16:03:47.813

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

or maybe this:

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

Tried this suggestion, however only returned the replacement for 'Office'. If you wouldn't mind taking a look at my Sample Sheet (https://docs.google.com/spreadsheets/d/1izgt3_vuTcVhaj9QjFha-6LVKpA3ysjop5GGH8JFlcA/edit?usp=sharing) as, I feel, your answer provides a clear and quicker way of the result. Thanks — Stephanie, Oct 26 '22 at 15:54

score 0 · Answer 2 · answered Feb 14 '20 at 12:12

You can use getDataRange function [1] to get the entire range where there's data in the sheet. To avoid matching the entire string, use indexOf method [2] to check if the searched string is inside the cell string. To only replace the searched string, use replace function [3] on the cell value. I also put a string validation so any other type of cell value will be ignored (Number, Date, Boolean):

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

loop through array, find sub-string in cell, replace text in Google Sheets

2 Answers2

Linked