超过了最大执行时间，Google Sheets脚本变通了 [英] Exceeded maximum execution time google sheets script work around

问题描述

我有一个脚本，该脚本为从母版纸上绘制的一组数据运行一个函数.每次运行该函数时，它都会基于工作表中的单元格(单元格A3)中指定的日期绘制和处理数据集.此单元格中的日期通过"for"循环针对我指定的日期范围进行了更新.该脚本可以在第6次运行中的某些时间段内执行超过最大执行时间"之前，可以执行这些循环的5次完整运行(即每次运行均执行该功能，需要花费一天的数据量).每次运行后，我的所有数据均已以连续更新的方式保存到电子表格中，因此，在五个周期后，我要做的是通过修改脚本从第6天开始手动重新启动运行另外5天.我正在阅读时间驱动的触发器，其中有人在暂停每5分钟执行一次脚本，以达到6分钟的执行时间限制，但这不符合我的需求，因为我希望每5个周期后就休息一下(不基于时间).我需要编写一个脚本，在该脚本中运行5个周期后，该脚本可能会中断，然后再恢复运行5个周期.这是我的代码:

I have a script that runs a function for a set of data that is drawn from a master sheet. Each time the function runs, it draws and processes a data set based on a date designated in a cell in a sheet (cell A3). The date in this cell is updated via a 'for' loop for a date range that I specified. the script can execute 5 complete runs (aka each run execute the function for a day worth of data) of these loops before I run into "Exceeded maximum execution time" some time during the 6th run. All of my data are already saved to the spreadsheet in a continual updating fashion after every run so what I have to do after five cycles is to manually restart the run for another 5 days starting at the 6th day by modifying my script. I am reading about time driven trigger where someone is pausing the script every 5 minutes to get around the 6 minutes execution time limit but it does not suit my needs because I would like a break after every 5 cycles, (not based on time). What I need is to write a script where after running 5 cycles, the script can break and then resume running again for another 5 cycles. Here is what my code looks like:

function runMultipleDates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var start = new Date("2018-11-05");
  var end = new Date("2018-11-09"); 
  var step = 1;

  for (var date1 = start; date1 <= end; date1.setDate(date1.getDate() + step)) {
    var date2 = new Date(date1.getTime());
    date2.setDate(date2.getDate() + 1);
    ss.getSheetByName('Time Range').getRange("A3").setValue(date2);
    runEverything();

  };
}

如您所见，现在我将开始日期和结束日期手动调整为5天，让其运行，然后通过将脚本开始日期调整为2018-11-09并将结束日期调整为重新开始该过程下一次运行时间为2018-11-13.此功能本身可以运行超过5天的周期，而没有执行时间的限制.因此，问题是在上述脚本的5个周期后是否有暂停脚本和恢复运行的途径.任何帮助，我们将不胜感激.

As you can see, right now I am adjusting the start and end date manually for 5 days, let it run and then restart the process again by adjusting my script start date to be 2018-11-09 and end date to be 2018-11-13 for the next run. This function, on its own, is to able to run for far more than 5 days cycles without the execution time restriction. So the question is whether there is away to pause the script and resume running after 5 cycles of the above script. Any help is greatly appreciated.

推荐答案

感谢提出的建议.我找到了一个针对此问题的答案，其中的以下代码略微改编自 benlcollins 在gitHub上.正如@Cooper所建议的，我每5.5分钟运行一次函数，这比我完成主要函数runMultipleDates()所需的时间要多一点(该函数一次只能运行5天)；我要重复20次，这意味着它将覆盖20x5 = 100天，这对我来说已经足够了.如果有帮助，我会发布此信息.

Thanks for the ideas sent my way. I have found an answer for this problem with the following codes adapted slightly from benlcollins on gitHub. As @Cooper suggested, I ran my function every 5.5 minutes which was just a little more than what it took for me to complete the main function runMultipleDates() (this function processes 5 days at a time on its own) ; I am repeating this 20 times which means it will cover 20x5=100 days which is good enough for me. I am posting this in case it helps someone.

// -----------------------------------------------------------------------------
// add menu
// -----------------------------------------------------------------------------
function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Auto Trigger")
    .addItem("Run","runAuto")
    .addToUi();
}
function runAuto() {  
  // resets the loop counter if it's not 0
  refreshUserProps(); 
  // create trigger to run program automatically
  createTrigger();
}

function refreshUserProps() {
  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty('loopCounter', 0);
}

function createTrigger() {
    ScriptApp.newTrigger('runGlobal')
      .timeBased()
      .everyMinutes(5.5)
      .create();
}

// -----------------------------------------------------------------------------
// function to delete triggers
// -----------------------------------------------------------------------------
function deleteTrigger() {
  
  // Loop over all triggers and delete them
  var allTriggers = ScriptApp.getProjectTriggers();
  
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}

// -----------------------------------------------------------------------------
// function to run called by trigger once per each iteration of loop
// -----------------------------------------------------------------------------
function runGlobal() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');  
  // get the current loop counter
  var userProperties = PropertiesService.getUserProperties();
  var loopCounter = Number(userProperties.getProperty('loopCounter'));  
  var limit = 20; // create trigger to run program automatically  
  // if loop counter < limit number, run the repeatable action
  if (loopCounter < limit) {       
    // do stuff
    runMultipleDates();   
    // increment the properties service counter for the loop
    loopCounter +=1;
    userProperties.setProperty('loopCounter', loopCounter);    
    // see what the counter value is at the end of the loop
    Logger.log(loopCounter);
  }
  // if the loop counter is no longer smaller than the limit number
  else {
    Browser.msgBox("Run Completed");    // Log message to confirm loop is finished
    deleteTrigger(); 
  }  
}

这篇关于超过了最大执行时间，Google Sheets脚本变通了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！