通过Google电子表格创建Google日历活动 - 脚本正在创建重复项 [英] Create Google Calendar events from a Google Spreadsheet - script is creating duplicates

问题描述

我发现这个绝妙的答案，这帮助我拼凑了脚本。与我一起，我不是Google Apps脚本中受过最高教育的人，但我正在到达！

从电子表格创建Google日历活动，但可以防止重复

这是为了显示如何防止重复，但这是不幸的是我的具体问题。我已经弄清楚，这是因为我的脚本没有在电子表格中创建eventid，我的脚本应该被识别，因此避免重复。

任何人都可以帮助我在哪里出错了？

这是我的电子表格中的标题是如何排列的，粗体字实际上适用于脚本：

0保存日期| 1客户| 2 日期总决赛 | 3出站| 4 入站 | 5 AST - TY | 6 AST - BV | 7座| 8餐| 9 FF | 10寝具| 11特价| 12 WHC | 13文件| 14空白| 15 开始时间 | 16 时间 | 17 事件ID WHC | 18 WHC标题 | 19 文档标题 | 20 EventID文档 / p>

  / ** 
 *添加一个自定义菜单到活动的电子表格，包含一个菜单项
 *用于调用exportEvents（）函数。 
 *当打开
 *电子表格时，onOpen（）函数在定义时会自动调用。 
 *有关使用电子表格API的更多信息，请参阅
 * https://developers.google.com/apps-script/service_spreadsheet 
 * / 
 function onOpen（）{ 
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export WHC，
 functionName：exportWHCs
}，
 {
 name：Export Docs ，
 functionName：exportDocs
}]; 
 sheet.addMenu（日历操作，条目）; 
}; 
 
 / ** 
 *将事件从电子表格导出到日历
 * / 
函数exportWHCs（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 4; //标题信息的行数（要跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i< headerRows）continue; //跳过标题行
 var row = data [i]; 
 var date = new Date（row [12]）; //第一列
 var title = row [18]; //第二列
 var tstart = new Date（row [15]）; 
 tstart.setDate（date.getDate（））; 
 tstart.setMonth（date.getMonth（））; 
 tstart.setYear（date.getYear（））; 
 var tstop = new Date（row [16]）; 
 tstop.setDate（date.getDate（））; 
 tstop.setMonth（date.getMonth（））; 
 tstop.setYear（date.getYear（））; 
 var id = row [17]; //第六列== eventId 
 //检查事件是否已经存在，如果
 
尝试{
 var event = cal.getEventSeriesById（id）; 
} 
 catch（e）{
 // do nothing  - 我们只想避免事件不存在的异常
} 
 if（！event） {
 //cal.createEvent(title，new Date（2010年3月3日08:00:00），新日期（2010年3月3日09:00:00）） 
 var newEvent = cal.createEvent（title，tstart，tstop）.addEmailReminder（5）.getId（）; 
 row [17] = newEvent; //更新事件ID 
} 
 else {
 event.setTitle（title）的数据数组; 
 // event.setTime（tstart，tstop）; // can not setTime on eventSeries。 
 // ...但是我们可以设置重复发生！ 
 var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）; 
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
调试器; 
} 
 //将所有事件ID记录到电子表格
 range.setValues（data）; 
 
 
} 
 
函数exportDocs（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 4; //标题信息的行数（要跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i< headerRows）continue; //跳过标题行
 var row = data [i]; 
 var date = new Date（row [13]）; //第一列
 var title = row [19]; //第二列
 var tstart = new Date（row [15]）; 
 tstart.setDate（date.getDate（））; 
 tstart.setMonth（date.getMonth（））; 
 tstart.setYear（date.getYear（））; 
 var tstop = new Date（row [16]）; 
 tstop.setDate（date.getDate（））; 
 tstop.setMonth（date.getMonth（））; 
 tstop.setYear（date.getYear（））; 
 var id = row [20]; //第六列== eventId 
 //检查事件是否已经存在，如果
尝试{
 var event = cal.getEventSeriesById（id）; 
} 
 catch（e）{
 // do nothing  - 我们只想避免事件不存在的异常
} 
 if（！event） {
 //cal.createEvent(title，new Date（2010年3月3日08:00:00），新日期（2010年3月3日09:00:00）） 
 var newEvent = cal.createEvent（title，tstart，tstop）.addEmailReminder（5）.getId（）; 
 row [20] = newEvent; //更新事件ID 
} 
 else {
 event.setTitle（title）的数据数组; 
 // event.setTime（tstart，tstop）; // can not setTime on eventSeries。 
 // ...但是我们可以设置重复发生！ 
 var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）; 
 event.setRecurrence（recurrence，tstart，tstop）; 
} 
调试器; 
} 
 //将所有事件ID记录到电子表格
 range.setValues（data）; 
} 
  

编辑：在看到Serge的评论后，这是我尝试的列，但它仍然不会填充eventID列，例如17：（

  function onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（日历操作，条目）; 
}; 
 
 / ** 
 *从电子表格导出到日历的事件
 * / 
