Friday, November 16, 2018

#1 2018-09-02 09:03:28 pm

Pharmwheel
Member
Registered: 2018-09-01
Posts: 4

Adding rows to Numbers AppleScript

I'm having trouble getting a script to run in Numbers.  The script is designed to add rows to a spreadsheet and populate each row after it's been added.  However, after about 10 rows have been added Numbers hangs and the AppleScript times out with a -1712 error.

Here is the code.  The purpose of this script is to download a historical set of stock data using the STOCKH function in Numbers.  I can post a sample data set and a target data set but it's basically a stock symbol, date, price, high, low and volume row.  The script adds historical data for each new stock symbol, working from the bottom of the table upwards.

Any help would be appreciated - Thanks!

[code]
tell application "Numbers"
    activate
    set daysToDownload to 10 -- number of total rows for each stock
   
    tell document 1
        tell active sheet
            set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
        end tell
       
        --display dialog "How many days do you want to download?" default answer ""
        --set daysToDownload to text returned of result
        --display dialog daysToDownload
       
        tell selectedTable
           
            -- store the row to copy as the reference row of the table; we pick the last row assuming that each row is a unique stock symbol
            set startingRow to last row's address of column 1
            repeat until startingRow = 1
                set rowTarget to startingRow
                set rowToCopy to value of cells of row rowTarget -- values for this row
               
                repeat with j from 2 to daysToDownload --create a row copy with a new historical date (working backwards throught the calendar)
                    set x to properties of row rowTarget
                    set r to make new row at after row rowTarget with properties x -- add in a blank row below rowTarget
                   
                    set value of cell 1 of row (rowTarget + 1) to item 1 of rowToCopy --fill in the blank row with the ticker symbol (item 1) from rowToCopy; Applescript seems to copy the formulas from columns C, D, E, and F even though it's not asked to copy them
                   
                    --get the location of the date cell and add a formula to subtrack one workday from the prior row; assumes date is in column B
                    set value of cell 2 of row (rowTarget + 1) to "=WORKDAY(" & "B" & rowTarget & ",-1)"
                   
                    set rowTarget to rowTarget + 1 -- add one row to the rowTarget and repeat the process
                   
                end repeat
                set startingRow to startingRow - 1
            end repeat
        end tell
    end tell
   
end tell
[/code]

Model: iMac 13,2
AppleScript: 2.7
Browser: Safari 605.1.15
Operating System: Mac OS X (10.13 Developer Beta 3)


Filed under: applescript, numbers, hang

Offline

 

#2 2018-09-03 07:57:05 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4720

Re: Adding rows to Numbers AppleScript

Hi. Welcome to MacScripter.

Just to point out before I reply that our forum for seeking help with scripts is AppleScript | Mac OS X. Code Exchange is for proffering working code which the authors think is pretty cool. So if you find this thread's been moved to the other forum, that's the reason.  smile  Also, we have our own [applescript] and [/applescript] tags for posting AppleScript code, which make posted scripts appear as below.

I think the problem with your script may be the amount of housekeeping Numbers has to do after each instruction. By working from the bottom up, the script's inserting rows in front of a growing collection of previously added rows, which have to be "moved aside" (whatever that involves internally) to accommodate the rows currently being added. Numbers 5 also has self-imposed screen-updating overheads, such as selecting cells when the script edits them, displaying their contents in the bar at the bottom of the window, scrolling the window, etc.

What minimises the overheads for me — although I don't have your data — is to set the size of the table to the required number of rows immediately (which also extends any formula runs at the end of the table), then transfer the existing data down-table and fill in the new. Nothing much happens on screen until the action arrives at the part of table you happen to be viewing:

Applescript:

