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