Highlighted

Script to search tables in a document and apply cell style based on Column Title

New Here ,
Mar 23, 2020

Copy link to clipboard

Copied

Hello,

I'm trying to get a script for applying cell styles based on content. Since I'm really new to scripting I can't get this to work as I want to. I have a script that will find a number and change the cell style based on the number, but I need to use a second formula for finding the cell to apply the cell style to, based on the Column Title.

 

So for example I have four columns that are "ranks," and those would just have red, yellow, or green backgrounds applied (which I change with a cell style)(e.g. 0-49.99, 50.00-69.99, 70.00-100). But then I have four columns that are "grades," and they have more variety in the colors (red, orange, yellow, green, blue; e.g. 0-24.99, 25.0-44.99, 45.0-59.99, 60.0-79.99, 80.0-100).

 

The following script does what I want for the first situation, but it then I can't run the second script because it will change the colors I already changed using the first script. Is there a way to search by column title and apply the formula, so I don't have to go through and manually select each text frame to apply the second forumula? Does that make sense?

 

Can anyone help me with this?

 

var myDoc = app.activeDocument ;
var myFrame = app.selection[0];

if(app.selection <1){

alert ("Please select a table or a frame containing a table.");

}
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[6-7]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Yellow";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[8]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Green";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[9]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Blue";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};

 

Screen Shot 2020-03-23 at 10.11.41 AM.png

Topics

Scripting

Views

626

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

Script to search tables in a document and apply cell style based on Column Title

New Here ,
Mar 23, 2020

Copy link to clipboard

Copied

Hello,

I'm trying to get a script for applying cell styles based on content. Since I'm really new to scripting I can't get this to work as I want to. I have a script that will find a number and change the cell style based on the number, but I need to use a second formula for finding the cell to apply the cell style to, based on the Column Title.

 

So for example I have four columns that are "ranks," and those would just have red, yellow, or green backgrounds applied (which I change with a cell style)(e.g. 0-49.99, 50.00-69.99, 70.00-100). But then I have four columns that are "grades," and they have more variety in the colors (red, orange, yellow, green, blue; e.g. 0-24.99, 25.0-44.99, 45.0-59.99, 60.0-79.99, 80.0-100).

 

The following script does what I want for the first situation, but it then I can't run the second script because it will change the colors I already changed using the first script. Is there a way to search by column title and apply the formula, so I don't have to go through and manually select each text frame to apply the second forumula? Does that make sense?

 

Can anyone help me with this?

 

var myDoc = app.activeDocument ;
var myFrame = app.selection[0];

if(app.selection <1){

alert ("Please select a table or a frame containing a table.");

}
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[6-7]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Yellow";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[8]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Green";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;

app.findGrepPreferences.findWhat = "^[9]\\d+(\\.\\d+)?";

var myFound = myFrame.findGrep();


for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Blue";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};

 

Screen Shot 2020-03-23 at 10.11.41 AM.png

Topics

Scripting

Views

627

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
Most Valuable Participant ,
Mar 24, 2020

Copy link to clipboard

Copied

Basedd on your current code, it's easiest to grab the text of the top cell in the column that you are testing. Below your current lines

 

if( myFound[i].parent.constructor.name == "Cell" )

 

add this further test:

if (myFound[i].parent.columns[0].cells[0].texts[0].contents.match(/^GRADE/))

 

and then the next line will only be run if the text matches.

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 ,
Mar 24, 2020

Copy link to clipboard

Copied

Thank you for the reply. I really have no idea what I'm doing so I literally just pasted that line with no other characters directly below the first line you reference.

 

if( myFound[i].parent.constructor.name == "Cell" )
if (myFound[i].parent.columns[0].cells[0].texts[0].contents.match(/^GRADE/))

 

Is that syntax correct? Because it seems to have no effect when I run the script. I apologize; I know I am in way over my head here.

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 ,
Mar 25, 2020

Copy link to clipboard

Copied

Hi,

 

