Search for...
Light Dark Auto
Workspace Essentials Posts
|
Google Calendar
| #145

Google Calendar: Export events to Google Sheets

(you can filter previous issues by application!)

🤔 What is it?

By default, Google Calendar exports your calendar in .ics file format:

But a .ics file format is useless if you're trying to view/manage your events in spreadsheet format.

And 99% of the solutions found online recommend using a third-party tool to convert your .ics file to .csv (spreadsheet-friendly), which isn’t very secure…

Step-by-step instructions

First, export your calendar and upload the .ics file onto your Google Drive (any folder will do).

Second, open up Google Apps script (don’t worry I’ll share the code with you below) > New Project > rename your project to “ICS to Google Sheets”

Copy this code below and replace the placeholder code within your project:

function importICSToSheet() {
  var icsFileId = 'YOUR_ICS_FILE_ID_HERE'; // Replace with your .ics file's ID
  var icsContent = DriveApp.getFileById(icsFileId).getBlob().getDataAsString();
  var lines = icsContent.split('\\r\\n');
  var events = [];
  var currentEvent = {}; // Initialize outside the loop
  
  // Parse the .ics file
  for (var i = 0; i < lines.length; i++) {
    if (lines[i].startsWith('BEGIN:VEVENT')) {
      currentEvent = {}; // Properly initialize for a new event
    } else if (lines[i].startsWith('END:VEVENT')) {
      events.push(currentEvent);
      currentEvent = {}; // Reset after pushing to avoid reference issues
    } else if (currentEvent && lines[i].startsWith('SUMMARY:')) {
      currentEvent.summary = lines[i].substr(8);
    } else if (currentEvent && lines[i].startsWith('DTSTART')) {
      currentEvent.startDate = lines[i].split(':')[1];
    } else if (currentEvent && lines[i].startsWith('DTEND')) {
      currentEvent.endDate = lines[i].split(':')[1];
    } else if (currentEvent && lines[i].startsWith('DESCRIPTION:')) {
      currentEvent.description = lines[i].substr(12);
    } else if (currentEvent && lines[i].startsWith('LOCATION:')) {
      currentEvent.location = lines[i].substr(9);
    }
  }
  
  // Create a new Google Sheet
  var sheet = SpreadsheetApp.create('Imported ICS Events').getActiveSheet();
  sheet.appendRow(['Title', 'Start Date', 'End Date', 'Description', 'Location']); // Headers
  
  // Write events to the sheet
  events.forEach(function(event) {
    sheet.appendRow([event.summary, event.startDate, event.endDate, event.description, event.location]);
  });
}

You should end up with something like this:

Next, right-click on the .ics file within Google Drive > Copy the share link, and find the File ID in the address bar:

Replace the ‘YOUR_ICS_FILE_ID_HERE’ with the File ID from your uploaded .ics file:

Save and Run the Apps script

💡
Note: This might take 10-20 minutes depending on how many events you have in your Google Calendar

After you see “Execution Completed,” go to your Google Sheets homepage and you should find a new file titled, “Imported ICS Events”

I know the instructions seem a bit complicated so let me know if you run into any issues!

I have a question!

I’m always looking for ways to improve so give me anonymous feedback here 📝

Want someone to be more productive? Let them subscribe here 😉

Thanks for being a subscriber, and have a great day!