0

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();
}
0

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 pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

5
  • thanks for the response! i tried your function and it cleared all kinds off stuff it shouldn't have. sorry but doesn't appear to be working properly for some reason. i will try a few quick edits first.. – Brett May 16 at 19:54
  • you are right i did try to run directly from script editor. sorry. soo i don't understand what you mean by call it from another function... i guess i do understand, but i've never done that and don't know how to try.... – Brett May 16 at 20:09
  • Simply run the clearCellsThatContain_Print_() function to test it. This structure lets us keep the clearCells() a bit more generic, so that you can create additional functions that clear cells using criteria other than "Print" if necessary. – doubleunary May 16 at 20:14
  • wait... you just told me in the above comment running directly in the script editor is not what i should do, but now you are telling me to simply run the clearCellsThatContain_Print_() function to test it? so where do i run it if it is not from the script editor?? i'm confused. but thanks for the help ... so i did run from the script editor this time and it did nothing... – Brett May 16 at 20:20
  • Do not run the clearCells() function in the script editor. Do run the clearCellsThatContain_Print_() function in the script editor, or through any other means such as a custom menu item or a button. – doubleunary May 16 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.