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
