Applescript with iWork Numbers (newbie)

Hi,

Here is a script to start, test it in the AppleScript editor

-------------------------------------------------------------------------

setthisCSVFiletochoose file

if (thisCSVFile as string) ends with ".csv" then -- CSV only

setnumbersFiletotext 1 thru -5 of (thisCSVFileasstring)

tell application "Numbers"

open thisCSVFile

tell front document

tell table 1 of sheet 1

add column after column "A"

set value of cell 1 of column "B" to "name"

tell row 2 to set value of cell 2 to my convertListToText(get value of cells 3 thru 5) --C2, D2 and E2

repeat with i from 3 to (count row) --concatenate the values of each row

tell row i to set value of cell 2 to my convertListToText(get value of cells 3 thru -1) -- cell "C" trought the end of this row

end repeat

end tell

save as "LSDocumentTypeNativeDocument" in numbersFile -- save as ".numbers"

close saving no

end tell

end tell

endif

onconvertListToText(tList)

settidtotext item delimiters

settext item delimitersto " " -- separator = space

settValtotListastext -- convert AppleScript's list to string --> (value separated by space)

settext item delimiterstotid

return tVal

endconvertListToText

-------------------------------------------------------------------------

if it works as you want;

Here is the script for a folder action.

-------------------------------------------------------------------------

onadding folder items tothis_folderafter receivingadded_items

repeatwiththisCSVFileinadded_items

if (thisCSVFile as string) ends with ".csv" then -- CSV only

set numbersFile to text 1 thru -5 of (thisCSVFile as string)

tell application "Numbers"

open thisCSVFile

tell front document

tell table 1 of sheet 1

add column after column "A"

set value of cell 1 of column "B" to "name"

tell row 2 to set value of cell 2 to my convertListToText(get value of cells 3 thru 5) --C2, D2 and E2

repeat with i from 3 to (count row) --concatenate the values of each row

tell row i to set value of cell 2 to my convertListToText(get value of cells 3 thru -1) -- cell "C" trought the end of this row

end repeat

end tell

save as "LSDocumentTypeNativeDocument" in numbersFile -- save as ".numbers"

close saving no

end tell

end tell

end if

endrepeat

endadding folder items to

onconvertListToText(tList)

settidtotext item delimiters

settext item delimitersto " " -- separator = space

settValtotListastext -- convert AppleScript's list to string --> (value separated by space)

settext item delimiterstotid

return tVal

endconvertListToText

-------------------------------------------------------------------------

In Mac help --> AppleScript Help, type folder actions to learn how to enable "folder actions", how to assign a script to a folder, and where to save the script.

Hi shantastik,

Here is a script fitting your question, written by Yvan Koenig. You'll see some differences in approach from the script above.

Instructions (in French, followed by the same instructions in English) are included in the notes at the beginning of the script.

Regards,

Barry

--{code}

--[SCRIPT forNewbie-2012-05-27]

(*

Enregistrer le script en tant que Script : forNewbie-2012-05-27.scpt

déplacer le fichier ainsi créé dans le dossier

<VolumeDeDémarrage>:Utilisateurs:<votreCompte>:Bibliothèque:Scripts:Applications :Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Aller au menu Scripts , choisir Numbers puis choisir “forNewbie-2012-05-27”

Le script demande de sélectionner un fichier CSV,

l’ouvre avec Numbers,

insère une colonne à droite de la colonne A

puis rempli les cellules de la nouvelle colonne B ainsi créée.

--=====

L’aide du Finder explique:

L’Utilitaire AppleScript permet d’activer le Menu des scripts :

Ouvrez l’Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case “Afficher le menu des scripts dans la barre de menus”.

Sous 10.6.x et plus récents,

aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript

puis cocher la case “Afficher le menu des scripts dans la barre des menus”.

--=====

Save the script as a Script : forNewbie-2012-05-27.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

You may have to create the folder Numbers and even the folder Applications by yourself.

Go to the Scripts Menu, choose Numbers, then choose “forNewbie-2012-05-27”

The script urge you to select a CSV file,

open it with Numbers,

insert a column on the right of column A

then fill the cells of the newly created column B.

--=====

The Finder’s Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the “Show Script Menu in menu bar” checkbox.

Under 10.6.x and higher,

go to the General pane of AppleScript Editor’s Preferences dialog box

and check the “Show Script menu in menu bar” option.

--=====

Yvan KOENIG (VALLAURIS, France)

2012/05/27

*)

