Exporting Data to an Ongoing Numbers Spreadsheet

Scripting Forums AppleScript | Mac OS X
#1

I am running an App on my Mac OS X server called Indigo which controls many aspects of my smart home.

The app contains variable data that monitors things like, how long the air conditioner runs in a given day.

What I want to do, is I want to grab that data every night at midnight, before it gets reset, and populate it into the next available row in a Numbers spreadsheet. It would go along side a date stamp, and a daily average of the temperature.

Looking back after a month, I’ll be able to illustrate air conditioner usage compared to relative outdoor temperatures, and be able to estimate my monthly energy usage (given that most utility company’s estimate that A/C use represents roughly 50% of your electric use).

Here’s an example of what I want to generate automatically using an AppleScript that runs at midnight each night:
http://increasinglycomplex.tumblr.com/post/32933875533/the-ideal-example-of-automatic-cell-data-i-want-to

Challenges I can forsee include:

  1. AppleScript identifying the next empty row for data input.
  2. Exporting my variables into duration values that Numbers understands.
#2

It would be useful to know what the downloaded datas resemble to to be able to decipher them before putting them in the table.

Yvan KOENIG (VALLAURIS, France) vendredi 5 octobre 2012 16:03:03

#3

The duration value would be available as a string in this format: HH:MM:SS

Same thing for the “vacancy time”.

And the temperature would be a numerical value.

The date stamp would come from within AppleScript, I presume.

#4

Script 1 :

the delivered datas are supposed to contain the date, the temperature and the two time values


# Here I assume that you get a string whose values are separated by commas.

set grabbedDatas to "blahBlah, 4/10/2012, 89.58, 13:25:43, 4:34:22, beurk"

set grabbedDatas to my decoupe(grabbedDatas, ",")
repeat with i from 1 to count grabbedDatas
	if item i of grabbedDatas contains "/" then exit repeat
end repeat
# Here i is the index of the date in the list 
# Extract this value and the three other just after it
set {theDate, theTemperature, theTotalRunTime, theVacantTime} to items i thru (i + 3) of grabbedDatas

set theTemperature to my supprime(theTemperature, space)
if theTemperature does not end with "°F" then set theTemperature to theTemperature & "°F"

set theTotalRunTime to my supprime(theTotalRunTime, space)
set {h, m, s} to my decoupe(theTotalRunTime, ":")
set theTotalRunTime to (h as text) & "h " & m & "m " & s & "s"

set theVacantTime to my supprime(theVacantTime, space)
set {h, m, s} to my decoupe(theVacantTime, ":")
set theVacantTime to (h as text) & "h " & m & "m " & s & "s"

# Now the values are in valid formats.

set docName to "kkachurak.numbers"
set sheetName to "Sheet 1"
set tableName to "kkachurak Table 1"
set theDoc to (path to desktop as text) & docName

tell application "Numbers"
	open theDoc
	tell document docName to tell sheet 1
		set sName to its name
		set tNames to name of tables
		if (not sName = sheetName) or (tNames does not contain tableName) then error "This doc is not the needed one !"
		set foundEmptyCell to false
		tell table tableName
			repeat with r from 2 to count rows
				if (value of cell 1 of row r) = 0.0 then
					set foundEmptyCell to true
					exit repeat
				end if
			end repeat
			if not foundEmptyCell then
				# every cells of column 1 are filled, must create a new row
				add row below row r
				set r to r + 1
			end if
			# Here, r is the index to the first row whose cell 1 is empty
			tell row r
				set value of cell 1 to theDate
				set value of cell 2 to theTemperature
				set value of cell 3 to theTotalRunTime
				set value of cell 4 to theVacantTime
			end tell # row
		end tell # table
	end tell # document & sheet
end tell # Numbers

--=====

