Using AppleScript with Numbers to synchronize two open documents

Question

Level 1

8 points

Using AppleScript with Numbers to synchronize two open documents

I have two Numbers documents used for a fantasy sports draft. Both documents need to be open on the same MacBook during the draft but one document will always be the active window for the operator and the other document will run in a passive window in a separate desktop and be projected to an external display for the rest of the draft participants to see.

The active document Draft is used to enter players selected by each fantasy owner. The primary input cell is a search field in a row which will trigger a lookup in a players database and return a unique ID# along with additional player information such as position and team.

The passive document Board is simply a matrix display of the draft progress. It uses the same background data as Draft to fill the contents of the draft board.

This fantasy draft usually takes 32 rounds (only 12 shown here) and is a session lasting several hours with hundreds of rows of data.

It is known this operation can be done by using separate devices, such as an iPad to enter the draft picks and the MacBook to display the draft board, and letting them synchronize using iCloud. However, this requires a reliable network connection (not always available) and results in a delay of 15-30 seconds between data entry and board update which is unacceptable.

The objective is as each row is completed in the Draft document, to send the value of the selected player's ID# to the Board document. So this as simple as piping the value of a single cell from one document to another.

Both documents share several identical tables, such as the list of Owners and a Players database. But most importantly, they each have a Picks table which will have the exact same dimensions, i.e. number of columns and rows. Consequently, the cell to synchronize will have the exact same address in the Picks table in both documents.

I’m trying to use AppleScript. However, I have no experience with it so am struggling with several I'm sure basic issues, most notably how to operate with two open Numbers documents at once.

Here is a view of the primary windows of each of the two documents, Draft and Board.

In Draft, the first row has already been entered, by typing the string "yama" into the search field (E52). The LOOKUP formula in cell F52 concatenates the search string with "*" to enable a wild card lookup in the Players database and returns that players ID# along with additional information.

The results are displayed in Row 3, which is in the frozen table header to allow scrolling of the entry rows below. Row 3 shows the Owner, Round, and ID#, Name, Position and Team of the selected Player. Also in this row are two key fields for controlling the script - the Cell column contains the address of the cell (ID#) in the Picks table to be sent from Draft to Board, and the Status column indicates whether or not this ID# has been sent (by the script).

So in this instance, the operator entered "yama" for Owner = Lincoln in the search field, which filled the rest of the row with ID# = 1183, Player = YAMAMOTO,Yoshinobu, Pos = P, and Tm = LAD. The cell to be synchronized is at the address F52, which contains the value 1183. The idea is when the script executes and sends the value at F52 from Draft to Board, it then sets the semaphore (Status) to TRUE.

Meanwhile, the new active row becomes Row 55, its Status is initialized to FALSE, and once entered will reset the contents of Row 3 with owner Jefferson's pick, and so on.

Here is my attempt using AppleScript, opening only one Numbers document. But I am stuck.

MacBook Pro (2017 – 2020)

Posted on Apr 7, 2024 6:59 AM

Reply

Answer 1

Top-ranking reply

smrc8081 Author

Level 1

8 points

Apr 13, 2024 7:29 PM in response to smrc8081

P.s.

set Focus to (sheet BoardSheet of boardDoc)

FAILS. Focus remains on “Results”

Reply

Answer 2

SGIII

Level 8

37,532 points

Apr 13, 2024 8:55 PM in response to smrc8081

'set focus to' is not in the AppleScript dictionary for Numbers, right?

Have you tried something that is in the dictionary, perhaps something like this?

tell table "yourTableName " of sheet "yourSheetName" of document "yourDocumentName"

SG

Reply

Answer 3

SGIII

Level 8

37,532 points

Apr 13, 2024 4:28 PM in response to smrc8081

You don't post your script so it's hard to give specifics.

Have you tried "telling" sheet "Board" of document "X", perhaps followed by an activate statement?

SG

Reply

Answer 4

smrc8081 Author

Level 1

8 points

Apr 13, 2024 7:04 PM in response to SGIII

Here is a pared down version of the script…

—————

set draftFile to choose file of type "numbers" —“Draft.numbers”

set draftSheet to "Entry"

set draftTable to "Picks"

set boardFile to choose file of type "numbers" —“Board.numbers”

set boardSheet to "Results"

set boardTable to "Selections"

set IDcell to “F53” —location of field to share

set focusSheet to "Board" —want this sheet mostly active

tell application "Numbers"

set boardDoc to open alias boardFile

set draftDoc to open alias draftFile

activate

—Get ID# from Draft doc

set IDnum to (value of cell IDcell of table draftTable of sheet draftSheet of draftDoc) —value of field to share

—Send to Draft Board

set (value of cell IDcell of table boardTable of sheet boardSheet of boardDoc) to IDnum —this changes focus to sheet “Results”

--Reset focus to Draft Board “Board”

set Focus to (sheet BoardSheet of boardDoc)

end tell --application Number

Reply

Answer 5

SGIII

Level 8

37,532 points

Apr 12, 2024 6:21 AM in response to smrc8081

In theory AppleScript can copy values from one document to another. In practice this becomes clunky if you want the values to be updated constantly without triggering by user.

Here's a script to copy values. You of course need to substitute your own document names. etc.

property src1 : {doc:"Source1.numbers", sht:"Sheet 1", tbl:"Table 1", rng:"A2:B4"}
property tgt1 : {doc:"Target.numbers", sht:"Sheet 1", tbl:"Consolidated", rng:"A2:B4"}

