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]);
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.
Regards
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!
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;
}
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.
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!
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:
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 )
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'.
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 )
Copy link to clipboard
Copied
Yep, that got it working.
Thank you Uwe!
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.
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 )
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?
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:
Regards,
Uwe Laubender
( ACP )
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;
}
}
}
}
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;
}
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.
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.
Copy link to clipboard
Copied
Hi everyone...
Trying to adapt this script so it has 15 or so variables, however it's colouring the whole table one colour 😕
Can you see where it's going wrong?
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);
// Set colour per digit range - original code
if (cellValue > 80) {
cells[c].fillColor = red;
continue;
}
if (cellValue = 81) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 95) {
cells[c].fillColor = green;
continue;
// Set colour per digit range - added code
}
if (cellValue = 1) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 2) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 3) {
cells[c].fillColor = red;
continue;
}
if (cellValue = 4) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 5) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 6) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 7) {
cells[c].fillColor = red;
continue;
}
if (cellValue = 8) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 9) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 10) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 11) {
cells[c].fillColor = red;
continue;
}
if (cellValue = 12) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 13) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue = 14) {
cells[c].fillColor = green;
continue;
}
if (cellValue = 15) {
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;
}
Copy link to clipboard
Copied
Boolean comparisons need to use two equal signs to get information about match, ie if (cellValue == 1). Using one equal sign changes the variable. Also, your first comparison uses a greater than comparison (cellValue > 80), which is skipping over the following two comparisons (81 and 82), even if they had the right syntax.
Copy link to clipboard
Copied
Thanks for the reply.
I added the extra = sign... which in itself made no difference :/, but i have examined the code further.. Whilst it doesnt give me different colours as such, it's now performing sufficiently for my needs...
fresh code 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 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;
continue;
// Set colour per digit range - added code
}
if (cellValue == 1) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue == 2) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue == 3) {
cells[c].fillColor = red;
continue;
}
if (cellValue == 4) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 5) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 6) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue == 7) {
cells[c].fillColor = red;
continue;
}
if (cellValue == 8) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 9) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue == 10) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 11) {
cells[c].fillColor = red;
continue;
}
if (cellValue == 12) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 13) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue == 14) {
cells[c].fillColor = green;
continue;
}
if (cellValue == 15) {
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;
}
Copy link to clipboard
Copied
What color are you expecting for cellValue == 1, red or yellow? Because right now, it is going to red. If you want to handle the 1-15 range as you have it outlined, take out the continue statement from your first cellValue < 80 condition.
Copy link to clipboard
Copied
Hi all
What element should I add if I want all colored cells turn red to also change font color to white for legibility?
app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
sel = app.selection[0];
var cells = sel.cells
// create color swatches
var red = swatchCreator("Red", [210,35,42]);
var yellow = swatchCreator("Amber", [0,51,99,0]);
var green = swatchCreator("Green", [69,8,87,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 < 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;
}