Highlighted

Automatization of table cells color tint

Community Beginner ,
Jun 01, 2020

Copy link to clipboard

Copied

This question is addressed in InDesign section as I'm using it for making the final layouts, but it might be that another software can do the task and the resulting file is imported in InDesign.

Is there a script or another way how to generate/automate cell fill color/tint in relation to number inside the cell? As in:

if number is +100, then color #1 tint is 100%

if number is +50, then color #1 tint is 50%

if number is 0, then no fill at all (or color tint is 0%)

if number is -50, then color #2 tint is 50%

if number is -100, then color #2 tint is 100%

 

Screenshot 2020-06-01 at 16.55.01.png

Adobe Community Professional
Correct answer by Sunil Yadav | Adobe Community Professional

You just need to play with the conditions, that is it.

For the selection of cells, you can try this code snippet:

///////////////////////////////////////
try{
    for(var i = 0; i < app.selection[0].cells.length; i++){
        var currentCell = app.selection[0].cells[i];
        if(parseFloat(currentCell.contents) > 0){
            currentCell.fillColor = "Color #1";
            currentCell.fillTint = parseFloat(currentCell.contents);
            }
        else if(parseFloat(currentCell.contents) < 0){
            currentCell.fillColor = "Color #2";
            currentCell.fillTint = parseFloat(currentCell.contents)*-1;
            }
        else if(parseFloat(currentCell.contents) ==0){

            currentCell.fillTint = 0;
            }
        }
    }
catch(e){}
///////////////////////////////////////

 

Best

Sunil

TOPICS
How to, Scripting

Views

763

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

Automatization of table cells color tint

Community Beginner ,
Jun 01, 2020

Copy link to clipboard

Copied

This question is addressed in InDesign section as I'm using it for making the final layouts, but it might be that another software can do the task and the resulting file is imported in InDesign.

Is there a script or another way how to generate/automate cell fill color/tint in relation to number inside the cell? As in:

if number is +100, then color #1 tint is 100%

if number is +50, then color #1 tint is 50%

if number is 0, then no fill at all (or color tint is 0%)

if number is -50, then color #2 tint is 50%

if number is -100, then color #2 tint is 100%

 

Screenshot 2020-06-01 at 16.55.01.png

Adobe Community Professional
Correct answer by Sunil Yadav | Adobe Community Professional

You just need to play with the conditions, that is it.

For the selection of cells, you can try this code snippet:

///////////////////////////////////////
try{
    for(var i = 0; i < app.selection[0].cells.length; i++){
        var currentCell = app.selection[0].cells[i];
        if(parseFloat(currentCell.contents) > 0){
            currentCell.fillColor = "Color #1";
            currentCell.fillTint = parseFloat(currentCell.contents);
            }
        else if(parseFloat(currentCell.contents) < 0){
            currentCell.fillColor = "Color #2";
            currentCell.fillTint = parseFloat(currentCell.contents)*-1;
            }
        else if(parseFloat(currentCell.contents) ==0){

            currentCell.fillTint = 0;
            }
        }
    }
catch(e){}
///////////////////////////////////////

 

Best

Sunil

TOPICS
How to, Scripting

Views

764

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
Jun 01, 2020 0
Adobe Community Professional ,
Jun 01, 2020

Copy link to clipboard

Copied

Conditional formatting is not natively built into ID tables, strangely, but this question has been asked on this forum. It's possible through scripting. Here's one example that you may be able to repurpose for your needs: 

 

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

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...
Jun 01, 2020 0
Adobe Community Professional ,
Jun 01, 2020

Copy link to clipboard

Copied

Try this code sample:

///////////////////////////////////////

var myDoc = app.documents[0];
for(var s = 0; s < myDoc.stories.length; s++){
    for(var t = 0; t < myDoc.stories[s].tables.length; t++){
        for(var c = 0; c < myDoc.stories[s].tables[t].cells.length; c++){
            var currentCell = myDoc.stories[s].tables[t].cells[c];
            try{
                if(parseFloat(currentCell.contents) > 0){
                    currentCell.fillColor = "color #1";
                    currentCell.fillTint = parseFloat(currentCell.contents);
                    }
                else if(parseFloat(currentCell.contents) < 0){
                    currentCell.fillColor = "color #2";
                    currentCell.fillTint = parseFloat(currentCell.contents)*-1;
                    }
                else if(parseFloat(currentCell.contents) == 0){
                    currentCell.fillTint = 0;
                    }
                }
            catch(e){}
            }
        }
    }

///////////////////////////////////////

End results you will get like this:

Result.png

Best

Sunil

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...
Jun 01, 2020 1
Community Beginner ,
Jun 02, 2020

Copy link to clipboard

Copied

I'm not adept with scripts at all, so naturally I run into a problem. Here's the error:

