Scripting iWork Numbers ’09 With Appscript

March 11th, 2009  |  Published in ruby  |  7 Comments

Simple work problem: I need to keep a log of stories published on the sites I cover. Part one of the problem is getting all the existing stories into the log. Part two of the problem is getting new stories into the log as I publish them from now on.

I was pretty excited about taking Numbers ’09’s scripting support out for a spin. Unfortunately it’s a little anemic, especially compared to full-featured Ruby libraries like roo and spreadsheet. For instance, it doesn’t really have a conception of “the last row with data in it,” as the other two do. So you can’t just plop new data into the first empty row without doing something like iterating through all rows and finding the first one where a particular cell is empty. I uploaded the appscript-exported Numbers scripting dictionary if you’re curious about what it can do.

There’s some interesting room to grow for both scripts. In addition to tracking stories, we have to keep up with their overall performance. With the rugalytics gem it’s possible to mine performance data from Google Analytics based on the title of a given article or regular expressions taken from the URL.

Anyhow, this one solves part one. It’s a one-time-only importer that runs through the indexes for each container and grabs everything:

    

    #!/usr/bin/env ruby

    require 'rubygems'

    require 'appscript'

    include Appscript

    require '/Users/mph/lib/ruby/CDEV.rb'

    require 'hpricot'

    require 'open-uri'

    include Hpricot





    num = app("Numbers")

    ss = num.open("/Users/mph/Desktop/Invoices.numbers")

    sheet = ss.sheets["Invoices"]

    table = sheet.tables["Current Quarter"]



    containers = ["netsysm", "nethub", "netsecur", "linux_unix", "netos", "windows"]

    base_url = "http://enterprisenetworkingplanet.com/"

    cdevids = []



    containers.each do |con|

      page = Hpricot(open("#{base_url}#{con}"))

      index = page.search("//div[@class='section_index_list'")

      articles = index.search("//div[@class='post']")



      articles.each do |a|

        link =  a.search("//h3/a").first

        url =  link[:href]

        art = CDEV.new(url)



        unless cdevids.include?(art.id)

          cdevids << art.id

          title = art.title

          url = art.url

          author = art.author

          pubdate = art.pubdate.strftime("%m/%d/%y")

          row = table.rows.add_row_above(table.rows[2])



          table.rows[2].cells[1].value.set(author)

          table.rows[2].cells[2].value.set(pubdate)

          table.rows[2].cells[3].value.set(title)

          table.rows[2].cells[6].value.set(url)

        end



      end

    end

    

This one is for part two. It’s just part one stripped down and cast toward working on the foremost window in Safari, so when I’m giving an article a look from the front-end to make sure the CMS didn’t mangle it, I can run the script and capture the article info in my spreadsheet.

This one points to another oversight in the existing dictionary for Numbers ’09: One of the new features in the software is something called “Table Categories.” You can pick any column and create a category based on its content. It’s handy for things like quarterly invoicing, because you can take a lump of rows, create a table category out of the publication date and get a nicely sorted table with quarterly subtotals for each month. That poses two problems for my script:

  1. When a spreadsheet has active Table Categories, you can’t add a row to it.

  2. The Table Categories functionality doesn’t exist in the current Numbers scripting dictionary.

So if the script is run while the table is categorized, it fails. It doesn’t have any way to query the table and figure out if it’s categorized, so I had to fall back on UI scripting to look for whether the “Table” menu has an item that reads “Disable All Categories.” If it does, the script clicks the menu item. Once the script is done adding stuff, it clicks a menu item called “Enable All Categories” to put the table into a more readable state again.

I hate UI scripting. Another blog entry gave me a head start on figuring out what to call the menu items I needed to find, and ASTranslate did me the favor of cutting through all the Applescript hyperventilation and rendering things down to something more usable.

So anyhow:




#!/usr/bin/env ruby



require 'rubygems'

require 'appscript'

require '/Users/mph/lib/ruby/CDEV.rb'

include Appscript



num = app("Numbers")

saf = app("Safari")

num_sys = app("System Events").application_processes["Numbers"]



num_cat_menu = num_sys.menu_bars[1].menu_bar_items["Table"].menus[1]

num_cat_disable = num_cat_menu.menu_items["Disable All Categories"]

num_cat_enable = num_cat_menu.menu_items["Enable All Categories"]



