How Do I Combine Multiple Numbers Sheets Into Existing One?

Yes it is strange, your original version moved the table down properly. Not sure why the newest version doesn’t do it as well?

I edited the script in message #25.
Now It define the first and the last column to transfer.

I tested it with 5 people documents.

Here the tables are correctly moved. In the Table settings, “Fix Header row” is checked. (not sure that it’s the exact English wording, it’s the menu item between [Header Rows] and [Header columns]).

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mercredi 12 avril 2017 19:34:06

You’re so smart! That fixed the overlapping tables. However, the new rows added still don’t inherit the formatting of text (font, bold, alignment, etc), and they lose their conditional highlighting rules as well?

I edited the paste instruction as :

keystroke "v" using {command down, option down, shift down} # paste applying the style defined in the receiver

Is it doing what you want ?

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mercredi 12 avril 2017 20:49:19

No, in fact that made it worse. Then it skips the PASTE HERE pasting completely?

I changed it to this:

tell application "System Events" to tell process "Numbers"
		tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item (o's targetName) # brings the target window to front
		keystroke "v" using {command down, option down, shift down} # paste applying the style defined in the receiver
	end tell

Isn’t it what is available in message #25 ?

 tell application "System Events" to tell process "Numbers"
       tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item (o's targetName) # brings the target window to front
       keystroke "v" using {command down, option down, shift down} # paste applying the style defined in the receiver
   end tell

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mercredi 12 avril 2017 21:11:25

Correct, #25 script will skip pasting the PASTE HERE since the keystroke paste line was changed. Not sure why it would affect that. But yes, it did not correct the formatting issue either anyways

keystroke "v" using {command down} 

triggers the menu item [Paste] which paste datas with the formatting attributes of the source

keystroke "v" using {command down, option down, shift down} 

triggers the menu item [Paste and match Style] which paste datas applying the style in use in the target cells.

In the files which you sent to me, in the people files AND in empty master.numbers, in every tables triggered by the script, the style is defined as Tahoma Bold 12.

I noticed a feature which may be the explanation.
In sheet [Watch List] the zoom factor is set to 76% while it’s set to 125% in sheet [PASTE HERE].

I don’t know how you defined a zoom factor of 76% because this value isn’t available in the dedicated menu.
Maybe you imported a Merdosoft document using this factor.
I changed the factor for the sheet [PASTE HERE] and defined it to the available value 75%
When the script finished its task, the factor was always 75% although it is 125% in the people files.

With such a setting, triggering [Paste] or [Paste and match Style] make no difference.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) jeudi 13 avril 2017 11:10:42

I’ve been playing with it this morning and still can’t explain why the formatting is getting reset to some sort of default for the new rows.

It respects the conditionally highlight rules and alignment if left to CMD V
It changes to align left and no conditional highlighting if same script uses SHIFT OPTION CMD V ??

It only affects the new rows added.
I did fix it so the PASTE HERE shows up with SHIFT OPTION CMD V though (I assume the text could have been white by default or something weird like that)
The last Numbers update lets you change the font globally, so I changed it all to SF Mono
I have changed all the sheets to use 75% zoom instead of 76%, same problem.

It’s a shame, because the PASTE HERE comes from a website, so it looks better with SHIFT OPTION CMD V

I’m debating adding more script to just select each column Row 2 to last row, and combine the highlighting rules for me, then set the alignment for each column. But that would add more variables that could cause issues down the road.

Are you working with the target file which you sent to me ?
Here the script never add new rows to [PASTE HERE] because the initial table has 1918 rows.

I repeat :
if you use {command down} the attributes of the original table are passed
if you use {command down, option down, shift down} the attributes of the target table are applied.
It’s the standard behavior of Numbers and I tested it to be sure that my memory wasn’t wrong.
For these tests I played with fonts, color, size, style and alignment.

To try to understand what you get, I must have the exact [empty master] file which you use for your test. I’m sure that it’s not the one which you sent to me.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) jeudi 13 avril 2017 20:48:20

