19 Replies Latest reply on Aug 15, 2012 4:47 PM by Trevorׅ

    Edit All Cells In All Tables

    Madhatinstr4

      Hello,

       

      This is my first post, but I have been working with InDesign scripting for a while, and am pretty fluent in Javascript.

       

      My scenario is, I have a document with about 1,000 pages. On each page is a table with approximately 100 cells, 10 rows and 10 columns with a description at the 0-space of each, followed by a floating point number (in the form of a percentage) in every other cell. What I need to do is format the text color of each percentage in each row depending on its value. For instance, in the first row, every percentage under 80.00% is red, while everything else needs to be green. All I need to do is color them based on their value. However, this value changes throughout the course of the 10 different rows.

       

      I was able to make a script that would allow me to go over each cell in row 1 of every table on a single page, then iterate over all the other remaining pages in the same way before continuning onto row 2. This is a VERY long process, typically leading to a crash in any document over ~30 pages in length. Given that I want to run this over a gigantic 1,000+ page document (granted there is only 1 table per page), it becomes slightly impractical...

       

      I'm simply curious if there is a way to run this in a more realistic manner. There has to be a quick way to iterate over each row, simply check the values, mark green or red and be done with it. My idea was to, perhaps, do every row in the table on one page and then go onto the next page, as opposed to row 1 over the entire document, followed by row 2, and so on and so forth? Perhaps that would help?

       

      The code is FARRR too long to post here, but the rough process I have right now is the following:

      1.) Setup variables to define the cells in each row:

           var allTables = app.activeDocument.stories.everyItem().tables.everyItem();

           var firstRowCells = allTables.rows[1].cells.everyItem().getElements();

       

               (There has to be a way to optimize that???)

       

      2.) Call a bunch of for statements for the amount of rows I need to process (10) per table:

       

               for(var i = 0; i < firstRowCells.length; ++i)

               {

                     var a = parseFloat(firstRowCells[i].contents);

                     if(a <= 80.00)

                     {

                           firstRowCells[i].texts[0].appliedCharacterStyle = "FAIL Red";

                     } else if(a > 80.00){

                           firstRowCells[i].texts[0].appliedCharacterStyle = "PASS Green";

                     }

               }

       

       

       

      ...all of this (about 10 for statements) results in a pretty rough crash. Anybody have a solution? Or at least an optimization tip?

       

      Thanks,

      -Chris

        • 1. Re: Edit All Cells In All Tables
          -hans- Level 4

          Hi,

           

          perhaps exporting the stories to tagged text. Do the search and resque may with the help of the shell, ruby whatever is faster then javascript. place the refurbished tagged text file ...

          • 2. Re: Edit All Cells In All Tables
            Madhatinstr4 Level 1

            Hmm.. Would there be a practical way to reimport it into the table, though? I don't know if it helps, but these multi-page documents are all results of a data merge created from an Excel .CSV file, imported into a template with the tables already inserted and character styles applied to the templated text.

            • 3. Re: Edit All Cells In All Tables
              Trevorׅ Adobe Community Professional

              Hello Mad, ehr Chris,

               

              Welcome to the forum

               

              Why don't you just set up table styles with cell styles with paragraph styles with grep styles and character styles?

               

              That would be very stylish

               

              Should be easy to script and quick.

               

              Trevor

              • 4. Re: Edit All Cells In All Tables
                Madhatinstr4 Level 1

                Haha yeah, "Chris" was taken and I wasn't feeling creative...

                 

                Does sound pretty stylish though! However, not to be a pain, but could you explain how I could utilize such styles on a row-by-row basis to color based on the content of the cell (again, an example is in row 1 where everything below 80.00% is red and equal to or over is green)? Not too familiar with grep styles, so I'm not sure if thats what I'm looking for or not, but I thought a parseFloat() function might be necessary?

                 

                Please do correct me if I'm wrong!

                 

                -Chris

                • 5. Re: Edit All Cells In All Tables
                  Trevorׅ Adobe Community Professional

                  Hi Chris,

                   

                  I'm going to sleep now, but if you could show a screen shot of a table or part of it then I'll try have some code in the morning.

                   

                  2:35am in my part of the planet.

                   

                  Trevor

                  • 6. Re: Edit All Cells In All Tables
                    Madhatinstr4 Level 1

                    Not a problem! This is what I'm looking at for data fields. I can post the code I'm currently using now if necessary too, but just is a bit long and repetitive - plus it only works on small batches of these tables haha

                     

                    -Chris

                     

                    Screen Shot 2012-08-14 at 7.37.39 PM.png

                    • 7. Re: Edit All Cells In All Tables
                      Trevorׅ Adobe Community Professional

                      Hi Chris,

                       

                      Oh dear I had to get up for something

                       

                      anyway presuming that you don't specifically need to make script, rather get the job done.

                      This should take about 3 minutes BUT try it out first on a one page doc!!! before.

                       

                      1) set up a character style call it "green" and use your wild imagination to define it's properties i.e. set the color to green.

                      2) set up a paragraph style and call it something like cell. set the text color to red and in the grep style do this!!!!

                      ScreenShot053.png

                      3) Set up another paragraph style call it something like cell2 (Yawn) set the color to black

                      4) Set up a cell style call it cell style 2 and set the paragraph style to cell2

                      5) Set up a cell style call it cell style 1 and set the paragraph style to cell

                      6) Set up a table style call it Table style 1 and do this !!!!

                       

                      ScreenShot052.png

                      Set the left column to cel style 1 and the body rows to cell style 2

                       

                      Voila

                       

                      Now for the scripty bit

                       

                      something very ish like (NOT TESTED AT THIS TIME OF NIGHT)

                      myDoc.tables.everyItem().appliedTableStyles="Table Style 1"

                       

                      Good Luck

                       

                      Trevor

                       

                      P.s. Learn Grep Styles, they can be very powerfull

                      P.P.s. If you wanted all numbers above 80 to be green but not 80 then the grep would be too complicated for me to work out at this time of night. (in the morning should be a doddle)

                      P.P.P.s I got my angelic wings ~ Trevor ~ because I couldn't get Trevor I didn't go mad but might have been already

                      1 person found this helpful
                      • 8. Re: Edit All Cells In All Tables
                        Madhatinstr4 Level 1

                        Okay thanks, Trevor! That definitely helps point me in the right direction! I'll see what I can do with this information and let you know how it goes! Grep styles certainly look a lot like regular expressions haha

                        • 9. Re: Edit All Cells In All Tables
                          Trevorׅ Adobe Community Professional

                          Hi Chris

                           

                          This is a better method.

                           

                          Leave the table style alone just set up the other styles

                          Then apply this code

                          myCells=app.documents[0].stories.everyItem().tables.everyItem().columns[0].cells.everyItem()
                          myCells.appliedCellStyle="Cell Style 1";
                          myCells.cells.everyItem().clearCellStyleOverrides (1)
                          

                          This will just change the 1st column in every table but leave evrything else alone

                           

                          Trevor

                          • 10. Re: Edit All Cells In All Tables
                            Trevorׅ Adobe Community Professional

                            Made a little (big) mistake in the grep I showed in the screenshot it should have had a ^ and been

                            ^(([89]\d\.?\d*%)|100%)

                            ~ Trevor ~ wrote:

                             

                            P.P.s. If you wanted all numbers above 80 to be green but not 80 then the grep would be too complicated for me to work out at this time of night. (in the morning should be a doddle)

                            Wasn't such a doddle but the grep would be

                            ^(80\.?(\d+)*(?=[1-9])\d*\%|8[1-9]\.?\d*?%|(9\d\.?\d*%)|100%)

                             

                             

                            Of course one could just swap the colors and change the grep to

                            ^([1-7]\d\.?d*%|80\.?0*%)

                            • 11. Re: Edit All Cells In All Tables
                              Madhatinstr4 Level 1

                              Okay, I managed to get a single row in a single table to work using the grep styles and cell styles, but because of the particular formatting I need the table styles won't work for me. This is actually for medical forms for a client, and I had to omit a far left column (name, medical record numbers, etc.), so using the "Left Column" option in the table styles isn't an adequate solution. Furthmore, the "Individual Trend" column goes by a different variable that I forgot to mention (if its negative its red, 0.00%+ is green).

                               

                              I managed to use the grep styles in a way that got rid of the need for the resource hogging parseFloat() calls, but with the script now, using these cell and grep styles I can only iterate over one row in one table, as I mentioned.

                               

                              My code now for one row:

                               

                              //define rows

                              var docTables = app.activeDocument.stories.everyItem().tables.everyItem();

                              var firstRowCells = docTables.rows[1].cells.everyItem().getElements();

                               

                              var cStyles = app.activeDocument.characterStyles;

                                                            if((cStyles.item("FAIL Red") != null) && (cStyles.item("PASS Green") != null))

                                                            {

                                                  for(var i = 2; i < 6; ++i)

                                                  {

                                                       firstRowCells[i].appliedCellStyle = "80";

                                                       firstRowCells[6].appliedCellStyle = "Trend";

                                                       firstRowCells[7].appliedCellStyle = "80";

                                                  }

                                                            } else {

                                                     alert("Please setup character styles for PASS Green and FAIL Red, and re-run the program.");

                                                            }

                               

                               

                              I've tried a few various combinations, but I only get one solid result, in that it will only highlight the first row in the first table correctly and not touch any other tables.

                               

                              My current grep for cell style instance "80" is as follows:

                              FAIL RED

                              ([1-7]\d.\d\d%)|([0].\d\d%)|(-\d?\d.\d\d%)

                              PASS Green

                              ([89]\d.\d\d%)|([1]\d\d.\d\d%)

                              FAIL RED

                              80.00%

                              GOALS Black

                              exclusion|N/A

                               

                              Any suggestions to make it iterate over each table, using the same for loop formula on each first row, ignoring the first two columns?

                               

                              Message was edited by: Madhatinstr4

                              • 12. Re: Edit All Cells In All Tables
                                Trevorׅ Adobe Community Professional

                                Can you do a little manual work and fomat 1 table as you want the final results (without the names of with them changed)

                                You can use the ink dropper tool to make it quick.

                                Send another screen shot and I'll be back in a few hours.

                                This will help a lot

                                 

                                Trevor

                                • 13. Re: Edit All Cells In All Tables
                                  Madhatinstr4 Level 1

                                  The goal is to make it as automated as possible, as these 1,000 page documents will be occurring about twice a week (as far as I know).

                                   

                                  Here is what I'm looking at now with six rows programmed for a demo of my situation (figured I'd include a dummy first row for example):

                                   

                                  Screen Shot 2012-08-15 at 1.29.54 PM.png

                                   

                                  The grep style hasn't changed, except varies from line to line to meet the goal criteria, but here is the code for the six rows. Again, all I need it to do is make the same code work in the same way on all tables in the document and we're golden. This seems like the right approach, though, as it is much faster than the parseFloat() call method. (Sorry about the hard to read code...for some reason it doesn't paste well into these forums??)

                                   

                                  var cStyles = app.activeDocument.characterStyles;

                                                                if((cStyles.item("FAIL Red") != null) && (cStyles.item("PASS Green") != null))

                                                                {

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    firstRowCells[i].appliedCellStyle = "80";

                                                                                    firstRowCells[6].appliedCellStyle = "Trend";

                                                                                    firstRowCells[7].appliedCellStyle = "80";

                                                                          }

                                                                          pBar.hit();

                                   

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    secondRowCells[i].appliedCellStyle = "80";

                                                                                    secondRowCells[6].appliedCellStyle = "Trend";

                                                                                    secondRowCells[7].appliedCellStyle = "80";

                                                                          }

                                                                          pBar.hit();

                                   

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    thirdRowCells[i].appliedCellStyle = "80";

                                                                                    thirdRowCells[6].appliedCellStyle = "Trend";

                                                                                    thirdRowCells[7].appliedCellStyle = "80";

                                                                          }

                                                                          pBar.hit();

                                   

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    fourthRowCells[i].appliedCellStyle = "40";

                                                                                    fourthRowCells[6].appliedCellStyle = "Trend";

                                                                                    fourthRowCells[7].appliedCellStyle = "40";

                                                                          }

                                                                          pBar.hit();

                                   

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    fifthRowCells[i].appliedCellStyle = "50";

                                                                                    fifthRowCells[6].appliedCellStyle = "Trend";

                                                                                    fifthRowCells[7].appliedCellStyle = "50";

                                                                          }

                                                                          pBar.hit();

                                   

                                                                          for(var i = 2; i < 6; ++i)

                                                                          {

                                                                                    sixthRowCells[i].appliedCellStyle = "30";

                                                                                    sixthRowCells[6].appliedCellStyle = "Trend";

                                                                                    sixthRowCells[7].appliedCellStyle = "30";

                                                                          }

                                                                          pBar.hit();

                                                                } else {

                                                                          alert("Please setup character styles for PASS Green and FAIL Red, and re-run the program.");

                                                                }

                                   

                                   

                                  Thanks again, Trevor, for all the help!!

                                  -Chris

                                   

                                  Edit: Actually...it didn't paste too too poorly after all once I clicked "Post"...hmmmm...

                                  • 14. Re: Edit All Cells In All Tables
                                    Trevorׅ Adobe Community Professional

                                    (Sorry about the hard to read code...for some reason it doesn't paste well into these forums??)

                                     

                                    ScreenShot054.pngScreenShot055.png

                                    I'll get back later

                                    • 15. Re: Edit All Cells In All Tables
                                      Trevorׅ Adobe Community Professional

                                      This is has a bit more Style

                                       

                                      // by Trevor http://forums.adobe.com/message/4623699#4623699
                                      app.doScript("main()", ScriptLanguage.javascript, undefined, UndoModes.ENTIRE_SCRIPT, "Format Table");
                                      function main()
                                      {
                                      var myCellStyle=["80","80","80","40","50","30"],v=myCellStyle.length,
                                          myTables=app.documents[0].stories.everyItem().tables.everyItem();
                                      while (v--)
                                      {
                                      myTables.rows[v+1].cells.itemByRange(2,5).appliedCellStyle=myCellStyle[v];
                                      myTables.rows[v+1].cells[7].appliedCellStyle=myCellStyle[v];
                                      }
                                      myTables.cells.everyItem().clearCellStyleOverrides (1);
                                      myTables.rows[1].cells[6].appliedCellStyle="Trend";
                                      }
                                      

                                       

                                      Interested to know how long it take to process the 1000 tables

                                       

                                      Trevor

                                      • 16. Re: Edit All Cells In All Tables
                                        Madhatinstr4 Level 1

                                        Woohoo! Modified some to accomodate the intricacies of the job and other features I didn't mention, but overall it worked. Was unaware of the itemByRange() function, that is VERY helpful! Thanks again Trevor!!!

                                        • 17. Re: Edit All Cells In All Tables
                                          Trevorׅ Adobe Community Professional

                                          Pleasure, glad it worked, there's a lot to learn on the forum.

                                           

                                          So just how long did it take to do the 1000 tables?

                                          • 18. Re: Edit All Cells In All Tables
                                            Madhatinstr4 Level 1

                                            Oh, sorry, forgot to mention that! On a 2.7 GHz i7 quad-core, 2012 MacBook Pro Retina with 16gb of ram the files wouldn't even budge with my old script using parseFloat() calls. However, with the new one it iterated over all 1,083 tables in about a minutes and a half! Again, couldn't have done it without you! Thanks!

                                            • 19. Re: Edit All Cells In All Tables
                                              Trevorׅ Adobe Community Professional

                                              Very Nice,

                                               

                                              Just one last word before we meet again on a different post.

                                               

                                              I just looked over your greps

                                               

                                              Take this example and check your Green one for the same mistakes

                                               

                                              FAIL RED

                                              ([1-7]\d.\d\d%)|([0].\d\d%)|(-\d?\d.\d\d%)

                                               

                                              1) This is going to fail on 80.53%


                                              the ([0].\d\d%) is going to "catch" the 0.53% You missed the little (big) mistake that I pointed out above

                                              need to add an ^ at the beginning of each  | or statement or bracket the or statements together and put the ^ (means beginning of line) at the beginning of the line

                                               

                                              2) The or statement in this case don't need to be bracketed in this case, although doesn't harm (might slow things down slightly, not to sure)

                                               

                                              3) The 0 does not need to be put in a [character class]

                                               

                                              4) The . needs escaping \. otherwise it meens any character or space THIS IS AN IMPORTANT RULE



                                              The correct grep in this case would be

                                              ^([1-7]\d\.\d\d%|0\.\d\d%|-\d?\d\.\d\d%)

                                              or better still

                                              ^([1-7]\d\.\d\d|0\.\d\d|-\d?\d\.\d\d)%

                                               

                                              VERY WORTHWHILE READING LEARNING UP PETER KARHELS BOOK ON GREPS

                                               

                                              You can well see from this post how powerful Greps can be.

                                               

                                              Regards

                                               

                                              Trevor