Monday, October 23, 2017

#26 2017-04-12 11:52:50 am

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Yvan Koenig wrote:

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.

Applescript:

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

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}
   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

Offline

 

#27 2017-04-12 12:16:04 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Last edited by Yvan Koenig (2017-04-12 12:21:04 pm)

Offline

 

#28 2017-04-12 12:25:55 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: 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?

Offline

 

#29 2017-04-12 12:34:33 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Last edited by Yvan Koenig (2017-04-12 12:40:11 pm)

Offline

 

#30 2017-04-12 12:46:03 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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?


Yvan Koenig wrote:

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

Offline

 

#31 2017-04-12 01:49:24 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

I edited the paste instruction as :

Applescript:

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

Offline

 

#32 2017-04-12 02:08:04 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

I changed it to this:

Applescript:

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 wrote:

I edited the paste instruction as :

Applescript:

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

Offline

 

#33 2017-04-12 02:11:39 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Applescript:

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

Offline

 

#34 2017-04-12 02:13:58 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Offline

 

#35 2017-04-13 04:10:49 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

Applescript:

keystroke "v" using {command down}

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


Applescript:

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

Offline

 

#36 2017-04-13 11:30:25 am

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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.

Offline

 

#37 2017-04-13 01:48:34 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Last edited by Yvan Koenig (2017-04-13 01:51:33 pm)

Offline

 

#38 2017-04-13 02:07:07 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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.

Applescript:

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

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

#=====

Offline

 

#39 2017-04-13 02:29:06 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Offline

 

#40 2017-04-13 02:36:59 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Yvan Koenig wrote:

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

Offline

 

#41 2017-04-14 04:23:53 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

Thanks.
It seems that I got it.

Applescript:

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

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

Last edited by Yvan Koenig (2017-04-14 04:51:01 am)

Offline

 

#42 2017-04-14 09:04:40 am

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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?

Applescript:

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

Offline

 

#43 2017-04-14 11:22:44 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Applescript:

--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

Offline

 

#44 2017-04-14 12:22:04 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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?

Offline

 

#45 2017-04-14 12:30:10 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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.

Offline

 

#46 2017-04-14 02:46:53 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3200

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

An exercise for the asker:

edit your notification so that it displays the required time. 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

Offline

 

#47 2017-04-16 06:00:31 pm

TheREDNAVE
Member
Registered: 2015-11-21
Posts: 38

Re: How Do I Combine Multiple Numbers Sheets Into Existing One?

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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)