7 Replies Latest reply: Nov 1, 2011 10:45 AM by KOENIG Yvan
David Gordon Level 1 Level 1 (115 points)

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,780 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

     

     

  • David Gordon Level 1 Level 1 (115 points)

    Many thanks Yvan. I'm struggling to understand how its working. I have been trying with another script you posted here before. But perhaps it would be clearer if you say all of my script and knew that I am extracting data from Aperture to write into a spreadsheet. I had help to do this using Excel which you can see here https://discussions.apple.com/message/15139752#15139752

     

    I'm currently trying with the scrip you posted  at https://discussions.apple.com/thread/2565108?start=0&tstart=0

     

    {code}

    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

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

     

                                  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}

     

    I'm getting the same data written out over and over again as I don't seem to be increasing the row number on each repeat.

     

    Maybe I should stick to Excel...!

  • Level 8 Level 8 (41,780 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

     

     

  • David Gordon Level 1 Level 1 (115 points)

    Thank you for your help. These scripts don't seem to be doing what I need. I think this is my fault for not explaining myself well enough and for not posting the whole script. I will explain a litte more and post my whole script.

     

    The purpose of the script is to extract some data from images in an Aperture library and place it into a spreadsheet. Each image selected in Aperture will result in one line on the spreadsheet. My script has 18 pieces of data which it collects from the Aperture library. Therefore the spreadsheet has 18 columns of data, one row of data per image selected. If I run the script with 5 images selected in Aperture I will have a spreadsheet with 5 rows and 18 columns, if I select 100 images the spreadsheet will be 100 rows and 18 columns.

     

    For simplicity I will post my script with just four peices of data to collect and call them TagA, TagB, TagC and TagD. And as an example if I select four images from my Aperture library and run the script I expect the result to be something like

     

     

    TagA

    TagB

    TagC

    TagD

    23455

    Alpha

    Y

    31.10.2011

    23456

    Beta

    N

    31.10.2011

    23457

    Beta

    N

    31.10.2011

    34567

    Beta

    Y

    1.11.2011













    (Sorry I can't format the table very well!)

     

    So the script first collects my data from an image then places that data on a row of the spreadsheet. The next set of data is collected and placed onto the row below and so on. Here's the full script including the Aperture section

     

     

    tell application "Aperture"

              set imageSel to (get selection)

              if imageSel is {} then

                        error "Please select an image."

              else

                        repeat with i from 1 to count of imageSel

                                  tell library 1

                                            tell item i of imageSel

      -- list of tags

                                                      set tags to {"TagA", "TagB", "TagC", "TagD"}

      -- empty storage for tag values

                                                      set tagValues to {}

     

                                                      repeat with thisTag in tags

      -- create a list of tag values extracted from aperture

                                                                if exists custom tag thisTag then

                                                                          set end of tagValues to value ofcustom tag thisTag

                                                                else

                                                                          set end of tagValues to ""

                                                                end if

                                                      end repeat

     

      -- write the list of tag values to excel, on row i

                                                      my writeToSpreadsheet(tagValues, i)

                                            end tell

                                  end tell

                        end repeat

              end if

    end tell

     

    on writeToSpreadsheet(tagValues, i)

              tell application "Microsoft Excel"

                        tell worksheet 1 of workbook 1

                             set theRange to range ("A" & i & ":" & "R" & i)

                                  set value of theRange to theData

                        end tell

              end tell

    end writeToSpreadsheet

  • David Gordon Level 1 Level 1 (115 points)

    I wonder if this is a help. I know the following script can target a cell in a range, that's what I need to do.

     

    tell application "Numbers"

              tell table 1 of sheet 1 of document 1

                        set value of cell 2 of range "A1:R1" to "hello world!"

              end tell

    end tell

     

    So I need something like that but this doesn't work

     

    on writeToSpreadsheet(tagValues, i)

              tell application "Numbers"

                        tell table 1 of sheet 1 of document 1

                                  set theRange to range ("A" & i & ":" & "R" & i)

                                  repeat with c from 1 to 18

                                            set value of cell c of range theRange to item c of tagValues

                                  end repeat

                        end tell

              end tell

    end writeToSpreadsheet

     

    the error is

     

    Result:

    error "Numbers got an error: Can’t make «class » \"A1:R1\" of table \"Table 1\" of sheet \"Sheet 1\" of document \"Untitled\" into type integer." number -1700 from «class » "A1:R1" of table "Table 1" of sheet "Sheet 1" of document "Untitled" to integer

     

    But perhaps I'm getting closer?

  • David Gordon Level 1 Level 1 (115 points)

    This is what I need and produces the spreadsheet in the format required!

     

    on writeToSpreadsheet(tagValues, i)

              tell application "Numbers"

                        tell table 1 of sheet 1 of document 1

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

                                  repeat with c from 1 to 18

                                            set value of cell c of range theRange to item c of tagValues

                                  end repeat

                        end tell

              end tell

    end writeToSpreadsheet

     

    Thanks for your help!

  • Level 8 Level 8 (41,780 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