Google Calendar XML

General HouseBot discussion. Any issues that don't fit into any of the other topics belong here.
allanstevens
Member
Posts: 81
Joined: Thu Sep 01, 2005 7:56 am
Location: UK

Re: Google Calendar XML

Post by allanstevens »

Steve Horn wrote:so much to learn; so little time...
I know the feeling!

I'll add some reoccuring events to my calendar and see if I can see anything.

I find XPath a great way of querying xml without stepping though elements one by one. And it's fast too. There are some good examples and tutorials on w3 schools.

http://www.w3schools.com/xpath/xpath_examples.asp

XPath that is used in my script, you have probably already used it without realising:

Selects each 'entry' element'

Code: Select all

objXml.selectNodes("/feed/entry") 
Returns the text in the 'title' element

Code: Select all

NodeEntriy.selectSingleNode("title").text
Returns the text in the 'startTime' attribute of the 'when' element.

Code: Select all

NodeEntriy.selectSingleNode("gd:when/@startTime").text
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

Ah, hah...! That explains the code pieces that I didn't recognize. Thanks!
Steve
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

Recurrence...
I'm now getting recurrence metadata showing up in the raw XML file, and it is crashing the script. The first -entry has no recurrence data; has a gd:when start and end date/time. The 2nd -entry in the XML is for the same 'event' but is for a different date(s) and has gd:recurrence data in it instead of the gd:when data. So when the script does a
StartData = NodeEntriy.selectSingleNode("gd:when/@startTime").text

It finds nothing and errors with '...no object' error.
And to make matters nastier, the recurrence data is really ugly:

gd:recurrence>DTSTART;TZID=America/Chicago:20110813T180000
DTEND;TZID=America/Chicago:20110813T190000
RRULE:FREQ=MONTHLY;BYDAY=2SA;UNTIL=20120311T000000Z
BEGIN:VTIMEZONE
TZID:America/Chicago
X-LIC-LOCATION:America/Chicago
BEGIN:DAYLIGHT
TZOFFSETFROM:-0600
TZOFFSETTO:-0500
TZNAME:CDT
DTSTART:19700308T020000
RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU
END:DAYLIGHT
BEGIN:STANDARD
TZOFFSETFROM:-0500
TZOFFSETTO:-0600
TZNAME:CST
DTSTART:19701101T020000
RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU
END:STANDARD
END:VTIMEZONE

Looking at various info on the net regarding Google Calendar Recurrence, it appears to me that the overall syntax of the XML is FAR more standard than just Google's idea of how calendar data should be reflected. Exhibit A: http://tools.ietf.org/html/rfc2445 .

I fear that if there are recurrent entries in Google Calendar any script to extract and parse the data for use by HB will either have to ignore them, or those entries would need to be removed and replaced with individual entries.

And I don't see a way of ignoring them in the script.
Steve
allanstevens
Member
Posts: 81
Joined: Thu Sep 01, 2005 7:56 am
Location: UK

Re: Google Calendar XML

Post by allanstevens »

I have just added two recurrence events in my calendar. One for today, and one for yesterday. Both repeating per day, 5 times.

When I run the script to return today's events, can can see both items with the correct @gd:when startTime, etc. I can see the recurrence element, but do you need to look at it?

One thing I noticed, that I had not realised. There are two gd:when elements, one with the startTime and one with just valueString="". I wonder if the xpath is picking up the incorrect one?

Can you try "gd:when[0]/@startTime"
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

I added the [0] designation to the xpath call to gd:when. The script still fails at the event (entry) that has the recurrence metadata in that entry. And, looking at the 'raw' XML, that entry has no other 'when' tag. So its failing because its looking for something in that entry that does not exist. Is there a way to test, using xpath or other means, the existence of the tag, i.e. a Boolean true/false, that could be used in an If-Then-End If? Of course, that does not explain why your test recurring events have 'when' tags as well as recurrence metadata and this one does not. Maybe I need to delete it and re-enter to see what shows up...
Steve
allanstevens
Member
Posts: 81
Joined: Thu Sep 01, 2005 7:56 am
Location: UK

Re: Google Calendar XML

Post by allanstevens »

That is odd, you can check that the element/attribute exists first with the following:

Code: Select all

If Not (NodeEntriy.selectSingleNode("gd:when/@startTime") Is Nothing) Then
     MsgBox NodeEntriy.selectSingleNode("gd:when/@startTime").Text
End If
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

That works like a champ. I had, last night, deleted the offending recurrence cal entry and input another and got similar results: The recurrent entry was listed first in the XML, including the recurrence metadata and with no gd:when tag. So the script failed immediately. With the existence test the recurring event is just skipped. Still a problem obviously but not a deal killer. Your experiences with your recurrence test is that you get the recurrence metadata in the XML as well as the gd:when data?
I guess that with that in place I can work on identifying, parsing, and making some sense out of the recurrence metadata so that it can be used.
Steve
allanstevens
Member
Posts: 81
Joined: Thu Sep 01, 2005 7:56 am
Location: UK

Re: Google Calendar XML

Post by allanstevens »

Yes, I get both gd:recurrence and gd:when elements.

Although NOT 2 gd:when elements, my eyes! I had mistaken the gd:where element. doh.

See example of xml, I have stripped some of it out, and just included the <entry> elements. I hope it helps.

Code: Select all

<entry>
  <id>http://www.google.com/calendar/feeds/default/private/full/</id> 
  <published>2012-04-18T14:56:50.000Z</published> 
  <updated>2012-04-18T14:56:50.000Z</updated> 
  <category scheme="http://schemas.google.com/g/2005#kind" term="http://schemas.google.com/g/2005#event" /> 
  <title type="text">rec test 2</title> 
  <content type="text" /> 
  <link rel="alternate" type="text/html" href="https://www.google.com/calendar/event?eid=stripped" title="alternate" /> 
  <link rel="self" type="application/atom+xml" href="https://www.google.com/calendar/feeds/default/private/full/stripped" /> 
  <link rel="edit" type="application/atom+xml" href="https://www.google.com/calendar/feeds/default/private/full/stripped" /> 
  <author>
  <gd:eventStatus value="http://schemas.google.com/g/2005#event.confirmed" /> 
  <gd:where valueString="" /> 
  <gd:who email="stripped" rel="http://schemas.google.com/g/2005#event.organizer" valueString="stripped" /> 
  <gd:recurrence>DTSTART;TZID=Europe/London:20120418T050000 DTEND;TZID=Europe/London:20120418T060000 RRULE:FREQ=DAILY;COUNT=4 BEGIN:VTIMEZONE TZID:Europe/London X-LIC-LOCATION:Europe/London BEGIN:DAYLIGHT TZOFFSETFROM:+0000 TZOFFSETTO:+0100 TZNAME:BST DTSTART:19700329T010000 RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU END:DAYLIGHT BEGIN:STANDARD TZOFFSETFROM:+0100 TZOFFSETTO:+0000 TZNAME:GMT DTSTART:19701025T020000 RRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU END:STANDARD END:VTIMEZONE</gd:recurrence> 
  <gd:reminder method="email" minutes="10" /> 
  <gd:reminder method="alert" minutes="10" /> 
  <gd:when endTime="2012-04-19T06:00:00.000+01:00" startTime="2012-04-19T05:00:00.000+01:00">
  <gd:reminder method="email" minutes="10" /> 
  <gd:reminder method="alert" minutes="10" /> 
  </gd:when>
  <gd:transparency value="http://schemas.google.com/g/2005#event.opaque" /> 
  <gd:visibility value="http://schemas.google.com/g/2005#event.default" /> 
  <gCal:anyoneCanAddSelf value="false" /> 
  <gCal:guestsCanInviteOthers value="true" /> 
  <gCal:guestsCanModify value="false" /> 
  <gCal:guestsCanSeeGuests value="true" /> 
  <gCal:sequence value="0" /> 
  <gCal:uid value="stripped" /> 
</entry>

<entry>
  <id>http://www.google.com/calendar/feeds/default/private/full/stripped</id> 
  <published>2012-04-18T14:54:22.000Z</published> 
  <updated>2012-04-18T14:56:28.000Z</updated> 
  <category scheme="http://schemas.google.com/g/2005#kind" term="http://schemas.google.com/g/2005#event" /> 
  <title type="text">rec test 1</title> 
  <content type="text" /> 
  <link rel="alternate" type="text/html" href="https://www.google.com/calendar/event?eid=stripped" title="alternate" /> 
  <link rel="self" type="application/atom+xml" href="https://www.google.com/calendar/feeds/default/private/full/stripped" /> 
  <link rel="edit" type="application/atom+xml" href="https://www.google.com/calendar/feeds/default/private/full/stripped" /> 
  <author>
  <gd:eventStatus value="http://schemas.google.com/g/2005#event.confirmed" /> 
  <gd:where valueString="" /> 
  <gd:who email="stripped" rel="http://schemas.google.com/g/2005#event.organizer" valueString="stripped" /> 
  <gd:recurrence>DTSTART;TZID=Europe/London:20120417T040000 DTEND;TZID=Europe/London:20120417T050000 RRULE:FREQ=DAILY;COUNT=5 BEGIN:VTIMEZONE TZID:Europe/London X-LIC-LOCATION:Europe/London BEGIN:DAYLIGHT TZOFFSETFROM:+0000 TZOFFSETTO:+0100 TZNAME:BST DTSTART:19700329T010000 RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU END:DAYLIGHT BEGIN:STANDARD TZOFFSETFROM:+0100 TZOFFSETTO:+0000 TZNAME:GMT DTSTART:19701025T020000 RRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU END:STANDARD END:VTIMEZONE</gd:recurrence> 
  <gd:reminder method="email" minutes="10" /> 
  <gd:reminder method="alert" minutes="10" /> 
  <gd:when endTime="2012-04-19T05:00:00.000+01:00" startTime="2012-04-19T04:00:00.000+01:00">
  <gd:reminder method="email" minutes="10" /> 
  <gd:reminder method="alert" minutes="10" /> 
  </gd:when>
  <gd:transparency value="http://schemas.google.com/g/2005#event.opaque" /> 
  <gd:visibility value="http://schemas.google.com/g/2005#event.default" /> 
  <gCal:anyoneCanAddSelf value="false" /> 
  <gCal:guestsCanInviteOthers value="true" /> 
  <gCal:guestsCanModify value="false" /> 
  <gCal:guestsCanSeeGuests value="true" /> 
  <gCal:sequence value="1" /> 
  <gCal:uid value="stripped" /> 
 </entry>
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

OK, unless I missed something, in your recurring events you're getting the recurring metadata (<gd:recurrence>) and an "empty" <gd:when>. All the date/time info is embedded in the recurrence data. I misunderstood your earlier post to say that your when data was also in the gd:when element.
My entry is essentially the same, although I do not have an empty gd:when element; just the recurrence data.
And looking at it - the recurrence data - for the recurring entry, some of it makes sense but some of it does not agree with the when/time of the event. Here is the recurrence data for an entry that occurs yearly on Aug 1 (a birthday):

DTSTART;VALUE=DATE:20120801
DTEND;VALUE=DATE:20120802
RRULE:FREQ=YEARLY
BEGIN:VTIMEZONE
TZID:America/Chicago
X-LIC-LOCATION:America/Chicago
BEGIN:DAYLIGHT
TZOFFSETFROM:-0600
TZOFFSETTO:-0500
TZNAME:CDT
DTSTART:19700308T020000
RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU
END:DAYLIGHT
BEGIN:STANDARD
TZOFFSETFROM:-0500
TZOFFSETTO:-0600
TZNAME:CST
DTSTART:19701101T020000
RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU
END:STANDARD
END:VTIMEZONE


The green stuff makes sense - freq=yearly on 8/1, ending on 8/2 (an all day event).
But the red stuff..I looked at the actual calendar for entries for the 2nd Sun. in March or 1st Sun in Nov. Nothing. Maybe I'm misinterpreting. :?:
Steve
allanstevens
Member
Posts: 81
Joined: Thu Sep 01, 2005 7:56 am
Location: UK

Re: Google Calendar XML

Post by allanstevens »

Sorry, not got back sooner. Struggling to find time with juggling work and house renovation !

Anyway, my events all include the gd:when endTime="..." startTime="...". So don't have a need to look at the <gd:recurrence> element. Sorry if this was not clear.

If I do look in the recurrence element, I can see the correct date and time in DTSTART, see below.

<gd:when endTime="2012-04-19T06:00:00.000+01:00" startTime="2012-04-19T05:00:00.000+01:00">

