Skip to content

Instantly share code, notes, and snippets.

@natalyjazzviolin
Created June 10, 2021 13:17
Show Gist options
  • Save natalyjazzviolin/2a85ed375ecfea77b58e853cddf95e33 to your computer and use it in GitHub Desktop.
Save natalyjazzviolin/2a85ed375ecfea77b58e853cddf95e33 to your computer and use it in GitHub Desktop.
Create row in sheet on Google Calendar event creation/deletion
//Function triggered on event creation/deletion
function eventTrigger() {
var trigger = ScriptApp.newTrigger('updateEvents')
.forUserCalendar('snedecello@gmail.com')
.onEventUpdated()
.create();
}
//Function to update spreadsheet
function updateEvents() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//Get all calendar events for the next month
var now = new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var oneMonthFromNow = new Date(now.getTime() + MILLIS_PER_DAY*90);
//Get calendar by ID
var cal = CalendarApp.getCalendarById("CALENDAR ID");
var events = cal.getEvents(now, oneMonthFromNow);
var lr = ss.getLastRow();//Get last row of spreadsheet
ss.getRange(2, 1, lr-1, 11).clearContent();//Clear data
for( var i =0; i < events.length; i++) {
var title = events[i].getTitle();
var description = events[i].getDescription().split("@");//Get description after @ symbol, this is the link
if (description[1] != undefined) {
var link = description[1].replace("&nbsp;","");
Logger.log("Link found.");
ss.getRange( i+ 2, 10).setValue(link);
} else {
ss.getRange( i+ 2, 10).setValue("");
}
//Get event location
var location = events[i].getLocation();
var startDate = Utilities.formatDate(events[i].getStartTime(), "EST", "MMM d h:mma").split(" ");
var endDate = Utilities.formatDate(events[i].getEndTime(), "EST", "MMM d h:mma").split(" ");
//Set values
ss.getRange( i+ 2, 1).setValue(title);
ss.getRange( i+ 2, 2).setValue(description[0]);
ss.getRange( i+ 2, 3).setValue(location);
ss.getRange( i+ 2, 4).setValue(startDate[0]);
ss.getRange( i+ 2, 5).setValue(startDate[1]);
ss.getRange( i+ 2, 6).setValue(startDate[2]);
ss.getRange( i+ 2, 7).setValue(endDate[0]);
ss.getRange( i+ 2, 8).setValue(endDate[1]);
ss.getRange( i+ 2, 9).setValue(endDate[2]);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment