超过Google表格自定义函数中的最大堆栈深度 [英] Exceeded maximum stack depth in a Custom Function for Google Sheets

查看:190
本文介绍了超过Google表格自定义函数中的最大堆栈深度的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在Google表格中创建了一个自定义函数,根据开始日期和我认为该任务需要的小时数来提供任务的结束日期。



即结束日期=开始日期+小时。



该功能旨在跳过周末并考虑9至5pm之间的工作日(我正在努力排除午餐时间并指定周末和假期,但都在适当的时间)。



该函数适用于大约五个活动,但之后出现错误超出最大堆栈深度。以下是我所指的内容的截图。





  // var startdate = new Date(2016,04,16,9, 0,0); 
// var addhours = 3;

Date.prototype.addHours = function(h){
this.setHours(this.getHours()+ h);
返回此;


Date.prototype.addDays = function(days){
var dat = new Date(this.valueOf());
dat.setDate(dat.getDate()+ days);
返回dat;
}

/ **
*添加小时以排除周末日期
*
* @param {number} startdate添加小时的日期到
* @param {number} addHours添加
* @return的小时数新的日期
* @customfunction
* /

函数MYWORKDAY startdate,addhours){
var endDate = new Date();
var endTime = new Date(startdate).setHours(17,0,0);
var remainingEffortHrs = new Date();
var availableTimeHrs = endTime - startdate;
availableTimeHrs =(availableTimeHrs / 1000)/ 60/60;

if(startdate.map){//测试输入是否为数组。
return startdate.map(MYWORKDAY); //如果存在,则递归数组。
} else {

//将小时添加到开始日期
// endDate = new Date(startdate).addHours(addhours);
endDate = new Date(startdate).addHours(addhours);

//计算剩余工作量 - 如果任务在下午5点后结束
if(endDate> endTime){
remainingEffortHrs =((Math.abs(endDate - endTime))/ 1000)/ 60/60;
} else {
remainingEffortHrs = 0; (1);
}

if(remainingEffortHrs> 0){
startdate = new Date(startdate).addDays(1);
startdate = MYWORKDAY(startdate,remainingEffortHrs);
} else {
//剩余工作量为0
startdate = endDate;
}
return GetNextWorking(startdate);



函数GetNextWorking(endDate){
//获取下一个工作日
if(endDate.getDay()!= 0& amp ;& endDate.getDay()!= 6){
return endDate;
} else {
adjustedEndDate = new Date(endDate.setDate(endDate.getDate()+ 1));
adjustedEndDate = new Date(adjustedEndDate);
//递归调用这个函数,直到返回的
//日期是一个工作日
return adjustedEndDate = GetNextWorking(adjustedEndDate);
}
}

我希望这是有道理的。这已经花了一段时间才能到达这个阶段,任何有关如何改进性能或重构的建议都将不胜感激。

解决方案

这是工作代码。我在尝试添加代码以包含午餐时遇到了一些严重问题,但这样做突出了我逻辑中的缺陷。现在,这也应该考虑到Google表格中第二张名为设置的午餐时间。 (在Google表格之外工作时,我还没有弄清楚如何绕过引用错误)。但是,这会解决超出最大堆栈深度错误。也许你可以建议改进?

  var dayStartTime = getStartTime(); 

var dayEndTime = getEndTime();

var lunchtimeEnd = getLunchtimeEnd();

var lunchtimeStart = getLunchtimeStart();


/ *第二天开始
*
* @param {number} startdate将小时添加到
*的日期@return新日期
* @customfunction
* /

Date.prototype.addDays = function(days){
var dat = new Date(this.valueOf());
dat.setDate(dat.getDate()+ days);
返回dat;


函数addHours(date,h){
return new Date(date.getTime()+(h * 60 * 60 * 1000));


$ b函数MYWORKDAY(startdate,effort){
if(startdate.map){
return startdate.map(MYWORKDAY);
} else {

var endTime = new Date();

var availableTimeHrs;

var endDate = 0;

while(effort> 0)
{
endTime = new Date(startdate).setHours(dayEndTime.getHours(),dayEndTime.getMinutes(),dayEndTime.getSeconds ));

lunchtimeEnd = todaysLunchEnd(startdate);

lunchtimeEnd = new Date(lunchtimeEnd);

lunchtimeStart = todaysLunchEnd(startdate);

lunchtimeStart =新日期(lunchtimeStart);

endDate = addHours(startdate,effort); (startdate< = lunchtimestart&& endDate> = lunchtimeEnd){
endDate = addHours(endDate,1);

if (endDate> endTime)
{
effort =((Math.abs(endDate - endTime))/ 1000)/ 60/60;
}

;
startdate = new Date(startdate).addDays(1);
startdate = GetNextWorking(startdate);
startdate = new Date(startdate).setHours(dayStartTime.getHours(),dayStartTime.getMinutes(),dayStartTime.getSeconds());
startdate = new Date(startdate);

}
else
{
effort = 0;
}
}
}
return endDate;
}

函数GetNextWorking(endDate){
if(endDate.getDay()!= 0&& endDate.getDay()!= 6){
返回endDate;
} else {
adjustedEndDate = new Date(endDate.setDate(endDate.getDate()+ 1));
adjustedEndDate = new Date(adjustedEndDate);
return adjustedEndDate = GetNextWorking(adjustedEndDate);



函数MYSTARTDATE(startdate){

// var startTime = getStartTime();

var morningStart = new Date();

if(startdate.getHours()== 17){
morningStart = startdate.addDays(1);
morningStart = GetNextWorking(morningStart);
morningStart.setHours(9);
} else {
morningStart = startdate;
}
return morningStart;
}

函数todaysLunchEnd(endDate){
var lunchtimeEnd = getLunchtimeEnd();

lunchtimeEnd = new Date(endDate).setHours(lunchtimeEnd.getHours(),lunchtimeEnd.getMinutes(),lunchtimeEnd.getSeconds());
lunchtimeEnd = new Date(lunchtimeEnd);
return lunchtimeEnd;


function getStartTime(){

var settingsSheet = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName(Settings);
var range = settingsSheet.getRange(B5);
var startTime = range.getValue();
var startTime;

if(!startTime){
startTime = new Date(28800000);
// startTime = new Date(32400000); // 09:00
}

return startTime;


function getEndTime(){
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName(Settings);
var range = settingsSheet.getRange(B6);
var endTime = range.getValue();

if(!endTime){
endTime = new Date(57600000);
// endTime = new Date(61200000); // 17:00
}

return endTime;
}

function getLunchtimeStart(){
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName(Settings);
var range = settingsSheet.getRange(B7);
var startTime = range.getValue();

if(!startTime){
startTime = new Date(39600000); // 11am
// startTime = new Date(43200000); // 12pm
}
return startTime;
}

function getLunchtimeEnd(){
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName(Settings);
var range = settingsSheet.getRange(B8);
var endTime = range.getValue();

if(!endTime){
endTime = new Date(43200000); // 12:00
// endTime = new Date(46800000); // 13:00
}

return endTime;
}


I'm creating a custom function in Google Sheets to provide the end date for a task based on the start date and the number of hours I think the task will take.

i.e. end date = start date + hours.

The function aims to skip weekends and consider a working day between 9 and 5pm (I'm working up to excluding lunchtimes and specifying weekends and holidays, but all in good time).

The function works fine for about five activities, but then errors "Exceeded maximum stack depth". Here's a screenshot of what I'm referring to.

And here's the AppScript / JavaScript.

//var startdate = new Date(2016, 04, 16, 9, 0, 0);
//var addhours = 3;

Date.prototype.addHours = function(h) {
  this.setHours(this.getHours() + h);
  return this;
}

Date.prototype.addDays = function(days) {
  var dat = new Date(this.valueOf());
  dat.setDate(dat.getDate() + days);
  return dat;
}

/**
 * Adds hours to a date excludes weekends
 *
 * @param {number} startdate The date to add the hours to
 * @param {number} addHours The hours to add
 * @return The new date
 * @customfunction
 */

function MYWORKDAY(startdate, addhours) {
  var endDate = new Date();
  var endTime = new Date(startdate).setHours(17, 0, 0);
  var remainingEffortHrs = new Date();
  var availableTimeHrs = endTime - startdate;
  availableTimeHrs = (availableTimeHrs / 1000) / 60 / 60;

  if (startdate.map) { // Test whether input is an array.
    return startdate.map(MYWORKDAY); // Recurse over array if so.
  } else {

    // Add the hours to the start date
    //endDate = new Date(startdate).addHours(addhours);
    endDate = new Date(startdate).addHours(addhours);

    // Calculate remaining effort - if the task ends after 5pm
    if (endDate > endTime) {
      remainingEffortHrs = ((Math.abs(endDate - endTime)) / 1000) / 60 / 60;
    } else {
      remainingEffortHrs = 0;
    }

    if (remainingEffortHrs > 0) {
      startdate = new Date(startdate).addDays(1);
      startdate = MYWORKDAY(startdate, remainingEffortHrs);
    } else {
      // Remaining effort is 0
      startdate = endDate;
    }
    return GetNextWorking(startdate);
  }
}

function GetNextWorking(endDate) {
  // Get the next working day
  if (endDate.getDay() != 0 && endDate.getDay() != 6) {
      return endDate;
  } else {
    adjustedEndDate = new Date(endDate.setDate(endDate.getDate() + 1));
    adjustedEndDate = new Date(adjustedEndDate);
    // Recursively call the this function until the returned
    // date is a working day
    return adjustedEndDate = GetNextWorking(adjustedEndDate);
  }
}

I hope this makes sense. This has taken a while to get to this stage and any suggestions as to how to improve the performance or refactor would be greatly appreciated.

解决方案

Here's the working code. I ran into some serious problems whilst trying to add code to include lunchtimes, but this worked to highlight the flaws in my logic. This should now also take into account a lunchtime from a second sheet called 'Settings' in Google Sheets. (I hadn't quite worked out how to bypass the Reference error when working outside of Google Sheets). This does however solve the Exceeded maximum stack depth error. Maybe you can suggest an improvement?

 var dayStartTime = getStartTime();

 var dayEndTime = getEndTime();

 var lunchtimeEnd = getLunchtimeEnd();

 var lunchtimeStart = getLunchtimeStart();


  /* Starts the next day
   *
   * @param {number} startdate The date to add the hours to
   * @return The new date
   * @customfunction
   */

  Date.prototype.addDays = function(days) {
    var dat = new Date(this.valueOf());
    dat.setDate(dat.getDate() + days);
    return dat;
  }

  function addHours(date, h) {
     return new Date(date.getTime()  + (h*60*60*1000));
  }


    function MYWORKDAY(startdate,effort) {
    if (startdate.map) { 
      return startdate.map(MYWORKDAY); 
    } else {

      var endTime = new Date();

      var availableTimeHrs;

      var endDate = 0;

      while (effort > 0) 
      {
        endTime = new Date(startdate).setHours(dayEndTime.getHours(), dayEndTime.getMinutes(), dayEndTime.getSeconds());

        lunchtimeEnd = todaysLunchEnd(startdate);

        lunchtimeEnd = new Date(lunchtimeEnd);  

        lunchtimeStart = todaysLunchEnd(startdate);

        lunchtimeStart = new Date(lunchtimeStart);  

        endDate = addHours(startdate, effort);

        if (startdate <= lunchtimeStart && endDate >= lunchtimeEnd) {
           endDate = addHours(endDate, 1);
        } 

        if(endDate > endTime)
        {
          effort = ((Math.abs(endDate - endTime)) / 1000) / 60 / 60;   
          startdate = new Date(startdate).addDays(1);
          startdate = GetNextWorking(startdate);
          startdate = new Date(startdate).setHours(dayStartTime.getHours(), dayStartTime.getMinutes(), dayStartTime.getSeconds());
          startdate = new Date(startdate);

        } 
        else
        {
            effort = 0;
        }
      }
     }
     return endDate;
  }

  function GetNextWorking(endDate) {
     if (endDate.getDay() != 0 && endDate.getDay() != 6) {
      return endDate;
    } else {
      adjustedEndDate = new Date(endDate.setDate(endDate.getDate() + 1));
      adjustedEndDate = new Date(adjustedEndDate);
      return adjustedEndDate = GetNextWorking(adjustedEndDate);
    }
  }

  function MYSTARTDATE(startdate) {

    //var startTime = getStartTime();

    var morningStart = new Date();

    if (startdate.getHours() == 17) {
      morningStart = startdate.addDays(1);
      morningStart = GetNextWorking(morningStart);
      morningStart.setHours(9);
    } else {
      morningStart = startdate;
    }
    return morningStart;
  }

  function todaysLunchEnd(endDate) {
    var lunchtimeEnd = getLunchtimeEnd();

    lunchtimeEnd = new Date(endDate).setHours(lunchtimeEnd.getHours(), lunchtimeEnd.getMinutes(), lunchtimeEnd.getSeconds());
    lunchtimeEnd = new Date(lunchtimeEnd);
    return lunchtimeEnd;
  }

function getStartTime() {

    var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
    var range = settingsSheet.getRange("B5");
    var startTime = range.getValue(); 
    var startTime;

    if (!startTime) {
      startTime = new Date(28800000);
      //startTime = new Date(32400000); // 09:00
     }

    return startTime;
  }

  function getEndTime() {
    var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
    var range = settingsSheet.getRange("B6");
    var endTime = range.getValue(); 

    if (!endTime) {
       endTime = new Date(57600000);
       //endTime = new Date(61200000); // 17:00      
    }

    return endTime;
  }

  function getLunchtimeStart() {
    var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
    var range = settingsSheet.getRange("B7");
    var startTime = range.getValue(); 

    if (!startTime) {
      startTime = new Date(39600000); //11am
       //startTime = new Date(43200000); // 12pm
    }
    return startTime;
  }

  function getLunchtimeEnd() {
    var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
    var range = settingsSheet.getRange("B8");
    var endTime = range.getValue(); 

    if (!endTime) {
      endTime = new Date(43200000); //12:00
      //endTime = new Date(46800000); //13:00
    }

    return endTime;
  }

这篇关于超过Google表格自定义函数中的最大堆栈深度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