As per the idea that Jongware gave, your code would be something like the following

for (var i = 0; i < myFound.length; i++) {
    if (myFound[i].parent.constructor.name == "Cell") 
	{
		if (myFound[i].parent.columns[0].cells[0].texts[0].contents.match(/^GRADE/))
			myFound[i].parent.appliedCellStyle = "Grade Yellow";
		else if(myFound[i].parent.columns[0].cells[0].texts[0].contents.match(/^RANK/))
			myFound[i].parent.appliedCellStyle = "Rank Yellow";
        var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
    }
}

So the idea is that when you have found the content, you check what is the text in the top cell of the column where that text was found, based on that you can choose which which cell style you want to apply. Here i used a sample Rank Yellow cell style to demonstrate the line where you can add which style you want to use in case the text is found under Rank column.

 

-Manan

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 ,
Mar 27, 2020

Copy link to clipboard

Copied

This is fabulous, thank you. But again, seems to have no effect when I run the script in InDesign.

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 ,
Mar 27, 2020

Copy link to clipboard

Copied

i will gladly pay someone to help with this...i really need to get this script working!!!

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...
Advocate ,
Mar 27, 2020

Copy link to clipboard

Copied

If you are glad to pay someone …

 

In this case! 😉

 

 

// Place the cursor in the cell of a table
var myCells = app.selection[0].parent.parent.cells.everyItem().getElements(),  
C = myCells.length,  c;

for ( c = 0; c < C; c++ ) {
    if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/^RANK/) && myCells[c].contents != "" ) {
        if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) < 50 ) myCells[c].appliedCellStyle = "Rank Red";
        if ( Number(myCells[c].contents) >= 50 && Number(myCells[c].contents) < 70 ) myCells[c].appliedCellStyle = "Rank Yellow";
        if ( Number(myCells[c].contents) >= 70 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Rank Green";
    }
    if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/^GRADE/) && myCells[c].contents != "" ) {
        if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) < 25 ) myCells[c].appliedCellStyle = "Grade Red";
        if ( Number(myCells[c].contents) >= 25 && Number(myCells[c].contents) < 45 ) myCells[c].appliedCellStyle = "Grade Orange";
        if ( Number(myCells[c].contents) >= 45 && Number(myCells[c].contents) <= 60 ) myCells[c].appliedCellStyle = "Grade Yellow";
        if ( Number(myCells[c].contents) >= 60 && Number(myCells[c].contents) <= 80 ) myCells[c].appliedCellStyle = "Grade Green";
        if ( Number(myCells[c].contents) >= 80 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Grade Blue";
    }
}

 

 

Capture d’écran 2020-03-28 à 04.34.01.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...
Most Valuable Participant ,
Mar 28, 2020

Copy link to clipboard

Copied

You will have to help us help you. Try to determine what specific part fails. Insert 'alert ('made it up to here: line xx')' statements throughout the script so you know where it stalls. Or save just that one table in a separate InDesign document, post it on a public server, and post the link here so we can locally test why our efforts don't work with that specific table.

 

As an example, I (and therefore Manan) assumed that the texts "GRADE" and "RANK" are actually typed in full caps. Maybe they are not? (The full caps would then be a text attribute.)

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 ,
Mar 28, 2020

Copy link to clipboard

Copied

YOU GUYS ARE GENIUSES! FRIdNGE the script is perfect and _Jongware_ you were exactly right. It LOOKED like they were all caps but it was a text attribute. THANK YOU, THANK YOU, THANK YOU!!!

 

The only thing I still have to figure out is how to apply it to all the tables in the document.

How do I pay for services rendered?? 🙂

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...
Advocate ,
Mar 28, 2020

Copy link to clipboard

Copied

Change the two first lines of my script by:

 

 

// All the tables in the active document! …
var myCells = app.activeDocument.stories.everyItem().tables.everyItem().cells.everyItem().getElements(),  

 

 

