others - Google脚本从Google电子表格"Exceeded maximum execution time" 创建Google日历事件

  显示原文与译文双语对照的内容

使用这个优秀的答案,我已经将它改变为创建一个脚本来从Google电子表格中导出事件。

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

然后我得到了一些不错的建议,并指出由于出现了大量的行,所以它没有填充eventID列,因为出现了大量行。

从Google电子表格创建Google日历事件- 脚本正在创建重复的内容。

我一直在寻找答案,找出解决这个问题的方法,但是看不出答案。 抱歉,我对所有这些都是全新的。

任何人都可以指向我,如何强制脚本在 5分钟之外进行处理,或者它的他任何事情。


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 ="pma5g2rd5cft4lird345j7pke8@group.calendar.google.com";//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;
}

时间: 原作者:

这个想法是计算脚本在主函数内的占用时间,并在达到限制时中断它。

我们必须在中断脚本的地方存储行号,然后在下一次运行时继续。

因为我们不想手动操作( 我们是多么的懒惰:- ) 我们会设置一个触发器每 5分钟运行一次。

下面是完整脚本。 它会在每次跑步时给你发封邮件告诉你进度。 你必须在测试之后删除这一行( 除非你喜欢每隔 5分钟接收电子邮件) !

你必须更改日历 ID,行分配( 我在一张比你的小的纸上测试过的),但这将非常容易。


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 ="h22nevo15tm0nojb6ul4hu7ft8@group.calendar.google.com";
 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);
}

原作者:

有多种气体库可以帮助你通过 5分钟执行超时,与 @Serge 建议的一样,使用了相同的思想。 我个人使用的是连续批处理库。

...