0

Thanks to this answer by Shyam Kansagra, I have modified the following code to check a Google Sheets cell background color and change a different cell's value based on that color.

I am close, but currently getRange is only checking column 6. I want it to check columns 6, 7, 8, 9, 10, and if any cell in that row is red, write "Red" in column 11. When getting the BG color, I thought bg = sheet1.getRange(i+2, 6, 10, 5).getBackground(); would check all 5 columns, but currently it is only checking column 6.

Here is the full code:

function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Compliance Main");

  var data1 = sheet1.getDataRange().getValues();  //2D array of sheet1 cell values
  var bg;

  for(var i=0; i<data1.length; i++)
  {
    bg = sheet1.getRange(i+2, 6, 6, 5).getBackground();
    if(bg == "#ff0000")  //Add more colors in else if and else if
    {
      sheet1.getRange(i+2, 11).setValue("Red");  //Set value in corresponding row
    } else if(bg == "#ffff00")
    {
      sheet1.getRange(i+2, 11).setValue("Yellow");
    } else if(bg == "#ffffff")
    {
      sheet1.getRange(i+2, 11).setValue("White");
    }
  }
}

For more clarity, Here is a screenshot: of the erroneous sheet - as you can see, I want it to reference the whole group of columns, but it is only referencing column 6.

I apologize if these edits are rough, I am a beginner with js. Thank you for any help you may provide!

Rubén
  • 34,714
  • 9
  • 70
  • 166
Ryan
  • 1
  • 1
  • Thank you @I'-'I. I've tried `bg = sheet1.getRange(i+2, 6, 6, 5).getBackgrounds();` and now it does not return any results. I'm looking over the docu and am having issues relating it to how I've set this up. – Ryan Feb 25 '18 at 21:58
  • 1
    This only returns a value for the upper leftmost cell in a range. bg = sheet1.getRange(i+2, 6, 6, 5).getBackground(); You must use getBackgrounds() instead. It returns a 2d array of background colors for each cell in a range (much like 'getValues()' returns an array of values). – Anton Dementiev Feb 25 '18 at 22:25
  • So in your line 2, column K, what is the desired output? Red, White, Yellow, Red, White? Also see http://googlescriptreference.com/category/spreadsheet-range-getbackgrounds/ – Jeremy Kahan Feb 28 '18 at 05:29

0 Answers0