I’m working on a Google Sheet for work (not allowed to share), and I want to create an Apps Script Function that parses through all the data and erases cell content if they contain “?”, “PPS”, “LES”, “MPES”,or “PSPEC”. I can’t find any way to do it without deleting the whole row, which i don’t want. Any help will be greatly appreciated, thank you.

CodePudding user response：

function delIfContains() {
  const contains = ["?","PPSF","LES","MPES","PSPEC"];//needles
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();//haystack
  contain.forEach(s => {
    sh.createTextFinder(s).matchEntireCell(false).findAll().forEach(rg => rg.clearContent());//reaper
  });
}

textFinder

CodePudding user response：

Cooper already showed how to do this with a loop that creates a TextFinder for value separately. To match all values in one go, use a regular expression, like this:

function test() {
  const range = SpreadsheetApp.getActiveSheet().getDataRange();
  const regexString = '\\?|PPS|LES|MPES|PSPEC';
  const numCleared = clearMatchingCells_(range, regexString);
  SpreadsheetApp.getActive().toast(`Cleared ${numCleared} cell(s).`);
}

/**
* Clears cells in a range that match a regular expression.
*
* @param {SpreadsheetApp.Range} range The range to search.
* @param {String} regexString The pattern to match with cells in range.
*/
function clearMatchingCells_(range, regexString) {
  const matches = range
    .createTextFinder(regexString)
    .useRegularExpression(true)
    .findAll();
  matches.forEach(cell => cell.clearContent());
  return matches.length;
}

Note that any regex special characters such as ? and * need to be double escaped in regexString.

CodePudding user response：

As another approach, how about using replaceAllWith with TextFinder as follows?

Sample script:

function myFunction() {
  const search = ["\\?", "PPS", "LES", "MPES", "PSPEC"];
  const r = search.map(e => `.*${e}.*`).join("|");
  SpreadsheetApp.getActiveSheet().getDataRange().createTextFinder(r).useRegularExpression(true).replaceAllWith("");
}

• When this script is run, the cell values including search are replaced with "".

Reference:

• replaceAllWith(replaceText)