How do you work with the Color Class array?

[Stefan Cook]

In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.

I have previously used `getFontColors()` and it worked, but it got deprecated so trying to update my script. I can read in the font colors using `getFontColorObjects()`, I can display them in the execution log (`outRngFontColors.asRgbColor().asHexString()` ), but I don't know how to change the values in the `Color[][]` array (ex. `#000000` to `#ff00ff`). So how do I do that? I only want to read the font colors once and then write them back out only once.

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();<br> var outColRange = sheet.getRange(1, 1, 3);

var outRngFontColors = outColRange.getFontColorObjects();

I did go into the developers.google.com link for these classes (Range, Color, RgbColor) before asking this question.

[cbmserv...@gmail.com]

To change the font color, you should use the set method here:

 

sesetFontColors(colors)

Range

Sets a rectangular grid of font colors (must match dimensions of this range).

 

Or if changing font color of only one cell, use this:

 

setFontColor(color)

Range

Sets the font color in CSS notation (such as '#ffffff' or 'white').

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e1e07a4e-0019-4244-b0a1-33071075e1den%40googlegroups.com.

[Stefan Cook]

Yes I agree, I am currently using the 'getFontColors' and 'setFontColors', BUT the 'getFontColors' is now deprecated so I was trying to get the replacement 'getFontColorObjects' working.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/jRBKQuRYWkM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/003801d98360%249d438fd0%24d7caaf70%24%40gmail.com.

--

Stefan Cook
IS WAREHOUSE TEST AUTO ANALYST

(POS / RTM Automation)

Costco Wholesale Corp
POS DEV - Bldg 6
21930 SE 51st St
Issaquah, WA 98029
425.416.2703 (Office)
Stefa...@Costco.com (Email)

[cbmserv...@gmail.com]

Yeah they did deprecate the getFontColors method. Usage of the getFontColorObjects is more complex but provides a lot more options and supports the theme colors.

 

Perhaps you need to explain what you are trying to do.

 

First of all a Range is either one cell (means you provide just 2 parameters (Row and Column) or it is multiple cells where you need to provide 4 parameters (row, column, num of rows, and num of columns).  In your example you are only giving 3 parameters, so you will need to correct that.

 

The getFontColorObjects method returns a 2 dimensional array of color objects. Each item in the 2 dimensional array is a color object and you need to use the methods on it to figure out what color it is.

 

How do you plan on using the colors it returns? Please explain so we can help you further.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5k%2B-x1%3D5u0QMju9mmjhyB1eCfoT_WO86vEOiyg2PDhAxA%40mail.gmail.com.

[Stefan Cook]

Ultimately, what I am trying to do is:

-Read in range of cells and get the font colors (such as A1:C3) --using 'getFontColorObjects'  (that is no issue).

-I then want to change some of the font colors (in essence, change a few cells font color)  (the issue).

-Read in another range of cells to output the changed font colors (such as A11:C13)   --using 'getFontColorObjects'  (that is no issue).

-Finally write the changed font colors array to the second range   --using 'setFontColorObjects'  (that is no issue, but that also depends on what the font change entails).

When I implement the changes to my production program, it will be reading a range of 30,000 cells so I only want to read once (actually twice since there is a destination range), then write once.

As for the Range piece that needs correcting, I am doing the getRange(row, col, numOfRows).  This is what I currently use in my production program.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/004f01d98368%24e88618c0%24b9924a40%24%40gmail.com.

[CBMServices Web]

Ok I am with you so far. So what issue are you having?

Perhaps show the code that manipulates the color object and what error you are getting would be helpful as well.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5mjN32swEArT%2B08vvWDM0JQcYaOGu%2BgSHJNiQSRWw2n%2Bw%40mail.gmail.com.

[cbmserv...@gmail.com]

I put together a quick example of how you can get the font colors and write them back to a new range.

 

Perhaps this may help you:

 

function changeColor() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var range = sheet.getRange("B2:D4");

 

var results = range.getFontColorObjects();

var colors = [];var row = [];

for (var i in results) {

  for (var j in results[i]) {

    row.push(results[i][j].asRgbColor().asHexString());

  }

  colors.push(row);

  row = [];

}

sheet.getRange("B5:D7").setFontColors(colors);

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5mjN32swEArT%2B08vvWDM0JQcYaOGu%2BgSHJNiQSRWw2n%2Bw%40mail.gmail.com.

[Stefan Cook]

OK, so I see that you are just moving the string values from the Color array to a generic 2D array then using the setFontColor to "write" it.  That will work as my mind was hard set on using the setFontColorObjects.

Plus, I guess I was "educationally" looking to see how to manipulate the Color array itself to then use the setFontColorObjects.  I was in the mindset of "they deprecated getFontColors, eventually they may deprecate the setFontColors" and trying to get ahead of the curve.

I couldn't show any code tried as I did not even know HOW or WHAT to use.

So I am good with your solution.  Thanks.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/005e01d98376%247f9a0480%247ece0d80%24%40gmail.com.

[cbmserv...@gmail.com]

You are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5m5rFLri0z%3Drx3JHW5b5sWQgGhePqGkHbobybcUCgKW-Q%40mail.gmail.com.