通过电子表格在多个Google日历中创建活动 [英] Create events in multiple Google Calendars from Spreadsheet

问题描述

我正在尝试创建一张工作表，该工作表可以通过单个Google工作表将事件创建为多个Google日历。我正在使用这篇文章中的奇妙解决方案修改的工作表创建谷歌日历事件从电子表格，但防止从Mogsdad重复。不过，我一直在重复我的工作，进入3个不同的日历，并希望我的第一次去编程。我的想法是我想更进一步，添加一个包含（未确认，保存日期，确认）的下拉列（标记为状态），然后在与条件命名相同的一个或全部三个日历中创建一个甚至是

我的工作表安排如下： - $ / $>

日期|标题|开始时间|结束时间|位置|说明|甚至ID |状态|确认详情|确认开始时间|确认结束时间|

正如您所看到的，我的想法是在已确认日历中的信息稍微不同于其他两种。

我使用的现有代码是

  / ** 
 *为活动电子表格添加一个自定义菜单，其中包含一个菜单项
 *，用于调用exportEvents（）函数。 
 * onOpen（）函数在定义时会在
 *电子表格打开时自动调用。 
 *有关使用Spreadsheet API的更多信息，请参阅
 * https://developers.google.com/apps-script/service_spreadsheet 
 * / 
函数onOpen（）{ 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（日历操作，条目）; 
}; 
 $ b / ** 
 *将电子表格中的事件导出到日历
 * / 
 function exportEvents（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 1; //标题信息的行数（跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; 
 var cal = CalendarApp.getCalendarById（calId）; （i <0; i  if（i  var row = data [i]; 
 var date = new Date（row [0]）; //第一列
 var title = row [1]; //第二列
 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]; //第六列== eventId 
 //检查事件是否已经存在，如果它已经存在，删除它
 try {
 var event = cal.getEventSeriesById（id）; 
 event.deleteEventSeries（）; 
 row [6] =''; //删除事件ID 
} 
 catch（e）{
 //什么也不做 - 我们只是想避免在事件不存在的情况下发生异常
} 
 //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; //用事件ID 
调试器更新数据数组; 
} 
 //将所有事件ID记录到电子表格
 range.setValues（data）; 
 
 $ / code> 

所以我意识到我需要定义新的信息进入证实日历以及2个额外的日历。我的问题是我不知道如何适应一系列的if循环来将事件引导到3个日历。我还希望日历可以添加，例如所有事件都会显示在未经确认的日历事件中，以添加该事件以保存该状态时的日期，并在设置为已确认时最终显示。因此，所有3个日历中都会出现一个确认事件，但只有未确认的事件才会显示在那里。





我几乎是全新的编程技术，所以请好好原谅我公然的别人工作（谢谢Mogsdad），我很感激任何帮助！

解决方案

欢迎编程！一旦掌握了它，您就需要编写您使用的每个Google产品的脚本。 ：）

如果我正确理解您的问题，您希望在运行函数 exportEvents时能够选择事件进入的日历（）。有几种方法可以做到这一点，并且不需要任何额外的循环！

我会先做什么，你现在在哪里定义 cal 和 calId ，是创建一个对象来定义三个日历，如下所示：

  var cal1 =[e-mail]; 
 var cal2 =第二个日历的字符串url; 
 var cal3 =第三个日历的字符串url; 
 var calendar = {
未经确认：CalendarApp.getCalendarById（cal1），
 SaveTheDate：CalendarApp.getCalendarById（cal2），
确认：CalendarApp.getCalendarById（cal3）
 } 
  

对象日历现在包含日历这三个日历的对象使得键是状态，值是对象。然后，当你抓取每一行的数据时，添加

  var cal = row [7]; 
  

现在， cal 包含字符串，状态。您可以通过对 newEvent 定义进行一次更改来充分利用链接：

  var newEvent = calendars [cal] .createEvent（title，tstart，tstop，{description：desc，location：loc}）。getId（）; 
  

这里发生的是日历[cal] 获取表中字符串对应的日历对象，然后添加新事件。这需要对工作表进行更改 - 将状态列中的标签从保存日期更改为SaveTheDate，以便与变量名称匹配。这应该做到这一点！

编辑

要将事件添加到多个日历，如果语句使用，但不需要循环。如下所示：



  calendars ['Unconfirmed']。createEvent（title ... // Add to unconfirmed no如果（cal！='Unconfirmed'）{
 calendars ['SaveTheDate']。createEvent（title ... //仅在未确认的情况下添加到SaveTheDate 
} 
如果（cal =='Confirmed'）{
 calendars ['Confirmed']。createEvent（title ... //只有在已确认
时才添加到已确认
  




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!

解决方案

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
}

这篇关于通过电子表格在多个Google日历中创建活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！