Copy link to clipboard
Copied
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;
}
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,1
...
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Thanks for the suggestion. Unfortunately it still shows an error in line 1.
Copy link to clipboard
Copied
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;
}
}
}
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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;
}
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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;
Copy link to clipboard
Copied
Thanks Rob
It appears to be working!
Any idea how to be more surgical and only run the script on highlighted columns?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Thanks Manan
It appears to be working!
Any idea how to be more surgical and only run the script on highlighted/selected columns?
Copy link to clipboard
Copied
What do you mean by the highlighted column? Does it mean the columns selected by the user?
-Manan
Copy link to clipboard
Copied
yes
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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")
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.