ss = num.open("/Users/mph/Desktop/Invoices.numbers")



sheet = ss.sheets["Invoices"]



table = sheet.tables["Current Quarter"]



num.activate

table.activate



page = saf.documents[0].get

url = page.URL.get

art = CDEV.new(url)



title = art.title

url = art.url

author = art.author

pubdate = art.pubdate.strftime("%m/%d/%y")



num_sys.frontmost.set(true)



if num_cat_disable.exists

 num_cat_disable.click

end



row = table.rows.add_row_above(table.rows[2])



table.rows[2].cells[1].value.set(author)

table.rows[2].cells[2].value.set(pubdate)

table.rows[2].cells[3].value.set(title)

table.rows[2].cells[6].value.set(url)



num_cat_enable.click



 

Responses

  1. Scripting iWork Numbers ‘09 With Appscript | Mac Affinity says:

    April 27th, 2009 at 10:55 am (#)

    […] Go here to read the rest: Scripting iWork Numbers ‘09 With Appscript […]

  2. faun says:

    April 30th, 2009 at 4:07 pm (#)

    What is CDEV.rb and is there a version available so can try out these scripts?

  3. mph says:

    April 30th, 2009 at 4:44 pm (#)

    Faun writes:

    What is CDEV.rb and is there a version available so can try out these scripts?

    Hi, Faun,

    It’s a class I wrote to parse items in a CMS I work with. It’s not really useful to anyone but me. That said, you could make a few modifications to this script to use it with Safari and any random page.

    Using the second code sample above:

    Drop the require line for CDEV.rb, then remove lines 27-32 and replace them with this:

     title = page.name.get
    

    Then remove lines 42 & 43. That’s enough to clear out the stuff CDEV provides and give you a look at the working script.

  4. faun says:

    May 1st, 2009 at 5:14 pm (#)

    Here’s what I settled on. It only places the URL into the Numbers spreadsheet, but it gave me a better handle on what this script is doing exactly. Thanks for posting this.

    !/usr/bin/env ruby

    require ‘rubygems’ require ‘appscript’ include Appscript require ‘hpricot’ require ‘open-uri’ include Hpricot

    num = app(“Numbers”) ss = num.open(“/Users/faun/Desktop/Invoices.numbers”) sheet = ss.sheets[“Invoices”] table = sheet.tables[“Current Quarter”]

    containers = [“netsysm”, “nethub”, “netsecur”, “linux_unix”, “netos”, “windows”] base_url = “http://enterprisenetworkingplanet.com/” cdevids = []

    containers.each do |con| page = Hpricot(open(“#{base_url}#{con}”)) index = page.search(“//div[@class=’section_index_list'”) articles = index.search(“//div[@class=’post’]”)

    articles.each do |a| link = a.search(“//h3/a”).first url = link[:href] puts url row = table.rows.add_row_above(table.rows[2]) table.rows[2].cells[6].value.set(url)

    end end

  5. faun says:

    May 1st, 2009 at 5:17 pm (#)

    wow. that was ugly. Try this: http://pastie.textmate.org/465607

  6. mipmip says:

    January 14th, 2010 at 3:52 am (#)

    Made a little script myself. Thanks for your inspiration. It generates passwords and md5 encrypts them.

    You’ll need apg (advanced password generator) or generate one with ruby code.

    !/usr/bin/env ruby

    require ‘rubygems’ require ‘appscript’ include Appscript require ‘digest/md5’

    num = app(“Numbers”) ss = num.documents[1] sheet = ss.sheets[1] table = sheet.tables[1]

    count=100

    for i in 1..count do

    pass = apg -m 6 -x 6 -n1 -M L
    pass = pass.gsub!(/\n/, "")
    digest = Digest::MD5.hexdigest(pass)

    row = table.rows.add_row_above(table.rows[2]) table.rows[2].cells[1].value.set(pass) table.rows[2].cells[2].value.set(digest)

    end

  7. David H Dennis says:

    May 25th, 2010 at 1:40 pm (#)

    Thank you very much for putting this out there – I was going to try to reverse engineer the Numbers XML format, but this was enormously easier :).

    My iTunes sales data are now all pretty graphs and updating is automatic!

    Thanks!

    David

Leave a Response

© Michael Hall, licensed under a Creative Commons Attribution-ShareAlike 3.0 United States license.