10 Replies Latest reply on Apr 24, 2017 3:58 AM by Too_Many_Words

    Script to automate every other row

    pelicanadv

      I am doing a large pricing catalog. I am linking my customer's Excel files to my InDesign CS4 tables. Every part of the table is formatted with cell styles. The first row has a .25 pt. rule below and the 2nd row has a 1 pt rule and it continues like this throughout the entire table. Some of the text is flush left, some center. I'd like to highlight an entire column, then run a script so this is automated. Is it even possible?

       

      I've attached a small example of one of my tables in case I haven't explained this well.33.jpg

       

      Thank you

        • 1. Re: Script to automate every other row
          John Hawkinson Level 5

          It's certainly possible.

           

          I am a bit confused why you want to do this column-by-column, since your example appears to have uniform rows.

           

          var i, r=app.selection[0].cells;
          for (i=1; i<r.length; i+=2) {
              r[i].cells.everyItem().appliedCellStyle="Cell Style 3";
          }
          

           

          That was originally written with the "cells" in the first line as "rows" (hence the variable r). But this loops over all the cells in the selection [a column], and applies a cell style (Cell Style 3) for each one that is odd-numbered (where the first cell is zero and thus even). You could modify individual attributes instead of using a style, but that seems inadvisible.

           

          You do have to watch out for the fact that the upper rule of one cell is the lower rule of another, so the order of applied cell styles can matter in terms of which rule is visible.

           

          If you want it to operate on entire rows of the selected table, swap out "cells" for "rows" in the first line.

           

          Hope that's useful. If not, please be more specific in what you would like the script to do.

          1 person found this helpful
          • 2. Re: Script to automate every other row
            pelicanadv Level 1

            I don't want to say I'm not too bright, but when it comes to scripting, etc. I have no idea what I'm doing.

             

            I thought I'd do columns instead of rows cause some text has been defined in the cell style which then links to the paragraph style as bold flush left/light flush left and the other is defined as bold center/light center. I thought I had it fixed cause I could do a search and replace on repeating text but when I went back to Excel and changed a number to text the link, I lost all my formatting.

             

            Let's say I highlight all the prices and give them all the cell style, bold/center, then I could do a find/change using /d/d.00 and replace the bold/center cell style with the bold/light center cell style. But I was looking on the Internet and couldn't find out how to do a find/change on cell styles. Is that even possible?

             

            The purpose of the linking is when prices rise or items get deleted in Excel, it would be a simple link in InDesign, quite a time-saver.

            • 3. Re: Script to automate every other row
              John Hawkinson Level 5

              So, does the script I supplied work for you, or does it not?

               

              I don't believe you can Find/Change cell styles, no.

               

              Perhaps you are better off without alternating the cell styles every row. In that case, instead of setting

              .appliedCellStyle="Cell Style 3";

              you could instead set the cell borders directly:

              .bottomEdgeStrokeWeight="0.25";

               

              etc.

              • 4. Re: Script to automate every other row
                pelicanadv Level 1

                I know how to get a script in ID but II need to take some time to learn what I'm supposed to do to use it. I'm sorry this must be very irritating for you.

                • 5. Re: Script to automate every other row
                  pelicanadv Level 1

                  No, I have to alternate every other row.

                   

                  First Row, Paragraph style, "helv-bold, FL", Cell style, Bold (based on paragraph style).

                  Second Row, Paragraph style, "helv-light, FL", Cell style, Light (based on paragraph style)

                   

                  Is it not possible to make a Java script to do this? I'll give up if you say it isn't.

                  Marcia

                  • 6. Re: Script to automate every other row
                    John Hawkinson Level 5

                    Good morning, Marcia. Please take the time to read a bit more carefully.

                     

                    Especially please read the example script I provided to you in the first reply, and the subsequent paragraph that explains how it works. Make sure you understand it, or ask questions to clarify your understanding.

                     

                    When I said, "Perhaps you are better off without alternating cell styles every row," the concern was whether using cell styles was wise. I was not suggesting you give up the alternation, which is clearly part of your design. As noted, you can accomplish your desired look without cell styles that specify the border. And because both the odd-row style and the even-row-style would have to specify the border change you want, it can be confusing if you use cell styles and aren't careful to keep their top/bottom definitions in careful sync (Style 1's bottom must be the same as style 2's top, and vice versa). But of course you can use cell styles. But I've given you sufficient tools to do that.

                     

                    Of course it is possible! If you look at the examples given, it should give you a very good idea of what is possible.

                    • 7. Re: Script to automate every other row
                      pelicanadv Level 1

                      John, you've been unusually patient with me and I want you to know I appreciated it. I guess my biggest problem in a nutshell is I don't know how to write the java script. I never had to deal with the technical end  of a design, just how it looks in the end. I flunked Algebra and had to take geometry 3X, my brain isn't oriented the way yours is and I just stay away from technical stuff. My job, till now, has always been to make my client's brochures, etc. look graphically pleasing.

                       

                      I was looking for something I could just put in the ID scripts file, have it work and be done with it. At the most, only put in the style names I was using. Then, when my customer updated their Excel file next year with price increases, I would just re-link the Excel file without losing my formatting in ID, or, just highlight it, run the script, and it would be right.

                       

                      Thanks for working with me thus far and hope you won't walk away but if you decide to, I understand.

                      Marcia

                      • 8. Re: Script to automate every other row
                        John Hawkinson Level 5

                        It would appear that the script I posted for you in the first reply meets your needs. Of course you will have to change "Cell Style 3" to whatever style name you have chosen. Put it in your scripts folder and change the cell style name.

                         

                        It's not helpful to proclaim a general lack of understanding. It's both unpleasant to read and also difficult to believe. Please spare us the drama, it just makes it difficult to help you.

                         

                        I point you, once again, to the explanation of the script I gave you in the first reply:

                         

                        ...this loops over all the cells in the selection [a column], and applies a cell style (Cell Style 3) for each one that is odd-numbered (where the first cell is zero and thus even).

                         

                        I think that is clear enough. Is the phrase "loops over" what is troubling you?  Substitute any verb you like. "Goes over," "looks at," "examines," "touches," "iterates upon," or barring that, "does stuff."

                         

                        If you didn't read the sentence the first two times, please read it this time. If that's not the problem, please be explicit about what is.

                        • 9. Re: Script to automate every other row
                          pelicanadv Level 1

                          You're absolutely right, it is unattractive and I needed to be called out on that. My problem was I wasn't going to the first email and once I found it it worked so thanks. Please accept my apologies.

                          m

                          • 10. Re: Script to automate every other row
                            Too_Many_Words

                            Thanks for this great script! Strange that you can apply fills and patterns to alternating rows, but not styles. This saved me loads of time on a large table!.

                             

                            Is there a library where you can find more of these?