Saturday, December 16, 2017

#1 2017-04-10 12:28:23 am

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

How Do I Combine Multiple Numbers Sheets Into Existing One?

Friends, I'm having a hard time thinking through this one. I have a Numbers spreadsheet, in the morning a script is run to clear out specific columns and standardize the formatting. it is then dispersed to 4-7 people. They fill out those empty columns with new data throughout the day. Then at the end of the day I will need to then collect all their individual spreadsheets, copy all their columns (they all will have different amount of rows used) and paste them into the original from the morning. Back and forth , copy & paste until everyone's collected data is in one master document. (Not all tables will necessarily be used by everyone, but everyone maintains the same tables and columns; they are never deleted if empty) The cycle is then repeated for the next morning with this new master file.

I would like to:
1) At the end of the day, collect everyones spreadsheets and move them into a folder on desktop
2) run a script to open each spreadsheet in that folder one by one (which they are all password protected with the same password all the time)
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)
4) go to my active still blank "Master" spreadsheet, and paste their data into each respective table
5) repeat for each document in the desktop folder, pasting new data in the row below the previous
6) *bonus* rename this master doc as "Master [todays date]"

Is this script even feasible? I haven't found anything about iterating through Numbers spreadsheets that are password protected. Also, The script used to clear out the master involves the same table and column needed! So I already know the names and ranges of the data I would need. Just instead of clearing the cell, I want it to copy it, hop over to my master and paste. I just can't think of how to then paste a row below the last for the next doc. Any advice about building the foundation of this script would be appreciated!

Here is the boiled down script used to clear out the master every morning so you have an understanding of what areas I need to copy back and forth.

Applescript:

tell application "Numbers"
   activate
   
   -- section A
   tell the first table of the sheet 1 of document 1
       clear the range ((name of the second cell of column "B") & ":" & ¬
           (name of the last cell of column "B"))
               
       clear the range ((name of the second cell of column "I") & ":" & ¬
           (name of the last cell of column "I"))
               
       clear the range ((name of the second cell of column "K") & ":" & ¬
           (name of the last cell of column "N"))
       
       set totalRows to (row count)
       if totalRows > 25 then set the selection range to range ((name of the 26th cell of column "B") & ":" & ¬
           (name of the last cell of column "B"))
       
   end tell
   tell application "System Events"
       tell process "Numbers"
           click menu item "Delete Rows" of menu 1 of menu bar item "Table" of menu bar 1
       end tell
   end tell
   
   -- Section B
   tell the second table of the active sheet of document 1
       clear the range ((name of the second cell of column "A") & ":" & ¬
           (name of the last cell of column "A"))
               
       clear the range ((name of the second cell of column "E") & ":" & ¬
           (name of the last cell of column "M"))
               
       set totalRows to (row count)
       --gets the row count for the current sheet
       if totalRows > 25 then set the selection range to range ((name of the 26th cell of column "A") & ":" & ¬
           (name of the last cell of column "A"))
       
       tell application "System Events"
           tell process "Numbers"
               click menu item "Delete Rows" of menu 1 of menu bar item "Table" of menu bar 1
           end tell
       end tell
       
   end tell
   
   -- Section C
   tell the sixth table of the active sheet of document 1
       clear the range ((name of the second cell of column "A") & ":" & ¬
           (name of the last cell of column "A"))
               
       clear the range ((name of the second cell of column "D") & ":" & ¬
           (name of the last cell of column "D"))
       
       clear the range ((name of the second cell of column "E") & ":" & ¬
           (name of the last cell of column "E"))
       
       set totalRows to (row count)
       --gets the row count for the current sheet
       if totalRows > 25 then set the selection range to range ((name of the 26th cell of column "A") & ":" & ¬
           (name of the last cell of column "A"))
       
       tell application "System Events"
           tell process "Numbers"
               click menu item "Delete Rows" of menu 1 of menu bar item "Table" of menu bar 1
           end tell
       end tell
       
   end tell
   
   -- Section D
   tell the first table of the sheet 3 of document 1
       clear the range ((name of the first cell of column "A") & ":" & ¬
           (name of the last cell of column "F"))
   end tell
   
   
end tell
display notification "The Master Has Been Cleared!" sound name "Glass.aiff"

