Macro with font color

ewok2 · 6 October 2024 17:47

Trying to write a marco on onlyoffice spreadsheet…

I want to be able to test a Cell FillColor (an idealy a FontColor) to compare it with a Ref and if equal do something.

Below a function trying to do it (You should be able to test it on your onlyoffice)
This function will write 3 Cell in Red, Green and No Color (As 3 ref)
Get the FillColor in variable

Then will Write 3 other cell with again color
I can set FillColor or Fontcolor by reding the adjacent cell but I am not able to compare the result with a ref…

My problme is with the test

if (cellToCheck == colorRed)

that does not works…

Thanks for help

(function()
{
const oWorksheet = Api.GetActiveSheet();
const colorRed = Api.CreateColorFromRGB(255, 0, 0)
const colorGreen = Api.CreateColorFromRGB(0, 255, 0)

oWorksheet.GetRangeByNumber(0, 0).SetValue("Red Ref");
oWorksheet.GetRangeByNumber(0, 0).SetFillColor(colorRed);
oWorksheet.GetRangeByNumber(1, 0).SetValue("Green Ref");
oWorksheet.GetRangeByNumber(1, 0).SetFillColor(colorGreen);
oWorksheet.GetRangeByNumber(2, 0).SetValue("No Ref");

oWorksheet.GetRangeByNumber(4, 0).SetValue("Test 1");
oWorksheet.GetRangeByNumber(4, 0).SetFillColor(colorRed);
oWorksheet.GetRangeByNumber(4, 1).SetValue("");
oWorksheet.GetRangeByNumber(4, 2).SetValue("");
oWorksheet.GetRangeByNumber(5, 0).SetValue("Test 2");
oWorksheet.GetRangeByNumber(5, 0).SetFillColor(colorGreen);
oWorksheet.GetRangeByNumber(5, 1).SetValue("");
oWorksheet.GetRangeByNumber(5, 2).SetValue("");
oWorksheet.GetRangeByNumber(6, 0).SetValue("Test 3");
oWorksheet.GetRangeByNumber(6, 1).SetValue("");
oWorksheet.GetRangeByNumber(6, 2).SetValue("");

var ref1 = oWorksheet.GetRangeByNumber(0, 0).GetFillColor();
var ref2 = oWorksheet.GetRangeByNumber(1, 0).GetFillColor();
var col = 0;

for (var ligne = 4; ligne < 7; ligne += 1) {
    var cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    if (cellToCheck !== "No Fill") {
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Check");
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetFontColor(cellToCheck);
    }
    else oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue(cellToCheck);
}

for (ligne = 4; ligne < 7; ligne += 1) {
    cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    if (cellToCheck == colorRed) {
        oWorksheet.GetRangeByNumber(ligne, col + 2).SetValue("Check");
        oWorksheet.GetRangeByNumber(ligne, col + 2).SetFontColor(colorRed);
    }
    else oWorksheet.GetRangeByNumber(ligne, col + 2).SetValue(cellToCheck);
}

})();

Constantine · 7 October 2024 11:38

Hello @ewok2

I’ve changed the comparison block a little and now it returns following result:

(function()
{
const oWorksheet = Api.GetActiveSheet();
const colorRed = Api.CreateColorFromRGB(255, 0, 0)
const colorGreen = Api.CreateColorFromRGB(0, 255, 0)

oWorksheet.GetRangeByNumber(0, 0).SetValue("Red Ref");
oWorksheet.GetRangeByNumber(0, 0).SetFillColor(colorRed);
oWorksheet.GetRangeByNumber(1, 0).SetValue("Green Ref");
oWorksheet.GetRangeByNumber(1, 0).SetFillColor(colorGreen);
oWorksheet.GetRangeByNumber(2, 0).SetValue("No Ref");

oWorksheet.GetRangeByNumber(4, 0).SetValue("Test 1");
oWorksheet.GetRangeByNumber(4, 0).SetFillColor(colorRed);
oWorksheet.GetRangeByNumber(4, 1).SetValue("");
oWorksheet.GetRangeByNumber(4, 2).SetValue("");
oWorksheet.GetRangeByNumber(5, 0).SetValue("Test 2");
oWorksheet.GetRangeByNumber(5, 0).SetFillColor(colorGreen);
oWorksheet.GetRangeByNumber(5, 1).SetValue("");
oWorksheet.GetRangeByNumber(5, 2).SetValue("");
oWorksheet.GetRangeByNumber(6, 0).SetValue("Test 3");
oWorksheet.GetRangeByNumber(6, 1).SetValue("");
oWorksheet.GetRangeByNumber(6, 2).SetValue("");

var ref1 = oWorksheet.GetRangeByNumber(0, 0).GetFillColor();
var ref2 = oWorksheet.GetRangeByNumber(1, 0).GetFillColor();
var col = 0;

for (var ligne = 4; ligne < 7; ligne += 1) {
    var cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    // console.log(cellToCheck);
    if (cellToCheck !== "No Fill") {
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Check");
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetFontColor(cellToCheck);
    }
    else oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Check").SetFontColor(cellToCheck);
}
})();

Second for block was removed. Some reference points were adjusted to follow the logic - getting fill color from column A, adding “Check” word to adjacent cell in column B, applying the same color from fill to inserted word.

Not sure if this is the goal according to your description, but feel free to correct me by providing more details on the desired result.

ewok2 · 7 October 2024 17:40

Sorry I did not see the difference?

It seem’s my question is not clear.

I want to check if the GetFillColor is green or red.

