Created
June 10, 2021 13:17
-
-
Save natalyjazzviolin/2a85ed375ecfea77b58e853cddf95e33 to your computer and use it in GitHub Desktop.
Create row in sheet on Google Calendar event creation/deletion
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 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(" ",""); | |
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