Clear cell contents based on color in Google Sheets-CodePudding

I need to write a script that if a cell color is red, it will be deleted. I have found the answer for excel but not google sheets.

CodePudding user response：

Here is a function for that. You might need to tweak it to suit your needs.

function deleteCellsDependingOnColor() {
    var colorToDelete = '#ff0000';
    var gs = SpreadsheetApp.getActive();
    var sn = gs.getSheetByName('Sheet1');
    var data = sn.getDataRange().getValues();
    for (var r = 0; r < data.length; r  ) {
        for (var c = 0; c < data[0].length; c  ) {
            var cellBgColor = sn.getRange(r   1, c   1).getBackground();
            if (cellBgColor === colorToDelete) {
                sn.getRange(r   1, c   1).deleteCells(SpreadsheetApp.Dimension.ROWS);
                //for clearing values only use
                //sn.getRange(r 1,c 1).clear();
            }
        }
    }
}

use method deleteCells() only if you actually want to delete cells, you want to delete values and background color only, use clear() method

CodePudding user response：

Here is solution that uses the best bractices:

function myFunction() {
  var color = '#ff0000';
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();               // get all data
  var backgrounds = range.getBackgrounds();   // get all backgrounds

  // check the backgrounds and change the data
  for (let row in data) for (let col in data[0]) {
    if (backgrounds[row][col] == color) data[row][col] = '';
  }
  
  range.setValues(data);  // set the data back on the sheet
}

It grabs all data on the sheet as a 2D array, changes the array, and put the modified array back on the sheet all at once.