<gd:recurrence>
DTSTART;TZID=Europe/London:20120418T050000DTEND;TZID=Europe/London:20120418T060000RRULE:FREQ=DAILY;COUNT=4 BEGIN:VTIMEZONE TZID:Europe/London X-LIC-LOCATION:Europe/London BEGIN:DAYLIGHT TZOFFSETFROM:+0000 TZOFFSETTO:+0100 TZNAME:BST DTSTART:19700329T010000 RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=-1SU END:DAYLIGHT BEGIN:STANDARD TZOFFSETFROM:+0100 TZOFFSETTO:+0000 TZNAME:GMT DTSTART:19701025T020000 RRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=-1SU END:STANDARD END:VTIMEZONE</gd:recurrence>

Did you try creating some new items, and are they populating the gd:when ?

I'm wondering because it's a birthday, it does not have a start or end time, so does not populate the when tag. If this is the case, you could add some code so that if the gd:when\@startTime is missing then pull the date from DTSTART;VALUE=DATE:20120801 and add 00:00 time. Let me know if you want me to do some scripting for this.*

*EDIT, Here is the additional script to pull from gd:recurrence:

Code: Select all

If Not (NodeEntriy.selectSingleNode("gd:when/@startTime") Is Nothing) Then
   MsgBox NodeEntriy.selectSingleNode("gd:when/@startTime").Text
Else
   MsgBox ParseRecurrence(NodeEntriy.selectSingleNode("gd:recurrence").Text,"DTSTART")
End If
...
...
...
Function ParseRecurrence (recurrenceData, valueToReturn)
  Dim sLine
  'Step through the lines
  For Each sLine in Split(recurrenceData,vbLf)
     If InStr(sLine,valueToReturn) = 1 Then
         ParseRecurrence = Mid(sLine,Len(valueToReturn)+2)
         Exit Function
     End If	
  Next
End Function
Last edited by allanstevens on Wed Apr 25, 2012 9:01 am, edited 1 time in total.
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

I've dealt with the recurrence entries; written some parsing code that extracts the start and end dates as well as the frequency (Yearly, monthly) data. Also have the data loading into an Access table - more of an exercise for me dealing with Access-related activities (Delete, Insert) in VBScript. (As part of all this, using the calendar work as an excuse to learn VBScript.)
My latest obstacle is that some of my calendar entries (unrelated to recurrence issues) do not appear in the extract XML or resulting table entries. Seems to be associated with the privacy setting (default/public/private)in Google Calendar. So I've been noodling with the various GET formats ("objHTTP.open "GET") that you offered up earlier. Complicating this is that my purchase of a smart phone and its calendar app triggered me to open a gmail account so that the app's calendar function would work with my already previously created Google calendar entries. From what I can determine, my 'old' calendar entries found their way to my gmail calendar and when I view the calendar via Google Calendar all the entries are there. But pulling the data via the script, some are not. If I change the missing entries to Public, they appear in the extract. So I've been trying various GET formats to try to 'get' all the entries without having to recreate the missing entries. That would be Plan B.
Sorry for the long reply :oops: But work on it proceeds, albeit slowly.
Steve
Steve Horn
HouseBot Guru
Posts: 757
Joined: Wed Apr 02, 2003 8:10 pm
Location: Pelham AL

Re: Google Calendar XML

Post by Steve Horn »

I'm beginning to see a pattern here. If I delete an "old" recurring event (i.e. one that existed prior to my gmail ID creation) and reenter it (same date, same recurrence) the resulting XML does contain the gd:when info you're seeing... along with the gd:recurrence tag data. I did the same for a monthly event - 2nd Thurs of each month - and the resulting XML -entry listed all the gd:when associated dates (at least for 2012) in that -entry. God Only Knows what happens when the calendar rolls over to 2013.
I had the gd:recurrence parsing down for entries without start/stop times. But noticed tonight that those with start/stop the parsing needs to be altered. BUT... those also have their data in the gd:when tags - in those cases I can ignore the recurrence data and grab the data from the gd:when's (or at least the first one since their all the same).
Sorry - thinking out loud. :oops: And God Help anyone reading this that has not been following along. So much nonsense.
edit: And all events have to be marked public or they won't appear in the XML... at least the way I'm pulling them.
Steve
Post Reply