Highlighted

Is it possible to write a script in InDesign to do conditional formatting in table cells?

Community Beginner ,
Aug 14, 2019

Copy link to clipboard

Copied

I have lots of data heavy excel documents that use conditional formatting to automatically colour a cell depending on the value of its contents.

IE- Green for 0-50, white for 51-150 Red for over 150.

I am copy this data into a design doc in InDesign. I would like to use the same conditional formatting in Indesign so I don't have to colour all cells by hand. I have been told that this is not a native feature in InDesign but it could be possible to write a script for this. Could anyone help with this?

I have a digital team that could do this for me but we need some more information. Has anyone done this before?

Thanks!

Hi,

you can try this one:

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;

}

Select the textframe which contains the table(s) and run that script. It will create color swatches Green, Red, White. Then it changes the color of the cells.

conditional cell formatting.png

Regards

Topics

Scripting

Views

899

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Is it possible to write a script in InDesign to do conditional formatting in table cells?

Community Beginner ,
Aug 14, 2019

Copy link to clipboard

Copied

I have lots of data heavy excel documents that use conditional formatting to automatically colour a cell depending on the value of its contents.

IE- Green for 0-50, white for 51-150 Red for over 150.

I am copy this data into a design doc in InDesign. I would like to use the same conditional formatting in Indesign so I don't have to colour all cells by hand. I have been told that this is not a native feature in InDesign but it could be possible to write a script for this. Could anyone help with this?

I have a digital team that could do this for me but we need some more information. Has anyone done this before?

Thanks!

Hi,

you can try this one:

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;

}

Select the textframe which contains the table(s) and run that script. It will create color swatches Green, Red, White. Then it changes the color of the cells.

conditional cell formatting.png

Regards

Topics

Scripting

Views

900

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Participant ,
Aug 14, 2019

Copy link to clipboard

Copied

Hi,

you can try this one:

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;

}

Select the textframe which contains the table(s) and run that script. It will create color swatches Green, Red, White. Then it changes the color of the cells.

conditional cell formatting.png

Regards

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Community Beginner ,
Aug 19, 2019

Copy link to clipboard

Copied

Thanks so much for this.

Our digital team are looking at implementing it now, so you've been a huge help!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

Hi, Thanks for sharing the script but unfortunately I got these two results even after minor tweaks in the code like, initially i have assigned fill color with combination of letters and numbers but later I have changed swatch name to blue, red and green but still problem persists. Please help me in resolving following errors.

 

 1.  Error Number: 25

Error String: Expected: ;

Engine: main

File path

Line

Source: Cell.fillColor = PROCESS 2748

Offending Text: 2748

 

 2.  Error Number: 21

Error String: Undefined is not an object

Engine: main

File path

Line

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

 

Following is the script I used to run in indesign

 

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("Blue", [100,88,0,14]);

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

var white = swatchCreator("Green", [76,0,38,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 <= 0.29) {

cells.fillColor = Blue;

continue;

}

if (cellValue >= 0.30 && cellValue <=0.69) {

cells.fillColor = Red;

continue;

}

if (cellValue > 1.00) {

cells.fillColor = Green;

}

}

}

}

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;

}

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Most Valuable Participant ,
Nov 07, 2019

Copy link to clipboard

Copied

You did not carefully copy the original script. There is no variable named "Red", nor is there one called "Green". But you try to use them anyway.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
New Here ,
Nov 08, 2019

Copy link to clipboard

Copied

Well actually if you look at the origianl script provided by crazy panda, you can see that there is name defined for color value and I did same by creating according to my requirement. 

 

Lets do one thing, just make me understand whether the script provided by crazy panda has any flaw or else it works fine. If not I hope you understand my requirement so could you please provide script for the same which I'm looking for.

 

Above all these could you please suggest me how to learn Java scripting which is required for this particular tool. I mean in simple way to suffice the purpose. I know its not easy, it has some prerequisite to learn the script, but still guide me in learning script. Thank you!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 12, 2019

Copy link to clipboard

Copied

Does this script still work in ID2020?  I keep getting the following error message when run on a Mac OSX 10.14.6:

Screen Shot 2019-11-12 at 22.06.04.png

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 12, 2019

Copy link to clipboard

Copied

Hi Colin,

what exactly did you select when running the script?

Hm. What's your exact code? Can you post it with code formatting of the forum?

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 14, 2019

Copy link to clipboard

Copied

Hi Uwe.

 

I made a new InDesign document and made a single textframe with a single 4x4 table with exactly the same numbers as per crazyPanda's script. The text frame was then selected with the black arrow tool and the script was run. Here is everything that is in the script below:

 

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;

}

 In ESTK, the script doesn't like the line:

 

