Clear range of cells

Question

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?

Answer 1

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

Answer 2

  function clear1() {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName('Blad1');
    sheet.getRange('D5:D12').clear({contentsOnly: true, skipFilteredRows: false});
    sheet.getRange('D15:D20').clear({contentsOnly: true, skipFilteredRows: false});
    sheet.getRange('D22:D30').clear({contentsOnly: true, skipFilteredRows: false});
  }

respectively

function clear1() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Blad1');
  var cRanges = ['D5:D12', 'D15:D20', 'D22:D30'];   

    for (var i=0; i<cRanges.length; i++) { 
      sheet.getRange(cRanges[i]).clear({formatOnly: true, contentsOnly: true, skipFilteredRows: false});
    }

};

should work as well