How to get background color of all cells in a range and set background to a different range of the same size using apps script?

Question

I am trying to copy the background color from a range of cells and paste it to a different range. In my case, my desired color is the range between rows 3 to 53 and every other column from E to BC. In A1 format, it would be (['E3:E53','G3:G53','I3:I53', ... 'BA3:BA53','BC3:BC53']). I want to get the background color of this range and then paste it to my target range, being between rows 3 to 53 and every other column from D to BB. This range in A1 notation would be (['D3:D53','F3:F53','H3:H53' ... 'AZ3:AZ53','BB3:BB53']).

In other words, I want every cell in my target range to be the same color as the adjacent cell to its right.

This is what I currently have.

This is my desired outcome using apps script.

I know I can change color manually, but the values I have in my sheet change frequently and the color of the cells I want to copy is based on conditional formatting rules, meaning I would have to manually change all the cell colors on a regular basis. That is why I want to use apps script, so all I have to do is run a function and it will create my desired effect for me.

I am very new to the world of coding, but this is what I have tried.

setColor(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coach");
  var targetArea = ss.getRangeList(['D3:D53','F3:F53','H3:H53','J3:J53','L3:L53','N3:N53','P3:P53','R3:R53','T3:T53','V3:V53','X3:X53','Z3:Z53',
                               'AB3:AB53','AD3:AD53','AF3:AF53','AH3:AH53','AJ3:AJ53','AL3:AL53','AN3:AN53','AP3:AP53','AR3:AR53','AT3:AT53','AV3:AV53','AX3:AX53','AZ3:AZ53',
                               'BB3:BB53']);
  var desiredColor = ss.getRangeList(['E3:E53','G3:G53','I3:I53','K3:K53','M3:M53','O3:O53','Q3:Q53','S3:S53','U3:U53','W3:W53','Y3:Y53',
                               'AA3:AA53','AC3:AC53','AE3:AE53','AG3:AG53','AI3:AI53','AK3:AK53','AM3:AM53','AO3:AO53','AQ3:AQ53','AS3:AS53','AU3:AU53','AW3:AW53','AY3:AY53',
                               'BA3:BA53','BC3:BC53']);
  var background = desiredColor.getBackgrounds;
  
  targetArea.setBackgrounds(background)
}
  

Iv'e ran the code, but it tells me that "targetArea.setBackgrounds is not a function". If I remove the "s" from .setBackground, it does not do anything at all. No error or anything.

Any help would be greatly appreciated! Thank you!

Answer 1

The setBackground() method on the RangeList object allows you to only provide one color. Instead, you want to be calling setBackgrounds() (or setBackgroundObjects()) on the individual ranges. You can get those by calling getRanges() and then iterating through them to apply whatever changes you want.

function setColor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coach");
  var targetRangeList = ss.getRangeList(['D3:D53','F3:F53','H3:H53','J3:J53','L3:L53','N3:N53','P3:P53','R3:R53','T3:T53','V3:V53','X3:X53','Z3:Z53',
                               'AB3:AB53','AD3:AD53','AF3:AF53','AH3:AH53','AJ3:AJ53','AL3:AL53','AN3:AN53','AP3:AP53','AR3:AR53','AT3:AT53','AV3:AV53','AX3:AX53','AZ3:AZ53',
                               'BB3:BB53']);
  var sourceRangeList = ss.getRangeList(['E3:E53','G3:G53','I3:I53','K3:K53','M3:M53','O3:O53','Q3:Q53','S3:S53','U3:U53','W3:W53','Y3:Y53',
                               'AA3:AA53','AC3:AC53','AE3:AE53','AG3:AG53','AI3:AI53','AK3:AK53','AM3:AM53','AO3:AO53','AQ3:AQ53','AS3:AS53','AU3:AU53','AW3:AW53','AY3:AY53',
                               'BA3:BA53','BC3:BC53']);
  
  var sourceRanges = sourceRangeList.getRanges();
  var targetRanges = targetRangeList.getRanges();
  for (var i = 0; i < sourceRanges.length; i++) {
    targetRanges[i].setBackgrounds(sourceRanges[i].getBackgrounds());
  }
}

By the way, you can probably get those ranges in a more programmatic way since the target ranges seems to be odd-numbered columns while the source ranges are even-numbered and they're all of the same height.

Answer 2

Issues:

• The line var background = desiredColor.getBackgrounds; does not actually execute getBackgrounds() because you forgot the parenthesis.
• However, even if you didn't forget it, both getBackgrounds() and setBackgrounds() are methods of a range object, not a rangeList object as you use in your approach.

---

Solution:

Since the ranges are non-Contiguous you can't just use getRange(). Instead you can create a list of ranges and iterate over them to get the desired result.

function setColor(){
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Coach");
  const targetArea = ['D3:D53','F3:F53','H3:H53','J3:J53','L3:L53','N3:N53','P3:P53','R3:R53','T3:T53','V3:V53','X3:X53','Z3:Z53',
                               'AB3:AB53','AD3:AD53','AF3:AF53','AH3:AH53','AJ3:AJ53','AL3:AL53','AN3:AN53','AP3:AP53','AR3:AR53','AT3:AT53','AV3:AV53','AX3:AX53','AZ3:AZ53',
                               'BB3:BB53'];
                               
  const desiredColor = ['E3:E53','G3:G53','I3:I53','K3:K53','M3:M53','O3:O53','Q3:Q53','S3:S53','U3:U53','W3:W53','Y3:Y53',
                               'AA3:AA53','AC3:AC53','AE3:AE53','AG3:AG53','AI3:AI53','AK3:AK53','AM3:AM53','AO3:AO53','AQ3:AQ53','AS3:AS53','AU3:AU53','AW3:AW53','AY3:AY53',
                               'BA3:BA53','BC3:BC53'];
                               
  
  const targetArea_Ranges = targetArea.map(ta=>ss.getRange(ta))
  const desiredColor_Ranges = desiredColor.map(dr=>ss.getRange(dr))
  
  targetArea_Ranges.forEach((tr,index)=>{
  tr.setBackground(desiredColor_Ranges[index].getBackground())
  });

}

Answer 3

The previous answers are right. You're calling methods that are not available on class Rangelist. In this answer, I would like to add a alternative approach, based on the fact that all your ranges are contiguous and follow a 1 to 1 pattern:

• get all of the ranges in 1 swing: D3:BC53
• getBackgrounds and copy paste colors in memory in array
• setBackgrounds the modified array.

function setColors() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Coach');
  const allArea = sh.getRange('D3:BC53');
  const backgrounds = allArea.getBackgrounds();
  for (const row of backgrounds) {
    for (let i = 0; i < row.length - 1; row[i++] = row[i++]);
  }
  allArea.setBackgrounds(backgrounds);
}