Sunday, January 23, 2022

#1 2022-01-04 03:36:29 pm

MTC
Member
Registered: 2021-12-30
Posts: 3

Applying Formats in Copy-Paste Script from one Numbers file to another

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!

Applescript:

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

Offline

 

#2 2022-01-05 05:45:37 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 5474

Re: Applying Formats in Copy-Paste Script from one Numbers file to another

Hi MTC.

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 …

Applescript:

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 …

Applescript:

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.

Please let us know how it goes.


NG

Offline

 

#3 2022-01-05 06:13:03 am

MTC
Member
Registered: 2021-12-30
Posts: 3

Re: Applying Formats in Copy-Paste Script from one Numbers file to another

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.

Applescript:

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

Thank you NG!

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)