worthingtoni
Level 1
(8 points)
Q: AppleScript for Numbers
Hello,
I've got an AppleScript which helps me generate a price list. Everything is working just fine but since an update it behaves a little different.
When I started using the script it was very fast staying at one sheet, but since I've updated my Mac (can't tell if it's a Number update or OS X update) it now switches between the two sheets involved in the script making it really slow.
Anyone else experienced this? Anyone got a solution?
This is the script in question:
tell application "Numbers"
activate
tell front document
tell sheet "Prislista"
tell table 1
set bredd_count to (count columns)
set hojd_count to (count rows)
set bredd_start to 2 -- to allow for 1 header column
set hojd_start to 3 -- to allow for 2 header rows
repeat with next_bredd from bredd_start to bredd_count
repeat with next_hojd from hojd_start to (hojd_count - 1) -- to allow for note in bottom row
set hojd to value of cell 1 of row next_hojd
set bredd to value of cell 2 of column next_bredd as string
if bredd contains "-" then set bredd to text -3 thru -1 of bredd
set bredd to (bredd as integer)
set pris to my get_price(bredd, hojd)
set value of cell next_bredd of row next_hojd to pris
end repeat
end repeat
end tell
end tell
end tell
display alert "Price list recalculated!" giving up after 20
end tell
on get_price(bredd, hojd)
tell application "Numbers"
tell document 1
tell sheet "Pris"
tell table "mått"
set value of cell "A2" to bredd
set value of cell "B2" to hojd
end tell
set pris to value of cell "B3" of table "Pris"
end tell
end tell
end tell
end get_price
MacBook Pro with Retina display, OS X El Capitan (10.11.4)
SGIII
Level 6
(10,647 points)
Numbers 3 AppleScript support is generally stronger than previous versions. However, retrieving and writing data in tables is slow. It would be much faster (probably 10 times as fast) to include your calculation logic directly in the script rather than have the script insert values and retrieve the result of calculations from Numbers.
However, a script like the one below, while still slow, may perform with acceptable speed with your document. Instead of having Numbers go back and forth between sheets, it uses AppleScript lists as much as possible to collect the values, and then pastes them in a block at the end.
SG
-- get bredd and hojd values and place in AppleScript lists
tell application "Numbers"
tell front document
tell sheet "Prislista"
tell table 1
set bredd_start to 2 -- to allow for 1 header column
set hojd_start to 2 -- to allow for 2 header rows
set the_bredds to row 2's (cells bredd_start thru -1)'s value
set the_hojds to column 1's (cells hojd_start thru -1 whose value is not missing value)'s value
end tell
end tell
end tell
end tell
-- get calculated values and place in tabbed string on clipboard
set text item delimiters to tab
set the_prices to ""
repeat with r from 1 to the_hojds's length
set price_row to {}
repeat with c from 1 to the_bredds's length
set bredd to the_bredds's item c
set hojd to the_hojds's item r
if bredd contains "-" then set bredd to bredd's text -3 thru -1
copy get_price(bredd, hojd) to price_row's end
end repeat
set the_prices to (the_prices & price_row as text) & return
end repeat
set the clipboard to the_prices
--paste the calculated values into the pricelist table
clpToTbl(1, "Prislista", 1, "B3")
-- handlers (subroutines)
on get_price(bredd, hojd) -- retrieve a price calculated in Numbers tables
tell application "Numbers"
tell document 1
tell sheet "Pris"
tell table "Mått"
set value of cell "A2" to bredd
set value of cell "B2" to hojd
end tell
set pris to value of cell "B3" of table "Pris"
end tell
end tell
end tell
end get_price
to clpToTbl(d, s, t, c) -- pastes block of data into Numbers table starting at designated cell c
tell application "Numbers"
activate
tell document d's sheet s's table t
set selection range to cell c
tell application "System Events" to keystroke "v" using {command down, shift down, option down}
end tell
end tell
end clpToTbl
