field list extension? - Githubissues

protw commented 7 years ago

Thanks to the author - it is simple, but very useful code.

To my regret some useful fields are absent in the list.

Since I'm a complete dummy in google scripting I'd like to ask the author to extend, if it is possible, the field list, namely:

guest list: comma separated e-mail list;
guests are invited: false or true;
email reminder: in minutes;
popup reminder: in minutes.

davepar commented 7 years ago

Sounds useful. I can take a look at this, but not sure I'll get to it right away.

heymeee commented 7 years ago

hey hi i really love it too ! thank you Davepar !! it s a similar request that i have,, i also need the below , fields to be added to sheet, and to the event.

guest list: comma separated e-mail list;
guests are invited: false or true; is there a way to autoconfirm their invitation, so they they dont have to do it and it goes straight to their own calendar ?
email reminder: in minutes if needed
popup reminder: in minutes if needed
attach files (from drive) from a list in the sheet for each event, if the list could auto fill itself up with a script that get a folder content in drive,,

best regards

heymeee commented 7 years ago

ouaa starting to love apps script ! i managed to add a column with guests emails, then it adds it to the event,, gonna try the attachments thing

i m sure i did it wrongly, but it works for guestlist .

i added a 'guest things' in the code below

// Script to synchronize a calendar to a spreadsheet and vice versa. // // See https://github.com/Davepar/gcalendarsync for instructions on setting this up. //

// Set this value to match your calendar!!! // Calendar ID can be found in the "Calendar Address" section of the Calendar Settings. var calendarId = 'ck9r8er1gni3i8s209rfv04iv4@group.calendar.google.com';

var titleRowMap = { 'title': 'Title', 'description': 'Description', 'location': 'Location', 'starttime': 'Start Time', 'endtime': 'End Time', 'id': 'Id', 'guests': 'Guests' }; var titleRowKeys = ['title', 'description', 'location', 'starttime', 'endtime', 'id', 'guests'];

// Adds the custom menu to the active spreadsheet. function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: "Update from Calendar", functionName: "syncFromCalendar" }, { name: "Update to Calendar", functionName: "syncToCalendar" } ]; spreadsheet.addMenu('Calendar Sync', menuEntries); }

// Creates a mapping array between spreadsheet column and event field name function createIdxMap(row) { var idxMap = []; for (var idx = 0; idx < row.length; idx++) { var fieldFromHdr = row[idx]; for (var titleKey in titleRowMap) { if (titleRowMap[titleKey] == fieldFromHdr) { idxMap.push(titleKey); break; } } if (idxMap.length <= idx) { // Header field not in map, so add null idxMap.push(null); } } return idxMap; }

// Converts a spreadsheet row into an object containing event-related fields function reformatEvent(row, idxMap) { return row.reduce(function(event, value, idx) { if (idxMap[idx] != null) { event[idxMap[idx]] = value; } return event; }, {}); }

// Converts a calendar event to a psuedo-sheet event. function convertCalEvent(calEvent) { convertedEvent = { 'id': calEvent.getId(), 'title': calEvent.getTitle(), 'description': calEvent.getDescription(), 'location': calEvent.getLocation(), 'guests': calEvent.getGuestList() }; if (calEvent.isAllDayEvent()) { convertedEvent.starttime = calEvent.getAllDayStartDate(); var endtime = calEvent.getAllDayEndDate(); if (endtime - convertedEvent.starttime === 24 3600 1000) { convertedEvent.endtime = ''; } else { convertedEvent.endtime = endtime; if (endtime.getHours() === 0 && endtime.getMinutes() == 0) { convertedEvent.endtime.setSeconds(endtime.getSeconds() - 1); } } } else { convertedEvent.starttime = calEvent.getStartTime(); convertedEvent.endtime = calEvent.getEndTime(); } return convertedEvent; }

// Converts calendar event into spreadsheet data row function calEventToSheet(calEvent, idxMap, dataRow) { convertedEvent = convertCalEvent(calEvent);

for (var idx = 0; idx < idxMap.length; idx++) { if (idxMap[idx] !== null) { dataRow[idx] = convertedEvent[idxMap[idx]]; } } }

// Returns empty string or time in milliseconds for Date object function getEndTime(ev) { return ev.endtime === '' ? '' : ev.endtime.getTime(); }

// Tests whether calendar event matches spreadsheet event function eventMatches(cev, sev) { var convertedCalEvent = convertCalEvent(cev); return convertedCalEvent.title == sev.title && convertedCalEvent.description == sev.description && convertedCalEvent.location == sev.location && convertedCalEvent.starttime == sev.starttime && convertedCalEvent.guests == sev.guests && getEndTime(convertedCalEvent) === getEndTime(sev);

}

