从Google电子表格创建Google日历活动的Google脚本 - “超出最长执行时间” [英] Google Script that creates Google Calendar events from a Google Spreadsheet - "Exceeded maximum execution time"

问题描述

使用这个很好的答案，我设法修改它以创建一个脚本，将事件从Google Spreadsheet导出到Google日历。

从电子表格创建Google日历事件，但防止重复

然后我得到了一些很好的建议，并发现它并没有填充eventID列，因为我得到的错误 - 超出最大执行时间 - 由于大量的行（最多1000）。

从Google电子表格创建Google日历活动 - 脚本正在创建重复项

我一直在寻找答案，尝试解决这个问题，但似乎找不到答案！道歉 - 我对这一切都很陌生。

任何人都可以指向正确的方向，至于如何强制脚本超过5分钟或其他任何内容？

  function onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Export Events，
 functionName：exportEvents
}]; 
 sheet.addMenu（日历操作，条目）; 
}; 
 $ b / ** 
 *将电子表格中的事件导出到日历
 * / 
 function 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  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 
 //检查事件是否已经存在，如果是，则更新它
 try {
 var event = cal.getEventSeriesById（id）; 
 event.setTitle（'got you'）; //如果事件不存在，这就是强制错误，我将永远不会显示真实;-) 
} 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; 
} 
  

解决方案

当脚本进入主函数内部并在达到极限时中断脚本。

我们必须将行号存储在中断脚本的地方并从那里继续。

由于我们不想手动执行该操作（我们是多么懒惰:-)，我们将设置一个触发器，以便每5秒钟运行一次分钟。

下面是一个完整的脚本。
它会在每次运行中向您发送一封电子邮件，告诉您进展情况...您经过测试后必须删除此行（除非您喜欢每隔5分钟收到一封电子邮件！）

您必须更改日历ID，行分布（我在列中列数少于您的表格的情况下对其进行了测试），但这相当容易。

 函数createEventsWithBatch（）{
 //检查脚本是否第一次运行，
 //如果是，创建触发器和PropertiesService.getScriptProperties（）脚本将使用
 //开始索引和总计数器处理项目
 //否则继续任务
 if（PropertiesService.getScriptProperties（） .getKeys（）。length == 0）{
 PropertiesService.getScriptProperties（）。setProperties（{'itemsprocessed'：0}）; 
 ScriptApp.newTrigger（'createEventsWithBatch'）。timeBased（）。everyMinutes（5）.create（）; 
} 
 //当我们开始一个新任务时初始化所有变量，notFinished是主循环条件
 var itemsProcessed = Number（PropertiesService.getScriptProperties（）。getProperty（'itemsprocessed'）） ）; 
 var startTime = new Date（）。getTime（）; 
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var headerRows = 1; //标题信息的行数（跳过）
 var range = sheet.getDataRange（）; 
 var data = range.getValues（）; 
 var calId =[e-mail]; 
 var cal = CalendarApp.getCalendarById（calId）; 
 for（var i = itemsProcessed; i< data.length; i ++）{
 if（i< headerRows）continue; //跳过标题行（s）
 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）; 
} else if（type =='PW'）{
 var recurrence = CalendarApp.newRecurrence（）。addWeeklyRule（）。times（times）
 event.setRecurrence（recurrence，tstart，tstop ）; 
} 
 data [i] = row; 
 Logger.log（i +''+ new Date（）。getTime（） -  startTime）
 if（new Date（）。getTime（） -  startTime> 240000）{// if> 4分钟
 var processed = i + 1; //保存有用变量
 PropertiesService.getScriptProperties（）。setProperties（{'itemsprocessed'：processed}）; 
 range.setValues（data）; 
 MailApp.sendEmail（Session.getEffectiveUser（）。getEmail（），'cal to progress'，'item processed：'+ processed）; 
 return; 
} 
} 
 range.setValues（data）; //这次我们完成了！ 
 killTrigger（）; //删除触发器
 PropertiesService.getScriptProperties（）。deleteAllProperties（）; //清理属性
 
 
函数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; 
} 
 
函数killTrigger（）{
 var trigger = ScriptApp.getProjectTriggers（）[0]; 
 ScriptApp.deleteTrigger（trigger）; 
} 
  

Using this great answer, I've managed to alter it to create a script to export events from a Google Spreadsheet to Google Calendar.

Create Google Calendar Events from Spreadsheet but prevent duplicates

I then got some great advice, and worked out that it wasn't populating the eventID column due to the error I was getting - "Exceeded maximum execution time" - due to the large number of rows (up to 1000).

Create Google Calendar events from a Google Spreadsheet - script is creating duplicates

I've been looking through answers to try and work out a way to get around this, but can't seem to work out an answer! Apologies - I'm quite new to all this.

Can anyone point me in the right direction, as to how I can either force the script to process beyond the 5 minutes, or anything else?

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;
}

解决方案

The idea is to count the time the script is taking inside the main function and to interrupt it when we reach the limit.

We have to store the row number where we interrupt the script and continue from there on the next run.

Since we don't want to do that manually (how lazy we are :-) we'll set up a trigger to run it every 5 minutes.

Below is a full script. It will send you an email on each run to tell you the progress... you'll have to remove this line after your test of course (unless you like receiving emails every 5 minutes from yourself !)

You will have to change the calendar ID, the row distribution (I tested it on a sheet with less column than yours) but that will be fairly easy.

function createEventsWithBatch() {
  // check if the script runs for the first time or not,
  // if so, create the trigger and PropertiesService.getScriptProperties() the script will use
  // a start index and a total counter for processed items
  // else continue the task
  if(PropertiesService.getScriptProperties().getKeys().length==0){ 
    PropertiesService.getScriptProperties().setProperties({'itemsprocessed':0});
    ScriptApp.newTrigger('createEventsWithBatch').timeBased().everyMinutes(5).create();
  }
  // initialize all variables when we start a new task, "notFinished" is the main loop condition
  var itemsProcessed = Number(PropertiesService.getScriptProperties().getProperty('itemsprocessed'));
  var startTime = new Date().getTime();
  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 (var i = itemsProcessed ; 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 = 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');
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
      row[8] = newEvent.getId();  // Update the data array with event 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);
    }else if(type=='PW'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
    }
    data[i] = row ; 
    Logger.log(i+'    '+new Date().getTime()-startTime)
    if(new Date().getTime()-startTime > 240000){ // if > 4 minutes
      var processed = i+1;// save usefull variable
      PropertiesService.getScriptProperties().setProperties({'itemsprocessed':processed});
      range.setValues(data);
      MailApp.sendEmail(Session.getEffectiveUser().getEmail(),'progress sheet to cal','item processed : '+processed);
      return;
    }
  }  
  range.setValues(data);// this time we are done !
  killTrigger();// delete the trigger
  PropertiesService.getScriptProperties().deleteAllProperties(); // clean up properties
}

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;
}

function killTrigger(){
  var trigger = ScriptApp.getProjectTriggers()[0];
  ScriptApp.deleteTrigger(trigger);
}

这篇关于从Google电子表格创建Google日历活动的Google脚本 - “超出最长执行时间”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！