0

I wrote this seemingly simple script to copy the background colors from one column to another.

When I run the script, I get no error messages, but nothing seems to happen.

This is what I type into a cell on the sheet:

=copyColor("B:B", "A:A")

I read a post on the Google Apps Script forum that implied that this type of procedure isn't possible, but I am determined to write a script that pulls it off.

Here is what the post said:

"As it is clearly explained in the documentation, Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. That is of course also true for other methods such as setBackground etc."

This is why I tried to get around the problem, by NOT USING the setBackgrounds() function.

Is there another way? Or is there a way I can fix mine to make it work?

function copyColor(rangeToCopy, rangeToPaste)
{
  //an array to store the first background colors
  var firstColors = [];
  //an array to store the second background colors
  var secondColors = [];

  //this will assign the first range into a variable
  var firstRange = SpreadsheetApp.getActiveSheet().getRange(rangeToCopy);
  //this will store the colors of the range into the firstColors array
  firstColors = firstRange.getBackgrounds();

  //this will assign the second range to a variable
  var secondRange = SpreadsheetApp.getActiveSheet().getRange(rangeToPaste);
  //this will store the colors of the range into the secondColors array
  secondColors = secondRange.getBackgrounds();

  //compare the two color arrays. if they do not match, apply the first array to the second array
  if (firstColors != secondColors)
  {
    secondColors = firstColors
  }

}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Nikki Luzader
  • 3
  • 2
  • By B:B you want to refer to which range ? – Umair Mohammad Mar 21 '18 at 13:44
  • `secondColors = secondRange.getBackgrounds()` sets `secondColors` equal to whatever is in the rvalue. Then, `secondColors = firstColors` sets `secondColors` = to `firstColors`. It does **not** update whatever secondColors was initially equal to. – tehhowch Mar 21 '18 at 13:45
  • this is not the same question as the suggested duplicate post. That post is asking _Why can't I call the test function from the spreadsheet cell?_ That is a completely different question than mine, although it does touch on mine a little. @|'-'| – Nikki Luzader Mar 21 '18 at 13:57
  • @Umair B:B refers to B1-Bx (however long the colums is, it covers the entire length of the colum; i.e. if B has 30 rows, its the same as saying B1:B30) – Nikki Luzader Mar 21 '18 at 14:01
  • 1
    Your question asks "how can i do this with this method" and the answer is "you cannot do it with that method", which is the same as the linked question. The solution to "how can i do this" is to - as expressed on the other question - is to run the function from a menu, script, trigger, or image. You **cannot** use custom functions for this, because the authorization level of the custom function is lower than that of other methods of running scripts. – tehhowch Mar 21 '18 at 14:03
  • @tehhowch thanks for clearing that up. – Nikki Luzader Mar 21 '18 at 14:20

0 Answers0