函数exportEvents（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 4; //标题信息的行数（跳过）
 var range = sheet.getDataRange （）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; //为测试使用默认条款
 var cal = CalendarApp .getCalendarById（calId）; 
 for（i in data）{
 if（i< headerRows）continue; // S kip标题行
 var row = data [i]; 
 var date = new Date（row [12]）; //第一列
 var title = row [18]; //第二列
 var tstart = setTimeToDate（date，row [15]）; 
 var tstop = setTimeToDate（date，row [16]）; 
 Logger.log（'date ='+ date +'tstart ='+ tstart +'tstop ='+ tstop）; 
 var type = row [21]; 
 var times = row [22] 
 var id = row [17]; 
 //检查事件是否已经存在，如果
尝试{
 var event = cal.getEventSeriesById（id）; 
 event.setTitle（'got you'）; //这是强制错误，如果事件不存在，il将永远不会显示为真;-) 
} catch（e）{
 var newEvent = cal.createEvent（title，tstart，tstop）; //创建一个正常事件
 row [17] = newEvent.getId（）; //更新事件ID 
的Logger.log（'event created'）; //调试
 var event = cal.getEventSeriesById（row [17]）; //使其成为事件系列
} 
 event.setTitle（title）; 
 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; //更新数据数组（行包含来自行[17] = newEvent的ID）
} 
 //将所有事件ID记录到电子表格
范围.setValues（数据）; 
} 
 
函数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; 
} 
  

解决方案

您的列设置。我删除了您没有使用的部分。

可用的测试表这里

  function onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（日历操作，条目）; 
}; 
 
 / ** 
 *将事件从电子表格导出到日历
 * / 
函数exportEvents（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 2; //标题信息的行数（要跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; //为测试使用默认的claendar 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（i in data）{
 if（i< headerRows）continue; //跳过标题行
 var row = data [i]; 
 var date = new Date（row [12]）; // WHC 
 var title = row [18]; // WHC标题
 var tstart = setTimeToDate（date，row [15]）; //开始时间
 var tstop = setTimeToDate（date，row [16]）; //结束时间
 Logger.log（'date ='+ date +'tstart ='+ tstart +'tstop ='+ tstop）; 
 var id = row [17]; // EventID WHC 
 //检查事件是否已经存在，如果
尝试{
 var event = cal.getEventSeriesById（id）; 
 event.setTitle（'got you'）; //这是强制错误，如果事件不存在，il将永远不会显示为真;-) 
} catch（e）{
 var newEvent = cal.createEvent（title，tstart，tstop）; //创建一个正常事件
 row [17] = newEvent.getId（）; //更新事件ID 
的Logger.log（'event created'）; //调试
 var event = cal.getEventSeriesById（row [17]）; //使其成为事件系列
} 
 event.setTitle（title）; 
} 
 //将所有事件ID记录到电子表格
 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; 
} 
  

I found this absolutely wonderful answer, which helped me cobble together a script. Bear with me, I'm not the most educated at the Google Apps Script, but I'm getting there!

Create Google Calendar Events from Spreadsheet but prevent duplicates

This is specifically to show how to prevent duplicates, but this is unfortunately my specific problem. I've worked out that it's due to my script not creating the eventids on the spreadsheet, that my script is supposed to recognise and therefore avoid a duplicate.

Can anyone help me with where I've gone wrong?

This is how the headers on my spreadsheet are lined up, the bolded ones actually apply to the script:

0 Date Deposited | 1 Customer | 2 Date Finals | 3 Outbound |4 Inbound| 5 AST - TY |6 AST - BV|7 Seats|8 Meals|9 FF |10 Bedding |11 Special |12 WHC|13 Docs|14 Blank |15 Start Time |16 End Time|17 EventID WHC|18 WHC Title |19 Docs Title |20 EventID Docs

/**
 * 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 WHCs",
    functionName : "exportWHCs"
  },
                {
    name : "Export Docs",
    functionName : "exportDocs"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportWHCs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 4;  // 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 in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[12]);  // First column
    var title = row[18];           // Second column
    var tstart = new Date(row[15]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[16]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var id = row[17];              // 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"));
      var newEvent = cal.createEvent(title, tstart, tstop).addEmailReminder(5).getId();
      row[17] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      // 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);


}

function exportDocs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 4;  // 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 in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[13]);  // First column
    var title = row[19];           // Second column
    var tstart = new Date(row[15]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[16]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var id = row[20];              // 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"));
      var newEvent = cal.createEvent(title, tstart, tstop).addEmailReminder(5).getId();
      row[20] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      // 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);
}

EDIT: This is what I tried, after seeing the comments from Serge, same columns as above, but it still won't populate the eventID column, eg 17 :(

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 = 4;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "[e-mail]";// 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[12]);  // First column
    var title = row[18];           // Second column
    var tstart = setTimeToDate(date,row[15]);
    var tstop = setTimeToDate(date,row[16]);
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var type = row[21];
    var times = row[22]
    var id = row[17]; 
    // 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); // create a "normal" event
      row[17] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[17]);// make it an event Serie
    }
    event.setTitle(title);
    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);
   }
    debugger;
    data[i]=row;// update data array (row contains ID from row[17]=newEvent)
  }
  // Record all event IDs to spreadsheet
  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;
}

解决方案

Here is the script adapted to your column setup. I removed the parts that you didn't use.

test sheet available here

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 = 2;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "[e-mail]";// 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[12]);  // WHC
    var title = row[18];           // WHC Title
    var tstart = setTimeToDate(date,row[15]);// start time
    var tstop = setTimeToDate(date,row[16]);// end time
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var id = row[17]; //EventID WHC
    // 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); // create a "normal" event
      row[17] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[17]);// make it an event Serie
    }
    event.setTitle(title);
  }
  // Record all event IDs to spreadsheet
  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;
}

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