var cells = tables.cells;

 

reports the error message "Object does not support the property or method 'cells'.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 14, 2019

Copy link to clipboard

Copied

Hi Colin,

ah. That's just a typo I think.

Since you are iterating over the tables collection with variable i as counter correct this line to:

 

var cells = tables[i].cells;

 

And also in the next loop, the loop that is iterating the cells collection with variable c as counter, you should address a single cell with its index [c]:

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

And of course also:

cells[c].fillColor = green;

 

cells[c].fillColor = white;

 

cells[c].fillColor = red;

 

Best,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 15, 2019

Copy link to clipboard

Copied

Yep, that got it working.

 

Thank you Uwe!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
New Here ,
Aug 03, 2020

Copy link to clipboard

Copied

Any chance you could explain how to check for text instead? So if the cell contains an "A" the cell color would change to red.

 

Appreciate any help.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Nov 18, 2019

Copy link to clipboard

Copied

Hi Colin,

glad it's working now.

Ahem. That said, out of the same reasons crazyPanda's code in the reply, that is currently marked as "Correct", will not work.

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
New Here ,
Feb 26, 2020

Copy link to clipboard

Copied

Hi, and thanks for sharing the script.

 

I try to use this script, I get no errors but it dosn't work on my computer...

 

Here is my script:

 

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 svert_bra = swatchCreator("svert_bra", [83,18,78,4]);
var veldig_bra = swatchCreator("veldig_bra", [53,2,99,0]);
var bra = swatchCreator("bra", [9,0,92,0]);
var mindre_bra = swatchCreator ("mindre_bra", [1,51,93,0]);
var svakt = swatchCreator ("svakt", [0,91,78,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 >=5) {
cells.fillColor = svert_bra;
continue;
}

if (cellValue >= 4 && cellValue <=6) {
cells.fillColor = veldig_bra;
continue;
}

if (cellValue >= 3 && cellValue <=5) {
cells.fillColor = bra;
continue;
}

if (cellValue >= 2 && cellValue <= 4) {
cells.fillColor = mindre_bra;
continue;
}

if (cellValue >= 0 && cellValue <= 3) {
cells.fillColor = svakt;
}
}
}
}
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;
}

 

 

What is wrong with this one?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Feb 26, 2020

Copy link to clipboard

Copied

Hi Thomas,

the code posted here cannot work. First I thought there were some typos, but now I know:

The code was damaged by transfering this thread from the old to the new Adobe Forum software last year.

 

You have to edit the code to fix it according to my post here:

https://community.adobe.com/t5/indesign/is-it-possible-to-write-a-script-in-indesign-to-do-condition...

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
New Here ,
Feb 26, 2020

Copy link to clipboard

Copied

I try to edit the code, but I still got no error-message and the script doesn't work either.

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 >=5) {
cells[c].fillColor = svert_bra;
continue;
}

if (cellValue >= 4 && cellValue <=6) {
cells[c].fillColor = veldig_bra;
continue;
}

if (cellValue >= 3 && cellValue <=5) {
cells[c].fillColor = bra;
continue;
}

if (cellValue >= 2 && cellValue <= 4) {
cells[c].fillColor = mindre_bra;
continue;
}

if (cellValue >= 0 && cellValue <= 3) {
cells[c].fillColor = svakt;
}
}
}
}

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Community Beginner ,
Aug 03, 2020

Copy link to clipboard

Copied

Hi there

Your support is amazing. Been trying to do all you mentioned before but keep getting error 21 "undefined is not an object" can you point to the right direction?

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 red = swatchCreator("Red", [15,100,100,0]);
    var yellow = swatchCreator("Amber", [0,51,99,0]);
    var green = swatchCreator("Green", [69,8,87,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 cellValue = parseInt(cells[c].texts[0].contents);       
            if (cellValue < 80) {
                cells[c].fillColor = red;
                continue;
            }
            if (cellValue >= 80 && cellValue < 95) {
                cells[c].fillColor = yellow;
                continue;
            }
            if (cellValue >= 95) {
                cells[c].fillColor = green;
            }
        }
    }
}
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;

}

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Adobe Community Professional ,
Aug 03, 2020

Copy link to clipboard

Copied

What line are you getting the error on? It should say in the error message, and tell you the line of code that has the undefined value. 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Explorer ,
Aug 03, 2020

Copy link to clipboard

Copied

It sounds silly but i would like to ask you one question, have you selected table while running script. If so please share screenshot with exact issue. Will help you out in resolving issue. I beleive that without selecting table you are running script.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...