Script to change the color of a shape based on numerical value within a table

[Samiul Haque]

Hello, I am creating a series of reports using data merge and I was wondering if someone could help me make it simpler using scripting. I have a table with different data points which shows their 'score' (a numeric value on a scale of 0 to 5), and their 'health' (a circle, colored according to their score: 0-1.5 = Red, 1.51-3.5 = Grey, 3.51-5 = Green). Right now I am using the data merge feature to insert an image of the circle. Can anyone help me developing a script so that I can change the color of the circles automatically?

[brianp311]

Not exactly a trivial task. Lots of ways to approach the problem, and lots of pitfalls depending on how tables are configured. One such approach might be something like: 

var main = function() {
    var allTables = app.activeDocument.stories.everyItem().tables.everyItem().getElements();
    var allCells, aCell, t, c, nc, r, num;
    for (t = 0; t < allTables.length; t++) {
        allCells = allTables[t].cells;
        for (c = 0; c < allCells.length; c++) {
            aCell = allCells[c];
            if (aCell.ovals.length == 1) {
                try { 
                r = aCell.parentRow;
                nc = r.cells.nextItem(aCell);
                if (isNaN(nc.contents)) { continue; }
                num = Number(nc.contents);
                if (num > 3.5) {
                    aCell.ovals[0].fillColor = app.activeDocument.swatches.itemByName("Green");
                }
                //else if, etc

                } catch(e) { continue; }
            }
        }
    }
}
main();

[Samiul Haque]

Thank you for the helpful response! I have never used scripting but I will try to figure it out from your script. If I cannot I will just ask my office to hire someone like you to do this for us lol