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:
- At the end of the day, collect everyones spreadsheets and move them into a folder on desktop
- 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)
- 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)
- go to my active still blank “Master” spreadsheet, and paste their data into each respective table
- repeat for each document in the desktop folder, pasting new data in the row below the previous
- 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.
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"