Google App Script code to import events from a Calendar (.ics) file into Google Sheets.

ICS Import.gs

function onOpen() {
  SpreadsheetApp.getUi().createAddonMenu().addItem('Upload', 'upload').addToUi();
}

function upload() {
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(
    '<form><input type="file" name="file" onchange="var reader = new FileReader(); reader.onloadend = (e) => google.script.run.withSuccessHandler(google.script.host.close).importICS(e.target.result); reader.readAsText(this.parentNode.file.files[0]);"></form>'
  ), 'Select .ics file');
}

function importICS(form) {
  var inputFields = ['DTSTART', 'DTEND', 'SUMMARY'], // add to this to add more fields
      data = [['Start', 'End', 'Summary']], // should correspond to inputFields
      reDateForDateFunction = /^(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})Z$/g,
      lines = form.split(/\r\n|\n|\r/);
  for (i in lines) {
    var lineData = lines[i].split(':'), col = inputFields.indexOf(lineData[0])
    if(lines[i] === "BEGIN:VEVENT") {
      data[data.length] = [];
    }
    else if(col !== -1) { // exists in inputFields
      data[data.length - 1][col] = (lineData[0].substr(0, 2) === 'DT') ? new Date(lineData[1].replace(reDateForDateFunction, "$1-$2-$3T$4:$5:$6Z")) : lineData[1]
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, data.length, data[0].length).setValues(data)
}

Hello Tom,
Yes, that's what I was thinking to do, since I am not sure how to change the code.
Another thing I noticed is that all recurrect events come with blank dates. Any idea if this is due to the code or? The events each have a line, but the date is not appearing.

Wow. Thank you so much! I can now analyze my events to do some retro!

Change Line 21 from:
else if(col !== -1) { // exists in inputFields
to:
else if(col !== -1 && data.length > 1) { // exists in inputFields, and BEGIN:VEVENT found
to avoid problems caused by DTSTART showing up in the .ics file before BEGIN:VEVENT.

@ElinaKra I noticed a few different formats for dates/times, so this may resolve the issue you were seeing with recurring events:

Follow the instructions here to create a Library for Moment.js, to be able to easily handle time zones:
https://stackoverflow.com/a/55027287

Add this function above the importICS function:

// https://stackoverflow.com/a/55027287
function toUtc(dateTime, timeZone) {  
  var DT_FORMAT = 'YYYY-MM-DD HH:mm:ss';
  var from = m.moment.tz(dateTime, DT_FORMAT, timeZone); // https://momentjs.com/timezone/docs/#/using-timezones/parsing-in-zone/
  return from.utc().format(DT_FORMAT);
}

then I made a few changes to the importICS function to speed up handling of the SUMMARY field, as well as handle the various date/time formats I had in my .ics file:

function importICS(form) {
  var inputFields = ['DTSTART', 'DTEND', 'SUMMARY'], // add to this to add more fields
      data = [['Start', 'End', 'Summary']], // should correspond to inputFields
      reDateForDateFunction = /^(\d{4})(\d{2})(\d{2})$/g,
      reDateTimeUTCForDateFunction = /^(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})Z$/g,
      lines = form.split(/\r\n|\n|\r/);
  for (i in lines) {
    var lineData = lines[i].split(/;|:/), col = inputFields.indexOf(lineData[0])
    if(lines[i] === "BEGIN:VEVENT") {
      data[data.length] = [];
    }
    else if(col == 2) {
      data[data.length - 1][2] = lineData[1]
    }
    else if((col == 0 || col == 1) && data.length > 1) { // exists in inputFields, and BEGIN:VEVENT found
      if(lineData.length == 2) {
        if(lineData[1].length == 16) {
          // DTSTART:19970714T173000Z
          data[data.length - 1][col] = new Date(lineData[1].replace(reDateTimeUTCForDateFunction, "$1-$2-$3T$4:$5:$6Z"))
        }
      }
      else if(lineData.length == 3) {
        if(lineData[1] == "VALUE=DATE") {
          // DTSTART;VALUE=DATE:19970714
          var year = lineData[2].replace(reDateForDateFunction, "$1")
          var month = lineData[2].replace(reDateForDateFunction, "$2")
          var day = lineData[2].replace(reDateForDateFunction, "$3")
          data[data.length - 1][col] = new Date(year, month-1, day)
        }
        else if(lineData[1].substr(0, 4) == "TZID") {
          // DTSTART;TZID=America/New_York:19970714T133000
          data[data.length - 1][col] = new Date(toUtc(lineData[2],lineData[1].substr(5)))
        }
      }
    }
  }
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, data.length, data[0].length).setValues(data)
}

