I’m new to AppleScript and trying to make something that will copy several long rows and columns from one Numbers Sheet to a MasterSheet. The day sheets will be specific job’s info. Some of the data is durations, some time/date, and some numbers. The master sheet will list all the job’s data.
I’m able to copy the values, but I’m unable to get the destination formatting correct for some of the cells, mostly the durations. I end up with 3hrs 10 minutes expressed in seconds for instance. I’ve tried changing the source and destination formats, but since I use a lot of simple equations (IF, multiplication, etc) I believe I’m restricted to the Automatic cell format in the source. This seems to be part of the issue.
Is there a way to format the new column I insert in the destination file, or otherwise solve this issue? Thanks very much for any help!
set JobFile_1 to choose file of type "numbers" with prompt "Choose the Job file"
set DestFileMaster to choose file of type "numbers" with prompt "Choose Master Log"
tell application "Numbers"
activate
delay 1 -- just a delay to prevent a crash. Probably don’t need
set document_1 to open JobFile_1
set tblWholeJob to table "JobDataTable" of sheet "Job Data" of document 1
set tblDayLog to table "DayLogBookTable" of sheet "DayLogBook" of document 1
set tblDayTreeData to table "TreeDataTable" of sheet "Tree Data" of document 1
set tblDayStumpData to table "StumpDataTable" of sheet "Stump Data" of document 1
set document_2 to open DestFileMaster -- yup
set tblAllJobData to table "AllJobDataTable" of sheet "Job Data" of document_2
set tblLogBookMaster to table "LogBook Master Table" of sheet "LogBookMaster" of document_2
tell tblDayLog
set srcRangeValues to value of cells of range "D1:D517"
end tell
tell tblLogBookMaster
add column before fifth column -- Add a new empty column to the Logbook Sheet
delay 1
set destCellList to cells of range "E1:E517"
repeat with i from 1 to length of destCellList
set value of item i of destCellList to item i of srcRangeValues
delay 0.03
end repeat
end tell
end tell
Scripting Numbers can be a little confusing because the ways it stores and returns some kinds of data are different from the ways the data are presented for view in the cells. Also, its AppleScript implementation requires the data to be entered in the form in which they’re to be interpreted, not necessarily in the form in which it returns them to the script!
As you’ve seen, your duration of 3hrs 10 minutes is returned to the script as the equivalent number of seconds — the AppleScript real 1.14E+4. When the script enters this bare number into the destination cell, the cell’s existing “Duration” format is overridden and the number’s rendered there as “11400”. The value needs to be entered into the destination cell in a form that Numbers recognises as one of its duration formats, which are text.
Fortunately, this is fairly easy as Numbers cells now have a formatted value property as well as a straight value. If you use formatted value to fetch the values …
tell tblDayLog
set srcRangeValues to formatted value of cells of range "D1:D517"
end tell
… and value to set the destination cell values as before …
repeat with i from 1 to length of destCellList
set value of item i of destCellList to item i of srcRangeValues
end repeat
… I think the problem should be largely solved. Note that if the destination cell has a different duration format from the original, the value will still be recognised as a duration and will be rendered at the destination in the destination cell’s format.
Well, that sure fixes it. Thank you! I had tried formatting the destination column, and formatted values in the destination code, and in both destination and source code, but not only formatted values in the source data. There it is. That makes sense.
On the off chance anybody is searching for a similar answer in the future, here is the final AppleScript that works. Delays not needed.
set JobFile_1 to choose file of type "numbers" with prompt "Choose the Job file"
set DestFileMaster to choose file of type "numbers" with prompt "Choose Master Log"
tell application "Numbers"
activate
delay 1 -- just a delay to prevent a crash. Probably don’t need
set document_1 to open JobFile_1
set tblWholeJob to table "JobDataTable" of sheet "Job Data" of document 1
set tblDayLog to table "DayLogBookTable" of sheet "DayLogBook" of document 1
set tblDayTreeData to table "TreeDataTable" of sheet "Tree Data" of document 1
set tblDayStumpData to table "StumpDataTable" of sheet "Stump Data" of document 1
set document_2 to open DestFileMaster -- yup
set tblAllJobData to table "AllJobDataTable" of sheet "Job Data" of document_2
set tblLogBookMaster to table "LogBook Master Table" of sheet "LogBookMaster" of document_2
tell tblDayLog
set srcRangeValues to formatted value of cells of range "D1:D517"
end tell
tell tblLogBookMaster
add column before fifth column -- Add a new empty column to the Logbook Sheet
delay 1
set destCellList to cells of range "E1:E517"
repeat with i from 1 to length of destCellList
set value of item i of destCellList to item i of srcRangeValues
delay 0.03
end repeat
end tell
end tell