Help with a script to automate data entry

Question

Created Mar ’24

Replies 2

Boosts 0

Participants 2

I am trying to write a script that will perform an operation in Numbers document TEST on sheet GAMES (it’s a bunch of random number generations for a game show simulator) look at the output in cell B66 of that sheet, output it in cell B3 on another sheet called TRIALS2 then perform the operation again and output the new result on sheet TRIALS2 in cell B4 and so on for 2000 repeats. Here’s the script I have but I get an error message. The document is open and I have confirmed all the document and sheet names are correct. There is definitely something in cell B66 of Sheet GAMES in document TEST. What am I missing?

tell application "Numbers" activate tell document "TEST" repeat 2000 times tell sheet "GAMES" set inputValue to value of cell "B66" end tell tell sheet "TRIALS2" set table1 to table 1 set outputColumn to column "B" set nextRow to (get cell (3 + (count of rows of table1)) of outputColumn) set value of nextRow to inputValue end tell end repeat end tell end tell

Here is the error message: error "Numbers got an error: Can’t get cell "B67" of sheet "GAMES" of document "TEST"." number -1728 from cell "B67" of sheet "GAMES" of document "TEST"

Boost

Answer 1

Todio OP

Mar ’24

Someone on another forum tried to help by writing a macro for Excel but I failed to tell him I was using Numbers so, of course, it doesn't work but they did give me a clue in their attempt. I need to write a script that: (I can change the names later) -calls up the document *** -calls up Sheet1 -copies the value of Sheet1, cell B67 -calls up sheet Sheet2 -pastes the result in cell A3 of Sheet2 -inserts a new column (or row) A on Sheet2 (preserving previous result) -recalculates Sheet1 -copies the result from Sheet1, cell B67 to column A of Sheet2 in cell A3 and do that 2000 times

Can any good apple script writers out there help me achieve this?

Thanks in advance

0

Answer 2

kmitko OP

Mar ’24

It would be much easier to debug if you provided your document, your explanation of what you are trying to achieve is not super clear to me. I assume you start with no data in sheet 2 and you don't care that I'm overwriting data in sheet 2 - is this true? Anyway, please go through this code and read the comments. It takes maybe 2-3 minutes on my computer to finish the script.

 tell application "Numbers"
	activate
	-- the following code is just to show you how do I think your document looks like
	make new document
	tell front document
		-- there should already be one sheet, let's rename it to GAMES
		set name of active sheet to "GAMES"
		-- create sheet TRIALS2
		-- this should also create table 1
		make new sheet
		set name of active sheet to "TRIALS2"
		-- making sure table 1 has at least 67 rows, otherwise we cannot access row 67
		if (row count of table 1 of sheet "GAMES" < 67) then
			set row count of table 1 of sheet "GAMES" to 67
		end if
		-- making sure table 1 has at least 2 columns, otherwise we cannot access column B
		if (column count of table 1 of sheet "GAMES" < 2) then
			set column count of table 1 of sheet "GAMES" to 2
		end if
		-- inserting some value into cell B67
		set value of cell "B67" of table 1 of sheet "GAMES" to random number
	end tell
	-- this is when to start if you already have "GAMES" and "TRIALS" set up
	tell front document
		-- we need 2003 rows because we do 2000 repeats 
		set row count of table 1 of sheet "TRIALS2" to 2003
		repeat with rowNumber from 3 to 2003
			-- copy value of one cell to another
			-- target for copy operation is cell A3, A4, A5...A2003
			set value of cell ("A" & (rowNumber as text)) of table 1 of sheet "TRIALS2" to (value of cell "B67" of table 1 of sheet "GAMES")
			-- generate new random value in cell B67
			set value of cell "B67" of table 1 of sheet "GAMES" to random number
		end repeat
	end tell
end tell

0

	tell application "Numbers"
	activate
	-- the following code is just to show you how do I think your document looks like
	make new document
	tell front document
	-- there should already be one sheet, let's rename it to GAMES
	set name of active sheet to "GAMES"
	-- create sheet TRIALS2
	-- this should also create table 1
	make new sheet
	set name of active sheet to "TRIALS2"
	-- making sure table 1 has at least 67 rows, otherwise we cannot access row 67
	if (row count of table 1 of sheet "GAMES" < 67) then
	set row count of table 1 of sheet "GAMES" to 67
	end if
	-- making sure table 1 has at least 2 columns, otherwise we cannot access column B
	if (column count of table 1 of sheet "GAMES" < 2) then
	set column count of table 1 of sheet "GAMES" to 2
	end if
	-- inserting some value into cell B67
	set value of cell "B67" of table 1 of sheet "GAMES" to random number
	end tell
	-- this is when to start if you already have "GAMES" and "TRIALS" set up
	tell front document
	-- we need 2003 rows because we do 2000 repeats
	set row count of table 1 of sheet "TRIALS2" to 2003
	repeat with rowNumber from 3 to 2003
	-- copy value of one cell to another
	-- target for copy operation is cell A3, A4, A5...A2003
	set value of cell ("A" & (rowNumber as text)) of table 1 of sheet "TRIALS2" to (value of cell "B67" of table 1 of sheet "GAMES")
	-- generate new random value in cell B67
	set value of cell "B67" of table 1 of sheet "GAMES" to random number
	end repeat
	end tell
	end tell