Google Calendar XML

[Steve Horn]

Has anyone done anything with Google Calendar's XML format, i.e. capturing, parsing and displaying in one form or another on a HB panel? I use Google Calendar, via Firefox on PCs and now with a smart phone capable of it as well. It seems appropriate to be able to display the same info on the HB tablets in the house. I've looked at the XML and identified the useful info. But am pretty green to XML parsing via vbscript, although I do have a partially functional weather feed working. But before I launch into this (at least until I lose enthusiasm or run short of time), I thought I'd check to see if anyone else here has already been down this road.

[Timoh]

Hi Steve,
Are you green to XML in general or XML parsing in vbscript?
From what I've learned doing a ton of XML stuff in my C++ web server plugin and javascript client, is that all libraries pretty much work the same way. Define you xml document, load stuff into it, define your root element, and then step through the nodes. And that is where is get's interesting... There are many ways to do that. I don't know if vbscript has native XML calls you can use or whether you need a seperate library to do it well.

Usually I found you need to setup some form of loop for each node in the doc. So if you xml is something like...
<Year>
<Month>
<Day>
<event time=10:00am> Dr. Appt </event>
</Day>
</Month>
</Year>

You would setup a loop to go through years, months, days, until you get to what you want, and can then pull values, attributes, for the node you are on.

Tim

[Steve Horn]

Pale green at XML, deeper green at how to parse the XML metadata with VBScript. The challenging part of this exercise I think will be to weed out the actual calendar info (when, where and what) from the node data. The Google XML is not as you described it (year, month ,day, time..). Rather (if I read this correctly), the nodes are, in part, <entry>, <published>,<updated>, <summary>, <content>. The what, when, and where info is embedded in the <content> node and looks something like this as:
<content type=<html">
When: Sat Jun 2, 2012<br /> <br />Where: RR Park <br />Event Status: confirmed <br />Event Description: Tchaikovsky Favorites
</content>

So, depending upon how the data is to be displayed, a fair amount of heavy lifting will be necessary to organize the data based on day/date and time. But you're right, looping until done or for a fixed number of entries would be the play, loading the resulting data into null device properties.
I was planning to use (or at least start with) the vbscript XML routines that are in the weather script that I either built up or stole from someone here.

[edgar]

Hey folks,

I cannot imaging this would be too difficult to script in VBS, especially for a VBS guru, unfortunately which I am not. The reason I say this is that I was able to get a script that checks my ENVI Powermeter. It spits out XML every 6 seconds. I created a generic serial device and launch a simple script to parse only the fields i needed using XMLDOM:

here is my basic code, while not very elegant, but it works!

v/r

Kevin

' Dim your variables
Dim EnviXML
Dim NodeList
Dim NodeList2
Dim NodeList3
Dim NodeList4
Dim objXMLDOC
Dim Root

EnviXML = GetPropertyvalue("Envi PowerMeter.Received Data")

Set objXMLDOC = CreateObject("Microsoft.XMLDOM")

' do not forget to use loadXML vice just load. Load is for a file...not the raw XML!

objXMLDOC.async = False
objXMLDOC.loadXML(EnviXML)

Set Root = objXMLDOC.documentElement
Set NodeList = Root.getElementsByTagName("ch2/watts")
Set NodeList2= Root.getElementsByTagName("ch1/watts")
Set NodeList3= Root.getElementsByTagname("tmprF")
Set NodeList4= Root.getElementsByTagname("time")

For each Elem in NodeList
SetPropertyValue "Envi: Data.CH 1 watts", Elem.firstchild.nodeValue & vbLF
Next

For each Elem in NodeList2
SetPropertyValue "Envi: Data.CH 2 watts", Elem.firstchild.nodeValue & vbLF
Next

For each Elem in NodeList3
SetPropertyValue "Envi: Data.Temp in F", Elem.firstchild.nodeValue & vbLF
Next

For each Elem in NodeList4
SetPropertyValue "Envi: Data.Time", Elem.firstchild.nodeValue & vbLF
Next

Set objXMLDOC = Nothing