Screenshot 2020-06-02 at 12.24.11.png

I guess there's a problem with color code format? I used HEX:

Screenshot 2020-06-02 at 12.21.56.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...
Jun 02, 2020 0
Community Beginner ,
Jun 02, 2020

Copy link to clipboard

Copied

Ok, it seems that the problem was that the wrong type of quotation marks were used (“ in place of "). There's no error anymore, but selected cells in the table after running the script are not colored, so I'm still doing something wrong.

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...
Jun 02, 2020 0
Adobe Community Professional ,
Jun 03, 2020

Copy link to clipboard

Copied

The above code is not for selected cells, it for all cells of all tables of all stories... SO don't be cofused about why this code isn't working...

For this second case, 100--50--0

For the selection of cells, you can try this code snippet:

///////////////////////////////////////
try{
    for(var i = 0; i < app.selection[0].cells.length; i++){
        var currentCell = app.selection[0].cells[i];
        if((parseFloat(currentCell.contents)-50) > 0){
            currentCell.fillColor = "Color #1";
            currentCell.fillTint = (parseFloat(currentCell.contents)-50)*2;
            }
        else if((parseFloat(currentCell.contents)-50) < 0){
            currentCell.fillColor = "Color #2";
            currentCell.fillTint = (parseFloat(currentCell.contents)-50)*-2;
            }
        else if((parseFloat(currentCell.contents)-50) ==0){

            currentCell.fillTint = 0;
            }
        }
    }
catch(e){}
///////////////////////////////////////

 

Best

Sunil

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...
Jun 03, 2020 1
Community Beginner ,
Jun 04, 2020

Copy link to clipboard

Copied

Awesome, thank you! I know I might get annoying, but I'd really appreciate if there would also be a version of +100 to -100 script which could be run only on the slected 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...
Jun 04, 2020 0
Adobe Community Professional ,
Jun 06, 2020

Copy link to clipboard

Copied

You just need to play with the conditions, that is it.

For the selection of cells, you can try this code snippet:

///////////////////////////////////////
try{
    for(var i = 0; i < app.selection[0].cells.length; i++){
        var currentCell = app.selection[0].cells[i];
        if(parseFloat(currentCell.contents) > 0){
            currentCell.fillColor = "Color #1";
            currentCell.fillTint = parseFloat(currentCell.contents);
            }
        else if(parseFloat(currentCell.contents) < 0){
            currentCell.fillColor = "Color #2";
            currentCell.fillTint = parseFloat(currentCell.contents)*-1;
            }
        else if(parseFloat(currentCell.contents) ==0){

            currentCell.fillTint = 0;
            }
        }
    }
catch(e){}
///////////////////////////////////////

 

Best

Sunil

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...
Jun 06, 2020 1
Community Beginner ,
Jun 07, 2020

Copy link to clipboard

Copied

Works like a charm. Again, thank you so much for providing this. It really helps out.

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...
Jun 07, 2020 0
Community Beginner ,
Jun 02, 2020

Copy link to clipboard

Copied

Thank you so much for this! If it's not too bothersome, could you also provide a code for a slightly different case?

If 100, then color #1 tint is 100%
If 50, then no fill at all

If 0, then color #2 tint is 100%

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...
Jun 02, 2020 0
Adobe Community Professional ,
Jun 03, 2020

Copy link to clipboard

Copied

For this second case, 100--50--0

Try this code sample:

///////////////////////////////////////

var myDoc = app.documents[0];
for(var s = 0; s < myDoc.stories.length; s++){
    for(var t = 0; t < myDoc.stories[s].tables.length; t++){
        for(var c = 0; c < myDoc.stories[s].tables[t].cells.length; c++){
            var currentCell = myDoc.stories[s].tables[t].cells[c];
            try{
                if((parseFloat(currentCell.contents)-50) > 0){
                    currentCell.fillColor = "color #1";
                    currentCell.fillTint = (parseFloat(currentCell.contents)-50)*2;
                    }
                else if((parseFloat(currentCell.contents)-50) < 0){
                    currentCell.fillColor = "color #2";
                    currentCell.fillTint = (parseFloat(currentCell.contents)-50)*-2;
                    }
                else if((parseFloat(currentCell.contents)-50) == 0){
                    currentCell.fillTint = 0;
                    }
                }
            catch(e){}
            }
        }
    }

///////////////////////////////////////

 

There would be a slight change in those conditions...

 

Best

Sunil

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...
Jun 03, 2020 0
Adobe Community Professional ,
Jun 02, 2020

Copy link to clipboard

Copied

You can't use HEX for fillColor. It should be a named color in your Swatches palette. 

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...
Jun 02, 2020 0
Community Beginner ,
Jun 02, 2020

Copy link to clipboard

Copied

Thank you for clarification, it works now!

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...
Jun 02, 2020 0