on decoupe(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to ""
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end supprime

--=====

Script 2
The datas are supposed to contain only the temperature and the two time values


# Here I assume that you get a string whose values are separated by commas.

set grabbedDatas to "89.58, 13:25:43, 4:34:22, beurk"

set grabbedDatas to my decoupe(grabbedDatas, ",")
repeat with i from 1 to count grabbedDatas
	if item i of grabbedDatas contains "/" then exit repeat
end repeat
# Here i is the index of the date in the list 
# Extract this value and the three other just after it
set {theTemperature, theTotalRunTime, theVacantTime} to items 1 thru 3 of grabbedDatas

set theDate to short date string of (current date)

set theTemperature to my supprime(theTemperature, space)
if theTemperature does not end with "°F" then set theTemperature to theTemperature & "°F"

set theTotalRunTime to my supprime(theTotalRunTime, space)
set {h, m, s} to my decoupe(theTotalRunTime, ":")
set theTotalRunTime to (h as text) & "h " & m & "m " & s & "s"

set theVacantTime to my supprime(theVacantTime, space)
set {h, m, s} to my decoupe(theVacantTime, ":")
set theVacantTime to (h as text) & "h " & m & "m " & s & "s"

# Now the values are in valid formats.

set docName to "kkachurak.numbers"
set sheetName to "Sheet 1"
set tableName to "kkachurak Table 1"
set theDoc to (path to desktop as text) & docName

tell application "Numbers"
	open theDoc
	tell document docName to tell sheet 1
		set sName to its name
		set tNames to name of tables
		if (not sName = sheetName) or (tNames does not contain tableName) then error "This doc is not the needed one !"
		set foundEmptyCell to false
		tell table tableName
			repeat with r from 2 to count rows
				if (value of cell 1 of row r) = 0.0 then
					set foundEmptyCell to true
					exit repeat
				end if
			end repeat
			if not foundEmptyCell then
				# every cells of column 1 are filled, must create a new row
				add row below row r
				set r to r + 1
			end if
			# Here, r is the index to the first row whose cell 1 is empty
			tell row r
				set value of cell 1 to theDate
				set value of cell 2 to theTemperature
				set value of cell 3 to theTotalRunTime
				set value of cell 4 to theVacantTime
			end tell # row
		end tell # table
	end tell # document & sheet
end tell # Numbers

--=====

on decoupe(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to ""
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end supprime

--=====

I don’t know the behavior of your server so I can’t help about the process grabbing the datas.

Yvan KOENIG (VALLAURIS, France) vendredi 5 octobre 2012 17:46:23

#5

Hi.

I’m no more clear than Yvan is about the form in which your data’s arriving. Just to observe that if your date column’s formatted to show dates in a preferred form, you can set the value of any cell in it to any date string which conforms to the user preferences on your machine and it will be displayed in the form you’ve set for the column.

If your temperature column’s formatted to show reals to two decimal places followed by a “℉” character (or a two-character equivalent), you only need to enter the real value and the formatting will be taken care of.

And if your time columns are formatted for durations of the type shown in your picture, you can simply enter the HH:MM:SS strings and Numbers will again make the conversions:

set {avTemp, totalRunTime, VacantTime} to {"89.58", "13:25:43", "4:34:22"} -- I'm not clear how these are obtained.

tell application "Numbers"
	tell table 1 of sheet 1 of document 1
		tell (last cell of column 1 whose value is not 0.0)
			if (it exists) then
				set r to (address of its row) + 1
			else
				set r to 2
			end if
		end tell
		set value of cell 1 of row r to date string of (current date)
		set value of cell 2 of row r to avTemp as real
		set value of cell 3 of row r to totalRunTime
		set value of cell 4 of row r to VacantTime
	end tell
end tell
#6

Hello Nigel
If avTemp is a number, don’t pass it as a real but as a string.
It may require to code
set value of cell 2 of row r to (avTemp as real) as text

On a machine running with the decimal period it will make no difference but on a machine running with the decimal comma it’s absolutely required. [b]With this settings[/b] we must pass the numerical values as text ones.

  • value 12.34
    It’s a correct real number in AppleScript which work with the decimal period for arithmetic.
    set value of cell 2 of row 10 to 12.34 will insert the value 12.34 which will be deciphered as a STRING

If I take care to coerce it as a string
12.34 will become the string “12,34”
set value of cell 2 of row 10 to “12,34 will” insert the value 12,34 which will be deciphered as a NUMBER.

In my code, as I concatenate the value and the string “°F” the passed value stay as is : a string.

If you want to pass a numerical value, the beginning of my scripts would be :

beg of script 1


# Here I assume that you get a string whose values are separated by commas.

set grabbedDatas to "blahBlah, 4/10/2012, 89.58, 13:25:43, 4:34:22, beurk"

set grabbedDatas to my decoupe(grabbedDatas, ",")
repeat with i from 1 to count grabbedDatas
	if item i of grabbedDatas contains "/" then exit repeat
end repeat
# Here i is the index of the date in the list 
# Extract this value and the three other just after it
set {theDate, theTemperature, theTotalRunTime, theVacantTime} to items i thru (i + 3) of grabbedDatas

set theTemperature to my supprime(theTemperature, space)

if theTemperature contains "." then
	my supprime(theTemperature, ".")
else if theTemperature contains "," then
	my supprime(theTemperature, ",")
end if
set theTemperature to ((result as integer) / 100) as text
--if theTemperature does not end with "°F" then set theTemperature to theTemperature & "°F"

set theTotalRunTime to my supprime(theTotalRunTime, space)

set theVacantTime to my supprime(theVacantTime, space)

beg of script 2


# Here I assume that you get a string whose values are separated by commas.

set grabbedDatas to "89.58, 13:25:43, 4:34:22, beurk"

set grabbedDatas to my decoupe(grabbedDatas, ",")
repeat with i from 1 to count grabbedDatas
	if item i of grabbedDatas contains "/" then exit repeat
end repeat
# Here i is the index of the date in the list 
# Extract this value and the three other just after it
set {theTemperature, theTotalRunTime, theVacantTime} to items 1 thru 3 of grabbedDatas

set theDate to short date string of (current date)

set theTemperature to my supprime(theTemperature, space)
if theTemperature contains "." then
	my supprime(theTemperature, ".")
else if theTemperature contains "," then
	my supprime(theTemperature, ",")
end if
set theTemperature to ((result as integer) / 100) as text
--if theTemperature does not end with "°F" then set theTemperature to theTemperature & "°F"

set theTotalRunTime to my supprime(theTotalRunTime, space)

set theVacantTime to my supprime(theVacantTime, space)

Yvan KOENIG (VALLAURIS, France) vendredi 5 octobre 2012 21:43:07

#7

Thanks, Yvan.

I hadn’t considered what might happen if the system’s set for decimal commas in text. But since kkachurak’s screen dump shows decimal points, there was probably no need.

I’ve been testing what you describe with my own system set for commas. The behaviour’s very kooky and undoubtedly a bug. The effect when setting a cell value to a real is the same as if the real had been coerced to text on a decimal-point system and the result coerced back to real on a decimal-comma system. Weird. :confused:

Your ‘(avTemp as real) as text’ suggestion doesn’t make any difference on my machine.

#8

Hello Nigel

Bug or feature, honestly, it’s not my problem.
Numbers behave this way since january 2008.
Given my old practice of betatester for AppleWorks, I work with the apps as they are, and don’t wait for a possible revised version.
I reported oddities in january 2008 and they are always striking so, I think that I took the good decisions.

Writing
“It may require to code
set value of cell 2 of row r to (avTemp as real) as text”
was a bad idea because there was a huge chance that readers miss the word may.

The two subsets of scripts described the correct scheme.

1 - a piece of code extract the passed value (theTemperature ) as a string one (I don’t know the original format and assumed that datas where passed in a string)
2 - the instructions :
if theTemperature contains “.” then
my supprime(theTemperature, “.”)
else if theTemperature contains “,” then
my supprime(theTemperature, “,”)
end if
remove the decimal character
so we get a string giving the temperature in cents of °F
Converting this string into integer is OK
3 - set theTemperature to ((result as integer) / 100) as text
retun the temperature in a string matching the system settings.
on a system using the decimal period, it will be “89.58”
on a system using the decimal comma, it will be “89,58”
so it will correctly treated by Numbers.

My guess is that the passed value is deciphered by the code which is used to decipher the manual entries in a cell.
Here, this code requires the comma as decimal separator.
This way,
using set value of aCell to “89,58” or
using paste “89,58” in aCell (using GUIScripting)
behave the same.

I discovered that the passed treatment behaved wrongly if the passed value was 89.5 so, here is a better scheme.



# Here I assume that you get a string whose values are separated by commas.
set localDeci to character 2 of (0.5 as text)
if result is "." then
	set foreignDeci to ","
else
	set foreignDeci to "."
end if

set grabbedDatas to "89.50, 13:25:43, 4:34:22, beurk"

set grabbedDatas to my decoupe(grabbedDatas, ",")
repeat with i from 1 to count grabbedDatas
	if item i of grabbedDatas contains "/" then exit repeat
end repeat
# Here i is the index of the date in the list 
# Extract this value and the three other just after it
set {theTemperature, theTotalRunTime, theVacantTime} to items 1 thru 3 of grabbedDatas

set theDate to short date string of (current date)

set theTemperature to my supprime(theTemperature, space)
if theTemperature contains foreignDeci then set theTemperature to my remplace(theTemperature, foreignDeci, localDeci)
log theTemperature

--if theTemperature does not end with "°F" then set theTemperature to theTemperature & "°F"

set theTotalRunTime to my supprime(theTotalRunTime, space)

set theVacantTime to my supprime(theVacantTime, space)

--=====

on decoupe(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to oTIDs
	return l
end decoupe

--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d1
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to ""
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end supprime

--=====

I know that it’s not perfect because, if it’s OK for temperature, it may be odd with amounts because English thousand separator is the comma when it’s a NO BREAK SPACE in French.
So, the test upon foreignDeci may return an odd result.

Yvan KOENIG (VALLAURIS, France) samedi 6 octobre 2012 15:37:40

#9

Hello Nigel

You are really optimistic.
At least once a week, I receive datas using the decimal period on my machines using the decimal comma.
It’s why I am so involved in this problem.

Here is a handler which works fine on my system using decimal comma.



set someValues to {"1,234.567", "1,234.567,8", "1'234,567'8", "1 234,567 8", "1.234.567", "1,234,567", "1 234 567,89", "12.34", "1,234", "1.234", "1234", "1.234.567.890,123", "1,234,567,890.123"}
set someCleaned to {}
repeat with aValue in someValues
	set end of someCleaned to my localizeThisValue(aValue)
end repeat
log someCleaned

# On my system using decimal comma, I get :
-->{1234,567, 1234,5678, 1234,5678, 1234,5678, 1234567, 1234567, 1234567,89, 12,34, ?1,234?, ?1.234?, 1234, 1234567890,123, 1234567890,123}
# On a system using decimal period, It's supposed to return :
-->{1234.567, 1234.5678, 1234.5678, 1234.5678, 1234567, 1234567, 1234567.89, 12.34, ?1,234?, ?1.234?, 1234, 1234567890.123, 1234567890.123}

on localizeThisValue(aValue)
	set digits to {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}
	set localDeci to character 2 of (0.5 as text)
	
	repeat while aValue starts with space
		if aValue is space then
			set aValue to ""
		else
			set aValue to text 2 thru -1 of aValue
		end if
	end repeat
	
	repeat while aValue ends with space
		set aValue to text 1 thru -2 of aValue
	end repeat
	
	set {delim1, off1} to {{}, {}}
	repeat with i from 1 to count aValue
		set aChar to text item i of aValue
		if aChar is not in digits then
			set end of delim1 to aChar
			set end of off1 to i
			exit repeat
		end if
	end repeat
	set {delim2, off2} to {{}, {}}
	if delim1 is not {} then
		repeat with i from off1 to count aValue by 4
			if (i + 4) > (count aValue) then exit repeat
			set aChar to text item (i + 4) of aValue
			if aChar is not in digits then
				if aChar = (item 1 of delim1) then
					set end of off1 to (i + 4)
				else
					set end of delim2 to aChar
					set end of off2 to (i + 4)
				end if # aChar = delim1
			end if # achar is not in digits
		end repeat
	end if # delim1 is not {}
	log ">>>> " & aValue
	log delim1
	log off1
	log delim2
	log off2
	if delim1 is not {} then
		set maybe1 to item 1 of delim1
		if delim2 is {} then
			set maybe2 to ""
		else
			set maybe2 to item 1 of delim2
		end if
		if maybe1 is in {",", "."} and maybe2 is not in {",", "."} then
			if ((count off1) = 1) then
				if (count aValue) - (item 1 of off1) = 3 then
					log "case 00"
					# Can't decide if it's the real vw.xyz or the integer vwxyz
					set aValue to "?" & aValue & "?"
				else
					log "case 0"
					if maybe2 is not "" then set aValue to my supprime(aValue, maybe2)
					set aValue to my remplace(aValue, maybe1, localDeci)
				end if
			else
				log "case 1"
				# no decimal separator
				set aValue to my supprime(aValue, maybe1)
				set aValue to my supprime(aValue, maybe2)
			end if
		else if maybe1 = "," then
			if maybe2 = "." then
				log "case 2"
				# xx,xxx.xxx is illegal with decimal comma
				# so "." is used as decimal separator
				set aValue to my supprime(aValue, maybe1)
				set aValue to my remplace(aValue, maybe2, localDeci)
			else
				log "case 3"
				# "," is used as decimal separator
				set aValue to my supprime(aValue, maybe2)
				set aValue to my remplace(aValue, maybe1, localDeci)
			end if # if maybe2 is "."
		else if maybe1 = "." then
			if maybe2 = "," then
				if (count off1) > 1 then
					log "case 4.1"
					# officially, xx.xxx.xxx,xxx is illegal with decimal comma but when it's used, we may decipher it
					# so "." is used as decimal separator
					set aValue to my supprime(aValue, ".")
					set aValue to my remplace(aValue, ",", localDeci)
				else
					log "case 4.2"
					# officially, xx.xxx,xxx is illegal with decimal comma
					# so "." is used as decimal separator
					set aValue to my supprime(aValue, ",")
					set aValue to my remplace(aValue, ".", localDeci)
				end if
			else
				log "case 5"
				# "." is used as decimal separator
				set aValue to my supprime(aValue, maybe2)
				set aValue to my remplace(aValue, maybe1, localDeci)
			end if # if delim2 is "."
		else
			log "case 6"
			# maybe1 is a thousand separator, drop it
			set aValue to my supprime(aValue, maybe1)
			set aValue to my remplace(aValue, maybe2, localDeci)
		end if
	else
		log "case 7"
	end if
	return aValue
end localizeThisValue

--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d1
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to "" & l
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
	local oTIDs, l
	set oTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to d
	set l to text items of t
	set AppleScript's text item delimiters to ""
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end supprime

--=====

There is just a case (00) which can’t decide what is the real value.
I never ran it on a system with decimal period.

Yvan KOENIG (VALLAURIS, France) dimanche 7 octobre 2012 17:13:49