Help with AppleScripting.

Question

Yellowbox Author

Level 7

20,383 points

Help with AppleScripting.

Hi Fellow Numbers Users,

I am new to AppleScripting. I was following this discussion:

https://discussions.apple.com/thread/6067355?answerId=25408029022#25408029022&ac_cid=tw123456#25408029

and had a try. Because we were getting off the topic, I have started this new discussion on scripting for newbies.

Here is the error message in AppleScript Editor:

After clicking on OK, the editor highlighted the script like this:

For more clarity, here is the script I tried in AppleScript Editor, while running Numbers 3.2 (the only version of Numbers running at the time).

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

set format to date and time

end repeat

end tell

--end script

I hope I have given sufficient detail.

Regards,

Ian

MacBook Pro (13-inch Mid 2012), OS X Mavericks (10.9.2), Pages 4.3, Numbers 2.3, Numbers 3.2

Posted on Apr 7, 2014 8:26 AM

Reply

Answer 1

Barry

Level 9

59,861 points

Apr 7, 2014 5:35 PM in response to Yellowbox

Hi Ian,

Screenshots not visible here.

Barry

Reply

Answer 2

Yellowbox Author

Level 7

20,383 points

Apr 7, 2014 8:05 PM in response to Barry

Numbers 3.2 (Numbers 2 not running). Input data in Column B selected. Column C ready to receive pasted output.

AppleScript Editor and click on Run

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

set format to date and time

end repeat

end tell

--end script

error "Numbers got an error: Can’t get date \"Friday, 27 November 1998 11:00:00 am\"." number -1728 from date "Friday, 27 November 1998 11:00:00 am"

1. The time associated with 27 /11/1998 is not 11:00:00 am

2. this is a different error message from my first try:

3. There is an indent in line 2 of the script that I can not remove. Nothing showing in Pages > View > Show Invisibles.

Interesting!

Regards,

Ian.

Reply

Answer 3

Yellowbox Author

Level 7

20,383 points

Apr 7, 2014 9:33 PM in response to Yellowbox

Update

My input dates were not all DD/MM/YYYY. Now they are:

Same error message

error "Numbers got an error: Can’t get date \"Friday, 27 November 1998 11:00:00 am\"." number -1728 from date "Friday, 27 November 1998 11:00:00 am"

In case this helps, here is a shot of the script after running. I assume the blue highlight on text 1 thru 2 is a diagnostic.

Regards,

Ian.

Reply

Answer 4

Barry

Level 9

59,861 points

Apr 7, 2014 10:39 PM in response to Yellowbox

HI Ian,

As I read the first error, AppleScript is reading the D&T value in the cell as a 'standard' Date and Time String rather than as a formatted value. That would be consistent with the blue highlight indicating where the error occurs.

What happens if you change

to set aValue to value of aCell as text

Just a guess, probably arising from reading scripts using this phrase in the past.

Regards, Barry

Reply

Answer 5

Yellowbox Author

Level 7

20,383 points

Apr 7, 2014 11:59 PM in response to Barry

Hi Barry,

Thanks for your reply. I added as text and ran this in AppleScript Editor:

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell as text

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

set format to date and time

end repeat

end tell

--end script

No error message! Without pasting anything, my Numbers table is now:

It is doing something!

Regards,

Ian.

Reply

Answer 6

Barry

Level 9

59,861 points

Apr 8, 2014 12:22 AM in response to Yellowbox

Hi Ian,

Looks like it's correctly following the instructions. The two letters at the end of the output are the first two letters of the D&T string in each case. The others, I expect are the characters in the named places of the string.

Working correctly, but not very useful, as here's too much variation in the position of the wanted values.

Time to check the AppleScript/Numbers dictionary again?

Is there a way to get the 'displayed' value in the cell?

Can AppleScript manipulate the actual D&T value (rather than its representation) to extract the Day, Month (number) and Year?

So many questions! 😉

Regards,

Barry

Reply

Answer 7

Yellowbox Author

Level 7

20,383 points

Apr 8, 2014 2:39 AM in response to Barry

Hi Barry,

Going back to the reason why I am trying AppleScript, this thread caught my interest:

https://discussions.apple.com/thread/6067355?answerId=25408029022#25408029022&ac_cid=tw123456#25408029

HYllom

07/04/2014 11:25 AM

Hello,

I am trying to format a column with dates.

Currently it is in DD/MM/YYYY format but i need it in YYYY-MM-DD format.

We can do this in Numbers, provided that our region settings allow YYYY/MM/DD (or YYYY-MM-DD) Date-and-Time cell formats.

Here it is in Numbers, spelled out one step at a time. Sorry for my pedestrian approach, but this may be the step-by-step approach necessary in a script.

Columns C, D and E use the YEAR, MONTH and DAY functions to pull those values from Column B.

Column F puts them back together using the DATE function. So far, so good and we don't need a script **provided that** our region settings allow Year, Month, Day cell format for Column F. Indeed, a simple format change to Column B would short-cut the process 😉.

SGIII was trying to help HYllom who can not access certain date formats. That piqued my interest in scripting.

Regards,

Ian.

Reply

Answer 8

SGIII

Level 8

35,306 points

Apr 8, 2014 7:04 AM in response to Yellowbox

Hi Ian,

For many hours I wasn't able to get into the discussions. I got a "not authorized" message of some sort. So I wasn't able to replay.

I think my added line set format to date and time is problematic and recommend deleting it or commenting it out and seeing if you still get an error message.

