Conditional coloring in inDesign

[oscare61346481]

Hi there

Im trying to apply this script found in another post but am unable to make it run.

Any extra info on how to change the number range into percentages (my cells have 80% instead of 80 for instance) would be much appreciated!

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {

    sel = app.selection[0];

    if (sel.constructor.name != "TextFrame") {

        alert("Please select the textframe that contains the table.");

        exit();

    }

    // create color swatches

    var green = swatchCreator("Green", [75,0,100,0]);

    var red = swatchCreator("Red", [0,100,100,0]);

    var white = swatchCreator("White", [0,0,0,0]);

    tables = sel.tables;

    for (var i = 0; i < tables.length; i++) {

        var cells = tables.cells;

        

        for (var c = 0; c < cells.length; c++) {

            

            var cellValue = parseInt(cells.texts[0].contents);

           

            if (cellValue <= 50) {

                cells.fillColor = green;

                continue;

            }

        

            if (cellValue >= 51 && cellValue <=150) {

                cells.fillColor = white;

                continue;

            }

        

            if (cellValue > 150) {

                cells.fillColor = red;

            }

        }

    }

}

function swatchCreator(colorname, values) {

    var swatch = app.activeDocument.colors.item(colorname);

    

    if (!swatch.isValid) {

        swatch = app.activeDocument.colors.add({

            name: colorname,

            model: ColorModel.PROCESS,

            space: ColorSpace.CMYK,

            colorValue: values

            });

    }

    return swatch;

}

[Manan Joshi]

Change the following line with the code snippet that follows

//Change the following line
var cellValue = parseInt(cells.texts[0].contents);

//Replace it with the following
var cc = cells.texts[0].contents.split("%")
var cellValue = parseInt(cc[0])
isNaN(cellValue) && cellValue = 0

-Manan

[oscare61346481]

Thanks for the suggestion. Unfortunately it still shows an error in line 1.

[rob day]

If you are running the script from ExtendScript Toolkit make sure you set InDesign as the target from the dropdown next to the link symbol.

Also in your for loop you are leaving out the i and c variables. So the loop should be this:

var cells = tables[i].cells; 

not var cells = tables.cells;

cells[c].texts[0].fillColor = green; 

not cells.fillColor = green;

    for (var i = 0; i < tables.length; i++) {
        var cells = tables[i].cells;
        for (var c = 0; c < cells.length; c++) {
            
            var cellValue = parseInt(cells[c].texts[0].contents);

            if (cellValue <= 50) {
                //$.writeln("Hey" + cellValue)
                cells[c].texts[0].fillColor = green;
                continue;
            }

            if (cellValue >= 51 && cellValue <=150) {
                cells[c].texts[0].fillColor = white;
                continue;
            }
        
            if (cellValue > 150) {
                cells[c].texts[0].fillColor = red;
            }
        }
    }

[Manan Joshi]

What is the error? I hope you are running the script by selecting the textframe containing the table, this is a requirement of the script. If it still does not work then send me a small test indd document, with the final code that you are executing, I will debug it and see what's the issue

-Manan

[oscare61346481]

Hi again.

I can't even reach the part where it prompts me to select the textframe.

Test file: https://we.tl/t-98Dgz45LXO

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {

    sel = app.selection[0];

    if (sel.constructor.name != "TextFrame") {

        alert("Please select the textframe that contains the table.");

        exit();

    }

    // create color swatches

    var green = swatchCreator("Green", [75,0,100,0]);

    var red = swatchCreator("Red", [0,100,100,0]);

    var white = swatchCreator("White", [0,0,0,0]);

    tables = sel.tables;

    for (var i = 0; i < tables.length; i++) {

        var cells = tables.cells;

        

        for (var c = 0; c < cells.length; c++) {

            

            var cc = cells.texts[0].contents.split("%")
            var cellValue = parseInt(cc[0])
            isNaN(cellValue) && cellValue = 0   

           

            if (cellValue <= 50) {

                cells.fillColor = green;

                continue;

            }

        

            if (cellValue >= 51 && cellValue <=150) {

                cells.fillColor = white;

                continue;

            }

        

            if (cellValue > 150) {

                cells.fillColor = red;

            }

        }

    }

}

function swatchCreator(colorname, values) {

    var swatch = app.activeDocument.colors.item(colorname);

    

    if (!swatch.isValid) {

        swatch = app.activeDocument.colors.add({

            name: colorname,

            model: ColorModel.PROCESS,

            space: ColorSpace.CMYK,

            colorValue: values

            });

    }

    return swatch;

}

[Manan Joshi]

I notice that Rob gave a very pertinent suggestion. the forum software broke a lot of code on old threads during migration, so such issues cropped up. Try the suggestions he made and then report back if it still does not work

Also please let us know the original thread from where you picked this code so that we can fix the code there as well.

-Manan

[rob day]

Also if you want the cell to be filled with the color rather than the text it would be this:

cells[c].fillColor = red;

This changes the text fill color:

cells[c].texts[0].fillColor = red;

[oscare61346481]

Thanks Rob

It appears to be working!

Any idea how to be more surgical and only run the script on highlighted columns?

[Manan Joshi · Accepted solution]

For the sake of completion, following is the code that works fine for me

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {
    sel = app.selection[0];
    if (sel.constructor.name != "TextFrame") {
        alert("Please select the textframe that contains the table.");
        exit();
    }
    // create color swatches
    var green = swatchCreator("Green", [75,0,100,0]);
    var red = swatchCreator("Red", [0,100,100,0]);
    var white = swatchCreator("White", [0,0,0,0]);
    tables = sel.tables;
    for (var i = 0; i < tables.length; i++) {
        var cells = tables[i].cells;
        for (var c = 0; c < cells.length; c++) {
            var cc = cells[c].texts[0].contents.split("%")
            var cellValue = parseInt(cc[0])
            isNaN(cellValue) && cellValue = 0
            if (cellValue <= 50) {
                cells[c].fillColor = green;
                continue;
            }
            if (cellValue >= 51 && cellValue <=150) {
                cells[c].fillColor = white;
                continue;
            }       
            if (cellValue > 150) {
                cells[c].fillColor = red;
            }
        }
    }
}

