-
-
Save tomjaimz/93bb639ba2bb61f99cf5cb942edc361b to your computer and use it in GitHub Desktop.
| 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) | |
| } |
@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:[email protected]
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)
}
Change Line 21 from:
else if(col !== -1) { // exists in inputFieldsto:
else if(col !== -1 && data.length > 1) { // exists in inputFields, and BEGIN:VEVENT foundto avoid problems caused by DTSTART showing up in the .ics file before BEGIN:VEVENT.