• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Automatization of table cells color tint

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

TOPICS
How to , Scripting

Views

1.3K

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
community guidelines

correct answers 4 Correct answers

Advocate , Jun 01, 2020 Jun 01, 2020

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.fill

...

Votes

Translate

Translate
Advocate , Jun 03, 2020 Jun 03, 2020

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

...

Votes

Translate

Translate
Advocate , Jun 03, 2020 Jun 03, 2020

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";
     

...

Votes

Translate

Translate
Advocate , Jun 06, 2020 Jun 06, 2020

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){
 

...

Votes

Translate

Translate
Community Expert ,
Jun 01, 2020 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...

Votes

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
community guidelines
Advocate ,
Jun 01, 2020 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

Votes

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
community guidelines
Community Beginner ,
Jun 02, 2020 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

Votes

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

Votes

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
community guidelines
Advocate ,
Jun 03, 2020 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

Votes

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
community guidelines
Community Beginner ,
Jun 04, 2020 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. 

Votes

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
community guidelines
Advocate ,
Jun 06, 2020 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

Votes

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
community guidelines
Community Beginner ,
Jun 07, 2020 Jun 07, 2020

Copy link to clipboard

Copied

LATEST

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

Votes

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
community guidelines
Community Beginner ,
Jun 02, 2020 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%

Votes

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
community guidelines
Advocate ,
Jun 03, 2020 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

Votes

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
community guidelines
Community Expert ,
Jun 02, 2020 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. 

Votes

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
community guidelines
Community Beginner ,
Jun 02, 2020 Jun 02, 2020

Copy link to clipboard

Copied

Thank you for clarification, it works now!

Votes

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
community guidelines