The script is not particularly interesting. It is just a "quick and dirty" to convert "pure text" (not recognized by Numbers as a date) into a string in a format that Numbers will recognize as a date. Make sure the values in the selected cells are text formatted as text (you could enter each DD/MM/YYYY value with a leading single quote). Then the script should not throw off an error.

Glad to see the interest in scripting! It can extend Numbers in useful ways without much trouble. Post if you are still having problems.

SG

Reply

Answer 9

Yellowbox Author

Level 7

20,383 points

Apr 8, 2014 7:50 AM in response to SGIII

Hi SG,

This is my Numbers 3.2 table before running the script. Dates (Column B) are formatted as text (left aligned). I was expecting a result on the clipboard ready to paste into Column C (silly me).

Script:

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

--set format to date and time

end repeat

end tell

--end script

'set format to date and time' is commented out with -- at the start of that line.

I removed Barry's helpful suggestion to add as text after 'acell'

Result in Numbers 3.2

Result in Column B is as expected (Header is wrong; I was expecting the result to appear (or be on the Clipboard for pasting) in Column C under YYYY MM DD but that was my misunderstanding of the script).

Thanks Barry and SG. I am getting there! For our next trick, how do we help HYllom turn the Text result into a Date-and-Time format?

Regards,

Ian.

Reply

Answer 10

SGIII

Level 8

35,306 points

Apr 8, 2014 7:58 AM in response to Barry

Hi Barry,

Is there a way to get the 'displayed' value in the cell?

Getting the valuereturns a date time string, e.g.

date "December 31, 2013 at 7:00:00 PM"

I'm not sure why it gives the above value when in the cell I have this:

1/1/2014, 12:00:00 AM

Numbers 3.2 has a new formula property, so you can get the formula as text, e.g. =SUM(40+2).

Can AppleScript manipulate the actual D&T value (rather than its representation) to extract the Day, Month (number) and Year?

Yes, AppleScript is good at this. See this page.

You may find the following useful for exploring AppleScript properties in a selected cell or cells:

--beginning of script

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with i from 1 to count cells

(* statements operating on selected cells go here, e.g.

set background color to "red"

set the value of cell i to i

get value of cell i

etc.

*)

end repeat

end tell

-- end of script

SG

Reply

Answer 11

SGIII

Level 8

35,306 points

Apr 8, 2014 8:03 AM in response to Yellowbox

Hi Ian,

how do we help HYllom turn the Text result into a Date-and-Time format?

If the string is rendered in a format that Numbers recognizes, I think all that's needed is to choose a Date & Time format for those cells. It could be automated in AppleScript (I haven't yet figured out the glitch) but the manual formatting after running the script is easy enough. Does that work on your machine?

SG

Reply

Answer 12

SGIII

Level 8

35,306 points

Apr 8, 2014 9:22 AM in response to Yellowbox

If trying to use the "experiment on selection range" script above in Numbers 2 instead of Numbers 3, then replace:

active sheet

with:

(first sheet whose every table's selection range's class contains range)

Thanks to Hiroto for pointing out that workaround here.

SG

Reply

Answer 13

SGIII

Level 8

35,306 points

Apr 8, 2014 1:07 PM in response to SGIII

Getting the valuereturns a date time string, e.g.

date "December 31, 2013 at 7:00:00 PM"

I'm not sure why it gives the above value when in the cell I have this:

1/1/2014, 12:00:00 AM

Reading old threads cleared up this particular mystery.

When AppleScript retrieves a date-time string from a Numbers cell it assumes it is UTC and needs to be converted to local time.

So using code like this will retrieve the date-time leaving it as it is in Numbers:

set theTime to the (value of cell i) - (time to GMT)

SG

Reply

Answer 14

Yellowbox Author

Level 7

20,383 points

Apr 9, 2014 8:08 AM in response to SGIII

Hi Barry and SG,

Thanks for your help to a newbie scripter. I found this to be a useful generic template for a script:

--beginning of script

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with i from 1 to count cells

(* statements operating on selected cells go here, e.g.

set background color to "red"

set the value of cell i to i

get value of cell i

etc.

*)

end repeat

end tell

-- end of script

I am beginning to see how these commands work:

tell

repeat with from to

set

get

end

And isn't it just great how the AppleScript Editor adds indents and colours and Bold and Italic to make the script easy to follow!

However (there are of lots of howevers in life) getting back to HYllom's question.

SG wrote: If the string is rendered in a format that Numbers recognizes, I think all that's needed is to choose a Date & Time format for those cells.

I am getting lost here. If HYllom has the required Date & Time format for his/her region, why do we need to go through AppleScript?

In Numbers, Column B has the date as DD/MM/YYYY. The formula in C2 (and Fill Down) is = B2 and C is formatted as YYYY-MM-DD

I must be missing the point of using AppleScript to convert a text string to another order and then converting back to a Date format.

As Homer Simpson would say: "Doh!"

Regards,

Ian.

Reply

Answer 15

SGIII

Level 8

35,306 points

Apr 9, 2014 9:20 AM in response to Yellowbox

Hi Ian,

I must be missing the point of using AppleScript to convert a text string to another order and then converting back to a Date format.

On my machine Numbers refuses to let me format a string in the format "DD/MM/YYYY" as Date & Time. But if I first convert that string (manually, or via AppleScript) to the format "YYYY-MM-DD" then Numbers will recognize it as Date & Time.

That I cannot format "DD/MM/YYYY" as Date & Time may be due to my particular localization settings. If Numbers recognizes "DD/MM/YYYY" as Date & Time on your machine, then I think you (and Homer) are correct: there would be no point at all in running the AppleScript, except as an exercise!

SG

Reply