Personally, not interested by your money!
I propose you a donation to an hospital fighting against covid-19 in your town! Take care of 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...
New Here ,
Mar 29, 2020

Copy link to clipboard

Copied

Awesome, thank you. I will definitely donate to the local hospital.

 

HOwever, I do get an error now with the script: Screen Shot 2020-03-29 at 3.20.49 PM.png

 

which I assume is obviously because I need to change line 8 to match something you changed in line 2, but so far I have not guessed or gleaned what.

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...
Advocate ,
Mar 29, 2020

Copy link to clipboard

Copied

Unable to reproduce your error! …

 

Could you share a small ID doc (saved as .idml)?

 

 

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 ,
Mar 30, 2020

Copy link to clipboard

Copied

It was operator error of course.

 

I wonder if I could trouble you for one last thing. Say instead of a number I want to find a team name and apply a cell style. How would I edit that second line? For example, if I wanted to search for "COLORADO AVALANCHE"? TIA!

 

if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/TEAM NAME/) && myCells[c].contents != "" ) {
if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Team Cell";
}

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...
Advocate ,
Mar 30, 2020

Copy link to clipboard

Copied

This could be enough relevant:

 

if ( myCells[c].texts[0].contents.match(/COLORADO AVALANCHE/) ) myCells[c].appliedCellStyle = "Team Cell";

 

… and thanks for your donation! [I appreciate!]  😉

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 ,
Apr 20, 2020

Copy link to clipboard

Copied

You guys are total rockstars. 

 

I am trying to add one more function now, to only search for numbers that have a decimal point. Any chance either of you could help me out with that? TIA!

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 ,
Apr 21, 2020

Copy link to clipboard

Copied

Try this if statement. It finds a match for any digit followed by a decimal followed by any digit. Sorry for no formatting (on mobile).

 

if ( myCells[c].texts[0].contents.match(/\d\.\d/) ) {
    myCells[c].appliedCellStyle = "Decimal Style";
}

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 ,
Apr 21, 2020

Copy link to clipboard

Copied

Thank you! That worked perfectly.

 

I thought I was all set but I have one last riddle to solve. I have several columns of numbers that need to remain with just a gray background. Using the decimal search allowed me to differentiate those numbers from the ones with the various color backgrounds. But there is a final column that has different conditional formatting... a larger range of colors to be applied to numbers. On this particular table, because it is a data merge and I don't want to repeat the headers over and over on the page, I have eliminated the Table Header, and now I no longer have a way to differentiate that column. I can apply a cell style or a paragraph style to the content in that cell, but I don't know how to both search for a cell or paragraph style and then still search for the range of numbers to apply the various colors. Does that make sense?

 

So I have

******

// All the tables in the active document! …
var myCells = app.activeDocument.stories.everyItem().tables.everyItem().cells.everyItem().getElements(),
C = myCells.length, c;


for ( c = 0; c < C; c++ ) {

if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/\d\.\d/) && myCells[c].contents != "" ) {
if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) < 59.99 ) myCells[c].appliedCellStyle = "Grade Red";
if ( Number(myCells[c].contents) >= 60 && Number(myCells[c].contents) < 69.99 ) myCells[c].appliedCellStyle = "Grade Orange";
if ( Number(myCells[c].contents) >= 70 && Number(myCells[c].contents) < 79.99 ) myCells[c].appliedCellStyle = "Grade Yellow";
if ( Number(myCells[c].contents) >= 80 && Number(myCells[c].contents) < 89.99 ) myCells[c].appliedCellStyle = "Grade Green";
if ( Number(myCells[c].contents) >= 90 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Grade Blue";
}

}
******

but for that first search

if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/\d\.\d/) && myCells[c].contents != "" ){

 

I need it to also either search for Cell Style "Points Gray" or Paragraph Style "Points" but I can't figure out how to do that.

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 ,
Apr 21, 2020

Copy link to clipboard

Copied

I will cough up another donation to a suggested charity if you guys can help me out with this one last task!

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