Skip navigation

AppleScript & Numbers - adding data to range of cells?

2541 Views 7 Replies Latest reply: Nov 1, 2011 10:45 AM by KOENIG Yvan RSS
David Gordon Level 1 Level 1 (115 points)
Currently Being Moderated
Oct 31, 2011 6:01 AM

I want to translate an Excel AppleScript to use with Numbers. I had

 

on writeToSpreadsheet(tagValues, i)

          tell application "Microsoft Excel"

                         tell worksheet 1 of workbook 1

                                        set theRange to range ("A" & i + 1 & ":" & "Q" & i + 1) --starts writing to row 2 to allow for instructions on row 1

                                             set value of theRange to tagValues

                         end tell

          end tell

end writeToSpreadsheet

 

My Numbers translation is having trouble with

 

set value of theRange to tagValues

 

Obviously (?) I now use

 

tell table 1 of sheet 1 of document 1

 

so I think I just need to understand how I address the range of cells I'm trying to get the data written to.

 

Any clues while I do more reseach appreaciated!

 

Thanks!

MacBook Pro, Mac OS X (10.7.2)
  • Level 8 Level 8 (41,760 points)

    I tried to be a bit didactic because the structure range is just partially used in current AppleScript support.

    We may extract the content of a range but we can't set the contents of such a structure.

     

    --{code}

    (*

    Yvan KOENIG (VALLAURIS, France)

    2011/10/31

    *)

    on run

              tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1

      (*

    1st example of what can't be used *)

                        set maybe to value of cells of range "B2:D5"

      --> {12.0, 56.0, "azer", 4.0, 2.0, 6.0, 1.0, 2.0, 3.0, 9.0, 10.0, 11.0}

                        try

                                  set value of cells of range "F5:H6" to maybe

      --> error number -10000

                        end try

      (*

    2nd example of what can't be used because the instruction return the contents of every cells of the rows of the range *)

                        set maybe to value of cells of every row of range "B2:D5"

      --> {{0.0, 12.0, 56.0, "azer", 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0}, {0.0, 4.0, 2.0, 6.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0}, {0.0, 1.0, 2.0, 3.0, 0.0, 12.0, 56.0, "azer", 0.0, 0.0, 0.0}, {0.0, 9.0, 10.0, 11.0, 0.0, 4.0, 2.0, 6.0, 0.0, 0.0, 0.0}}

      (*

    Here is a code which do the trick *)

                        tell range "B2:D5"

                                  set colsInRange to count columns

                                  set rowsInRange to count rows

                                  set maybe to value of cells

      --> {12.0, 56.0, "azer", 4.0, 2.0, 6.0, 1.0, 2.0, 3.0, 9.0, 10.0, 11.0}

                        end tell -- range

      (*

    Get the coordinates of the 1st cell of the target range.

    I code this way so that we define the target cell only once

    which is less prone to typos *)

                        tell cell "F5"

                                  set colNum1 to address of its column

                                  set rowNum1 to address of its row

                        end tell -- cell

     

                        repeat with i from 1 to count of maybe by colsInRange

                                  repeat with j from 0 to (colsInRange - 1)

                                            set a_val to item (i + j) of maybe

                                            tell cell (colNum1 + j) of row (rowNum1 - 1 + (i div colsInRange))

                                                      if a_val is 0.0 then

                                                                clear

                                                      else

                                                                set value to a_val as text

                                                      end if

                                            end tell -- cell…

                                  end repeat -- j

                        end repeat -- i

              end tell -- Numbers…

    end run

    --{code}

     

    As the value extracted from a blank cell is 0.0, I decided to clean every cell whose source's value is 0.0.

    If you wish to put 0.0 in the target cell, it's easy to edit the code.

     

    replace :

    --{code}

    if a_val is 0.0 then

                  clear

            else

                  set value to a_val as text

    end if

    --{code}

     

    by :

     

    --{code}

    set value to a_val as text

    --{code}

     

    I already explained that the coercion to text is required to get rid of a bug striking when the hosting system doesn't use the decimal period.

    The treatment of dates is more complicated (due to UTC and Daylight offset) but, if my memory is right, I already treated it with details.

     

    Yvan KOENIG (VALLAURIS, France) lundi 31 octobre 2011 19:19:58

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

     

     

  • Level 8 Level 8 (41,760 points)

    What you wrote is not a complete script.

    I added some of the missing instructions and it became :

    --{code}

    my writeToSpreadsheet({"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15", "A16", "A17", "A18"}, 69)

    (*

    I don't understand what's the need for the i parameter passed to your handler *)

    on writeToSpreadsheet(tagValues, i)

              tell application "Numbers"

                        tell table 1 of sheet 1 of document 1

     

                                  set colDest to 1

                                  set rowDest to 2 --starting on row 2 to allow for header in row 1

                                  (*

    noRows isn't used *)

                                  set noRows to 5 --needs to be set by number of selected images in Aperture

                                  repeat while (count column) < 18

      add column after last column

                                  end repeat

     

                                  repeat while (count row) < (18 + 1) (* +1 required because you leave row 1 empty *)

      add row below last row

                                  end repeat

                                  repeat with r from 1 to 18

                                            repeat with c from 1 to 18

                                                      set value of cell (rowDest - 1 + r) of column (colDest - 1 + c) to item c of tagValues

                                            end repeat

                                  end repeat

     

                        end tell

              end tell

     

    end writeToSpreadsheet

    --{code}

    The result is :

    2011-10-31T23.15.42.jpg

     

    But maybe what you want is what is achieved by this script :

     

    --{code}

    my writeToSpreadsheet({"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15", "A16", "A17", "A18", "A19", "A20", "A21", "A22", "A23", "A24", "A25", "A26", "A27", "A28", "A29", "A30", "A31", "A32", "A33", "A34", "A35", "A36", "A37", "A38", "A39", "A40", "A41", "A42", "A43", "A44", "A45", "A46", "A47", "A48", "A49", "A50", "A51", "A52", "A53", "A54", "A55", "A56", "A57", "A58", "A59", "A60", "A61", "A62", "A63", "A64", "A65", "A66", "A67", "A68", "A69", "A70", "A71", "A72", "A73", "A74", "A75", "A76", "A77", "A78", "A79", "A80", "A81", "A82", "A83", "A84", "A85", "A86", "A87", "A88", "A89", "A90", "A91", "A92", "A93", "A94", "A95", "A96", "A97", "A98", "A99", "A100", "A101", "A102", "A103", "A104", "A105", "A106", "A107", "A108", "A109", "A110", "A111", "A112", "A113", "A114", "A115", "A116", "A117", "A118", "A119", "A120", "A121", "A122", "A123", "A124", "A125", "A126", "A127", "A128", "A129", "A130", "A131", "A132", "A133", "A134", "A135", "A136", "A137", "A138", "A139", "A140", "A141", "A142", "A143", "A144", "A145", "A146", "A147", "A148", "A149", "A150", "A151", "A152", "A153", "A154", "A155", "A156", "A157", "A158", "A159", "A160", "A161", "A162", "A163", "A164", "A165", "A166", "A167", "A168", "A169", "A170", "A171", "A172", "A173", "A174", "A175", "A176", "A177", "A178", "A179", "A180", "A181", "A182", "A183", "A184", "A185", "A186", "A187", "A188", "A189", "A190", "A191", "A192", "A193", "A194", "A195", "A196", "A197", "A198", "A199", "A200", "A201", "A202", "A203", "A204", "A205", "A206", "A207", "A208", "A209", "A210", "A211", "A212", "A213", "A214", "A215", "A216", "A217", "A218", "A219", "A220", "A221", "A222", "A223", "A224", "A225", "A226", "A227", "A228", "A229", "A230", "A231", "A232", "A233", "A234", "A235", "A236", "A237", "A238", "A239", "A240", "A241", "A242", "A243", "A244", "A245", "A246", "A247", "A248", "A249", "A250", "A251", "A252", "A253", "A254", "A255", "A256", "A257", "A258", "A259", "A260", "A261", "A262", "A263", "A264", "A265", "A266", "A267", "A268", "A269", "A270", "A271", "A272", "A273", "A274", "A275", "A276", "A277", "A278", "A279", "A280", "A281", "A282", "A283", "A284", "A285", "A286", "A287", "A288", "A289", "A290", "A291", "A292", "A293", "A294", "A295", "A296", "A297", "A298", "A299", "A300", "A301", "A302", "A303", "A304", "A305", "A306", "A307", "A308", "A309", "A310", "A311", "A312", "A313", "A314", "A315", "A316", "A317", "A318", "A319", "A320", "A321", "A322", "A323", "A324"}, 69)

    (*

    I don't understand what's the need for the i parameter passed to your handler *)

    on writeToSpreadsheet(tagValues, i)

              tell application "Numbers"

                        tell table 1 of sheet 1 of document 1

     

                                  set colDest to 1

                                  set rowDest to 2 --starting on row 2 to allow for header in row 1

                                  (*

    noRows isn't used *)

                                  set noRows to 5 --needs to be set by number of selected images in Aperture

                                  repeat while (count column) < 18

      add column after last column

                                  end repeat

     

                                  repeat while (count row) < (18 + 1) (* +1 required because you leave row 1 empty *)

      add row below last row

                                  end repeat

                                  repeat with r from 1 to 18

                                            repeat with c from 1 to 18

                                                      set value of cell (rowDest - 1 + r) of column (colDest - 1 + c) to item (((r - 1) * 18) + c) of tagValues

                                            end repeat

                                  end repeat

     

                        end tell

              end tell

     

    end writeToSpreadsheet

    --{code}

    2011-10-31T23.19.59.jpg

     

    Yvan KOENIG (VALLAURIS, France) lundi 31 octobre 2011 23:21:05

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

     

     

  • Level 8 Level 8 (41,760 points)

    Hello

     

    I learnt something.

     

    As :

    tell application "Numbers"

              tell table 1 of sheet 1 of document 1

                        set theRange to get range "C5:K12"

                        set c to 10

                        set value of cell c of theRange to "azerty"

              end tell

    end tell

     

    fails, I never guessed that

    tell application "Numbers"

              tell table 1 of sheet 1 of document 1

                        set theRange to "C5:K12"

                        set c to 10

                        set value of cell c of range theRange to "azerty"

              end tell

    end tell

     

    which theorically, is exactly the same thing, would behave flawlessly.

     

    Yvan KOENIG (VALLAURIS, France) mardi 1 novembre 2011 18:38:48

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

     

     

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.