Last active
January 2, 2024 12:12
-
-
Save tomjaimz/93bb639ba2bb61f99cf5cb942edc361b to your computer and use it in GitHub Desktop.
Google App Script code to import events from a Calendar (.ics) file into Google Sheets.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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).
`