tell application "Numbers"
   activate
   set daysToDownload to 10 -- number of total rows for each stock
   
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
       end tell
       
       --display dialog "How many days do you want to download?" default answer ""
       --set daysToDownload to text returned of result
       --display dialog daysToDownload
       
       tell selectedTable
           
           set lastRow to row count
           
           -- Increase the size of the table to what will be required, assuming that row 1 is blank or contains headings. This extends any formula runs at the end of the table.
           set row count to (lastRow - 1) * daysToDownload + 1
           
           -- Move the existing data down-table and insert the new.
           repeat with originalRow from lastRow to 2 by -1
               set startOfBlock to (originalRow - 2) * daysToDownload + 2
               
               -- Set this block's stock symbols en masse.
               set value of cells startOfBlock thru (startOfBlock + daysToDownload - 1) of column 1 to value of cell originalRow of column 1
               
               -- Set the first date in the block to the original.
               set value of cell 2 of row startOfBlock to value of cell 2 of row originalRow
               -- Put WORKDAY() formulae into the remaining date cells.
               repeat with j from (startOfBlock + 1) to (startOfBlock + daysToDownload - 1)
                   set value of cell 2 of row j to "=WORKDAY(" & "B" & (j - 1) & ",-1)"
               end repeat
           end repeat
           
       end tell
   end tell
end tell


NG

Offline

 

#3 2018-09-03 12:26:00 pm

Pharmwheel
Member
Registered: 2018-09-01
Posts: 4

Re: Adding rows to Numbers AppleScript

Thanks, for your help. Your code is much faster.  However, Numbers still hangs.  I've narrowed it down to the addition of the WORKDAY formula.  If this line is removed from the script, all the rows are added in the correct locations with the ticker symbol.  As soon as 10 or so WORKDAY formulas are added, Numbers hangs.

Is another way to add in the dates that won't cause the hang?

Could the real problem be that adding in the dates causes Numbers to start to populate the dependent formula columns, somehow causing a hang?

Pharmwheel

Offline

 

#4 2018-09-03 01:17:10 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4720

Re: Adding rows to Numbers AppleScript

My own suspicion is Numbers is having formulae thrown at it faster than it can compute the results. If you don't necessarily need formulae, the script could calculate the dates itself and insert them as absolute values, very much more quickly:

Applescript:

tell application "Numbers"
   activate
   set daysToDownload to 10 -- number of total rows for each stock
   
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
       end tell
       
       --display dialog "How many days do you want to download?" default answer ""
       --set daysToDownload to text returned of result
       --display dialog daysToDownload
       
       tell selectedTable
           
           set lastRow to row count
           
           -- Increase the size of the table to what will be required, assuming that row 1 is blank or contains headings. This extends any formula runs at the end of the table.
           set row count to (lastRow - 1) * daysToDownload + 1
           
           -- Move the existing data down-table and insert the new.
           repeat with originalRow from lastRow to 2 by -1
               set startOfBlock to (originalRow - 2) * daysToDownload + 2
               
               -- Set this block's stock symbols en masse.
               set value of cells startOfBlock thru (startOfBlock + daysToDownload - 1) of column 1 to value of cell originalRow of column 1
               
               -- Set the first date in the block to the original.
               set thisDate to value of cell 2 of row originalRow
               set value of cell 2 of row startOfBlock to thisDate
               
               -- Calculate the preceding work dates and insert them into the block's remaining date cells.
               repeat with j from (startOfBlock + 1) to (startOfBlock + daysToDownload - 1)
                   set w to thisDate's weekday
                   if (w is Monday) then
                       set thisDate to thisDate - 3 * days
                   else if (w is Sunday) then
                       set thisDate to thisDate - 2 * days
                   else
                       set thisDate to thisDate - days
                   end if
                   
                   set value of cell 2 of row j to thisDate
               end repeat
           end repeat
           
       end tell
   end tell
end tell

Last edited by Nigel Garvey (2018-09-03 01:18:01 pm)


NG

Offline

 

#5 2018-09-03 08:21:10 pm

Pharmwheel
Member
Registered: 2018-09-01
Posts: 4

Re: Adding rows to Numbers AppleScript

The hang in Numbers is clearly an issue with the STOCKH formula.  If I remove the STOCKH formula from all cells in the starting spreadsheet, the script runs perfectly, adding all the symbols and dates as intended.  As soon as I try to add in the STOCKH formula, it adds about 10 cells worth of values and then hangs.

I shifted the timing around so the STOCKH formulas were added last but it didn't matter. I suppose it's trying to pull in the data for the formula at the same time the script is adding formulas to the spreadsheet and it's causing it to hang.

Is there a way to temporarily suspend calculations by Numbers until all the formulas are in place?

Applescript:

