从电子表格创建Google日历定期事件 [英] Create Google Calendar Recurring Events from Spreadsheet

问题描述

底部是以前博客的代码，非常棒！
此代码由以下Google工作表头设置：

 日期|标题|开始时间|结束时间|位置|说明| EventID 
  

但是，我需要能够创建周期性事件。
新的Google工作表标题如下：

 日期|标题|开始时间|结束时间|位置|说明|类型|重复| EventID 
  

如果Type =PM（新列）每月需要创建周期性事件为重复（也是一个新列）数月。
这是怎么回事，但每次脚本运行时都没有重复？

  / ** 
 *将一个自定义菜单添加到活动电子表格中，其中包含一个菜单项
 *，用于调用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 =YOUR_CALENDAR_ID; 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 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）; 
} 
 catch（e）{
 //什么都不做 - 我们只是想避免在事件不存在的情况下发生异常
} 
 if（！event） {
 //cal.createEvent(title，new Date（March 3，2010 08:00:00），new Date（March 3，2010 09:00:00），{description：desc，位置：LOC}）; 
 var newEvent = cal.createEvent（title，tstart，tstop，{description：desc，location：loc}）。getId（）; 
 row [6] = newEvent; //用事件ID 
更新数据数组
 else {
 event.setTitle（title）; 
 event.setDescription（desc）; 
 event.setLocation（loc）; 
 // event.setTime（tstart，tstop）; //无法在eventSeries上设置时间。 
 // ...但我们可以设置重复！ 
 var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）; 
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
调试器; 
} 
 //将所有事件ID记录到电子表格
 range.setValues（data）; 
  

解决方案

好的，这又是一件有趣的事情。上面的代码需要做一些修改来完成你想要的功能：

因为新创建的事件不是系列的（或者它们必须被创建为eventSeries但是这会使条件更加复杂......）当我们创建一个新事件时，我们不使用该对象，而是使用 getEventSeriesById（）来获取它，它隐含地改变了它的本质，而不需要以定义一个重复。

这个技巧工作得很好，使代码更简单。关于设置时间和日期：您的代码在没有年份的情况下从日期对象中获取小时/分钟值（读取SS时这很正常），但这意味着Javascript日期在1月（月0）有一个日期值，1月在冬季（正如你所知道的XD），所以我们遇到了夏令时的问题，所有的时间值都在1小时后，因为设置了月份和dat e之后并没有改变小时值（这个问题目前还不清楚......但是你可以使用你的代码来检查它）

我必须反转进程并将时间值设置为date对象，这会给出正确的结果。

由于它的代码更多，所以我创建了一个小功能来完成这项工作：它有助于保持主代码更清洁。

在完整的代码下面，我还添加了PER WEEK重复测试这个想法...保留它，或者如果你不需要它，就离开它。

  //日期|标题|开始时间|结束时间|位置|说明|类型|重复| EventID 
 
 function 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 = CalendarApp.getDefaultCalendar（）。getId（）; //使用默认的claendar进行测试
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i  var row = data [i]; 
 var date = new Date（row [0]）; //第一列
 var title = row [1]; //第二列
 var tstart = setTimeToDate（date，row [2]）; 
 var tstop = setTimeToDate（date，row [3]）; 
 Logger.log（'date ='+ date +'tstart ='+ tstart +'tstop ='+ tstop）; 
 var loc = row [4]; 
 var desc = row [5]; 
 var type = row [6]; 
 var times = row [7] 
 var id = row [8]; 
 //检查事件是否已经存在，如果是，则更新它
 try {
 var event = cal.getEventSeriesById（id）; 
 event.setTitle（'got you'）; //如果事件不存在，这就是强制错误，我将永远不会显示真实;-) 
} catch（e）{
 var newEvent = cal.createEvent（title，tstart，tstop，{description：desc，location：loc}）; //创建一个正常事件
 row [8] = newEvent.getId（）; //更新事件ID 
的数据数组Logger.log（'event created'）; //调试
 var event = cal.getEventSeriesById（row [8]）; //将其设为事件意甲
} 
 event.setTitle（title）; 
 event.setDescription（desc）; 
 event.setLocation（loc）; 
 if（type =='PM'）{
 var recurrence = CalendarApp.newRecurrence（）。addMonthlyRule（）。times（times）
 event.setRecurrence（recurrence，tstart，tstop）; //我们需要保持开始和停止，否则如果仅使用start，它将成为AllDayEvent 
} else if（type =='PW'）{
 var recurrence = CalendarApp.newRecurrence（）。addWeeklyRule ）.times（times）
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
 data [i] = row; 
} 
 range.setValues（data）; 
 
 
函数setTimeToDate（date，time）{
 var t = new Date（time）; 
 var hour = t.getHours（）; 
 var min = t.getMinutes（）; 
 var sec = t.getSeconds（）; 
 var dateMod = new Date（date.setHours（hour，min，sec，0））
 return dateMod; 
} 
  

测试表仅在这里查看

At the bottom is the code from a previous blog, which works great! This code is set up with the following Google sheet header:

Date | Title | Start Time | End Time | Location | Description | EventID

However, I need to have the ability to create recurring events. The new Google sheet header is as follow:

Date | Title | Start Time | End Time | Location | Description | Type | Recurring | EventID

I need to create recurring events if Type = "PM" (new column) on a monthly basis for "Recurring" (also a new column) amount of months. How is this possible while still not having duplicates every time the script is ran?

/**
 * 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 = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    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, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //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
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);

解决方案

Ok, this was again something interesting... The code above needed a few modification to do what you wanted :

Since newly created events are not series (or else they must be created as eventSeries but this would make the conditions more complicated...) when we create a new event we dont use that object but get it using getEventSeriesById() which implicitly changes its nature without needing to define a recurrence.

This trick works just fine and makes the code simpler.

The other issue was about setting time and dates : your code took the hour/minutes value from a date object without year (that's normal when reading a SS) but it means that the Javascript Date has a date value in January (month 0) and January is in winter (as you know XD) so we had a problem with daylight savings and all time values were 1 hour later because setting month and date afterwards didn't change hour value (this is unclear I'm afraid...but you could check it using your code these days)

I had to invert the process and set time value to the date object instead, this gives the right result.

Since it's a bit more code to write I created a small function to do the job : it helps to keep the main code "cleaner".

Below it the full code, I added also a 'PER WEEK' recurrence to test the idea... keep it or leave it if you don't need it .

//    Date | Title | Start Time | End Time | Location | Description | Type | Recurring | EventID

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 = CalendarApp.getDefaultCalendar().getId();// use default claendar for tests
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    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 = setTimeToDate(date,row[2]);
    var tstop = setTimeToDate(date,row[3]);
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var loc = row[4];
    var desc = row[5];
    var type = row[6];
    var times = row[7]
    var id = row[8]; 
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');// this is to "force error" if the event does not exist, il will never show for real ;-)
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}); // create a "normal" event
      row[8] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[8]);// make it an event Serie
    }
    event.setTitle(title);
    event.setDescription(desc);
    event.setLocation(loc);
    if(type=='PM'){
      var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);// we need to keep start and stop otherwise it becomes an AllDayEvent if only start is used
    }else if(type=='PW'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
    }
  data[i] = row ;
  }
range.setValues(data);
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}

test sheet here in view only

这篇关于从电子表格创建Google日历定期事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！