Need help troubleshooting script to apply cell styles to tables

[ashleew44226948]

I have a document where certain tables need to have certain cell styles applied according to the values contained within each cell. Here's an example of the final output:

(Effectively, all numbers from 00.00-69.99 will have one style, and numbers from 70.00-89.99 will have another style. I'm selecting for the former with the GREP ^[2-6]\d+(\.\d+)? and the latter with ^[7-8]\d+(\.?\d+)?)

My current approach is using Grep styles within the paragraph style for the cells to color code the values themselves, making it easier for me to spot and change them individually. This is still a pretty tedious approach, though, especially since there are several pages of tables where I'd need to do this.

This seemed like the kind of thing that could be automated via scripting, so I did some digging and I found a script for a similar solution here: How to apply a table cell style based on grep search?​, which I modified to suit a grep-based search approach rather than a text-based one. My modified code is below:

var myDoc = app.activeDocument
app.findGrepPreferences = app.changeGrepPreferences = null
app.findGrepPreferences.findWhat = "^[2-6]\d+(\.\d+)?"
var myFound = myDoc.findGrep()
for(i=0; i<myFound.length; i++)
{
    if(myFound.parent.constructor.name == "Cell")
    {
   myFound.parent.appliedCellStyle = "Heatmap Poor"
      var overrides = myFound.clearOverrides()          //this is the new line added in this content
    }
}

The problem? Running the script doesn't give me any errors, but it also doesn't do anything. And I don't know JS well enough to really troubleshoot why that's the case. Also, I'm not sure how to make it find the two separate conditions that must be searched for within the same script.

I figured more experienced eyes than mine could point out what I'm doing wrong. I appreciate your help!

[Laubender]

Hi,

maybe the problem is assigning the cell style by its name? And not using the cell style itself.

You can define your cell style this way, if it is not stored in a style group:

var myDoc = app.activeDocument ;
var cellStyleHeatMapPoor = myDoc.cellStyles.itemByName( "Heatmap Poor" ) ;
/* more of your code */
myFound.parent.appliedCellStyle = cellStyleHeatMapPoor ;

Very important: End every statement with a ;

Hm. Is your cell style part of a style group?
Then you cannot assign it by its name only.

Regards,
Uwe

[ashleew44226948]

No joy; the script doesn't produce errors, but it has no effect either. In the console all it says is "result:undefined."

I searched through the forums and the net at large to find some other scripts I could try and tweak, but I wasn't able to get those working either. I'm sure what I want is technically possible. I think the main part where things are failing is the Grep: it seems like InDesign isn't actually searching for anything before proceeding through the rest of the script.

[Laubender]

… No joy; the script doesn't produce errors, but it has no effect either. In the console all it says is "result:undefined."…

That's no flaw.

For example run the following line in the ESTK:

var myDoc = app.documents[0];

You'll see  result: undefined  if there is a document open.

Nothing unusual here.

… I think the main part where things are failing is the Grep …

Then see if your GREP is finding anything with myFound.length .

If the length is 0 you know that your GREP finds nothing.

Ah. Inspect your GREP statement:

"^[2-6]\d+(\.\d+)?"

You'll have to escape the escapes:

"^[2-6]\\d+(\\.\\d+)?"

To test that just do a simple:

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

And now inspect the GREP input field of the user interface in InDesign.

After that do:

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

and again inspect the GREP input field of the UI.

See the difference?

Regards,

Uwe

[ashleew44226948]

Perfect, that did the trick! So I take it slashes are one of those characters JS doesn't recognize unless they're escaped, even within quotes?

Here's the updated code. I modified it to only work on the selected table or frame, and added an alert to let the user know this. Is there a way to make the code stop executing after the error is detected? Currently it throws error #21 after showing the alert (because myFrame isn't defined)

// JavaScript Document
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 = "^[2-6]\\d+(\\.\\d+)?";
var myFound = myFrame.findGrep();
var cellStyleHeatMapPoor = myDoc.cellStyles.itemByName( "Heatmap Poor" ) ;
for(i=0; i<myFound.length; i++)
{
    if(myFound.parent.constructor.name == "Cell")
    {
        myFound.parent.appliedCellStyle = "Heatmap Poor";
        var overrides = myFound.clearOverrides()//this is the new line added in this content
    }
}
app.findGrepPreferences = app.changeGrepPreferences = null;
app.findGrepPreferences.findWhat = "^[7-8]\\d+(\\.?\\d+)?";
var myFound = myFrame.findGrep();
var cellStyleHeatMapPoor = myDoc.cellStyles.itemByName( "Heatmap Mediocre" ) ;
for(i=0; i<myFound.length; i++)
{
    if(myFound.parent.constructor.name == "Cell")
    {
        myFound.parent.appliedCellStyle = "Heatmap Mediocre";
        var overrides = myFound.clearOverrides()//this is the new line added in this content
    }
}

[Stefan Rakete]

Hi,

first I would create cellStyle with itemByName not with a string name.

Secondly use clearCellStyleOverrides:

if (curCellStyleName != "" && isValidCellStyle) {

              var newCellStyle = curDoc.cellStyles.itemByName(curCellStyleName);

              curCell.appliedCellStyle = newCellStyle;

              curCell.clearCellStyleOverrides(true);

}

Thanks Stefan

[ashleew44226948]

Hi Stefan! Would this replace the if(myFound)... section of the script? And do I need to define curCellStyleName, ValidCellStyle, etc?

[Benreyn]

Hi ashleew,

Below is a simple script that I made (i'm not the best scripting, but it works).

With you document open in InDesign, run the script and it will parse through the entire document finding text frames that are tables, then it pushes (and parses to integer) each individual cell's content into an array, then cycles through that array while still being on the active table and determines if said value of said cell needs to have a cell style (colored background) applied by the range of the value of that cell's content (hope that made sense).

Additionally, the beginning of the script was to programmatically make a couple cell styles. You remove that bit of code and just use cell styles already setup in the InDesign document by inserted the name where indicated below in the script.

___________________________________

var doc = app.activeDocument; //Current active document in InDesign

// Cell Style Creation, makes a cell fill color to an already created color swatch

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

    var cellStyle = doc.cellStyles.add();

          cellStyle.name = "CellStyle"+(c+1);

          cellStyle.fillColor = doc.colors.item("CellColor"+(c+1));

}

// Loop through document and find tables and apply cell styles as needed

for(i = 0; i < doc.textFrames.length; i++) {

    if(doc.textFrames.item(i).tables[0].isValid) {

        var tempCellData = [];

        for(j = 0; j < doc.textFrames.item(i).tables[0].cells.length; j++) {

            tempCellData.push(parseInt(doc.textFrames.item(i).tables[0].cells.contents));

            for(k = 0; k < tempCellData.length; k++) {

                if(tempCellData <= 69.99) {

                    doc.textFrames.item(i).tables[0].cells.appliedCellStyle = "CellStyle1"; //Green Color Cell Style

                } else if(tempCellData >= 70 && tempCellData <= 89.99) {

                    doc.textFrames.item(i).tables[0].cells.appliedCellStyle = "CellStyle2"; //Yellow Color Cell Style

                }

            }

            tempCellData = []; // Reset array to nothing for next iteration/loop

        }

    }

}