Previous 1 2 Next 16 Replies Latest reply: Jun 10, 2012 9:51 AM by shantastik
shantastik Level 1 Level 1 (0 points)

Hello;

 

Never used Applescript here but seems from my reading it may be exactly what I need.

 

Basically I need something fairly simple with NUMBERS. 

 

I need applescript to insert a Column after column A and add "name" as the value the top row (column header).  This now becomes Column B.

 

Then I need the value of column B row 2 to be a concatenate value of Cell C2, D2, E2 with a space between each

 

Then I need to "FILL" all rows below in column B to concatenate the values of each row.


Example value of B3 would be C3, D3, E3 and F3, etc.

 

Can the "Fill" command be variable?     I will be working with different spreadsheets and the number of rows will vary.

 

Ideally when a csv is added to a folder, i'd love a script to run and then save the file with the changes

 

However, it will save me a lot of time even if I can just open the numbers file and run a script.

 

Any advise will be appreciated.


APPLESCRIPT NEWBIE!


Mac Pro, Mac OS X (10.6.2), Quad Core Nehalem Mac Pro / IPhone 3G / Unibody Macbook
  • Jacques Rioux Level 4 Level 4 (2,705 points)

    Hi,

     

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

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

    set thisCSVFile to choose file

    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

     

    on convertListToText(tList)

          set tid to text item delimiters

          set text item delimiters to " " -- separator = space

          set tVal to tList as text -- convert AppleScript's list  to string --> (value separated by space)

          set text item delimiters to tid

          return tVal

    end convertListToText

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

     

     

    if it works as you want;

    Here is the script for a folder action.

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

    on adding folder items to this_folder after receiving added_items

          repeat with thisCSVFile in added_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

          end repeat

    end adding folder items to

     

    on convertListToText(tList)

          set tid to text item delimiters

          set text item delimiters to " " -- separator = space

          set tVal to tList as text -- convert AppleScript's list  to string --> (value separated by space)

          set text item delimiters to tid

          return tVal

    end convertListToText

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

    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.

  • Barry Level 7 Level 7 (29,350 points)

    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

    *)

    --=====

     

    on run

      my activateGUIscripting()

              (*

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

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

      tell application "Numbers"

                        (*

    Open the CSV file *)

      open result

      tell document 1 to tell sheet 1 to tell table 1

      add column after column 1

                                  set value of cell "B1" to "name"

      set delim to my getLocalizedDelimiter()

                                  (*

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

      set selection range to range ("B3 : " & name of last cell of column 2)

      end tell

      end tell

              (*

              Fill the selected range *)

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

    end run

     

    --=====

    (*

    Set the parameter delimiter which must be used in Numbers formulas

    *)

    on getLocalizedDelimiter()

      if character 2 of (0.5 as text) is "." then

                        return ","

      else

                        return ";"

      end if

    end getLocalizedDelimiter

     

    --=====

     

    on activateGUIscripting()

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

              tell application "System Events"

      if not (UI elements enabled) then set (UI elements enabled) to true

      end tell

    end activateGUIscripting

     

    --=====

    (*

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

    ==== Uses GUIscripting ====

    *)

    on selectSubMenu(theApp, mt, mi, ms)

     

      activate application theApp

      tell application "System Events" to tell application process theApp to tell menu bar 1 to ¬

      tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms

    end selectSubMenu

     

    --=====

    --[/SCRIPT]

    --{code}

  • shantastik Level 1 Level 1 (0 points)

    Hi Jacques;

     

    Thanks for your reply.  I tried it and it worked, with a few problems.    There are already columns A - X in the spreadsheet, so the script does add column B correctly as name -- however it seems to be concatenating all columns.  Sorry I didn't mention that there are lots of columns before.   I only mentioned the ones I needed to be involved in the concatentation.

     

    Also, the first cell "C" is the number of a street address -- such as 402 Elm Street.   In the concatenation its displaying as 402.0

     

    It's adding a decimal that isn't there in the column C  (or I guess its there but it doesn't show)

     

    Also, CELL D is a street direction -- such as 402 "West" Elm Street.   So sometimes Cell D is blank as not all streets have an "east" or "west."    The concatenation you gave puts a "0.0" value if cell D is empty.    It does it correctly if there is a value in Cell D.

     

    Other than that -- it's exactly what I need!

     

    Any advice would be great. 

  • shantastik Level 1 Level 1 (0 points)

    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

     

     

    getvalue of cells 3 thru -1 to

    getvalue of cells 3 thru 6

    (this corrected having every column concatenate ) ) 

     

    Thanks


    Shannon

  • shantastik Level 1 Level 1 (0 points)

    Hi Barry;

     

    Thanks for the info!   I followed all instructions.

     

    Everything works with 1 exception!

     

    the new "name" column (B) only concatenates columns C,D,E,&F on rows 2 and 3 and then stops.   Any rows after that (4 and above) are empty in column B.


    Any advice?

     

    Shannon

  • Barry Level 7 Level 7 (29,350 points)

    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 Level 1 Level 1 (0 points)

    Hmmm, well some progress.

     

    I do receive this error

     

    error "System Events got an error: Can’t get menu 1 of menu item 8 of menu 1 of menu bar item 5 of menu bar 1 of application process \"Numbers\". Invalid index." number -1719 from menu 1 of menu item 8 of menu 1 of menu bar item 5 of menu bar 1 of application process "Numbers"

     

    Before, nothing happened after correct creation of a new column B and correct concatenation of B2 and B3

     

    With the changes, it seems to go a step further.....

     

    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.

     

    Progress

     

    Is there anything I can tell you from this end to help solve?

     

    Shannon

  • shantastik Level 1 Level 1 (0 points)

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

     

    Thanks...

    /___sbsstatic___/migration-images/185/18551134-1.jpg

  • Jacques Rioux Level 4 Level 4 (2,705 points)

    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"

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

    set thisCSVFile to choose file

    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"

                            set format of column "C" to text -- 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

    end if

     

    on convertListToText(tList)

          if item 2 of tList = 0 then set item 2 of tList to "" -- blank cell (format of column "D" must be a string)

          set tid to text item delimiters

          set text item delimiters to " " -- separator = space

          set tVal to tList as text -- convert AppleScript's list  to string --> (value separated by space)

          set text item delimiters to tid

          return tVal

    end convertListToText

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

     

     

    --

     

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

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

    set thisCSVFile to choose file

    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"

                            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

    end if

     

    on makeFormula(cList, n, rowN) -- (concatenate cells)

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

          set tc to (count cList)

          repeat with i from 2 to tc -- make the first formula

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

          end repeat

          set tList to {tformula}

          set tid to text item delimiters

          set text item delimiters to (n as string)

          set t to text items of tformula

          repeat with i from (n + 1) to rowN --make the others formula

                set text item delimiters to (i as string)

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

          end repeat

          set text item delimiters to tid

          return tList

    end makeFormula

  • Jacques Rioux Level 4 Level 4 (2,705 points)

    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

    on selectSubMenu(theApp, mt, mi, ms)

          activate application "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

    end selectSubMenu

  • Barry Level 7 Level 7 (29,350 points)

    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

  • shantastik Level 1 Level 1 (0 points)

    Hi Barry;


    Correct -- B3 and all rows in that column are all thats highlighted.

     

    However, it is not "filling down."

     

    Seems that everything is working but the CLICK after the "FILL DOWN" is selected.   At least that is what appears to be happeneing.

     

    Shannon

  • Barry Level 7 Level 7 (29,350 points)

    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

  • Barry Level 7 Level 7 (29,350 points)

    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

Previous 1 2 Next