// Determine whether required fields are missing function fieldsMissing(idxMap) { return ['id', 'title', 'starttime', 'endtime'].some(function(val) { return idxMap.indexOf(val) < 0; }); }

// Set up formats and hide ID column for empty spreadsheet function setUpSheet(sheet, fieldKeys) { sheet.getRange(1, fieldKeys.indexOf('starttime') + 1, 999).setNumberFormat('M/d/yyyy H:mm'); sheet.getRange(1, fieldKeys.indexOf('endtime') + 1, 999).setNumberFormat('M/d/yyyy H:mm'); sheet.hideColumns(fieldKeys.indexOf('id') + 1); }

// Display error alert function errorAlert(msg, evt, ridx) { var ui = SpreadsheetApp.getUi(); if (evt) { ui.alert('Skipping row: ' + msg + ' in event "' + evt.title + '", row ' + (ridx + 1)); } else { ui.alert(msg); } }

// Synchronize from calendar to spreadsheet. function syncFromCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); var calEvents = calendar.getEvents(new Date('1/1/1970'), new Date('1/1/2030'));

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); var eventFound = new Array(data.length);

// Check if spreadsheet is empty and add a title row var titleRow = []; for (var idx = 0; idx < titleRowKeys.length; idx++) { titleRow.push(titleRowMap[titleRowKeys[idx]]); } if (data.length < 1) { data.push(titleRow); range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

if (data.length == 1 && data[0].length == 1 && data[0][0] === '') { data[0] = titleRow; range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); setUpSheet(sheet, titleRowKeys); }

// Map spreadsheet headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id');

// Verify header has all required fields if (fieldsMissing(idxMap)) { errorAlert('Spreadsheet must have Title, Start Time, End Time, All Day Event, and Id columns'); return; }

// Array of IDs in the spreadsheet var sheetEventIds = data.slice(1).map(function(row) {return row[idIdx];});

// Loop through calendar events for (var cidx = 0; cidx < calEvents.length; cidx++) { var calEvent = calEvents[cidx]; var calEventId = calEvent.getId();

var ridx = sheetEventIds.indexOf(calEventId) + 1;
if (ridx < 1) {
  // Event not found, create it
  ridx = data.length;
  var newRow = [];
  var rowSize = idxMap.length;
  while (rowSize--) newRow.push('');
  data.push(newRow);
} else {
  eventFound[ridx] = true;
}
// Update event in spreadsheet data
calEventToSheet(calEvent, idxMap, data[ridx]);

}

// Remove any data rows not found in the calendar var rowsDeleted = 0; for (var idx = eventFound.length - 1; idx > 0; idx--) { if (!eventFound[idx]) { data.splice(idx, 1); rowsDeleted++; } }

// Save spreadsheet changes range = sheet.getRange(1, 1, data.length, data[0].length); range.setValues(data); if (rowsDeleted > 0) { sheet.deleteRows(data.length + 1, rowsDeleted); } }

// Synchronize from spreadsheet to calendar. function syncToCalendar() { // Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); if (!calendar) { errorAlert('Cannot find calendar. Check instructions for set up.'); } var calEvents = calendar.getEvents(new Date('1/1/1970'), new Date('1/1/2030')); var calEventIds = calEvents.map(function(val) {return val.getId()});

// Get spreadsheet and data var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getDataRange(); var data = range.getValues(); if (data.length < 2) { errorAlert('Spreadsheet must have a title row and at least one data row'); return; }

// Map headers to indices var idxMap = createIdxMap(data[0]); var idIdx = idxMap.indexOf('id'); var idRange = range.offset(0, idIdx, data.length, 1); var idData = idRange.getValues()

// Verify header has all required fields if (fieldsMissing(idxMap)) { errorAlert('Spreadsheet must have Title, Start Time, End Time, All Day Event, and Id columns'); return; }

// Loop through spreadsheet rows var numAdds = 0; var numUpdated = 0; var changesMade = false; for (var ridx = 1; ridx < data.length; ridx++) { var sheetEvent = reformatEvent(data[ridx], idxMap);

// Do some error checking first
if (!sheetEvent.title) {
  errorAlert('must have title', sheetEvent, ridx);
  continue;
}
if (!(sheetEvent.starttime instanceof Date)) {
  errorAlert('start time must be a date/time', sheetEvent, ridx);
  continue;
}
if (sheetEvent.endtime !== '') {
  if (!(sheetEvent.endtime instanceof Date)) {
    errorAlert('end time must be empty or a date/time', sheetEvent, ridx);
    continue;
  }
  if (sheetEvent.endtime < sheetEvent.starttime) {
    errorAlert('end time must be after start time for event', sheetEvent, ridx);
    continue;
  }
}

