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!

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

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

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. :slight_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:

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

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

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:

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

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?

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

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! :slight_smile:

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 …

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:

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:

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

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

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.

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

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

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