property src2 : {doc:"Source2.numbers", sht:"Sheet 1", tbl:"Table 1", rng:"A2:B4"}
property tgt2 : {doc:"Target.numbers", sht:"Sheet 1", tbl:"Consolidated", rng:"A5:B7"}


copyVals(src1, tgt1)
copyVals(src2, tgt2)

to copyVals(src, tgt)
	tell application "Numbers"
		try
			--read values in source range into AppleScript list
			tell document (src's doc)
				tell sheet (src's sht)
					tell table (src's tbl)
						tell range (src's rng)
							set vv to cells's value
						end tell
					end tell
				end tell
			end tell
			
			--write values from list to target range
			tell document (tgt's doc)
				tell sheet (tgt's sht)
					tell table (tgt's tbl)
						tell range (tgt's rng)
							repeat with i from 1 to count cells
								set cell i's value to vv's item i
							end repeat
						end tell
					end tell
				end tell
			end tell
		on error
			display alert "Check addresses, ranges, and size of destination table" buttons "Ok"
		end try
		
	end tell
end copyVals

To get that to run automatically is another issue. I understand there are various ways to set up "cron jobs" on the Mac that will run a script. For me, that is too involved to be practical, but you may feel otherwise. You could consider putting the script in Run AppleScript action in a Shortcut in the Shortcuts app and using the third-party app Shortery (available on the Mac App Store) to run the shortcut automatically. But I haven't tried that and suspect it may be too much "overhead" for what you are trying to do.

SG

Reply

Answer 6

smrc8081 Author

Level 1

8 points

Apr 9, 2024 10:48 PM in response to smrc8081

The complexity of this scenario seems to have inhibited any replies. Let me simplify it.

I have two Numbers documents which each contain a table of identical dimensions, i.e. exact same number of columns and rows. I need to keep both document tables in sync, without having to copy/paste cells or toggle between windows. I can have both documents open concurrently but only one in an active window. Due to the restriction where Numbers can only have one window opened per document, I believe the only way to accomplish this operation is by using AppleScript.

The desired operation is simple. Take the value of a single cell in Document 1 / Table A and copy it directly into a cell at the same address in Document 2 / Table B.

Ultimately, this operation would run entirely in the background. It would repeat for hours, as data is entered into the cells/rows in Table A it would be sent to Table B, until the end of the session is detected (controlled by semaphores).

Here is an example of the documents and tables but most importantly, a script. I am not a novice coder but new at AppleScript. I have the AppleScript 1-2-3 book but can’t seem to find many consistent examples of AppleScript and Numbers, and none operating on two documents/tables at once. I seem hung up some on syntax and handling of objects, but it is also confusing to me how to direct the script to do one thing with one document and something else with another document. In other words, it would seem to me I would need some sort of handle to differentiate Document 1/Table A from Document 2/Table B. Is this accomplished within the "Tell document" structures?

Below are images of the Document 1 ("Source") / Table A ("Picks"), and

Document 2 ("Destination") / Table B ("Log"),

and the script attempting the operation ("Synchro"). The error is shown in the Result pane of the script window.

I would greatly appreciate any advise, corrections, suggestions and any help.

Reply

Answer 7

smrc8081 Author

Level 1

8 points

Apr 13, 2024 3:41 PM in response to SGIII

Very helpful and useful, thank you. I got it working between the two documents in my original (complex) scenario, and running automatically in a repeat loop.

Now another question...

For reference:

Document1 = Draft (Sheet = Picks, Table = Picks)

Document2 = Board (Sheet = Picks, Table = Picks)

When document Board is opened, it is set to sheet Board (to display the Draft Board). I wish to keep the Draft Board always displayed in that document window.

However, when the script modifies the ID# cell in Board/Picks/Picks the window switches focus from the Board sheet to the Picks sheet.

How do I have the script make the Board window leave the Picks sheet go back to the Board sheet?

Reply

Answer 8

smrc8081 Author

Level 1

8 points

Apr 14, 2024 9:40 AM in response to smrc8081

This worked:

--BoardSheet = "Board"

set (name of sheet BoardSheet of boardDoc) to BoardSheet --resets focus to Draft Board

Kind of a no-op but sets the focus on that Sheet without activating any cell/table in that sheet.

Thanks for all the help!

Reply

Answer 9

smrc8081 Author

Level 1

8 points

May 25, 2024 11:05 PM in response to smrc8081

FYI I got this all working with a Script and the two intended documents, an active operational tracker/processor and a passive display sheet. I overcame struggles to open the documents, manage focus, and read/write cells in tables in both documents. I also implemented interactive operations to facilitate and validate user actions. Got it all working!

Way too much logic to show here but glad to share offline if there is any interest.

Reply

Answer 10

smrc8081 Author

Level 1

8 points

Jun 4, 2024 11:10 PM in response to SGIII

Greetings again. I thought I had everything working. Turns out almost. Now doing a trial run... the script has two Numbers documents open on two separate Desktops/Displays... the main document is open on my Main Display, and the logging document is open on my Extended Display. All good. Except one problem. When I run my script, the Display Dialog window appears NOT on the Main Display, which I want, but on the Extended Display, which I do NOT want. I can't figure out why, or how to control that. I've tried setting focus in the script to the main document, but that seems to make no difference...

Except now it came up randomly on the main display, don't ask me why because I cannot replicate it.

How do I direct Display Dialog Windows to appear on the Main Display and not an Extended Display?

Reply