tell application "Numbers"
   activate
   set daysToDownload to 10 -- number of total rows for each stock
   
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
       end tell
       
       --display dialog "How many days do you want to download?" default answer ""
       --set daysToDownload to text returned of result
       --display dialog daysToDownload
       
       tell selectedTable
           
           -- store the row to copy as the reference row of the table; we pick the last row assuming that each row is a unique stock symbol
           set lastRow to row count
           --Increase the size of the table to what will be required, assuming that row 1 is blank or contains heading. This extends any formula runs at the end of the table
           set row count to (lastRow - 1) * daysToDownload + 1
           
           -- Move the existing data down-table and insert the new.
           repeat with originalRow from lastRow to 2 by -1
               set startOfBlock to (originalRow - 2) * daysToDownload + 2
               
               -- Set this block's stock symbols en masse.
               set value of cells startOfBlock thru (startOfBlock + daysToDownload - 1) of column 1 to value of cell originalRow of column 1
               
               -- Set the first date in the block to the original.
               set value of cell 2 of row startOfBlock to value of cell 2 of row originalRow
               set thisDate to value of cell 2 of row originalRow
               set value of cell 2 of row startOfBlock to thisDate
               
               -- Calculate the preceding work dates and insert them into the block's remaining date cells.
               repeat with j from (startOfBlock + 1) to (startOfBlock + daysToDownload - 1)
                   set w to thisDate's weekday
                   if (w is Monday) then
                       set thisDate to thisDate - 3 * days
                   else if (w is Sunday) then
                       set thisDate to thisDate - 2 * days
                   else
                       set thisDate to thisDate - days
                   end if
                   
                   set value of cell 2 of row j to thisDate
               end repeat
           end repeat
           set lastRow to row count --count all the rows
           repeat with r from 2 to lastRow --starting at row 2 to the end of the table set the value of columns 3-6 with STOCKH formula
               set value of cell 3 of row r to "=STOCKH(" & "A" & r & ",0," & "B" & r & ")" --Set Price at close
               set value of cell 4 of row r to "=STOCKH(" & "A" & r & ",4," & "B" & r & ")" --Set Volume
               set value of cell 5 of row r to "=STOCKH(" & "A" & r & ",2," & "B" & r & ")" --Set High
               set value of cell 6 of row r to "=STOCKH(" & "A" & r & ",3," & "B" & r & ")" --Set Low
           end repeat
       end tell
   end tell
   
end tell

Offline

 

#6 2018-09-04 02:28:16 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4720

Re: Adding rows to Numbers AppleScript

If you don't mind using GUI Scripting, this works for me. It enters the formulae in row 2 only, then selects those four columns as a block and clicks the "Autofill Down" item under "Autofill Cells" in Numbers's "Table" menu. This seems to allow Numbers to handle the formulae in its own time. I even get stock quotes in dollars with the codes I made up myself!  smile

Applescript:

tell application "Numbers"
   activate
   set daysToDownload to 10 -- number of total rows for each stock
   
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
       end tell
       
       --display dialog "How many days do you want to download?" default answer ""
       --set daysToDownload to text returned of result
       --display dialog daysToDownload
       
       tell selectedTable
           
           set lastRow to row count
           
           -- Increase the size of the table to what will be required, assuming that row 1 is blank or contains headings. This extends any formula runs at the end of the table.
           set row count to (lastRow - 1) * daysToDownload + 1
           
           -- Move the existing data down-table and insert the new.
           repeat with originalRow from lastRow to 2 by -1
               set startOfBlock to (originalRow - 2) * daysToDownload + 2
               
               -- Set this block's stock symbols en masse.
               set value of cells startOfBlock thru (startOfBlock + daysToDownload - 1) of column 1 to value of cell originalRow of column 1
               
               -- Set the first date in the block to the original.
               set thisDate to value of cell 2 of row originalRow
               set value of cell 2 of row startOfBlock to thisDate
               
               -- Calculate the preceding work dates and insert them into the block's remaining date cells.
               repeat with j from (startOfBlock + 1) to (startOfBlock + daysToDownload - 1)
                   set w to thisDate's weekday
                   if (w is Monday) then
                       set thisDate to thisDate - 3 * days
                   else if (w is Sunday) then
                       set thisDate to thisDate - 2 * days
                   else
                       set thisDate to thisDate - days
                   end if
                   
                   set value of cell 2 of row j to thisDate
               end repeat
           end repeat
           
           -- Insert the formulae for cells C2 thru F2.
           set value of cell "C2" to "=STOCKH(A2,0,B2)" --Set Price at close
           set value of cell "D2" to "=STOCKH(A2,4,B2)" --Set Volume
           set value of cell "E2" to "=STOCKH(A2,2,B2)" --Set High
           set value of cell "F2" to "=STOCKH(A2,3,B2)" --Set Low
           
           -- Select the whole of those four columns except for row 1.
           set selection range to range ("C2:F" & (row count))
       end tell
   end tell
