How Do I Combine Multiple Numbers Sheets Into Existing One?

At this time, you haven’t downloaded the second set of files.

The script named 4TheREDNAVE .scpt does the job with no extraneous row.

May you execute my script upon my sample files with no change ?

Just drag and drop the file mainDocument.numbers and the folder Daily Sheets on the desktop.
Execute the script named 4TheREDNAVE .scpt

In the script I carefully wrote : # I’m not sure that the upper row to import is the same in every tables

So I define several ones.

As in your first script you cleared the contents of tables starting from row 2 I assumed that the script is not supposed to paste the values from row 1.
This is why I defined :
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upper3_1 to 2
set upperInMain to 2

If you want to transfer the row 1 you just have to edit as :
set upper1_1 to 1
set upper1_2 to 1
set upper1_6 to 1
set upper3_1 to 1
set upperInMain to 1

From my point of view it would be a bad idea because when it will transfert values from the 2nd people file, if you set the values to 1, you will retrieve the contents of headers from this 2nd document in the main document just below the lower row containing a value imported from the 1st file.

At this time, the datas from 2nd file are pasted EXACTLY below the lower row containing a value imported from the 1st file.

Always the same kind of problems when the asker doesn’t give every relevant informations.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) lundi 10 avril 2017 21:43:13

First off, THANK YOU so much for your help so far. I would never have made it on my own.
Your new set of files does work flawlessly.
When run with my data, the only issue now that I cant figure out is that is it adding empty rows in between pasting from file 2. So if file 2 had 13 empty rows in table 1, it will put 13 empty rows after file 1’s data. is there any way to just have it skip adding a column which has an empty cell (the first columns for all table’s should ALWAYS have data, if not then I wouldn’t need data from the other columns. Let me know if you need any other info to figure this last issue out.

Thanks again!

The script check that cells are really blank (value of theCell is missing value).

You wrote :
[format]3) jump between each needed table for two sheets, and copy the entered data from the specific columns (and stop at the last row that had data in it preferably)[/format]

If in your peoples files, some cells aren’t really empty but contain a string of one of several space characters or even an empty string the script treat them as cells with something stored.

To get rid of that one instruction must be edited:

if value of cell i is not missing value then exit repeat
# must become:
if value of cell i is not in {missing value, "", space, space & space, space & space & space} then exit repeat

If what I described is really the problem, it would be a good idea to make a bit of cleaning in your peoples files.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) lundi 10 avril 2017 22:10:49

I’m still confused why it is putting empty rows in between instead of simply adding a row below and pasting there. I sent you a PM of a video recording of what I see.

But I also noticed that if a column is totally blank, it will paste in the first header row instead. Is there a way to stop it from doing that if a column happens to be empty?

Lastly, I don’t know if this is related to the empty rows, but I noticed some columns are missing the formulas and some properly carried their formulas down with the pasted data. This is my fault and something wrong with the spreadsheet itself.

(1) a video will not help
(2) the problem of formulas is easy to solve. I was just unable to guess that the people documents contained calculated values !
(3) Now that I know that some imported cells contain formulas, I’m quite sure that the explanation about blank lines is due to that : formulas supposed to return a “blank” cell set the result to a string which may be an empty one, a one space one.
Please check that before asking for changes in the script.

May you anonymise the content of a people file and send it to: k o e n i g . y v a n @ s f r . f r ?

With such a file I will be able to know what may fool the script - if something does that.

But maybe I don’t understand what you describe.
The script is designed to transfer every cell from a column, starting from row 2 (or 1 if you edited the code) thru the lower cell which is not empty.
If rows are empty in the people file but some rows below contain values, they are transferred.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mardi 11 avril 2017 10:10:43

Well none of the columns being copied have a formula in them. Some of the columns have check boxes though. Could those be throwing it off since they are either true/false?

It would be more fool proof to calculate the last row based off the first column used, since all the data depends on the values in the first column to be copied. (The first column is never a checkbox, always text or number). If the first column doesn’t have a value in its row, then the rest of the row won’t need values to be copied.

Simply put, If a row in the first column to be copied from the table (usually column A or B) is empty/blank/missing value/spaces, it should stop there and not bother copying the other columns past that row.

If it was setup that way I think that would solve all the unforeseen issues.

I’ll see what I can do about anonymity to the document but the file I sent should still show which columns have rows and which do not. Again, NO columns to be copied will have formulas in them.

Thanks again.

Your last message gave an info which would had be given since the very beginning.
Maybe other useful ones are not given at this time.

I’m tired to have to guess how the source files are organized.

The script is already seriously enhanced here but I won’t post new code until I have a sample of the source file.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mardi 11 avril 2017 16:12:22

I completely understand, there are too many assumptions.

here is a link to my files with random names and data in a more realistic view.

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

Now I understand better.

In the late script which I sent you, there is a line spelled:

SO DATAS FROM THE NEXT PEOPLE WILL BE PASTED STARTING FROM THE SAME ROW IN EVERY TABLE.

or maybe

So datas from the next people will be pasted starting from the same row in every table.

They describe the way I understood your requirements.

