Apps Script to clear multiple cells in row if they don't contain a value in another cell-CodePudding

I have a simple sheet that's scraped product details from a website along with their image URLs. Not all URLs are correct for the product and I would like to remove the cells that contain the incorrect ones. Each URL contains the product SKU. The correct product SKU can be found in Column C. All image URLs are in Columns C-X.

Screenshot of sheet

Is there a simple script that can do this for me?

Thanks!

CodePudding user response：

Probably something like this:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var range = sh.getDataRange();
  var [header, ...rows] = range.getValues();
  var new_rows = [];

  for (let row of rows) {
    var sku = row[2];
    var images = row.slice(8).map(x => x.indexOf(sku)>-1 ? x : ''); // '8' is column 'J'
    new_rows.push([...row.slice(0,8), ...images]); 
  }

  range.setValues([header, ...new_rows]);
}

It cleans all cells in columns J, K, L, etc. if a cell doesn't contain 'SKU' (column C, current row)