// Determine if spreadsheet event is already in calendar and matches
var addEvent = true;
if (sheetEvent.id) {
  var eventIdx = calEventIds.indexOf(sheetEvent.id);
  if (eventIdx >= 0) {
    calEventIds[eventIdx] = null;  // Prevents removing event below
    var calEvent = calEvents[eventIdx];
    if (eventMatches(calEvent, sheetEvent)) {
      addEvent = false;
    } else {
      // Delete and re-create event. It's easier than updating in place.
      calEvent.deleteEvent();
      numUpdated++;
    }
  }
}
if (addEvent) {
  var newEvent;
  if (sheetEvent.endtime === '') {
    newEvent = calendar.createAllDayEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent);
  } else {
    newEvent = calendar.createEvent(sheetEvent.title, sheetEvent.starttime, sheetEvent.endtime, sheetEvent);
  }
  // Put event ID back into spreadsheet
  idData[ridx][0] = newEvent.getId();
  changesMade = true;

  // Updating too many calendar events in a short time interval triggers an error. Still experimenting with
  // the exact values to use here, but this works for updating about 40 events.
  numAdds++;
  if (numAdds > 10) {
    Utilities.sleep(75);
  }
}

}

// Save spreadsheet changes if (changesMade) { idRange.setValues(idData); }

// Remove any calendar events not found in the spreadsheet var numToRemove = calEventIds.reduce(function(prevVal, curVal) { if (curVal !== null) { prevVal++; } return prevVal; }, 0); if (numToRemove > 0) { var ui = SpreadsheetApp.getUi(); var response = ui.Button.YES; if (numToRemove > numUpdated) { response = ui.alert('Delete ' + numToRemove + ' calendar event(s) not found in spreadsheet?', ui.ButtonSet.YES_NO); } if (response == ui.Button.YES) { calEventIds.forEach(function(id, idx) { if (id != null) { calEvents[idx].deleteEvent(); Utilities.sleep(20); } }); } } }

heymeee commented 7 years ago

well it works from sheet to calendar,, but if i try to sync from cal to sheet, then in my sheet the list of emails that was already there is replaced by'EventGuest',,, would someone have a clue please ? cheers

davepar commented 7 years ago

The problem is that the getGuestList function returns an array of EventGuest. You need to call getEmail on each one of those to get the email addresses. https://developers.google.com/apps-script/reference/calendar/event-guest

I just updated the script with functionality to sync a comma separated list of guest email addresses. Thanks @heymeee for working on it. I also added a new variable "SEND_EMAIL_INVITES" which toggles whether to send email invites to each guest. (See notes in the code.)

I tested the code, and it all seems to work ok.

I didn't add the following fields:

guests are invited. Calendar doesn't keep track of this. You can send email invites when the event is created, but that's it.
email reminder. As far as I know, this only affects your personal copy of the event. Any guests would use their own personal settings for default reminders.
popup reminder. Same.
attach Drive files. I can see how this would be handy, but seems like a limited use case. I'm happy to answer questions if you want to implement it, though.

Dave

heymeee commented 7 years ago

hey hello Dave thank you so much for answering so quickly! i m gonna try this today for sure.

i have some more details on the drive thing that i need and a few other questions,,, i hope you dont mind ;)

1/i think i read somewhere that,there is a way to have the guest auto confirm the event attendance,, how could i do this , so they dont have to manually confirm all events i invite them to,,,

2/ i need a way of adding a attachments column in the sheet to add files from google drive to the event. i would actually need to create a folder in the drive when an event is created, with the folder's content synched with the list of attached files in the event. (files are added after the event is created) when creating the event, maybe it should create a folder named "start date"+"event title" like "20170220_meeting_office_London" it s one of the main thing i need, because the guest are uploading files to the drive for each events, and i need everyone to have it in the calendar,,

3/ i need to add time zone for start and end times,, i think it s possible now,,

4/ Thank you

davepar commented 7 years ago

I haven't seen anything about auto confirming attendance, but maybe it exists.

The Google Drive integration sounds complicated. An easier approach might be to just put a link to the Drive folder in the calendar event instead of the individual files. I haven't worked at all with scripts and Drive, but it seems like what you need should be possible.

Sheets doesn't have very good support for time zones, at least from what I've ready. You can set a time zone for the entire sheet, but not on individual entries. More info in the README file.

Dave

heymeee commented 7 years ago

Hi Dave i ve been using the script a lot thank you i have like 200 events in the sheet and each time i try to update to calendar , iget the error message, "You have been creating or deleting too many calendars or calendar events in a short time. Please try again later." i worked fine but , i moprted like 120 events to the sheet and still worked fine, but now it seem to be stucked,, how can i still use the script ? should i change the values here : numAdds++; if (numAdds > 10) { Utilities.sleep(75); }

it s very useful for me,,

thanks for your help

davepar / gcalendarsync

field list extension? #15