As the table PASTE HERE of sheet PASTE HERE of people 1 contains 93 rows filled with datas the four tables in the “empty master.numbers” document will receive 93 rows.

Other point, a cell containing a checkbox is never an empty cell so the script saw 25 useful rows in Watch List and in Scheduled CallBacks. It saw 17 filled rows in Voicemail Tracker.

Knowing that I will have to redesign the entire script.

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mardi 11 avril 2017 17:28:56

Ah yes, that does make sense now! I’m so sorry I didn’t realize some of these small details could be so important!

I’m glad you and I understand each other now.

Here is the script rebuilt to match what I discovered in the real files.

As I’m not a sooth sayer, I was unable to guess that three tables have an header row but that the 4th one hasn’t !

It’s not the script which transfer formulas. They are stored in your target file (empty master.numbers).

(*
master merge.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
*)

script o
	# These properties will be visible from the handler but will not be saved on disk
	property peopleNames : {}
	property targetName : missing value
	property columnNames : {}
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
# Build a list used to test groups of contiguous columns
set o's columnNames to {}
repeat with i from 65 to 78
	set end of o's columnNames to character id i
end repeat

# 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", "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", {"K", "L", "M", "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", "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", {"E", "F", "G", "H", "I", "J", "K", "L", "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", {"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", {"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", {"A", "B", "C", "D", "E", "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"

#=====

on importFrom(aPeopleName, sheetName, tableName, theColumns, higherRowInPeople, higherRowInTarget, maxNumber)
	local 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 ((count theColumns) > 1) and (o's columnNames) contains theColumns then
		
		# transfer the group of contiguous columns
		tell application id "com.apple.iwork.numbers"
			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
				set theRange to (name of cell higherRowInPeople of column (theColumns's item 1)) & ":" & (name of cell (maxNumber) of column (theColumns's item -1))
				set selection range to range theRange
			end tell # document
		end tell # Numbers
		my copyPasteSelection(aPeopleName, sheetName, tableName, higherRowInTarget, (theColumns's item 1))
		
	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 maybe to i - higherRowInPeople + 1
					if (maybe > maxNumber) then set maxNumber to maybe
					set selection range to range theRange
				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}
	end tell
end copyPasteSelection

#=====

Yvan KOENIG running Sierra 10.12.4 in French (VALLAURIS, France) mardi 11 avril 2017 19:36:09

WOWOWOW
IT WORKS! BEAUTIFULLY!

THNAK YOU SO MUCH!!!

It is exactly what I was needing. This will save countless hours not just for me but many other people.

Now, for extra credit. You can decline if it’d be too hard. How difficult would it be to first ask for a password to be typed out in a dialog box first, so that when it opens each file from folder and asks for the password, it will just automatically fill it in? (again, all files use the same password) It’s just mildly tedious to have to type it after script opens each file.

If that is possible then it would be totally hands free.

THNAK YOU AGAIN SO MUCH!

After some thought and research, I decided it simple enough to just ask for the password to be securely entered once, so it can be copied to the clipboard automatically and is ready to paste into the document prompt(s) if needed easily.
I just put this above where it would ask to choose the main document. Hope this project can help others down the road who may have similar questions.

display dialog "What is the document password?

I will copy it to the clipboard for you!" default answer ""  with hidden answer
set thePW to text returned of result
set the clipboard to thePW

I just discover your messages.

Thanks for the feedback.
I never used password protected Numbers document so I let you treat the problem.

Below is an alternate version.

For each treated table, it copy paste in a single action the cells from every columns starting from the first one defined in the caller instruction.

On my side I see no difference in the final result but it’s REALLY FASTER.

(*
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", "N"}, 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", "M"}, 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", "E"}, 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", "F"}, 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, theColumns, higherRowInPeople, higherRowInTarget)
	local i, theRange
	# transfer every columns starting from (theColumns's item 1)
	tell application id "com.apple.iwork.numbers"
		tell document aPeopleName to tell sheet sheetName to tell table tableName
			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
			set theRange to (name of cell higherRowInPeople of column (theColumns's item 1) & ":" & (name of cell (maxNumber) of column (theColumns's item 2)))
			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 (theColumns's item 1))
			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} # paste applying the style defined in the receiver
	end tell
	return maxNumber
end importFrom

#=====

Is it really useful to keep thousands of rows in the table “PASTE HERE” ?

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

I still need to review your newest version and what you changed. I just used it in a realistic test. The last version took 57 seconds. This new version took just 47 seconds. The data appears to be the same but the old version handled the columns better. This new version seems to be adding rows that don’t respect conditional highlighting rules for some reason. Also near the end, the new rows are added to the point where they overlap the scheduled callbacks table. It seems the new version is copying the formulas correctly into the new rows, but resetting the formatting and conditional highlighting; and not bumping down the table so it doesn’t overlap the table beneath it. But yes, it is noticeably faster. I’ll probably stick with the last version as there were less issues.

table

In the good old iWork’09, we were able to ask the app to move the tables when one was receiving new rows.
I didn’t saw this feature in the “modern” version but maybe I missed it.

Oops, I missed that we don’t have to move the very last columns. I will repair that soon.

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

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