Connect to Calendar – Create Events From Spreadsheet Data

Creating Calendar events from data in a Sheet.

Important notes:
* You must have write access to the calendar being accessed in order to use .createEvent()
* A calendar can be retrieved as the default calendar, by ID, or by name. Calling by ID or Name returns an array, so be sure to get the first element of that array [0]
* The /options/ parameter of the .createEvent() method can accept description, location, guests, and sendInvites
* .setHours() is more readable than .setTime() because the latter accepts “a numeric value representing the number of elapsed milliseconds since midnight” and adjusts the entire Date object while the former only adjusts the hrs/min/sec/ms of the existing Date

Spreadsheet (get your own copy using “Make a copy” from the File meu)
https://docs.google.com/spreadsheets/d/1QmKAJdr7-Uhr89lzaotfol82Y6t29JeT0CPDf61cLGA/edit#gid=0

Script:
function createCalendarEvents() {
const sh = SpreadsheetApp.getActive();
const cal = CalendarApp.getDefaultCalendar(); // or any other method to access a calendar
const ss = sh.getActiveSheet();

let data = ss.getRange(2,1,ss.getLastRow(),4).getValues();
let client,loc,d1,d2,t;

for (let i in data){
client = data[i][0];
loc = data [i][1];
d1 = new Date(data[i][2]);
t = new Date(data[i][3]);
d1.setTime().setHours(t.getHours(),t.getMinutes());
d2 = new Date(d1.getTime() + 30 * 60000);

cal.createEvent(“Meeting with ” + client,d1,d2,{location: loc});
}
}

Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305

Donate on Paypal: spencer.farris@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *