Exceeded maximum stack depth in a Custom Function

2019-09-10 02:55发布

问题:

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.

回答1:

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