using the script below, and the master named “Empty Master” that I sent you, I am seeing the formatting messed up in the Watch List sheet for the Watch List table. Row 26 onwards gets set to align left and loses conditional highlighting rules. Do you still have the Empty Master, and the People 1 and People 2 examples I sent you? I can send them again if needed.

(*
master merge alt.scpt
written for http://macscripter.net/viewtopic.php?id=45652

Consolidate datas from several Numbers documents in a single one.

This alternate version copy paste every columns of the 4 tables.

KOENIG Yvan, (VALLAURIS, France)
2017/04/12
*)

script o
	# These properties will be visible from the handler but will not be saved on disk
	property peopleNames : {}
	property targetName : missing value
end script

property transferHeaders : false
# true = transfer the row headers
# false = doesn't transfer the row headers

property twoSpaces : space & space
property threeSpaces : space & space & space
property windowMenu : 11 # Index of the menu "Window"

set path2Target to choose file with prompt "Please select today's Master:" of type {"com.apple.iwork.numbers.numbers", "com.apple.iwork.numbers.sffnumbers"} # Edit to fit your needs
set folder4peoples to (path to desktop as text) & "Daily Sheets" # Edit to fit you needs
tell application "Finder"
	set peopleFiles to every file of folder folder4peoples as alias list
end tell

# Now we have a list of the available people files
# The main document is supposed to be 
tell application id "com.apple.iwork.numbers"
	open path2Target # it's an alias
	set o's targetName to name of document 1
	
	set o's peopleNames to {}
	repeat with aPeopleFile in peopleFiles
		set aPeopleDoc to open aPeopleFile
		set end of o's peopleNames to name of aPeopleDoc
	end repeat
end tell
# Now we have a list of the available people documents

# params for tables from sheet Watch List
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upperInMain to 2

# params for table from sheet PASTE HERE
set upper3_1 to 1 # CAUTION, there is no header row in the table PASTE HERE !
set upperInMain3 to 1

set filledInWatchList to upperInMain
set filledInScheduledCallbacks to upperInMain
set filledInVoicemailTracker to upperInMain
set filledInPASTEHERE to upperInMain3

repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
	set rowsInWatchList to 0
	set rowsInScheduledCallbacks to 0
	set rowsInVoicemailTracker to 0
	set rowsInPASTEHERE to 0
	
	#----- treat table Watch List
	set rowsInWatchList to my importFrom(aPeopleName, "Watch List", "Watch List", "B", upper1_1, filledInWatchList) # Pass the starting column (here "B")
	set filledInWatchList to filledInWatchList + rowsInWatchList - 1 # don't repeat the header row
	
	#----- treat table Scheduled Callbacks
	set rowsInScheduledCallbacks to my importFrom(aPeopleName, "Watch List", "Scheduled Callbacks", "A", upper1_2, filledInScheduledCallbacks)
	set filledInScheduledCallbacks to filledInScheduledCallbacks + rowsInScheduledCallbacks - 1 # don't repeat the header row
	
	#----- treat table Voicemail Tracker
	set rowsInVoicemailTracker to my importFrom(aPeopleName, "Watch List", "Voicemail Tracker", "A", upper1_6, filledInVoicemailTracker)
	set filledInVoicemailTracker to filledInVoicemailTracker + rowsInVoicemailTracker - 1 # don't repeat the header row
	
	#----- treat table PASTE HERE
	set rowsInPASTEHERE to my importFrom(aPeopleName, "PASTE HERE", "PASTE HERE", "A", upper3_1, filledInPASTEHERE)
	set filledInPASTEHERE to filledInPASTEHERE + rowsInPASTEHERE # no header row in PASTE HERE
end repeat
display notification "The Master Has Been Merged!" with title "Thanks For Using Master Merger!" sound name "Glass.aiff"

#=====