--=====

onrun

myactivateGUIscripting()

(*

Use choose file to locate the CSV file to open *)

choose file of type {"public.comma-separated-values-text"}

tellapplication "Numbers"

(*

Open the CSV file *)

openresult

telldocument 1 totellsheet 1 totelltable 1

add column aftercolumn 1

set value of cell "B1" to "name"

setdelimtomygetLocalizedDelimiter()

(*

Build the formula =C2&" "&D2&" "&E2 and insert it in B2 *)

set value of cell "B2" to "=C2&" & quote & space & quote & "&D2&" & quote & space & quote & "&E2"

(*

Build the formula =C3&" "&D3&" "&E3&" "&F3 and insert it in B3 *)

set value of cell "B3" to "=C3&" & quote & space & quote & "&D3&" & quote & space & quote & "&E3&" & quote & space & quote & "&F3"

(*

Select the range to fill *)

setselection rangetorange ("B3 : " & nameoflastcellofcolumn 2)

endtell

endtell

(*

Fill the selected range *)

my selectSubMenu("Numbers", 5, 10, 2) (* Fill Down *)

endrun

--=====

(*

Set the parameter delimiter which must be used in Numbers formulas

*)

ongetLocalizedDelimiter()

ifcharacter 2 of (0.5 astext) is "." then

return ","

else

return ";"

endif

endgetLocalizedDelimiter

--=====

onactivateGUIscripting()

(* to be sure than GUI scripting will be active *)

tell application "System Events"

ifnot (UI elements enabled) thenset (UI elements enabled) totrue

endtell

endactivateGUIscripting

--=====

(*

my selectSubMenu("Pages",6, 4, 26)

==== Uses GUIscripting ====

*)

onselectSubMenu(theApp, mt, mi, ms)

activateapplicationtheApp

tellapplication "System Events" totellapplication processtheApptotellmenu bar 1 to ¬

tellmenu bar itemmttotellmenu 1 totellmenu itemmitotellmenu 1 toclickmenu itemms

endselectSubMenu

--=====

--[/SCRIPT]

--{code}

Hi Shannon,

Just a guess, as I'm not running a new enough system for the script to work.

Looks like this line (last line before "end run" ) is pointing to the wrong menu item:

my selectSubMenu("Numbers", 5, 10, 2) (* Fill Down *)

Change to:

my selectSubMenu("Numbers", 5, 8, 2) (* Fill Down *)

Open the script in the Script Editor, make the change, then Save it as a Script again in the same location, replacing the original.

Regards,

Barry

Note: Not tested.

shantastik wrote:

Jacques!

I did figure out changing this below corrects the concatenation problem, however wasn't sure what to do to fix the decimals or to address if CELL D is blank

To fix the decimals in column "C" and the blank cell in column "D"

------------------------------------------

setthisCSVFiletochoose file

if (thisCSVFile as string) ends with ".csv" then -- CSV only

setnumbersFiletotext 1 thru -5 of (thisCSVFileasstring)

tell application "Numbers"

open thisCSVFile

tell front document

tell table 1 of sheet 1

add column after column "A"

set value of cell 1 of column "B" to "name"

setformatofcolumn "C" totext -- change the format "Automatic" to text, e.g. 402 is "402" not 402.0

tell row 2 to set value of cell 2 to my convertListToText(get value of cells 3 thru 5) --C2, D2 and E2

repeat with i from 3 to (count row) --concatenate the values of each row

tell row i to set value of cell 2 to my convertListToText(get value of cells 3 thru 6) -- cell "C" trought cell "F" of this row

end repeat

end tell

save as "LSDocumentTypeNativeDocument" in numbersFile -- save as ".numbers"

close saving no

end tell

end tell

endif

onconvertListToText(tList)