Hmm. Looks like it still doesn't work for recurring events, but looks like that's gonna involve doing some calculations whenever an event has the RRULE field, which is a bit more involved...

BEGIN:VEVENT
DTSTART;TZID=America/New_York:20210527T170000
DTEND;TZID=America/New_York:20210527T180000
RRULE:FREQ=WEEKLY;WKST=TU;UNTIL=20210603T035959Z
DTSTAMP:20210908T183617Z
UID:xxxxxxxxxxxxxxxxxxxxxxx@google.com
CREATED:20210528T170413Z
DESCRIPTION:
LAST-MODIFIED:20210602T183748Z
LOCATION:
SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:Some event
TRANSP:OPAQUE
END:VEVENT

Thank you Tom!

Any idea how I can load the .ics-file direcly from the web from a known URL? I do not understand exactly whitch type of data is passed from upload() to importICS(form)

Change line 22 from
data[data.length - 1][col] = (lineData[0].substr(0, 2) === 'DT') ? new Date(lineData[1].replace(reDateForDateFunction, "$1-$2-$3T$4:$5:$6Z")) : lineData[1]
to
data[data.length - 1][col] = (lineData[0].substr(0, 2) === 'DT') ? new Date(lineData[1].replace(reDateForDateFunction, "$1-$2-$3T$4:$5:$6Z")) : lineData.slice(1).join(':')
to avoid cutting off data witch contains :

@mrhr95 Any answer found on your question how to load the .ics-file direcly from the web? Would love to automate my Google Calendar to Sheets, and that's the only missing piece for me.

