clearing contents of specific range of cells by color codes

Question

Updated with a new approach: Clearing contents of a specific range of cells by color codes. I feel its almost there but im missing something where its clearing contents of all the range but not specifically the color codes i have list in those specified range of cells.

This is what I have been trying to work with.

  var sheet = SpreadsheetApp.getActive().getSheetByName('HSRD');
  var range = sheet.getRange("HSRD!B5:B8");
  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] === '#F7B16B','#B6D7A8','#C9DBF8') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}
    ;

Try using one sheet to make a table that describes all of the options for each location and then write a script that loops through that table carrying out your instructions. You will most likely make a lot of changes during this activity. If you run into problems that you can't figure out then return to this site for help with those problems. — Cooper, Jun 17 '19 at 00:32
[Looping through sheets in Google Spreadsheet...](https://stackoverflow.com/questions/49283560/) has a good example of how to loop through sheets in a spreadsheet, and [Google Apps Script - Looping through range of cells looking for values](https://stackoverflow.com/questions/32396813/) has some code for iterating through a range. How do they compare to your code? — Tedinoz, Jun 17 '19 at 01:03
Possible duplicate of [Google Apps Script - Looping through range of cells looking for values](https://stackoverflow.com/questions/32396813/google-apps-script-looping-through-range-of-cells-looking-for-values) — Tedinoz, Jun 17 '19 at 01:04
I have tried using macros which will clear contents of the cells I provide in the Macro but it clears all values in the cells and wont leave some values that I want to stay. I have also tried modifying a remove row script that I thought would be be perfect and change it to clear contents of the cell instead but it wasn't working ill post it. — Patrick DeNatale, Jun 17 '19 at 15:23

score 0 · Accepted Answer · answered Jun 19 '19 at 07:38

There are 2 problems with your code to achieve what you want:

The if condition it’s wrong, it’ll always return true, because you’re using “,” to join conditions and this will return the last element (which is a string and will be taken as “true”). To read more about the comma operator read [1]. Instead you have to join your conditions with a “||” operator. Another way would be using an array (if it’s easier for you), this would be the condition:

['#F7B16B', '#B6D7A8', '#C9DBF8'].includes(bgColors[i][j])

You’re using the clearContent() function to clear the contents, but you also need to use the clearFormat() function if you wish to clear the format. [2]

function myFunction() {
    var sheet = SpreadsheetApp.getActive().getSheetByName('HSRD');
    var range = sheet.getRange("HSRD!B5:B8");
    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] === '#F7B16B' || bgColors[i][j] === '#B6D7A8 || bgColors[i][j] === '#C9DBF8) {
        range.getCell(i+1,j+1).clearContent().clearFormat();
      }
    }
  }
}

Also, be sure you’re using the right color codes, you can check this using Logger.log(bgColors) to see in the App script log (View->Logs) all the color codes you have in that range.

[1] https://javascriptweblog.wordpress.com/2011/04/04/the-javascript-comma-operator/

[2] https://developers.google.com/apps-script/reference/spreadsheet/range#clearFormat()

clearing contents of specific range of cells by color codes

1 Answers1