Google-sheets – Export Multiple Google Calendars to Google Sheets

googlegoogle sheetsgoogle-calendar

I have several Google Calendars I'm subscribed to, one for each of my sales and logistics team members. I want to grab the events from each calendar for both today and tomorrow and put them into my main Logistics spreadsheet. I'm stuck on the "get all the events" part; I'm pretty sure I can get the right info to the cells I want them to once I've figured this out.

Here's what I have so far, which is my newbie attempt to go through each calendar and grab the events. I'm getting the error that the parameters for CalendarApp.Calendar.getEventsForDay are incorrect.

Exception: The parameters (number) don't match the method signature for CalendarApp.Calendar.getEventsForDay.
at calendarGrabTodayTomorrow(Code:85:28)

  var calendars = CalendarApp.getAllCalendars();
  var date = new Date();
  var dateTom = (date.getDate()+1);
    for (var i=0; i < calendars.length; i++) {
    var curCal = calendars[i];
    var eventsTod = curCal.getEventsForDay(date);
    var eventsTom = curCal.getEventsForDay(dateTom);
  Logger.log(eventsTod);
    Logger.log(eventsTom);}
}

Any help on this is greatly appreciated!

Best Answer

I ended up finding the answer here: https://www.cloudbakers.com/blog/export-google-calendar-entries-to-a-google-spreadsheet

Here is a generalized version of the script that ended up working:

  var mycal = "myemail@email.com";
  var cal = CalendarApp.getCalendarById(mycal);
  var today = new Date();
  var endDate = new Date(today.getTime() + (7 * 24 * 60 * 60 * 1000)); //sets the end date at 7 days past today so the calendar is a rolling 7-day calendar
  var events = cal.getEvents(today, endDate);

This also allowed me to grab calendars that are shared with me as long as I replaced "myemail@gmail.com" with the email address of the sharer. So for each salesperson I made a new copy of this formula, and created a new "Refresh" formula that runs each of the sales' calendars at once.

function calendarRefresh(){
myCalToSheet();
jasonCalToSheet();
joshCalToSheet();
alenCalToSheet();
paigeCalToSheet();
}
Related Topic