end tell

-- Click the "Autofill Down" item under "Autofill Cells" in the "Table" menu.
-- USES GUI SCRIPTING. THE APPLICATION RUNNNING THE SCRIPT MAY NEED TO BE OK'd FOR THIS FIRST TIME. (System Preferences -> Security & Privacy -> Accessibility.)
tell application "System Events"
   tell application process "Numbers"
       set frontmost to true
       click menu item "Autofill Down" of menu 1 of menu item "Autofill Cells" of menu 1 of menu bar item "Table" of menu bar 1
   end tell
end tell

By the way, a slightly shorter alternative to …

Applescript:

set w to thisDate's weekday
if (w is Monday) then
   set thisDate to thisDate - 3 * days
else if (w is Sunday) then
   set thisDate to thisDate - 2 * days
else
   set thisDate to thisDate - days
end if

… would be:

Applescript:

set w to thisDate's weekday as integer
if (w > 2) then
   set thisDate to thisDate - days
else
   set thisDate to thisDate - (w + 1) * days
end if

It's no more efficient. Just less understandable and less code.  wink

Last edited by Nigel Garvey (2018-09-04 02:39:16 am)


NG

Offline

 

#7 2018-09-04 07:32:42 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3287

Re: Adding rows to Numbers AppleScript

If you are not running Numbers in English you will have to make some changes.

Applescript:

tell application "Numbers"
   set Table_loc to localized string "Table" from table "TSApplication" # Added
   --> Tableau
   set STOCKH_loc to localized string "STOCKH" from table "TSCALCULATIONEngine" # Added
   --> ACTIONH
   set autofillCells_loc to localized string "Autofill Cells" from table "TSTables" # Added, Oops I dropped one l !
   --> "Remplir automatiquement les cellules"
   set autofillDown_loc to 3 # Added
   
   # In the main code
   
   set value of cell "C2" to "=" & STOCKH_loc & "(A2,0,B2)" --Set Price at close
   set value of cell "D2" to "=" & STOCKH_loc & "(A2,4,B2)" --Set Volume
   set value of cell "E2" to "=" & STOCKH_loc & "(A2,2,B2)" --Set High
   set value of cell "F2" to "=" & STOCKH_loc & "(A2,3,B2)" --Set Low
end tell


# Lower in the main code
tell application "System Events"
   tell application process "Numbers"
       set frontmost to true
       click menu item autofillDown_loc of menu 1 of menu item autofillCells_loc of menu 1 of menu bar item Table_loc of menu bar 1
   end tell
   
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) mardi 4 septembre 2018 15:32:36

Last edited by Yvan Koenig (2018-09-04 10:32:37 am)

Offline

 

#8 2018-09-04 10:19:22 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4720

Re: Adding rows to Numbers AppleScript

Yvan Koenig wrote:

If you are not running Numbers in English you will have to make some changes.


Thanks, Yvan. It's interesting that the function name's localised as well.

"Autofil Cells" should be "Autofill Cells". It doesn't actually error until the script attempts to access the menu item.


NG

Offline

 

#9 2018-09-04 10:35:48 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3287

Re: Adding rows to Numbers AppleScript

Thanks Nigel.

Oops, I made a typo writing Autofil Cells when it was supposed to be Autofill Cells with two l !

I edited the script above.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) mardi 4 septembre 2018 18:35:43

Offline

 

#10 2018-09-04 09:20:04 pm

Pharmwheel
Member
Registered: 2018-09-01
Posts: 4

Re: Adding rows to Numbers AppleScript

Thanks for all the help with the script!  I've added in a dialog to ask for the number of days to download and instructions as well as the expected output.  There are probably a few other tweaks I could make to ensure it doesn't fail but it works really well.

Here's the final code.  Thanks again for everyone's help.

