Multiple Google Calendars to Google Sheet Two Way Sync Issue

Question

Hi there I am trying to modify a script that syncs google calendars with google sheets. Both from Calendar to Sheet and from Sheet to calendar.

Currently the calendar ID is manually input on this line in the google code:

var calendarId = '(calendarid@group.calendar.google.com';

This is from the script by Dave Par https://github.com/Davepar/gcalendarsync

I have modified the menu that is displayed as follows:

  var ui = SpreadsheetApp.getUi();


var subMenu = SpreadsheetApp.getUi().createMenu('Update to Calendar')
                          .addItem('Update to Calendar', 'syncToCalendar')


var topMenu = SpreadsheetApp.getUi().createMenu('Calendar Sync')
                          .addItem('Update from Calendar', 'syncFromCalendar')
                          .addSubMenu(subMenu);

topMenu.addToUi();
}

What im trying to do is get each of the two functions SyncToCalendar and SyncFromCalendar in the menu to be able to have a submenu as a drop down list populated of all my calendars using the below function

https://developers.google.com/calendar/v3/reference/calendarList/list#examples

  var calendars, pageToken;
  do {
    calendars = Calendar.CalendarList.list({
      maxResults: 100,
      pageToken: pageToken
    });
    if (calendars.items && calendars.items.length > 0) {
      for (var i = 0; i < calendars.items.length; i++) {
        var calendar = calendars.items[i];
        Logger.log('%s (ID: %s)', calendar.summary, calendar.id);
      }
    } else {
      Logger.log('No calendars found.');
    }
    pageToken = calendars.nextPageToken;
  } while (pageToken);
}

so essentially being able to select a specific calendar to run Function SyncTocalendar and function SyncFromCalendar on.

Any help to achieve this would be much appreciated.

I have seen a topic where this has been done on a cell where you just change an id, so im hoping maybe this cript can be modified to have the calendar names in a sub menu drop down instead?

https://www.oipapio.com/question-5536049

Answer 1

Your best option would be to change the syncToCalendar and syncFromCalendar to enter a parameter. But that is not possible because the functions executed via menus don't take parameters. You can see a very similar problem here.

If the numbers of calendars are fix and few you could try to just wrap the two functions and call one or other depending in the menu like done in this question.

But if you are stuck with a huge number of calendars that you cannot wrap statically you should try to do it dynamically with the Function constructor. This will give you a way to create a functions while you are running the code, take a look at this question to create functions.

Also you could try an other approach, for example you could create a cell with a dropdown getting all the calendar ID. And get the calendar ID from that cell.