[Steve Horn]

... And it may not be that big of a problem. But what surprised me when I saw the structure of the XML metadata was that the what, when, and where of a typical calendar entry were not separate 'nodes' (if that's the proper designation) of the file. Rather, all the what/when/where is embedded in a 'content' node. So further parsing of the captured content to extract not only the date, event info, but the (optional) begin and end hours is needed.
Here is one complete calendar entry:

typical Google Calendar XML entry

goocalxml.JPG (86.06 KiB) Viewed 5735 times

Notice the Content node: date, 'from' and 'to' time etc. are essentially just a string within the node data. Other calendar entries do not have 'from' and 'to' times; they are just tied to a date. So parsing the content data returned from the XML parse call will need to be flexible enough to recognize varying formats of the content string.

[Timoh]

You should be able to get to your content node easily with the vbscript xml capabilities, but after that it will be string matching to get what you need.

You could setup a whole bunch of string matches for the bits which are consistent, and then grab the positions of each, break it into sub strings and parse from there's, trim off the bits you don't need.

Or you could look into regex or regular expressions. It is a string matching "language". Very powerful with a bit of a learning curve. There are plenty of sites where you can enter your text and play with regex on the fly and see your results immediately.

Tim

[Steve Horn]

I agree with your approach and pretty much concluded the same thing. I need to define all the possible formats of the content string. Will set up some dummy appointments in various flavors then look at the resulting XML. Rainy day project - sounds like no one fro HB-Land has been here before though.
Thanks for the tip on regex - didn't know about that.

[Timoh]

Could be a very interesting project when it comes to thinking outside the HB box... ie Not just using it for the users calander, but also HBs calendar.

You could conceivably build your automation schedule on Google and have HB pikc it up... Lights on at 6pm, temperature change at noon, alarm arm at 9am, etc. Modify your HB schedule from anywhere in the world and have HB respond.

Tim

[Steve Horn]

Whoah, you ARE outside the box. I'll be doing good if I can get it to capture and display my events... such as they are. I haven't gone any further on it other than to think about it. Decided that the most flexible approach would be to capture the content data, parse it, then load it into an SQLite table so that it can be 'reported on' in whatever way - i.e. day, week, month, agenda. But all that's a challenge to me, both from a VBScript, XML, and SQLite standpoint. Baby steps.
4/11: it occurred to me that in order to be able to access the XML version of a calendar, the calendar must be marked public, meaning anyone could stumble across it during a web search. So putting HB-related schedule info into Google Calendar with the intent of HB getting updates via Calendar should be done with that in mind.

[allanstevens]

I wrote a script last year that returns the current days events from google calendar. But only got to the stage of writing it outside housebot, with msgbox alerts. Just never got round to putting it into a script device. Feel free to mess about with it, if it's what you're after

It's similar to my google contacts script.

Code: Select all

email = "[email protected]" 
passwd = "PASSWORD" 

Set objHTTP = CreateObject("Microsoft.XMLHTTP") 
objHTTP.open "POST", "https://www.google.com/accounts/ClientLogin", FALSE 
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
objHTTP.send "Email=" + email + "&Passwd=" + passwd + "&service=cl&source=HouseBotScript" 

If objHTTP.status <> 200 Then
	MsgBox "Unable to log on"
Else

	strAuthTokens = objHTTP.responseText
	strAuthTokens = Replace(strAuthTokens, vbCr, "")
	strAuthTokens = Replace(strAuthTokens, vbLf, "")
	strAuthTokens = Replace(strAuthTokens, vbCrLf, "")
	strAuthTokens = Replace(strAuthTokens, "SID", "&SID", 1, 1)
	strAuthTokens = Replace(strAuthTokens, "LSID", "&LSID")
	strAuthTokens = Replace(strAuthTokens, "Auth", "&Auth")
	strAuthTokens = Right(strAuthTokens, Len(strAuthTokens)-Len("Auth=")-InStr(strAuthTokens, "Auth=")+1)
	
	Set objHTTP = Nothing
   
	Set objHTTP = CreateObject("Microsoft.XMLHTTP")
				
	objHTTP.open "GET", "https://www.google.com/calendar/feeds/default/private/full?start-min=" & TodaysDate & "T00:00:00&start-max=" & TodaysDate & "T23:59:59", FALSE
	'objHTTP.open "GET", "https://www.google.com/calendar/feeds/default/owncalendars/full", FALSE
	objHTTP.setRequestHeader "Content-Type", "application/atom+xml" 
	objHTTP.setRequestHeader "X-If-No-Redirect", "True"
	objHTTP.setRequestHeader "Authorization", "GoogleLogin auth=" & strAuthTokens
	objHTTP.send 
	
	headers = objHTTP.getAllResponseHeaders()
	strResponse = objHTTP.responseText
	
	Set objHTTP = Nothing
	
	Display strResponse
	
End If


Function Display(message) 

	'Output complete xml to file (for dev)
	'Set myFSO = CreateObject("Scripting.FileSystemObject")
	'Set WriteStuff = myFSO.OpenTextFile("google.xml", 8, True)
	'WriteStuff.WriteLine(message)
	'WriteStuff.Close
	'SET WriteStuff = Nothing
	'SET myFSO = Nothing
	'MsgBox message
	
	Set objXml = CreateObject("Microsoft.XMLDOM")
	objXml.async = false
	objXml.loadxml (message)
	
	'Step through each calander event
	For Each NodeEntriy in objXml.selectNodes("/feed/entry")
		'Add this to housebot
		MsgBox "Title: " & NodeEntriy.selectSingleNode("title").text & ",  When: " & NodeEntriy.selectSingleNode("gd:when/@startTime").text
	Next

	Set objXml = Nothing
	
	If Err.Number <> 0 Then 
        'Add HouseBot Error Logging
        End If
	
End Function

Function TodaysDate

	Dim y,m,d,dt
	
	dt = Now()
	
	y = year(dt)
	m=month(dt)
	d=day(dt)
	
	if m < 10 then m="0" & m
	if d < 10 then d="0" & d	

	TodaysDate = y & "-" & m & "-" & d
	
End Function

[Steve Horn]

Allen, thanks for the code. It may help with the node parsing piece. Now trying to ramp up on the navigation/traversing of the XML via msxml. Maybe an old dog CAN learn new tricks. Or maybe not...
4/11 6pm: Allen, thanks for the script; it was a HUGE help! All the data is being pulled and parsed. Now need to load into a table...

[allanstevens]

Pleased it helped.

Just an idea, but could you skip using a database? Just have the script re-run with different start-min and start-max, depending if you choose Day, Month, Year, etc. It would then repopulate an AlphaList in your script device.

[Steve Horn]

Yes, could do that. My thinking was to refresh the table once or twice a day, then depending upon the display option (day, week, month, agenda), pull the associated data based on start/end dates. But your approach might be more efficient.
What I have noticed, but haven't had a chance to investigate, is that recurring events (i.e every month on 2nd Thursday) as built by Google Calendar are not being pulled. But I haven't searched the raw XML data yet to see if they're in there.
One more observation that I haven't investigated... your approach to pulling the data via what I call the gmail method (via the use of the user's gmail account) differs from the way I started with - using the XML stream created by the URL in one of my previous posts. The resulting XML metadata structure is different... The gmail method seems easier to navigate and parse... to me anyway.

[allanstevens]

I don't think I have any reoccurring events, so had not noticed. Just comment back in the section 'Output complete xml to file (for dev)' and it will create a google.xml file with all the xml.

Hopefully it's there, and you can use some xpath with NodeEntriy.selectSingleNode("XPATH").text. I don't know what I would do without xpath, very powerful stuff.

Good luck

[Steve Horn]

I did the google.xml dump yest to get a feel for the format of the data. Looked for some recurring events in it this morning. None. Also dumped the XML using the other method I had planned to use and there were none in there either. So there must be some other means of "requesting" that data via the gmail or other method that we don't know about. No big deal - just curious.
xpath? Need to look into that too. so much to learn; so little time...