How to get all PAST events from ALL my Google Calendars into Google Spreadsheet?

Quantified Self Data Formats, Export and APIs
#1

I have multiple calendars for tracking how much time I spend in each part of my life.
image

How can I export all my PAST events into a Google Sheet?
I’m trying to do it now with coding, but it takes so long because I’m only taking 1 event each second to avoid over using their API.
Can I just download all events somewhere and put them in my spreadsheet?

#2

You can use Google Checkout Takeout to get an export of all calendars, and then you can maybe use your slow 1 event/sec method to keep it up-to-date (since Google Takeout is a very manual process).

#3

It looks like Google Checkout is for payments?
image

How can I use this to export all my Google Calendar events?
Can you send a link with a tutorial?

#4

Sorry, my bad, it’s “Google Takeout”: https://takeout.google.com

1 Like
#5

For anyone else facing this issue:
After using Google Takeout you get Icalendar files. This converter is really good for converting those to .csv files:
https://www.indigoblue.eu/ics2csv/

#6

ChatGPT helped me get a WAY better solution…

Here’s my prompt to CHATGPT:
Can you make a Google Script for getting all my Google Calendar events from ALL my google calendars on my account into Google sheets. I want the events name, start date+time end date+time, description and name of calendar the event belongs to.

Here is the answer from CHATGPT:
Certainly! Here’s a Google Apps Script code that retrieves all your Google Calendar events from all your calendars and writes them into a Google Sheets spreadsheet with the event name, start date+time, end date+time, description, and the name of the calendar the event belongs to.

Please follow these steps to set up the script:

  1. Open a new Google Sheets document.
  2. Go to “Extensions” in the menu bar and select “Apps Script” to open the Google Apps Script editor.
  3. Replace the default code in the editor with the following script:
function exportCalendarEventsToSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Calendar Events"); // Change "Calendar Events" to the name of the sheet you want to use
  
  // Clear existing data in the sheet
  sheet.clearContents();
  
  // Get all calendars
  var calendars = CalendarApp.getAllCalendars();
  
  // Write headers
  sheet.appendRow(["Event Name", "Start Date+Time", "End Date+Time", "Description", "Calendar Name"]);
  
  // Iterate through each calendar
  for (var i = 0; i < calendars.length; i++) {
    var calendar = calendars[i];
    var events = calendar.getEvents(new Date(), new Date(2099, 12, 31)); // Adjust the end date as needed
    
    // Iterate through each event
    for (var j = 0; j < events.length; j++) {
      var event = events[j];
      
      // Get event details
      var eventName = event.getTitle();
      var startDateTime = event.getStartTime();
      var endDateTime = event.getEndTime();
      var description = event.getDescription();
      var calendarName = calendar.getName();
      
      // Write event details to the sheet
      sheet.appendRow([eventName, startDateTime, endDateTime, description, calendarName]);
    }
  }
}
  1. Save the script by clicking on the floppy disk icon or pressing Ctrl + S.
  2. Close the Google Apps Script editor.

Now, you can run the script by following these steps:

  1. Open your Google Sheets document.
  2. Go to “Extensions” in the menu bar and select “Apps Script.”
  3. In the Apps Script editor, select the function exportCalendarEventsToSheet.
  4. Click the play button (:arrow_forward:) to run the script.
  5. Grant necessary permissions when prompted.
  6. Wait for the script to finish executing. It may take some time if you have a large number of events.

Once the script completes, you should see a new sheet named “Calendar Events” (or the name you specified) with all your Google Calendar events listed, including the event name, start date+time, end date+time, description, and the name of the calendar the event belongs to.

Note: You might need to run the script periodically or trigger it automatically if you want to keep the Google Sheets document updated with the latest calendar events.

3 Likes
#7

However it’s not THAT perfect… because it has a time limit of 6 minutes… Don’t know how to avoid that… (so it stops before it has fetched all events…

Maybe I’m just going to fetch one month at a time

#8

I haven’t used the google_sheets API, but you could break up the process in smaller chunks and save intermediate results in places where you hide later.

For instance (just an example):

  1. Get all the Calendars, and store a serialized version of the link to that calendar in a column
  2. Then process each calendar separately in multiple runs.

Additionally, you could get the events in multiple date-chunks. Of course, just don’t clear the sheet after the first one.

#9

I don’t understand this part.

My plan is just to take one month at a time.

1 Like
#10

Nevermind. I thought you were just asking for brainstorming other possibilities.

Processing individual calendars seemed like another way, but it much more complicated due to sorting, etc.

Doing month by month seems like a very logical solution.