Script to search my Google Sheets for a partial text match in a cell and if it contains a match then clear or delete entire cell

Question

I thought this would be more simple and easy to find online, but I'm having a difficult time so I'll try here first. I'm Looking for a script to search my Google Sheets for a partial text match in a cell and if it contains a partial match then clear or delete entire cell. I've seen this out there that deletes the entire row, but I want to delete just the cell with a partial match.

For example, search through and clear or delete the cell data that contains any partial match to 'Print'
There will be a random image name and number in front of the word Print like "19-Test-IMG_5690 :: Print"
Is it possible to have a script search my sheet or range of cells and clear or delete those cells with the partial match?

This was the closest I could find: That does what I want but only in column B and it doesn't work on partial matches.

function clean0() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange('B:B').getDisplayValues();
  var range = [];
  data.forEach(function(e, i){
    if (e[0] == "Print") range.push("B" + (i + 1));
  });
  sheet.getRangeList(range).clearContent();
}

Answer 1

Try this:

function clearCellsThatContain_Print_() {
  clearCells(/Print/i);
}

function clearCells(searchFor, sheet = SpreadsheetApp.getActiveSheet()) {
  if (!searchFor) return;
  const cellsToClear = [];
  sheet.getDataRange().getDisplayValues()
    .forEach((row, rowIndex) => row
      .forEach((value, columnIndex) => {
        if (value.match(searchFor)) {
          cellsToClear.push(sheet.getRange(rowIndex + 1, columnIndex + 1));
        }
      })
    );
  cellsToClear
    .forEach(cell => cell.clearContent());
}

The searchFor parameter should be a regular expression. You can also use a normal text string, as long as it does not contain any characters that require escaping.

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.

Answer 2

There's a new TextFinder class that makes it much easier to search for all cell's containing text. https://developers.google.com/apps-script/reference/spreadsheet/text-finder