2

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?

  • Hi (Hoi) Joris, please mark the answer as best answered. This not only gives credit to 1999, but also indicates to future visitors the best (and only) answer. Take the tour to learn more about the site: welcome on Web Applications !! – Jacob Jan Tuinstra Jul 6 '15 at 18:32
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.