Last edited by TheREDNAVE (2017-04-10 01:44:00 am)


Filed under: numbers, folders, merge, combine

Offline

 

#2 2017-04-10 10:23:27 am

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

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

If I understood well, the script below will transfer the datas as required.

CAUTION. As I don't like to call sheets or tables by their index I call them by names.

Applescript:

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

set path2Target to (path to desktop as text) & "mainDocument.numbers" # Edit to fit your needs
set folder4peoples to (path to desktop as text) & "peoples" # 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 "Numbers"
   open path2Target as «class furl»
   set o's targetName to name of document 1
   tell document 1 to tell sheet "sheet1"
       if not (exists table "fake") then
           make new table with properties {name:"fake", header row count:1, header column count:0, row count:10, column count:2}
       end if
   end tell
   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
# I'm not sure that the upper row to import is the same in every tables
# So I define several ones.
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upper3_1 to 2
set upperInMain to 2
repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
   my importFrom(aPeopleName, "sheet1", "table1-1", {"B", "I", "K"}, upper1_1, upperInMain)
   
   my importFrom(aPeopleName, "sheet1", "table1-2", {"A", "B", "C", "D", "E"}, upper1_2, upperInMain)
   
   my importFrom(aPeopleName, "sheet1", "table1-6", "A", upper1_6, upperInMain) # I deliberately missed the brackets
   
   my importFrom(aPeopleName, "sheet3", "table3-1", {"A", "F"}, upper3_1, upperInMain)
   
   tell application "Numbers" to tell document (o's targetName) to tell sheet 1 to tell table "fake" to tell column 1
       repeat with i from (count rows) to 1 by -1
           if value of cell i is not missing value then exit repeat
       end repeat
       # i is the index of the lower row used
       if i = (count rows) then
           add row below last row
       end if
   end tell
   set upperInMain to i
   # So datas from the next people will be pasted starting from the same row in every table.
end repeat

# Remove the fake table
tell application "Numbers" to tell document (o's targetName) to tell sheet 1 to delete table "fake"



on importFrom(aPeopleName, sheetName, tableName, theColumns, higherRowInPeople, higherRowInTarget)
   # 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}
   set windowMenu to 11 # Index of the menu "Window"
   copy higherRowInTarget to upperRow
   
   tell application "Numbers"
       # Scan the table of every peoples to determine the lower row filled
       repeat with c from 1 to count theColumns
           set aColumn to (item c of theColumns) as text
           tell document aPeopleName
               tell sheet sheetName to tell table tableName
                   tell column aColumn
                       repeat with i from (count rows) to 1 by -1
                           if value of cell i is not missing value then exit repeat
                       end repeat
                       set theRange to (name of cell higherRowInPeople) & ":" & (name of cell i)
                   end tell
                   
                   set selection range to range theRange
               end tell # sheet…
           end tell # document
           tell application "System Events" to tell process "Numbers"
               set frontmost to true
               tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item aPeopleName # brings the people window to front
               keystroke "c" using {command down}
           end tell
           # the clipboard contains the datas from the people
           tell document (o's targetName) to tell sheet sheetName to tell table tableName
               if (upperRow > (count rows)) then
                   repeat (upperRow - (count rows)) times
                       add row below last row
                   end repeat
               end if
               set selection range to range (name of cell upperRow of column aColumn)
           end tell
           
           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 main window to front
               keystroke "v" using {command down}
           end tell
           tell document (o's targetName) to tell sheet 1 to tell table "fake"
               if (upperRow > (count rows)) then
                   repeat (upperRow - (count rows)) times
                       add row below last row
                   end repeat
               end if
               set selection range to range (name of cell upperRow of column 1)
           end tell
           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 main window to front
               keystroke "v" using {command down}
           end tell
       end repeat
   end tell # Numbers
   #
end importFrom

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

Offline

 

#3 2017-04-10 10:59:46 am

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

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

Wow what a response. I love this community!
I've never used some of the script that refers to them by their names instead of indexes but I'm willing to learn if it is a better way.

I modified it a little bit so it asks me where my master is rather than assuming where it will be. But regardless, it errors out when it reaches the section involving the fake table (I'm still not entirely sure what this is doing).
It runs, asks me where the Master is. I choose it. Then it begins to open all the spreadsheets in my folder4peoples I named "Daily Sheets". Asks for the password for each of them (totally fine with that). They open, but then errors:

"Numbers got an error: Can’t get sheet "sheet1" of document 1." and then it "outlines make new table with properties {name:"fake", header row count:1, header column count:0, row count:10, column count:2}"

Any idea what happened?

Thanks again in advance.


Applescript:

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

set path2Target to choose file with prompt "Please select today's Master:" of type {"com.apple.iwork.numbers.numbers", ¬
   "com.apple.iwork.numbers.sffnumbers", ¬
   "com.microsoft.excel.xls", ¬
   "org.openxmlformats.spreadsheetml.sheet"}
# 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 "Numbers"
   open path2Target as «class furl»
   set o's targetName to name of document 1
   tell document 1 to tell sheet "sheet1"
       if not (exists table "fake") then
           make new table with properties {name:"fake", header row count:1, header column count:0, row count:10, column count:2}
       end if
   end tell
   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

Offline

 

#4 2017-04-10 11:51:01 am

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

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

With which main file are you starting ?
I never use Merdosoft products so I'm not sure that you use an Excel file.
As far as I know, in such file we can't have several tables in a sheet so we can't have a table named "table1_2" and a table named "table1_6" in the sheet named "sheet1".


Are you sure that you use the names of items used in my script.
I carefully named the sheets "sheet1" and "sheet3" and named the tables as "table1-1", "table1-2", "table1-6", "table3-1".
The described error message let me think that in your file the first sheet is not named "sheet1".

Below is an enhanced version which compute the number of rows imported from a people without using the table "fake".

Applescript:

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

set path2Target to (path to desktop as text) & "mainDocument.numbers" # Edit to fit your needs
set folder4peoples to (path to desktop as text) & "peoples" # 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 "Numbers"
   open path2Target as «class furl»
   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
# I'm not sure that the upper row to import is the same in every tables
# So I define several ones.
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upper3_1 to 2
set upperInMain to 2
set rowsFilled to 0
repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
   set rowsCnt to my importFrom(aPeopleName, "sheet1", "table1-1", {"B", "I", "K"}, upper1_1, upperInMain, 0)
   
   set rowsCnt to my importFrom(aPeopleName, "sheet1", "table1-2", {"A", "B", "C", "D", "E"}, upper1_2, upperInMain, rowsCnt)
   
   set rowsCnt to my importFrom(aPeopleName, "sheet1", "table1-6", "A", upper1_6, upperInMain, rowsCnt) # I deliberately missed the brackets
   
   set rowsCnt to my importFrom(aPeopleName, "sheet3", "table3-1", {"A", "F"}, upper3_1, upperInMain, rowsCnt)
   set rowsFilled to rowsFilled + rowsCnt
   set upperInMain to upperInMain + rowsFilled
   # So datas from the next people will be pasted starting from the same row in every table.
end repeat


on importFrom(aPeopleName, sheetName, tableName, theColumns, higherRowInPeople, higherRowInTarget, maxnumber)
   # 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}
   set windowMenu to 11 # Index of the menu "Window"
   copy higherRowInTarget to upperRow
   tell application "Numbers"
       
       repeat with c from 1 to count theColumns
           set aColumn to (item c of theColumns) as text
           tell document aPeopleName
               tell sheet sheetName to tell table tableName
                   tell column aColumn
                       repeat with i from (count rows) to 1 by -1
                           if value of cell i is not missing value then exit repeat
                       end repeat
                       set theRange to (name of cell higherRowInPeople) & ":" & (name of cell i)
                   end tell
                   set maybe to i - higherRowInPeople + 1
                   if (maybe > maxnumber) then set maxnumber to maybe
                   set selection range to range theRange
               end tell # sheet…
           end tell # document
           tell application "System Events" to tell process "Numbers"
               set frontmost to true
               tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item aPeopleName # brings the people window to front
               keystroke "c" using {command down}
           end tell
           # the clipboard contains the datas from the people
           tell document (o's targetName) to tell sheet sheetName to tell table tableName
               if (upperRow > (count rows)) then
                   repeat (upperRow - (count rows)) times
                       add row below last row
                   end repeat
               end if
               set selection range to range (name of cell upperRow of column aColumn)
           end tell
           
           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 main window to front
               keystroke "v" using {command down}
           end tell
           
       end repeat
       return maxnumber
   end tell # Numbers
end importFrom

You may download the script and the documents used to test it at :
https://app.box.com/s/seq9ofwvkkmfyiugjtzw71axxhare8t1

From my point of view, if you want to choose the main file it would be safer to use :

Applescript:

set path2Target to choose file with prompt "Please select today's Master:" of type {"com.apple.iwork.numbers.numbers", "com.apple.iwork.numbers.sffnumbers"}

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

Last edited by Yvan Koenig (2017-04-10 11:57:18 am)

Offline

 

#5 2017-04-10 12:41:09 pm

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

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

hmm, your example works flawlessly. I'm not sure what the difference is.
Here is a copy of the spreadsheet I'm working with (personal information and other irrelevant sheets removed of course)

https://app.box.com/s/0t3vezdvi6fme3mwrx1igyl1zcclh1br

Any idea what the issue is now?

Offline

 

#6 2017-04-10 01:03:00 pm

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

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

I think I see the issue now! I'm mildly dumb and didn't notice the section where I need to rename accordingly.

repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
    set rowsCnt to my importFrom(aPeopleName, "Watch List", "table1-1", {"B", "I", "K"}, upper1_1, upperInMain, 0)
   
    set rowsCnt to my importFrom(aPeopleName, "Watch List", "table1-2", {"A", "B", "C", "D", "E"}, upper1_2, upperInMain, rowsCnt)
   
    set rowsCnt to my importFrom(aPeopleName, "Watch List", "table1-6", "A", upper1_6, upperInMain, rowsCnt) # I deliberately missed the brackets
   
    set rowsCnt to my importFrom(aPeopleName, "PASTE HERE", "table3-1", {"A", "F"}, upper3_1, upperInMain, rowsCnt)
    set rowsFilled to rowsFilled + rowsCnt
    set upperInMain to upperInMain + rowsFilled

So now it gets past the error looking for sheet 1 since I named it Watch List. But now it's confused about where table1-1 is etc. How do I know what the table names are?

Edit: Found the checkbox in format section to unhide table names! about to test it out!

Last edited by TheREDNAVE (2017-04-10 01:06:07 pm)

Offline

 

#7 2017-04-10 01:32:12 pm

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

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

Okay, good news it it works partially after renaming all the table names appropriately. However, after the first document is done copying to the master, it keeps creating infinite rows in the first table before it starts copying from the next document. Thoughts?

Applescript:

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

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 "Numbers"
   open path2Target as «class furl»
   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
# I'm not sure that the upper row to import is the same in every tables
# So I define several ones.
set upper1_1 to 2
set upper1_2 to 2
set upper1_6 to 2
set upper3_1 to 2
set upperInMain to 2
set rowsFilled to 0
repeat with aPeopleName in o's peopleNames # Edit sheets and tables names to fit your needs.
   set rowsCnt to my importFrom(aPeopleName, "Watch List", "Watch List", {"B", "I", "K", "L", "M", "N"}, upper1_1, upperInMain, 0)
   
   set rowsCnt to my importFrom(aPeopleName, "Watch List", "Scheduled Callbacks", {"A", "E", "F", "G", "H", "I", "J", "K", "L", "M"}, upper1_2, upperInMain, rowsCnt)
   
   set rowsCnt to my importFrom(aPeopleName, "Watch List", "Voicemail Tracker", {"A", "D", "E"}, upper1_6, upperInMain, rowsCnt) # I deliberately missed the brackets
   
   set rowsCnt to my importFrom(aPeopleName, "PASTE HERE", "PASTE HERE", {"A", "B", "C", "D", "E", "F"}, upper3_1, upperInMain, rowsCnt)
   set rowsFilled to rowsFilled + rowsCnt
   set upperInMain to upperInMain + rowsFilled
   # So datas from the next people will be pasted starting from the same row in every table.
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)
   # 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}
   set windowMenu to 11 # Index of the menu "Window"
   copy higherRowInTarget to upperRow
   tell application "Numbers"
       
       repeat with c from 1 to count theColumns
           set aColumn to (item c of theColumns) as text
           tell document aPeopleName
               tell sheet sheetName to tell table tableName
                   tell column aColumn
                       repeat with i from (count rows) to 1 by -1
                           if value of cell i is not missing value then exit repeat
                       end repeat
                       set theRange to (name of cell higherRowInPeople) & ":" & (name of cell i)
                   end tell
                   set maybe to i - higherRowInPeople + 1
                   if (maybe > maxnumber) then set maxnumber to maybe
                   set selection range to range theRange
               end tell # sheet…
           end tell # document
           tell application "System Events" to tell process "Numbers"
               set frontmost to true
               tell menu bar 1 to tell menu bar item windowMenu to tell menu 1 to click menu item aPeopleName # brings the people window to front
               keystroke "c" using {command down}
           end tell
           # the clipboard contains the datas from the people
           tell document (o's targetName) to tell sheet sheetName to tell table tableName
               if (upperRow > (count rows)) then
                   repeat (upperRow - (count rows)) times
                       add row below last row
                   end repeat
               end if
               set selection range to range (name of cell upperRow of column aColumn)
           end tell
           
           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 main window to front
               keystroke "v" using {command down}
           end tell
           
       end repeat
       return maxnumber
   end tell # Numbers
end importFrom

Offline

 

#8 2017-04-10 01:50:01 pm

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

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

Did you get a correct result with my files with absolutely no change ?


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

Offline

 

#9 2017-04-10 01:52:31 pm

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

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

Correct, Not sure if my spreadsheet having thousands of rows more messed something up?

Yvan Koenig wrote:

Did you get a correct result with my files with absolutely no change ?


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

Offline

 

#10 2017-04-10 02:26:36 pm

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

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

I added columns to the different tables to match your settings.
I changed the names of sheets and tables to match your settings.

The script did its job flawlessly.
You just must be patient because for each people it must export 25 blocks of values.

The modified files and your script are available at:
https://app.box.com/s/kb251xva9t6lwh935onlg92sr3uocdty

In your script I just edited an instruction.


Applescript:

# was
open path2Target as «class furl»
# EDITED
open path2Target # it's an alias

For me it's sime to shut off. I will be back tomorrow.

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

Offline

 

#11 2017-04-10 02:27:16 pm

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

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

It seems I was right. I deleted hundreds of empty rows from the sheet 3 aka PASTE HERE. Now the script finishes. However, it is adding many rows into between; rather than pasting spreadsheet 2 data one row below the previous spreadsheet data. And for some reason it is incorrectly copying the header row for spreadsheet 2, and skipping row 1 on sheet 3. I'm much better off than I was thanks to you, but it looks like there are some issues still in there.

Offline

 

#12 2017-04-10 02:45:16 pm

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

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

Last edited by Yvan Koenig (2017-04-10 02:55:59 pm)

Offline

 

#13 2017-04-10 02:50:14 pm

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

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

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!

Offline

 

#14 2017-04-10 03:14:47 pm

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

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

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

You wrote :

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)

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:

Applescript:

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

Offline

 

#15 2017-04-10 05:00:43 pm

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

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

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.

Last edited by TheREDNAVE (2017-04-10 06:15:50 pm)

Offline

 

#16 2017-04-11 03:10:49 am

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

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

(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

Offline

 

#17 2017-04-11 08:53:56 am

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

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

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.


Yvan Koenig wrote:

(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

Offline

 

#18 2017-04-11 09:12:28 am

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

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

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

Offline

 

#19 2017-04-11 10:01:29 am

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

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

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

Yvan Koenig wrote:

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

Offline

 

#20 2017-04-11 10:29:00 am

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

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

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

Offline

 

#21 2017-04-11 10:32:41 am

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

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

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.

Yvan Koenig wrote:

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

Offline

 

#22 2017-04-11 12:36:20 pm

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

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

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

Applescript:

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


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

Offline

 

#23 2017-04-11 12:53:45 pm

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

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

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!

Yvan Koenig wrote:

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

Applescript:

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


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

Offline

 

#24 2017-04-11 11:33:58 pm

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

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

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.

Applescript:

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

Offline

 

#25 2017-04-12 04:19:23 am

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

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

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

Last edited by Yvan Koenig (2017-04-12 01:48:15 pm)

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)