March Madness Challenge - Day 14

I wrote some code earlier that scraped the guest from an event in the Google Calendar the hard way. With a little bit of searching around it looks Google Apps Scripting is the right solution. So in keeping with the spirit of March Madness I will need to do something more than rewrite that entry. These functions dump the entire Default Calendar events into a Google Spreadsheet. I would have liked to have write a script that get's the guests from one event, creates a new event and invites them to it, but I got side tracked.



I really liked the Logger object, but it started causing errors so I removed that code. I put it back and everything seemed good so I moved on.

Hint to get oriented for selecting range values:

var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,8);
  Browser.msgBox("row: " + range.getRowIndex() + " rows: " + range.getNumRows() + " col: " + range.getColumnIndex() + " cols: " + range.getNumColumns() + " Vals: " + range.getValues());
 

The code for tonight. Make a Google Apps Script and paste the code in. However, I use the JSON.stringify library for the full dump of the event Object. It has to be pasted in at the bottom of the file. You can get that code here:


This code dumps the entire object including the functions. So if you need analyze what Google is doing this approach get's every piece of data and method for the object. Some times it's nice to get all the detail. As a short cut I use JSON.stringify to dump the data held in some of the fields. If you can read the JSON this is nice because it directly translates into the object, and you can keep drilling down.

function dumpEventObject() {
  // The code below will retrieve all the events for the user's default calendar and
  // display the description of the first event
  var cal = CalendarApp.getDefaultCalendar();
  var events = cal.getEvents(new Date("July 21, 2009 EST"), new Date());
  var text = "";
  var range;
  
  event = [];
 
  Logger.log(JSON.stringify(events[0]));

  //set the headers
  var key;
  for (key in events[0]){
         event.push(key);
  }

  range = SpreadsheetApp.getActiveSheet().getRange(2,1,1,event.length);
  range.setValues([event]);

  //add the dat into appropriate rows/columns
  var i;
  for (i = 0; i < events.length; i++)
  {
    event = [];
     for (ev in events[i] )
    {
     if (typeof events[i][ev] != 'function') {
        event.push(JSON.stringify(events[i][ev]));
      } else {
              event.push(events[i][ev]);
      }
    }
    
    range = SpreadsheetApp.getActiveSheet().getRange((3+i),1,1,event.length);
    range.setValues([event]);
   }
}

Dumps only the data. This is useful to get information you need and work with. I can see cleaning this up more and really make a nice library. This doesn't use JSON.stringify. It iterates through any arrays and separates them on a new line. So all the email address for guests of an event are clearly listed.

function dumpEventData () {
  // The code  below will retrieve all the events for the user's default calendar and
  // display the  description of the first event
  var cal = CalendarApp.getDefaultCalendar();
  var events = cal.getEvents(new Date("July 21, 2009 EST"), new Date());
  var event = [];

  //set the headers
  for (var key in events[0]){
       if ((typeof events[0][key] != 'function'  ) && ( typeof  events[0][key] != 'undefined' ) ){
         event.push(key);
      }
  }
  var range = SpreadsheetApp.getActiveSheet().getRange(2,1,1,event.length);
  range.setValues([event]);
  
  for (var i = 0; i < events.length; i++)
  {
     event =  [];
    for (key in events[i] )
     {
       if ((typeof events[i][key] != 'function'  ) && ( typeof  events[i][key] != 'undefined' ) ){
         if ((typeof  events[i][key] == 'object') && (events[i][key].length))
         {
           var txt = "";
           for (var k in events[i][key])
           {
             txt = txt + events[i][key][k] + "\n";
           }
           event.push(txt);
         }
         else
         {
           event.push(events[i][key]);
         }
       }      
     }
    range = SpreadsheetApp.getActiveSheet().getRange((3+i),1,1,event.length);
    range.setValues([event]);
  }
}

I'm pretty excited about this new capability. If anyone has feedback I'd appreciate it. This is my first rough crack at Google Apps Scripting. I think there is some amazing potential here.