ifitem 2 oftList = 0 thensetitem 2 oftListto "" -- blank cell (format of column "D" must be a string)

settidtotext item delimiters

settext item delimitersto " " -- separator = space

settValtotListastext -- convert AppleScript's list to string --> (value separated by space)

settext item delimiterstotid

return tVal

endconvertListToText

--------------------------------------

--

If you prefer to have formulas in column "B", here is the script :

-------------------------------------

setthisCSVFiletochoose file

if (thisCSVFile as string) ends with ".csv" then -- CSV only

setnumbersFiletotext 1 thru -5 of (thisCSVFileasstring)

tell application "Numbers"

open thisCSVFile

tell front document

tell table 1 of sheet 1

add column after column "A"

set value of cell 1 of column "B" to "name"

set value of cell "B2" to "=C2&\" \"&D2&\" \"&E2"

set tc to (count row)

set tFormulas to my makeFormula(get name of cells 3 thru 6 of row 3, 3, tc)

repeat with i from 3 to tc

set value of cell 2 of row i to item (i - 2) of tFormulas

end repeat

end tell

save as "LSDocumentTypeNativeDocument" in numbersFile -- save as ".numbers"

close saving no

end tell

end tell

endif

onmakeFormula(cList, n, rowN) -- (concatenate cells)

set tformula to "=" & (item 1 of cList)

set tc to (count cList)

repeatwithifrom 2 totc -- make the first formula

set tformula to tformula & "&\" \"&" & (item i of cList)

endrepeat

settListto {tformula}

settidtotext item delimiters

settext item delimitersto (nasstring)

set t to text items of tformula

repeatwithifrom (n + 1) torowN --make the others formula

settext item delimitersto (iasstring)

set end of tList to (t as string) -- replace all 3 by i

endrepeat

settext item delimiterstotid

return tList

endmakeFormula

shantastik wrote:

I've included a screenshot of where in the code is HIGHLIGHTED after the error and it stops.

What is the index of the menu item "Fill Down" ?

If it doesn't work with these indexes (5, 10, 2), try with the english titles

onselectSubMenu(theApp, mt, mi, ms)

activateapplication "Numbers"

tell application "System Events"

click menu item "Fill Down" of menu "Fill" of menu item "Fill" of menu "Insert" of menu bar item "Insert" of menu bar 1 of application process "Numbers"

end tell

endselectSubMenu

Hi Shannon,

My bad!

I made a wrong guess in changing the menu item count from 10 to 8. Apparently the "empty" separator lines in the menu count as 'items' to the script, so the initial (5, 10, 2) values should be the correct ones.

Try changing the 8 back to a 10 and running it again.

"Watching the file, with the change you gave, the entire B column is now highlighted.... so it seems to stop right before the FILL DOWN command."

Correct analysis, but check the highlighted part of the description—only B3 to the last cell in column B should be highlighted. The content of the top cell of the selection is what is filled down the column.

Regards,

Barry

Odd.

Yvan reported earlier that he had tested the script on his machine, running Lion. It also runs successfully on my iBook running Tiger, after I remove the instructions not understood by that system (which are not at the point where it sems to be choking on yours).

Would you try this test, please:

Open the script in the Script Editor,

Open Numbers.

Command-tab to return the the Script editor, with numbers as the first application behind it.

Click Run

Return to the Script editor.

Click the Result button.

Select and Copy the text in the bottom pane of the window.

Paste that into a reply to this message.

Type a return, a line of xxxxxx, and a second return (to make a separator in the message).

Return to the Script editor.

Click the Event Log button.

Select and Copy the text in the bottom pane of the window.

Paste that into the reply to this message.

Add any comments, then Add Reply.

Regards,

Barry

Hi Shannon,

Your profile line (in your original message) indicated you are using OS X v10.6.2 (Snow Leopard). If that is correct, you should update to the most current issue of Snow Leopard.

Yvan Koenig reports he's mystified by the script's failing to complete, and requests that you email a copy of the version of the script file being used and a sample .csv file on which it's not working to him. His address is available in his profile, here.

Regards,

Barry