APPS Script to add events to your calendar from a sheet. This Idea also allows for you to submit the events with a form which auto adds to the calendar.
This is an example of how to use Apps Script to collect appointments from clients, (this is just an example, so feel free to see how it works).
Check the calendar to see if I have any appointments open. Use the Form to submit an appointment. Refresh to page to see that your appointment is now on my calendar.
This is the APPS Script Code that will update your calendar from a sheet. The form above is attached to this sheet, which has a script that adds the events to the calendar of your choice on form submit. You can also choose to just add the events to the sheet by hand and then use the 'Add to Cal' menu to manually run the script. It is now set with the trigger to run on form submit so the events are added automatically.
The first column is 0.
function onOpen(e) {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp
.createMenu('Calendar')
.addItem('Add to Cal', 'createCalendarEvent')
.addToUi();
}
function createCalendarEvent() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('appointments');
var calendar = CalendarApp.getCalendarById('YOUR CALENDAR ID HERE');
var startRow = 2; // First row of data to process - 2 exempts my header row
var numRows = sheet.getLastRow(); // Number of rows to process
var numColumns = sheet.getLastColumn();
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();
var complete = "Event Added";
for (var i = 0; i < data.length; ++i) {
var column = data[i];
var name = column[1]; //Item Name, the first column is 0
var description = column[2]; //Description
var date = new Date(column[3]); //start date
var eDate = new Date(column[4]); //end date
var eventID = column[5]; //event marked Done
if (eventID != complete) {
var currentCell = sheet.getRange(startRow + i, numColumns);
calendar.createEvent(name, date,eDate,{description: description});
currentCell.setValue(complete);
}
}
}
