I have a unique code that successfully imports my shared Google Calendar into a spreadsheet. In my medical office, I manage all appointments through a master Calendar. The calendar data includes start time, location, description, and title in columns B, C, D, and E. I have added additional columns F to O with data such as 'no show', time of arrival, rescheduled appointments, and formulas to calculate the days between appointments.
However, when the calendar events update in the sheet, the added columns F to O no longer align with the correct row of data. This means that the data I entered for 'no show' or other details end up in the wrong cell. How can I ensure that new rows of data are added to the bottom of the sheet and remain in the correct order to avoid mixed-up data?
function importCalendar(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calendar Import');
var calendarName = sheet.getRange('C2').getValue();
var start = sheet.getRange('C3').getValue();
var end = sheet.getRange('C4').getValue();
var calendar = CalendarApp.getCalendarById(calendarName);
if(calendar) {var calendarId = calendar.getId();}
if(!calendar) {
var calendar = CalendarApp.getCalendarsByName(calendarName)[0];
var calendarId = calendarName;
}
var events = calendar.getEvents(start, end);
var eventDetails = [];
for(var i = 0; i<events.length; i++){
eventDetails.push([events[i].getStartTime(), events[i].getTitle(), events[i].getDescription(), events[i].getLocation()]);
}
//write calendar details to spreadsheet
var startRow = 8;
var startCol = 2;
for(var j = 0; j<eventDetails.length; j++){
var tempRange = sheet.getRange(startRow+j, startCol, 1, 4);
var eventArray = new Array(eventDetails[j]);
tempRange.setValues(eventArray);
}
return eventDetails;
}