2 Replies Latest reply: Mar 10, 2014 11:16 AM by SGIII
mingsai Level 1 Level 1 (30 points)

Problem

 

I am trying to automate the load of one csv into a Numbers file and apply formatting. The premise is that I would load a variable number of rows into columns A-G. Then I need to add formulas to columns H-M and fill down to the last row. I'd like advice on ways to tackle this problem using some level of automation.

 

Alternatives I am considering:

 

  1. AppleScript to insert rows, add calculated columns and fill down (using GUI scripting)
  2. AppleScript to insert rows, add calculated columns and fill down (using complex scripting.. arrays, etc.)
  3. Custom user template (not sure this is possible or how to)

 

Questions

 

Are there better ways to handle this that I have not reviewed above?

Can anyone provide a sample, tips, or reference link for AppleScript GUI scripting in Numbers 3.1 or Numbers 2.3?

  • Jerrold Green1 Level 7 Level 7 (29,905 points)

    Ming,

     

    There probably is a good case for scripting here. I'm not a scripter, so my approach would be to see what I could do with a template. Expressions can be written to adapt to the number of rows with data in the target area.

     

    I would probably put the calculations in a separate table so that they don't get overwritten during the introduction of the csv file.

     

    Jerry

  • SGIII Level 5 Level 5 (5,425 points)

    trying to automate the load of one csv into a Numbers file and apply formatting. The premise is that I would load a variable number of rows into columns A-G. Then I need to add formulas to columns H-M and fill down to the last row.

     

    I do not think you need to resort to gui-scripting for this. You can do it with "regular" AppleScripting.  For Numbers 3, see the explanations and examples at these links in particular among the many relevant examples at the macosxautomation.com:

     

    reading data from file

    adding rows

    populating tables      

    adding formulas with AppleScript

     

     

    And there is also the "lazy person's method" of getting csv data into Numbers:

     

    Format the receiving tables the way you want them, use a script read the csv data into the system clipboard as tab-separated data, click once in a cell in the pre-formatted table, and option-shift-command-v (Edit > Paste and Match Style) to paste. Numbers automatically expands the table to accommode the number of rows and columns needed.

     

    If you want to try that, here is a CSV to Tabs on Clipboard Automator Service that takes any CSV file and places its contents on the clipboard for pasting into Numbers. To install it just double-click the .workflow file and if needed click 'Download Anyway' in System Preferences > Privacy and Security. Thereafter, just select the file in Finder, right-click and choose from the Services contextual menu.

     

    There is also an impressive stand-alone script (haven't had a chance to convert it into a Service) that is likely better at doing the same thing (converts csv to tsv for pasting into Numbers) recently posted by Hiroto here.

     

    SG