Import google calendar data into existing google spreadsheet

Question

I am trying to keep a record of google calendar entries in a google spreadsheet to further process the data. I have the following code, which I borrowed from other sources:

function importEvents(){
  var startOfDay = new Date();
      startOfDay.setUTCHours(0);
      startOfDay.setMinutes(0);
      startOfDay.setSeconds(0);
      startOfDay.setMilliseconds(0);  
  var endOfDay = new Date(startOfDay.getTime() + 24 * 60 * 60 * 1000);

  var Calendar = CalendarApp.getCalendarById("[calendarIDhere]");
  var events = Calendar.getEvents(startOfDay, endOfDay)
  var events_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ImportedEvents");
  var lr = events_sheet.getLastRow();

  var eventarray = new Array();

  var i = 0; // edited
    for (i = 0; i < events.length; i++) {
      line = new Array();
      line.push(events[i].getTitle());
      line.push(events[i].getDescription());
      line.push(events[i].getStartTime());
      line.push(events[i].getEndTime());
      eventarray.push(line);
    }

    events_sheet.getRange('A1:D' + (i)).setValues(eventarray);

  var l = events_sheet.getLastRow();
  var m = events_sheet.getMaxRows();
  events_sheet.deleteRows(l+1,m-l);
}

For some reason, every time I import a day's entries, several empty rows are added to the ImportedEvents sheet. This is why I delete the empty rows at the end of the sheet.

What I would like to do is to have this script append new imported entries below existing entries in the ImportedEvents sheet. I can't figure this out because of my limited programming skills (again, the code above is the result of implementing solutions provided elsewhere).

Any help will be greatly appreciated!

EDIT: Based on Lothar's suggestion, I tried something else:

events_sheet.getRange("A"+(lr+1)+":D"+(lr+i)).setValues(eventarray);

And it worked! Also, a very special thank you to whomever voted -1 for the incentive to dig deeper.

Cheers!

Lothar Lothar · Accepted Answer · 2012-12-12T16:06:41

Try rewriting this line so that

events_sheet.getRange('A1:D' + (lr)).setValues(eventarray);

if you tried to append the new (event) data to the end of the spreadsheet. Otherwise you will be putting the data in the row corresponding to the number of events you are getting.

Import google calendar data into existing google spreadsheet

1 Answers