Scripting "Fill Down" in Apple's Numbers app

johnnylundy · July 5, 2011, 8:20pm

I have a great script to do a curl and parse out a couple of values from a web service. I use that to make a new row in an Excel worksheet. As part of the script, I tell Excel to fill down to make the added row.

Since I would have to update to Excel 2011 for Lion anyway, I’d like to switch to a Numbers sheet for this. I looked at the AppleScript dictionary for Numbers and do not find any “fill” or “fill down” commands.

Is there a “Numbers way” to do this (fill down), or should I start looking at assigning a keystroke to the Fill Down menu item and calling System Events to do the Fill Down?

Hard to believe there’s no command in the dictionary to do it, but I don’t see it.

I could also rewrite the whole AppleScript to Objective-C/AppleScript (I am fluent in both) if that would solve the problem.

Nigel_Garvey · July 5, 2011, 11:22pm

Sometimes you have to write your own code.

The ‘Fill down’ command in Numbers’s GUI only works within the selection range. You can’t use it to add new rows. However, AppleScript can do that.

tell application "Numbers"
	tell document 1
		tell table 1 of sheet 1
			-- Create a new row at the bottom and get its row number.
			set newRowNumber to address of (make new row at end of rows)
			-- Set the values of all the cells in it to those in the row above.
			set rowAboveNumber to newRowNumber - 1
			repeat with thisColumn in columns
				tell thisColumn
					set val to value of cell rowAboveNumber
					-- Numbers's AppleScript implementation can't tell the difference between an 'empty' cell value and the value 0.0. It can set them differently however. You have to use your own judgement whether or not to make the following line conditional.
					if (val is not 0.0) then set value of cell newRowNumber to val
				end tell
			end repeat
		end tell
	end tell
end tell

johnnylundy · July 6, 2011, 1:45am

Excellent, Nigel. I can see a workaround.

Actually, on looking again at my current Excel scripting, it appears I am not adding a new row per se but rather selecting part of the last added row AND the cells below it and doing a fill down.

Here’s the relevant section:


set value of selection to tiaaDate -- column 1
set value of (find range "Date_Price_List" what "" after selection search order by rows) to tiaaPrice --column 2, same row
select range (convert formula formula to convert "R[-1]C[2]:RC[7]" from reference style R1C1 to reference style A1) --range to fill (same row and row above it)
fill down selection

So it’s basically a select range and a fill down. The thing is, it isn’t a simple value copy. It has to update the relative references like a real fill-down would do. If the formula in column 6 of row N refers to column 3 and 4, then after fill-down, column 6 of row N+1 needs to refer to column 3 and 4 of row N+1, not row N.

This is why I thought I might have to bail and use a keystroke.

Yvan_Koenig · July 6, 2011, 2:43pm

I apologize but for the first version of a dictionary, the Numbers one is already a good one.
Of course if you compare to an old beast like XL it may seems to be short but the comparison would be ridiculous.
Numbers is not trying to replace XL, it just wish to be the spreadsheet for those which don’t need to pay a dime to Me.doSoft.

Achieving what you ask just requires a trivial piece of code, at least if the targeted sheet is at front.

Assuming that this condition is met, edit the seven variables defined at the beginning to fit your needs.


my activateGUIscripting()

set dName to 1
set sName to 1
set tName to 1
set rowNum1 to 5
set colNum1 to 3
set rowNum2 to 25
set colNum2 to 7

tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
	if rowNum2 > (count row) then
		repeat rowNum2 - (count row) times
			add row above row -1
		end repeat
	end if
	(*
It seems useless to check if column colNum2 is available.
If it wasn't, I can't guess what would be to use to fill down *)
	set selection range to range (name of cell rowNum1 of column colNum1 & ":" & name of cell rowNum2 of column colNum2)
end tell
my selectSubMenu("Numbers", 5, 10, 2)

--=====

on activateGUIscripting()
	(* to be sure than GUI scripting will be active *)
	tell application "System Events"
		if not (UI elements enabled) then set (UI elements enabled) to true
	end tell
end activateGUIscripting