on importFrom(aPeopleName, sheetName, tableName, theColumn, higherRowInPeople, higherRowInTarget)
	local i, theRange
	# transfer every columns starting from theColumn
	tell application id "com.apple.iwork.numbers"
		tell document aPeopleName to tell sheet sheetName to tell table tableName
			tell column theColumn
				repeat with i from 1 to (count rows)
					if value of cell i is in {missing value, "", space, twoSpaces, threeSpaces} then exit repeat
				end repeat
			end tell
			copy i to maxNumber
			set theRange to (name of cell higherRowInPeople of column theColumn & ":" & (name of cell (maxNumber) of column -1))
			set selection range to range theRange
		end tell # document
	end tell # Numbers
	
	tell application "System Events" to tell process "Numbers"
		set frontmost to true # REQUIRED
		tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item aPeopleName # brings the source window to front
		keystroke "c" using {command down}
	end tell
	
	tell application id "com.apple.iwork.numbers"
		# the clipboard contains the datas from a people
		tell document (o's targetName) to tell sheet sheetName to tell table tableName
			if (higherRowInTarget > (count rows)) then
				repeat (higherRowInTarget - (count rows)) times
					add row below last row
				end repeat
			end if
			set theRange to (name of cell higherRowInTarget of column theColumn)
			set selection range to range theRange
		end tell
	end tell # Numbers
	
	tell application "System Events" to tell process "Numbers"
		tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item (o's targetName) # brings the target window to front
		keystroke "v" using {command down, option down, shift down}
	end tell
	return maxNumber
end importFrom

#=====

May you repeat the tests with the script which is in message #25.
The one which you posted here is the old version which copied/pasted every columns starting from the defined starting one.
I will not waste time running an obsolete version as you are doing.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) jeudi 13 avril 2017 21:28:59

Apologies, I pasted the wrong one. I am using the script from #25

Here is a link to all files I’m using that experience the issue:

https://app.box.com/s/ydtt2cc0de8y9jljggb3szaikl5gyzkj

Thanks.
It seems that I got it.

(*
master merge new.scpt
written for http://macscripter.net/viewtopic.php?id=45652

Consolidate datas from several Numbers documents in a single one
KOENIG Yvan, (VALLAURIS, France)
2017/04/10
2017/04/13 use a flag to rule the mode areContiguous or aren't
*)

--use AppleScript version "2.4"
--use scripting additions
--use framework "Foundation"

script o
	# These properties will be visible from the handler but will not be saved on disk
	property peopleNames : {}
	property targetName : missing value
end script

property twoSpaces : space & space
property threeSpaces : space & space & space
property windowMenu : 11 # Index of the menu "Window"

set path2Target to choose file with prompt "Please select today's Master:" of type {"com.apple.iwork.numbers.numbers", "com.apple.iwork.numbers.sffnumbers"} # Edit to fit your needs
--set startDate to current application's NSDate's |date|()
set folder4peoples to (path to desktop as text) & "Daily Sheets" # Edit to fit you needs
tell application "Finder"
	set peopleFiles to every file of folder folder4peoples as alias list
end tell

# Now we have a list of the available people files
# The main document is supposed to be 
tell application id "com.apple.iwork.numbers"
	open path2Target # it's an alias
	set o's targetName to name of document 1
	
	set o's peopleNames to {}
	repeat with aPeopleFile in peopleFiles
		set aPeopleDoc to open aPeopleFile
		set end of o's peopleNames to name of aPeopleDoc
	end repeat
end tell
# Now we have a list of the available people documents

# params for tables from sheet Watch List
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upperInMain to 2

# params for table from sheet PASTE HERE
set upper3_1 to 1 # CAUTION, there is no header row in the table PASTE HERE !
set upperInMain3 to 1

set filledInWatchList to upperInMain
set filledInScheduledCallbacks to upperInMain
set filledInVoicemailTracker to upperInMain
set filledInPASTEHERE to upperInMain3

repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
	set rowsInWatchList to 0
	set rowsInScheduledCallbacks to 0
	set rowsInVoicemailTracker to 0
	set rowsInPASTEHERE to 0
	
	#----- treat table Watch List
	set rowsInWatchList to my importFrom(aPeopleName, "Watch List", "Watch List", false, {"B", "I"}, upper1_1, filledInWatchList, 0) # 0 to force to to grab the count of rows filled
	# transfer contiguous columns
	set rowsInWatchList to my importFrom(aPeopleName, "Watch List", "Watch List", true, {"K", "N"}, upper1_1, filledInWatchList, rowsInWatchList)
	set filledInWatchList to filledInWatchList + rowsInWatchList - 1 # don't repeat the header row
	
	#----- treat table Scheduled Callbacks
	set rowsInScheduledCallbacks to my importFrom(aPeopleName, "Watch List", "Scheduled Callbacks", false, {"A"}, upper1_2, filledInScheduledCallbacks, 0) # 0 to force to to grab the count of rows filled (I deliberately missed the brackets around "A")
	# transfer contiguous columns
	set rowsInScheduledCallbacks to my importFrom(aPeopleName, "Watch List", "Scheduled Callbacks", true, {"E", "M"}, upper1_2, filledInScheduledCallbacks, rowsInScheduledCallbacks)
	set filledInScheduledCallbacks to filledInScheduledCallbacks + rowsInScheduledCallbacks - 1 # don't repeat the header row
	
	#----- treat table Voicemail Tracker
	set rowsInVoicemailTracker to my importFrom(aPeopleName, "Watch List", "Voicemail Tracker", false, {"A"}, upper1_6, filledInVoicemailTracker, 0) # 0 to force to to grab the count of rows filled (This time I put the brackets around "A")
	# transfer contiguous columns
	set rowsInVoicemailTracker to my importFrom(aPeopleName, "Watch List", "Voicemail Tracker", true, {"D", "E"}, upper1_6, filledInVoicemailTracker, rowsInVoicemailTracker) # I deliberately missed the brackets
	set filledInVoicemailTracker to filledInVoicemailTracker + rowsInVoicemailTracker - 1 # don't repeat the header row
	
	#----- treat table PASTE HERE
	# transfer contiguous columns
	set rowsInPASTEHERE to my importFrom(aPeopleName, "PASTE HERE", "PASTE HERE", true, {"A", "F"}, upper3_1, filledInPASTEHERE, 0) # 0 to force to to grab the count of rows filled
	set filledInPASTEHERE to filledInPASTEHERE + rowsInPASTEHERE # no header row in PASTE HERE
end repeat
display notification "The Master Has Been Merged!" with title "Thanks For Using Master Merger!" sound name "Glass.aiff"
--set timeDiff to startDate's timeIntervalSinceNow()
--tell application (path to frontmost application as string) to display dialog "That took " & (-timeDiff as real) & " seconds."

#=====

on importFrom(aPeopleName, sheetName, tableName, areContiguous, theColumns, higherRowInPeople, higherRowInTarget, maxNumber)
	local firstColumn, lastColumn, aColumn, i, maybe, theRange
	
	# Take care of possible omission of brackets when there is a single column to treat
	if class of theColumns is not list then set theColumns to {theColumns}
	
	if areContiguous then
		# the list describes a group of contiguous columns
		set firstColumn to theColumns's item 1
		set lastColumn to theColumns's item -1 # use -1 in case the list contain the name of every columns to treat
		tell application id "com.apple.iwork.numbers" to tell document aPeopleName to tell sheet sheetName to tell table tableName
			if maxNumber = 0 then
				tell column firstColumn
					repeat with i from 1 to (count rows)
						if value of cell i is in {missing value, "", space, twoSpaces, threeSpaces} then exit repeat
					end repeat
				end tell
				copy i to maxNumber
			end if
			set theRange to (name of cell higherRowInPeople of column firstColumn) & ":" & (name of cell (maxNumber) of column lastColumn)
			set selection range to range theRange
		end tell # Numbers
		my copyPasteSelection(aPeopleName, sheetName, tableName, higherRowInTarget, firstColumn)
		
	else
		# transfer non contiguous columns
		tell application id "com.apple.iwork.numbers"
			repeat with aColumn in theColumns
				tell document aPeopleName to tell sheet sheetName to tell table tableName
					if maxNumber = 0 then
						tell column (theColumns's item 1)
							repeat with i from 1 to (count rows)
								if value of cell i is in {missing value, "", space, twoSpaces, threeSpaces} then exit repeat
							end repeat
						end tell
						copy i to maxNumber
					end if
					tell column aColumn
						set theRange to (name of cell higherRowInPeople) & ":" & (name of cell (maxNumber))
					end tell
					set selection range to range theRange
					set maybe to i - higherRowInPeople + 1
					if (maybe > maxNumber) then set maxNumber to maybe
				end tell # document
				my copyPasteSelection(aPeopleName, sheetName, tableName, higherRowInTarget, aColumn)
			end repeat
		end tell # Numbers
	end if
	return maxNumber
end importFrom

#=====

on copyPasteSelection(docSourceName, sheetName, tableName, targetRow, targetColumn)
	tell application "System Events" to tell process "Numbers"
		set frontmost to true # REQUIRED
		tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item docSourceName # brings the source window to front
		keystroke "c" using {command down}
	end tell
	
	tell application id "com.apple.iwork.numbers"
		# the clipboard contains the datas from a people
		tell document (o's targetName) to tell sheet sheetName to tell table tableName
			if (targetRow > (count rows)) then
				repeat (targetRow - (count rows)) times
					add row below last row
				end repeat
			end if
			set selection range to range (name of cell targetRow of column targetColumn)
		end tell
	end tell # Numbers
	
	tell application "System Events" to tell process "Numbers"
		tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item (o's targetName) # brings the target window to front
		keystroke "v" using {command down, option down, shift down}
	end tell
end copyPasteSelection

#=====

I left some instructions (disabled) used as a timer when I ran it.

It appears that pasting the full range of columns with a script doesn’t behave exactly as pasting the same range by hand. So I returned to the scheme pasting only the columns in which peoples insert datas.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) vendredi 14 avril 2017 11:19:53

Yes! It works now, you figured it out! That’s interesting to know how the script can behave differently than by hand.
Also, I tried to use your timer by re-enabling the 3 lines, but on run it said “NSDate doesn’t understand the “date” message.” Do you use something special that I don’t have for that?

set startDate to current application's NSDate's |date|()

I guess that you missed to enable three instructions at the very beginning.

--use AppleScript version "2.4" # Enable it
--use scripting additions # Enable it
--use framework "Foundation" # Enable it

script o
   # These properties will be visible from the handler but will not be saved on disk
   property peopleNames : {}
   property targetName : missing value
end script

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) vendredi 14 avril 2017 18:22:24

Hahaha I did not know those were commands, thought they were just notes!
In fact I might leave the timer on in the final version I like it so much; is there a way to just round the time to the closest second?

Nevermind, changed REAL to INTEGER! I’m learning!
Thanks again for all the help! I hope I can use some of this script in a future project I have in mind.

An exercise for the asker:

edit your notification so that it displays the required time. :stuck_out_tongue:

Leaving the decimal value is useful when we want to test pieces of code executing quickly to compare different versions.

Sometimes we may insert the code in a loop repeating the task 10 or 100 times but when the task moves a file we can’t use the loop trick.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) vendredi 14 avril 2017 21:46:47

Okay , the script has been working GREAT the last few days! The only thing that bugs me is having tons of documents open once the merge is done. IS there a way to tell it to close all the files it opened after the merge was completed?

Thanks