function swatchCreator(colorname, values) {
    var swatch = app.activeDocument.colors.item(colorname);
    if (!swatch.isValid) {
        swatch = app.activeDocument.colors.add({
           name: colorname,
           model: ColorModel.PROCESS,
           space: ColorSpace.CMYK,
           colorValue: values
            });
    }
    return swatch;
}

-Manan

[oscare61346481]

Thanks Manan

It appears to be working!

Any idea how to be more surgical and only run the script on highlighted/selected columns?

[Manan Joshi]

What do you mean by the highlighted column? Does it mean the columns selected by the user?

-Manan

[oscare61346481]

yes

[Manan Joshi]

For running the code only on selected columns, change the main function to the following

function main() {
    sel = app.selection[0];
	var cells = sel.cells
    // create color swatches
    var green = swatchCreator("Green", [75,0,100,0]);
    var red = swatchCreator("Red", [0,100,100,0]);
    var white = swatchCreator("White", [0,0,0,0]);
	for (var c = 0; c < cells.length; c++) {
		var cc = cells[c].texts[0].contents.split("%")
		var cellValue = parseInt(cc[0])
		isNaN(cellValue) && cellValue = 0
		if (cellValue <= 50) {
			cells[c].fillColor = green;
			continue;
		}
		if (cellValue >= 51 && cellValue <=150) {
			cells[c].fillColor = white;
			continue;
		}       
		if (cellValue > 150) {
			cells[c].fillColor = red;
		}
	}
}

-Manan 

[Manan Joshi]

You can use the following version of main method to run it with selection of textframe, cells, rows, or columns.

function main() {
    sel = app.selection[0];
	if (sel.constructor.name != "TextFrame" && sel.constructor.name != "Cell") {
        alert("Please select the textframe that contains the table, or a column, cell or row of a table");
        exit();
    }
    if(sel.constructor.name == "TextFrame")
       var cells = sel.tables.everyItem().cells.everyItem().getElements()
    else
       var cells = sel.cells
    // create color swatches
    var green = swatchCreator("Green", [75,0,100,0]);
    var red = swatchCreator("Red", [0,100,100,0]);
    var white = swatchCreator("White", [0,0,0,0]);
	for (var c = 0; c < cells.length; c++) {
		var cc = cells[c].texts[0].contents.split("%")
		var cellValue = parseInt(cc[0])
		isNaN(cellValue) && cellValue = 0
		if (cellValue <= 50) {
			cells[c].fillColor = green;
			continue;
		}
		if (cellValue >= 51 && cellValue <=150) {
			cells[c].fillColor = white;
			continue;
		}       
		if (cellValue > 150) {
			cells[c].fillColor = red;
		}
	}
}

-Manan

[SychevKA]

hi Manan,
I am getting error on this line :

sel = app.selection[0];

because my selection has nothing.
I check the selection to avoid errors:

    if(typeof app.selection != "undefined" && app.selection.length != 0){ 
        // code
    }

and I would add a check that the selection is a table.

in short the code looks like this:

function main() {
    var cells = ""
    if(typeof app.selection != "undefined" && app.selection.length != 0){ 
        if (app.selection[0].constructor.name == "TextFrame") { 
            if (app.selection[0].tables.length != 0) { cells = app.selection[0].tables }
        }
        if (app.selection[0].constructor.name == "Cell" || app.selection[0].constructor.name == "Table") { cells = app.selection }
    }
    if(cells == ""){alert("Please select the textframe that contains the table, or a column, cell or row of a table");exit()}

    // create color swatches
    var green = swatchCreator("Green", [75,0,100,0]);
    var red = swatchCreator("Red", [0,100,100,0]);
    var white = swatchCreator("White", [0,0,0,0]);
    for (var i = 0; i < cells.length; i++) {
        var cells = cells[i].cells;
        for (var c = 0; c < cells.length; c++) {
            var cc = cells[c].texts[0].contents.split("%")
            var cellValue = parseInt(cc[0])
            isNaN(cellValue) && cellValue = 0
            if (cellValue <= 50) {
                cells[c].fillColor = green;
                continue;
            }
            if (cellValue >= 51 && cellValue <=150) {
                cells[c].fillColor = white;
                continue;
            }       
            if (cellValue > 150) {
                cells[c].fillColor = red;
            }
        }
    }
}

function swatchCreator(colorname, values) {
    var swatch = app.activeDocument.colors.item(colorname);
    if (!swatch.isValid) {
        swatch = app.activeDocument.colors.add({
           name: colorname,
           model: ColorModel.PROCESS,
           space: ColorSpace.CMYK,
           colorValue: values
            });
    }
    return swatch;
}

app.doScript("main()", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting")

[Manan Joshi]

If there is no document open then the statement app.selection does cause a crash, so if you check if there is indeed a document open before checking the selection then it should avoid this issue. Also, app.selection is never undefined, it's an array of length 0 when there is not selection.

Did you try the snippet I shared, it should work for all the selection types like textframe, table, cells, column, rows.

-Manan

[SychevKA]

I saw once that app.selection is undefined, I don't know how or why, but I saw it with my eyes )))

Yes, your code works, but when a textframe without tables, the user doesn't get the alert.