The first “for” works already but it apply the color if cellToCheck !== “No Fill”

I add a second “for” and would like it set color only if cellToCheck is equal to red.

Constantine · 8 October 2024 08:28

It also looks into the same cell array and returns No fill as the first check. Second does not work because GetFillColor() returns internal code for RGB and not exact one. You can try running check with variable ref1 for red, for instance, like that:

for (ligne = 4; ligne < 7; ligne += 1) {
    cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    if (cellToCheck = ref1) {
        oWorksheet.GetRangeByNumber(ligne, col + 2).SetValue("Check");
        oWorksheet.GetRangeByNumber(ligne, col + 2).SetFontColor(colorRed);
    }
    else oWorksheet.GetRangeByNumber(ligne, col + 2).SetValue(ref1);
}

It’d result in placing “Check” in red in all three adjacent cells in the column C. Is that what you are looking for?

ewok2 · 8 October 2024 19:09

Not sure to understand your answer

I had made a more simple test below
The expected behaviour is that “Red” is written in the red cell and “Not Red” is written in the green and white cell

But nothing is written…
Is there au “log” somewhere when something is wrong?

(function()
{
const oWorksheet = Api.GetActiveSheet();

oWorksheet.GetRangeByNumber(0, 0).SetValue("Ref");
oWorksheet.GetRangeByNumber(0, 0).SetFillColor(Api.CreateColorFromRGB(255, 0, 0));

oWorksheet.GetRangeByNumber(4, 0).SetFillColor(Api.CreateColorFromRGB(255, 0, 0));
oWorksheet.GetRangeByNumber(5, 0).SetFillColor(Api.CreateColorFromRGB(0, 255, 0));
oWorksheet.GetRangeByNumber(4, 0).SetValue("");
oWorksheet.GetRangeByNumber(5, 0).SetValue("");
oWorksheet.GetRangeByNumber(6, 0).SetValue("");

var refRed = oWorksheet.GetRangeByNumber(0, 0).GetFillColor();
var col = 0;

for (ligne = 4; ligne < 7; ligne += 1) {
    var cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    if (cellToCheck == refRed) {
        oWorksheet.GetRangeByNumber(ligne, col).SetValue("Red");
        oWorksheet.GetRangeByNumber(ligne, col).SetFillColor(colorRed);
    }
    else {
        oWorksheet.GetRangeByNumber(ligne, col).SetValue("Not Red");
    }
}

})();

Constantine · 8 October 2024 21:50

Issue comes from the fact that GetFillColor() returns an object instead of string which is generally an internal code for RGB. Even though it is possible to use GetGRB() on what GetFillColor()returns to get real RGB code, the problem is that it does not work with unfilled cells and returnsTypeError`, hence you cannot rely on this approach in cases where search area contains unfilled cells.

Solution here is not quite standard - to parse all RGB colors into JSON and run if and else if blocks comparing the data, for instance, like that:

(function()
{
const oWorksheet = Api.GetActiveSheet();
var cRed = Api.CreateColorFromRGB(255, 0, 0);
var cGreen = Api.CreateColorFromRGB(0, 255, 0);
oWorksheet.GetRangeByNumber(0, 0).SetValue("Ref");
oWorksheet.GetRangeByNumber(0, 0).SetFillColor(cRed);

oWorksheet.GetRangeByNumber(4, 0).SetFillColor(cRed);
oWorksheet.GetRangeByNumber(5, 0).SetFillColor(cGreen);
oWorksheet.GetRangeByNumber(4, 0).SetValue("Red");
oWorksheet.GetRangeByNumber(5, 0).SetValue("Green");
oWorksheet.GetRangeByNumber(6, 0).SetValue("No Fill");
oWorksheet.GetRangeByNumber(7, 0).SetValue("Red");
oWorksheet.GetRangeByNumber(7, 0).SetFillColor(cRed);

var refRed = oWorksheet.GetRangeByNumber(0, 0).GetFillColor();
var JSONRed = JSON.stringify(refRed);
var col = 0;

for (var ligne = 4; ligne <= 7; ligne++) {
    var cellToCheck = oWorksheet.GetRangeByNumber(ligne, col).GetFillColor();
    var colorsArr = JSON.stringify(cellToCheck);

    if (colorsArr == "\"No Fill\"") {
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Not Filled");
    }
    else if (colorsArr == JSONRed) {
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Red");
    }
    else {
        oWorksheet.GetRangeByNumber(ligne, col + 1).SetValue("Not Red");
    }
}
})();

Generally, I am stringifying objects that GetFillColor() returns, then parsing cellToCheck as JSON to get RGB data from each cell and then writing down results into adjacent cells according to the logic:

If cell has no fill, then insert “Not Filled” into next cell;
If cell has red fill (parsed as JSON from refRed variable), then insert “Red” into next cell;
Everything else is simply “Not red”.

Made some tweaks here and there to make sure that it follows the logic even if some cells also have red fill. That it way it works.

ewok2 · 9 October 2024 18:32

Wonderful
Json works but I prefer tio use .GetRGB() wich is more simple

Thanks a lot

Constantine · 9 October 2024 19:45

As I mentioned this method returns numeric value, but when obtaining range by number you may also get a cell with no fill color - trying to use GetRGB() on such cell will return TypeError and further execution of macro will fail.

I am glad to help.

ewok2 · 9 October 2024 19:52

Yes I perform the test if (cellToCheck !== “No Fill”) befor use it

Constantine · 9 October 2024 20:08

All right, I guess this topic can be closed as solved then. PM me to re-open it, if you have any questions left.