--=====
(*
my selectSubMenu("Numbers",5, 10, 2)

==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
	
	tell application theApp
		activate
		tell application "System Events" to tell process theApp to tell menu bar 1 to ¬
			tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
	end tell -- application theApp
end selectSubMenu

--=====

Yvan KOENIG (VALLAURIS, France) mercredi 6 juillet 2011 16:42:05

johnnylundy · July 6, 2011, 3:29pm

Thank you Yvan. This approach looks as if it will do the trick. I will post back once I have incorporated it.

johnnylundy · July 10, 2011, 9:32pm

Thanks to you guys, I have most of it working.

The part I want to tune up is a familiar topic in AppleScript - date comparisons.

Here’s the story:

the web service returns a pure text version of the date which I parse out to, e.g. “07/08/2011”. This got entered into the Excel sheet.
the Numbers sheet, which is imported from my Excel sheet that I used previously, shows an actual AppleScript “date” class for that cell, and is listed in Script Debugger as

date “Tuesday, July 5, 2011 7/5 8:00:00 PM” (I have no idea how it added the 8:00 time)

if I apply the “short date string” method to that, I get

“7/5/11”

which is great since the spurious time is eliminated.

Now I have “07/08/11” from the web service, and “7/5/11” from my attempt to remove the spurious time component. As strings, these are not going to compare. I need them to be dates with no time, or at least identical times, so I can compare them.

So I need some help in converting that “07/08/11” string into a date with no time, and the same for the “7/5/11”.

The basic question is: is there a way to just remove the time from an AppleScript date object, or at least set it to midnight? I want to compare dates, but they won’t compare if they have the time component included in them.

P.S. I read through the long series of methods here on MacScripter on manipulating dates, but didn’t see a way to just axe the time component.

Yvan_Koenig · July 11, 2011, 10:41am

I guess that this piece of code may help.


set date_with_time to current date
set date_0000 to date (short date string of date_with_time)

As you extract your dates from a Numbers document,

this other piece of code will be useful.



--{code}
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	tell cell "B16"
		set {maybe, maybef} to {value, format}
	end tell
	
	if class of maybe is date then
		set maybe to maybe - (my time_to_GMT_without_DST())
	end if
	tell cell "D14"
		set {value, format} to {maybe as text, maybef}
	end tell
end tell

on time_to_GMT_without_DST()
	if last word of (do shell script "zdump `date +\"%Z\"`") = "UTC" then
		return (time to GMT) - 3600
	else
		return time to GMT
	end if
end time_to_GMT_without_DST
--{code}

because when AppleScript extracts a date_time from a Numbers cell, it receive the true_value + TIME_TO_GMT_of_Winter.
I know, it’s foolish but it behaves this way.
I guess that it’s why you get the_date 8:00

Yvan KOENIG (VALLAURIS, France) lundi 11 juillet 2011 12:41:21

johnnylundy · July 11, 2011, 12:39pm

Got it. What a strange phenomenon. Anyway, many thanks. I should be able to finish this up soon, and the dates will compare correctly.

One big advantage of Numbers over Excel is that you actually CAN “add a row” rather than having to search for the first “blank row.” I noticed this last night. It makes keeping a list much easier.

Yvan_Koenig · July 11, 2011, 3:18pm

Was the time value which you got really the TIME TO GMT offset as I guessed ?

Yvan KOENIG (VALLAURIS, France) lundi 11 juillet 2011 17:18:40

johnnylundy · July 11, 2011, 3:42pm

I just checked a few rows, with this:


tell document 1 to tell sheet 1 to tell table 1 to tell row 147 to get {value, class} of first cell

And for all the rows I tested, I get

date “Wednesday, June 29, 2011 6/29 8:00:00 PM”

and

class: cell

So my local TZ is EDT. So that’s GMT-0400, but originally these dates were entered without any time component, so I think you are right that it is using 2400 and then subtracting 0400. I wish had left it at 2400.

Actually, I just was thinking - since ALL of the dates have 2000 hours as the time, I can still compare them and the times should cancel out, right?

All I need to do is compare the date string I get from the Web with (current date - 1) because the Web service updates at about 00:25 with data for previous day, and find they are not equal. That means the Web data isn’t updated yet, or it’s a holiday, so I don’t do anything.

I use launchd to fire the script every AM at 00:30. The script just returns if current date is a Sunday or Monday, as that means the data is for Saturday or Sunday and doesn’t exist (bank data).

Yvan_Koenig · July 11, 2011, 4:49pm

As long as some of the dates are extracted from a Numbers table, you must apply my tip.

Just an example:

I was born on 1943/12/31 23:55
When I extract this date from a Numbers table without the tip I get 1944/01/01 00:55

Difficult to make comparisons with such behavior.

Yvan KOENIG (VALLAURIS, France) lundi 11 juillet 2011 18:48:50

johnnylundy · July 11, 2011, 7:10pm

Ah, yes. OK.

I think I am all set. I will post back with how it goes.

Much appreciated.

johnnylundy · July 12, 2011, 4:51pm

OK, I am getting close.

I noticed in your example, the converted date is the next day. I need it to be the same day, just without the time.

Now I have this code:

tell application "Numbers"
	activate
	open "Snow Leopard:Users:holbrook:Desktop:NewTIAARealEstate.numbers"
	tell document 1 to tell sheet 1 to tell table 1 to tell row 146
		set rawCellDate to value of first cell
		get rawCellDate
		tell me to set today to date (short date string of (current date))
		tell me to set correctedCellDate to getTheDate given aNumberDate:rawCellDate
	end tell
end tell

to getTheDate given aNumberDate:theNumberDate
	if class of theNumberDate is date then
		set newDate to theNumberDate - (my time_to_GMT_without_DST())
	end if
	
end getTheDate


on time_to_GMT_without_DST()
	if last word of (do shell script "zdump `date +\"%Z\"`") = "UTC" then
		return (time to GMT) - 3600
	else
		return time to GMT
	end if
end time_to_GMT_without_DST

And here is the output:

correctedCellDate = date “Wednesday, June 29, 2011 6/29 1:00:00 AM”
rawCellDate = date “Tuesday, June 28, 2011 6/28 8:00:00 PM”
today = date “Tuesday, July 12, 2011 7/12 12:00:00 AM”

So if my script runs at 0030 hours, and checks the last row’s date with correction, it will get a date that is +1 days off.

In other words, if the stored Numbers date is “8PM Monday night”, I want to correct it to just “Monday” (which day does 2400 hours belong to anyway - the next day?)

Yvan_Koenig · July 12, 2011, 7:07pm

It appears that the problem is more complicated that what I thought.

I made tests with old date_time values like my birthday.
As they are out of the Daylight Saving Time, I had to use the handler which remove one hour to the TIME_TO_GMT values.

In fact, the year doesn’t matter.
What is needed is a way to determine if a given date is in DST or isn’t.
My scheme is wrong.

At this time, edit the handler this way :


on time_to_GMT_without_DST()
	if last word of (do shell script "zdump `date +\"%Z\"`") = "UTC" then
		log -3600
		return (time to GMT) (* - 3600 *)
	else
		log 0
		return time to GMT
	end if
end time_to_GMT_without_DST

I will continue to search.

Yvan KOENIG (VALLAURIS, France) mardi 12 juillet 2011 21:07:12

Yvan_Koenig · July 12, 2011, 7:41pm

It seems that this one is OK.


--{code}
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	repeat with r from 2 to 4
		tell cell ("B" & r)
			set {maybe, maybef} to {value, format}
		end tell
		
		if class of maybe is date then
			set maybe2 to my shorten_date(maybe)
			tell cell ("D" & r)
				set value to (maybe2 as text)
				set maybe2 to hours of (get value)
			end tell
			set maybe to maybe - (maybe2 * 3600)
		end if -- class of.
		tell cell ("D" & r)
			set {value, format} to {maybe as text, maybef}
		end tell
	end repeat
end tell

on shorten_date(une_date)
	return date (short date string of une_date)
end shorten_date

--{code}

Yvan KOENIG (VALLAURIS, France) mardi 12 juillet 2011 21:41:32

johnnylundy · July 12, 2011, 9:10pm

I just realized that ALL of the dates in the Numbers table have the same (incorrect) time. So I can just do

tell document 1 to tell sheet 1 to tell table 1 to tell row 146
		set rawCellDate to value of first cell
		set rawCellTime to time of rawCellDate
		set rawCellDay to (rawCellDate - rawCellTime)
end tell

And I get 12 midnight of the stored day. Then when I compare to the current day, it should work.

Nigel_Garvey · July 12, 2011, 11:19pm

Or you could simply set the date’s time to 0:

tell document 1 to tell sheet 1 to tell table 1 to tell row 146
		set rawCellDate to value of first cell
		set time of rawCellDate to 0
end tell

johnnylundy · July 13, 2011, 1:43am

Amazing how the problem becomes more clear as you work with it.

I don’t see why that shouldn’t work. All I need to do is compare one day to another to see if it is the same day or not (e.g. if the webservice is still showing Wednesday’s result and I already have that in the spreadsheet as the first cell of the last row, then I do not add it again).

Nice exercise. I’ll see if this solves the problem.

Yvan_Koenig · July 13, 2011, 8:11am

CAUTION

Setting the time part to zero apply safely only in case of entered values being pure dates.
In such case, the time component extracted by Applescript is the TIME_TO_GMT + DST value.

I’m curious to know what is extracted by AppleScript if someone in the USA insert 06/06/2011.
Here it extracts : date “lundi 6 juin 2011 02:00:00”
With 03/03/2011 it extracts : date “jeudi 3 mars 2011 01:00:00”
With 11/11/2011 it extracts : date “vendredi 11 novembre 2011 01:00:00”

So, I guess that in a country whose GMT offset is -6h
With 06/06/2011 it extracts : date “dimanche 5 juin 2011 19:00:00”
With 03/03/2011 it extracts : date “mercredi 2 mars 2011 18:00:00”
With 11/11/2011 it extracts : date “jeudi 10 novembre 2011 18:00:00”

Yvan KOENIG (VALLAURIS, France) mercredi 13 juillet 2011 10:10:58

Nigel_Garvey · July 13, 2011, 11:32am

After some experimentation with changing my computer’s time zone, it appears that, while Numbers still displays my original dates in the GUI, the dates it returns to AppleScript are what the local date/times would be if those in the table were GMT. (I this is accidental rather than deliberate.) So as Yvan says, the proper correction is to transpose the returned date to GMT, not just to zap its time. The ‘time to GMT’ function is probably not suitable here, as it returns the computer’s time zone offset at the time the script’s run, which may not be right for the date under examination.

The following’s a bit Heath Robinson, but it works. Someone with a better knowledge of shell script functions than my own may be able to come up something better. The line at the bottom calls the process. Your script has to provide the date from Numbers.

(* Convert an ISO-format date string to an AppleScript date. *)
on isotToDate(isot)
	set n to (text 1 thru 8 of isot) as integer
	set ASDate to (current date)
	tell ASDate to set {day, year, its month, day} to {1, n div 10000, n mod 10000 div 100, n mod 100}
	if ((count isot) > 8) then
		set n to (text 10 thru 15 of isot) as integer
		set ASDate's time to n div 10000 * hours + n mod 10000 div 100 * minutes + n mod 100
	end if
	
	return ASDate
end isotToDate

(* Transpose an AppleScript date/time from the given time zone to GMT. *)
on TZtoGMT(TZDate, TZ)
	-- The difference between TZDate when it's local and the GMT date we want is usually
	-- the same as the difference between the local date when TZDate is GMT and TZDate itself .
	set GMTDate to TZDate - (GMTtoTZ(TZDate, TZ) - TZDate)
	-- . but not around the time the clocks go forward. If the GMT obtained doesn't reciprocate to TZDate,
	-- shift to a nearby local date where the above DOES work, get a new GMT, unshift it by the same amount.
	set testDate to GMTtoTZ(GMTDate, TZ)
	if (testDate is not TZDate) then
		if (GMTDate > testDate) then -- "Clocks forward" is towards GMT.
			set shift to GMTDate - testDate
		else -- "Clocks forward" is away from GMT.
			set shift to -days
		end if
		set nearbyDate to TZDate + shift
		set GMTDate to nearbyDate - (GMTtoTZ(nearbyDate, TZ) - nearbyDate) - shift
	end if
	
	return GMTDate
end TZtoGMT

(* Transpose an AppleScript date/time from GMT to the given time zone. *)
on GMTtoTZ(GMTDate, TZ)
	-- Subtract date "Thursday 1 January 1970 00:00:00" from the GMT date. Result in seconds, as text. 
	copy GMTDate to date19700101
	tell date19700101 to set {year, its month, day, time} to {1970, 1, 1, 0}
	set eraTime to (GMTDate - date19700101)
	if (eraTime > 99999999) then
		set eraTime to (eraTime div 100000000 as text) & text 2 thru 9 of (100000000 + eraTime mod 100000000 as integer as text)
	else if (eraTime < -99999999) then
		set eraTime to (eraTime div 100000000 as text) & text 3 thru 10 of (-100000000 + eraTime mod 100000000 as integer as text)
	else
		set eraTime to eraTime as text
	end if
	
	return isotToDate(do shell script ("TZ='" & TZ & "' /bin/date -r " & eraTime & " +%Y%m%dT%H%M%S"))
end GMTtoTZ

set myTimeZone to (do shell script ("/usr/bin/perl -le 'print( readlink(\"/etc/localtime\") =~m{zoneinfo/(.*)} )' ")) -- Perl code by Mark J. Reed.
TZtoGMT(dateReturnedByNumbers, myTimeZone)

Edit: Yvan’s pointed out to me privately that my original method for converting the era time to text in the GMTtoTZ() handler didn’t work on systems which use commas for decimal points. I’ve now rewritten that section to split large “E” numbers arithmetically rather than textually, which incidentally makes it more efficient.