Create events in multiple Google Calendars from Sp

问题:

I'm trying to create a sheet that creates events into multiple google calendars from a single google sheet. I am using a sheet modified from the fantastic solution on this post Create Google Calendar Events from Spreadsheet but prevent duplicates from Mogsdad. However I have been triplicating my work to go into 3 different calendars and would like to have my first go at programming. My idea is I would like to go one step further and add a drop down column (labeled status) containing either (Unconfirmed, Save the date, Confirmed) which would then create an even in one or all three calendars named the same as the conditional drop down.

My sheet is arranged as :-

Date | Title | Start Time | End Time | Location | Description | Even ID | Status | Confirmed details | Confirmed Start time | confirmed end time |

As you can see my idea is to have slightly different info in the confirmed calendar than the other two.

The existing code i'm using is

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "[e-mail]";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
// Check if event already exists, delete it if it does
try {
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  row[6] = '';  // Remove event ID    
}
catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
}
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[6] = newEvent;  // Update the data array with event ID
debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

So I realize I need to define the new info to go into the "confirmed" calendar as well as the 2 additional calendars. My issue is I don't know how to fit in a series of if loops to direct events to the 3 calendars. I would also like the calendars to be additive e.g. all events appear in "unconfirmed calendar" events get added to save the date when uprated to that status and then finally appear in "confirmed" when set to that. So a confirmed event appears in all 3 calendars but an unconfirmed only appears there.

I'm virtually brand new to programming so please be nice and excuse my blatant plagarism of others work (thanks Mogsdad) and I appreciate any help!

回答1:

Welcome to programming! Once you get the hang of it, you'll want to script every Google product you use. :)

If I understand your question correctly, you would like to be able to choose which calendar an event goes into when you run the function exportEvents(). There are several ways to do this, and you don't need any additional loops! You can make use of objects and refer to them by name.

What I would do first, where you currently define cal and calId, is create an object that defines the three calendars like this:

var cal1 = "[e-mail]";
var cal2 = "string url for second calendar";
var cal3 = "string url for third calendar";
var calendars = {
   Unconfirmed: CalendarApp.getCalendarById(cal1),
   SaveTheDate: CalendarApp.getCalendarById(cal2),
   Confirmed: CalendarApp.getCalendarById(cal3)
}

The object calendars now contains the calendar objects for the three calendars such that the key is the status and the value is the object. Then, when you're grabbing the data for each row, add

var cal = row[7];

Now, cal contains the string indicating the status. You can make great use of chaining by making one change to your newEvent definition:

var newEvent = calendars[cal].createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();

What's happening here is calendars[cal] gets the calendar object corresponding to the string in the table, to which you can then add the new event. This does require making a change to your sheet - change the label in your status column from 'Save the Date' to 'SaveTheDate' so it matches the variable name. That should do it!

EDIT

To add the event to multiple calendars, I would use if statements, but you don't need a loop. Something like the following would work:

calendars['Unconfirmed'].createEvent(title...  // Add to unconfirmed no matter what
if (cal != 'Unconfirmed'){
  calendars['SaveTheDate'].createEvent(title... // Add to SaveTheDate only if not Unconfirmed
}
if (cal == 'Confirmed'){
  calendars['Confirmed'].createEvent(title...  // Only add to Confirmed if Confirmed
}