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)
}

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)

}