Google-sheets – Clear range of cells

google sheetsgoogle-apps-script

I'm trying to make a script for Google Spreadsheets (which is my first time), by clearing a range of some cells.

So: for example I want to clear D5:D12 but not D13 and D14, but then again I want to clear D15:D20, but not D21, butD22:D30` must be cleared (and it goes on like this all over the sheet).

All I have now is this:

function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menubuttons = [ {name: "Clear Cells", functionName: "clear1"} ];
    ss.addMenu("Functions", menubuttons);
} 

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  sheet.getRange('D5:D12').clearContent(),
  sheet.getRange('D15:D20').clearContent(),
  sheet.getRange('D22:D30').clearContent();
}

Hope you can help me to make this more easier. Because it's a lot of work to specify each range.

btw: all these cells are colored (orange color), is there a way the clear a specific color maybe?

Best Answer

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange. 

function clearOrange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  for (var i=0; i<bgColors.length; i++) {
    for (var j=0; j<bgColors[i].length; j++) {
      if (bgColors[i][j] === '#ff9900') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}

I knew the color code for orange used in Google sheets because I first ran this code with the "if" statement replaced by Logger.log(bgColors[i][j]);

The reason for (i+1,j+1) is that indices in JavaScript are 0-based while row and column numbers are 1-based.

Hypothetically speaking, if you didn't have orange background as the criterion for clearing, you'd have to specify the ranges explicitly; the script has to know what to clear. You could still shorten the script by putting the ranges in an array, as below. 

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var rangesToClear = ['D5:D12', 'D15:D20', 'D22:D30'];
  for (var i=0; i<rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}
Related Topic