AppleScript & Numbers - adding data to range of cells?

Question

David Gordon Author

Level 1

127 points

AppleScript & Numbers - adding data to range of cells?

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 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)

Posted on Oct 31, 2011 6:01 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,807 points

Oct 31, 2011 11:29 AM in response to David Gordon

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

Reply

Answer 2

David Gordon Author

Level 1

127 points

Oct 31, 2011 11:44 AM in response to KOENIG Yvan

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/thread/3037514?answerId=15139752022#15139752022

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 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...!

Reply

Answer 3

KOENIG Yvan

Level 8

41,807 points

Oct 31, 2011 3:21 PM in response to David Gordon

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 (countrow) < (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 tell

end writeToSpreadsheet

--{code}

The result is :

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 (countrow) < (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 tell

end writeToSpreadsheet

--{code}

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

Reply

Answer 4

David Gordon Author

Level 1

127 points

Nov 1, 2011 5:10 AM in response to KOENIG Yvan

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 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 writeToSpreadsheet

Reply

Answer 5

David Gordon Author

Level 1

127 points

Nov 1, 2011 6:00 AM in response to David Gordon

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

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 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?

Reply

Answer 6

David Gordon Author

Level 1

127 points

Nov 1, 2011 8:34 AM in response to David Gordon

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 writeToSpreadsheet

Thanks for your help!

Reply

Answer 7

KOENIG Yvan

Level 8

41,807 points

Nov 1, 2011 10:45 AM in response to David Gordon

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

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

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

Reply