@Herriaan Yes indeed. I'll post it below, but i'd recommend not using this for syncing Goolge Calender with Google Sheet, especially not your private ones. Instead you should use the built in ability of Apps Script to directly read and write events in Google Calender. Works better and is more secure (you don't need to publish an .ics of your calender)
See documentation of the Calendar-Service

This following function downloads an .ics-file that is publicly available under a known domain, and hands it to the function importICS(form):

function downloadICS() {

  var fileURL = 'https://.../calendar.ics';

  var response = UrlFetchApp.fetch(fileURL, {muteHttpExceptions: true}) ;
  var rc = response.getResponseCode()
  if (rc == 200) {
    importICS(response)
  }
    
}

Hi Tom et al, this is an incredibly useful script which i have working. I can't work out how to display the start and end time of each appointment though. I guess it is $4 and $5 but I can't work out how to display it. Any help much appreciated. Thanks very much

While I still can't display the time properly, in case it helps anyone else, I had to change Line 15 to lines = form.toString().split(/\r\n|\n|\r/); to avoid an error message. Brilliant script, i'm using it to push data to a web portal

There where some errors in the code above, like for example when date is including TimeZone or when a FIELD is not existing on the ics file.
I have done some corrections and this is my working code. It allows not only to upload a .ics file from your computer but also import it from a URL (that has to be obtained from your Google calendar private url).
`

function onOpen(e) {
  SpreadsheetApp.getUi().createAddonMenu()
  .addItem('Upload file', 'upload')
  .addItem('Open file from URL', 'downloadICS')
  .addToUi();
  //downloadICS()
}

function downloadICS() {
  var fileURL = 'https://calendar.google.com/....................../basic.ics';
  var response = UrlFetchApp.fetch(fileURL, {muteHttpExceptions: true}) ;
  var rc = response.getResponseCode()
  if (rc == 200) {
    importICS(response.getContentText())
  }
}

function upload() {
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(
    '<form><input type="file" name="file" onchange="var reader = new FileReader(); reader.onloadend = (e) => google.script.run.withSuccessHandler(google.script.host.close).importICS(e.target.result); reader.readAsText(this.parentNode.file.files[0]);"></form>'
  ), 'Select .ics file');
}

function importICS(form) {
  var inputFields = ['DTSTART', 'SUMMARY', 'DESCRIPTION'], // add to this to add more FIELDs
      data = [['Inicio', 'Resumen', 'Descripcion']], // should correspond to inputFields
      lines = form.split(/\r\n|\n|\r/);
  for (i in lines) {
    var lineData = lines[i].split(':'), col = inputFields.indexOf(lineData[0].split(';',1)[0])
    if(lines[i] === "BEGIN:VEVENT") { //Adds a new row and initialize it to avoid issues when not found
      data[data.length] = ["","",""]; //If a FIELD is not present on the file, it makes an error: Better to initialize!
      // Its length should correspond to inputFields. Eg: for 7 read fiels --> = ["","","","","","",""]; 
    }
    else if (lines[i].substr(0, 1) === ' ') {
      //If row starts with just SPACE instead of a FIELD, it must be added to previous line as it belows to a multiple line FIELD
      // https://datatracker.ietf.org/doc/html/rfc5545#section-3.3.11
      data[data.length - 1][previous_col] = data[data.length - 1][previous_col] + lines[i]
    }
    else if(col !== -1 && data.length > 1) { // exists in inputFields, and BEGIN:VEVENT found
      //Browser.msgBox("Dealing with: /////////0:" + lineData[0] + " //////////////1: " +  lineData[1] )
      // Date (YYYY,MM,DD) Thus: lineData[1].substr(0, 4) is YYYY, lineData[1].substr(4, 2)-1 is MM (from 0-11) and the other is DD. And last, lineData[1] is the text DATA to import when it is not a Date (DTStart or DTend)
      data[data.length - 1][col] = (lineData[0].substr(0, 2) === 'DT') ? new Date(lineData[1].substr(0, 4),lineData[1].substr(4, 2)-1,lineData[1].substr(6, 2) ) : lineData[1]
      
      for (var j=2; j<lineData.length ; j=j+1) { //It might happen that someone added ':' to the FIELD and therefore it was split too 
        data[data.length - 1][col] = data[data.length - 1][col] + ":" + lineData[j]
      }
      var previous_col = col //To remember the last FIELD saved
    }
  }
  Browser.msgBox( "Total imported lines: " + data.length + " Columns: " + data[0].length )
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, data.length, data[0].length).setValues(data)
}

`

Another version that avoid usage of set values to allow the output to be inserted into the context of the command.

usage: =insertICS(".../basic.ics")
:

function icsToTable(icsString) {
  var inputFields = ['DTSTART', 'SUMMARY', 'DESCRIPTION'], // add to this to add more FIELDs
    data = [['Start', 'Summary', 'Description']], // should correspond to inputFields
    lines = icsString.split(/\r\n|\n|\r/);
  for (i in lines) {
    var lineData = lines[i].split(':'), col = inputFields.indexOf(lineData[0].split(';', 1)[0])
    if (lines[i] === "BEGIN:VEVENT") { //Adds a new row and initialize it to avoid issues when not found
      data[data.length] = ["", "", ""]; //If a FIELD is not present on the file, it makes an error: Better to initialize!
      // Its length should correspond to inputFields. Eg: for 7 read fiels --> = ["","","","","","",""]; 
    }
    else if (lines[i].substr(0, 1) === ' ') {
      //If row starts with just SPACE instead of a FIELD, it must be added to previous line as it belows to a multiple line FIELD
      // https://datatracker.ietf.org/doc/html/rfc5545#section-3.3.11
      data[data.length - 1][previous_col] = data[data.length - 1][previous_col] + lines[i]
    }
    else if (col !== -1 && data.length > 1) { // exists in inputFields, and BEGIN:VEVENT found
      //Browser.msgBox("Dealing with: /////////0:" + lineData[0] + " //////////////1: " +  lineData[1] )
      // Date (YYYY,MM,DD) Thus: lineData[1].substr(0, 4) is YYYY, lineData[1].substr(4, 2)-1 is MM (from 0-11) and the other is DD. And last, lineData[1] is the text DATA to import when it is not a Date (DTStart or DTend)
      data[data.length - 1][col] = (lineData[0].substr(0, 2) === 'DT') ? new Date(lineData[1].substr(0, 4), lineData[1].substr(4, 2) - 1, lineData[1].substr(6, 2)) : lineData[1]

      for (var j = 2; j < lineData.length; j = j + 1) { //It might happen that someone added ':' to the FIELD and therefore it was split too 
        data[data.length - 1][col] = data[data.length - 1][col] + ":" + lineData[j]
      }
      var previous_col = col //To remember the last FIELD saved
    }
  }
  return data
}

async function download(fileURL) {
  let response = UrlFetchApp.fetch(fileURL, { muteHttpExceptions: true });
  let rc = await response.getResponseCode()
  console.log(rc)
  if (rc == 200) {
    return response.getContentText()
  }

}

async function insertICS(url) {
  let content = await download(url)
  return icsToTable(content)

}