Best Regards - Pharmwheel

Applescript:

tell application "Numbers"
   set Table_loc to localized string "Table" from table "TSApplication" # Added
   --> Tableau
   set STOCKH_loc to localized string "STOCKH" from table "TSCALCULATIONEngine" # Added
   --> ACTIONH
   set autofillCells_loc to localized string "Autofill Cells" from table "TSTables" # Added, Oops I dropped one l !
   --> "Remplir automatiquement les cellules"
   set autofillDown_loc to 3 # Added
   
   activate
   -- This script will run on a Numbers table with Ticker Stock symbols in column A and corresponding historical start dates in column B. The script will ask for the number of historical days to download and work backwards from the column B date excluding weekends. The rest of the spreadsheet should be empty. The first row can be used for the headings but is not required for the script to run. I am capping the number of days at 45 to prevent a very large table. However, nothing over 10 rows has been tested.
   -- The output will generate the following columns B = date of historical data; C = Price (at close); D = Volume; E = High; F = Low
   -- USES GUI SCRIPTING. THE APPLICATION RUNNNING THE SCRIPT MAY NEED TO BE OK'd FOR THIS FIRST TIME. (System Preferences -> Security & Privacy -> Accessibility.)
   
   tell document 1
       tell active sheet
           set the selectedTable to (the first table whose class of selection range is range) --need to select at least 1 cell in the target spreadsheet
       end tell
       
       repeat -- Get the numbers of historical days from the user and validate that it's a number between 2 and 45
           try
               display dialog "How many days do you want to download? Input a number from 2 to 45 days" default answer ""
               set daysToDownload to text returned of result as integer
               
               if daysToDownload > 1 and daysToDownload < 46 then
                   exit repeat
               end if
           on error number -1700 -- The user didn't enter a number
           end try
           display dialog "You must enter a number between 2 and 45 days" buttons {"Enter again", "Cancel"} default button 1
       end repeat
       
       
       tell selectedTable
           
           -- store the row to copy as the reference row of the table; we pick the last row assuming that each row is a unique stock symbol
           set lastRow to row count
           --Increase the size of the table to what will be required, assuming that row 1 is blank or contains heading. This extends any formula runs at the end of the table
           set row count to (lastRow - 1) * daysToDownload + 1
           
           -- Move the existing data down-table and insert the new.
           repeat with originalRow from lastRow to 2 by -1
               set startOfBlock to (originalRow - 2) * daysToDownload + 2
               
               -- Set this block's stock symbols en masse.
               set value of cells startOfBlock thru (startOfBlock + daysToDownload - 1) of column 1 to value of cell originalRow of column 1
               
               -- Set the first date in the block to the original.
               set value of cell 2 of row startOfBlock to value of cell 2 of row originalRow
               set thisDate to value of cell 2 of row originalRow
               set value of cell 2 of row startOfBlock to thisDate
               
               -- Calculate the preceding work dates and insert them into the block's remaining date cells.
               repeat with j from (startOfBlock + 1) to (startOfBlock + daysToDownload - 1)
                   set w to thisDate's weekday
                   if (w is Monday) then
                       set thisDate to thisDate - 3 * days
                   else if (w is Sunday) then
                       set thisDate to thisDate - 2 * days
                   else
                       set thisDate to thisDate - days
                   end if
                   
                   set value of cell 2 of row j to thisDate
               end repeat
           end repeat
           
           --Insert the STOCKH formula for C2 through F2 in the 2nd row
           set value of cell "C2" to "=STOCKH(A2,0,B2)" --Set Price at close
           set value of cell "D2" to "=STOCKH(A2,4,B2)" --Set Volume
           set value of cell "E2" to "=STOCKH(A2,2,B2)" --Set High
           set value of cell "F2" to "=STOCKH(A2,3,B2)" --Set Low
           --Select the whole of those four columns except for row 1
           set selection range to range ("C2:F" & (row count))
           
       end tell
   end tell
end tell

-- Click the "Autofill Down" item under "Autofill Cells" in the "Table" menu.

tell application "System Events"
   tell application process "Numbers"
       set frontmost to true
       click menu item autofillDown_loc of menu 1 of menu item autofillCells_loc of menu 1 of menu bar item Table_loc of menu bar 1
   end tell
end tell

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)