I have the below code that works the way as intended (3 functions total). I am looking for help in how to write better code. I'm thinking there has to be a more efficient or cleaner way to write this code. In particular, the way I wrote the date code I am confused how it is working, just know that it is. Please and thank you for any help I can get on this!

The idea was developing a function that could determine what a delay of revenue timing could look like, given the seasonality of the business. The function pulls in a months value (original end date - new end date). Then the function will begin to sum the percentages based on the month following the current end month and until the difference in the dates is reached. Then this percentage value is taken to get us the impact in delayed timing.

IE supposed to start January 2021, but had to move until May 2021. This causes a delay of 4 months. Starting from February, sum the percentages for the following 4 months.

Google Sheet:

Update:

• Here is a more optimized way of calculating all rows at once, and a significant reduction of method calls. This also makes the other functions not needed. This solution only calls getValues once and then processes all the data. Feel free to modify/adjust the script as needed.

Script:

function rateChange() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Rate Change");

  var startRow = 3;
  var endDateStartCol = 6;
  var numRows = sheet.getLastRow() - startRow   1;
  var data = sheet.getRange(startRow, endDateStartCol, numRows, 17).getValues();

  return data.map(function (row) {
    var [endMth, newEndMth, monthNum] = row.slice(0, 3);
    endMth = new Date(endMth).getMonth();
    newEndMth = endMth   monthNum;

    if (monthNum >= 12)
      return [1];
    else if (monthNum >= 1) {
      var mth = row.slice(5);
      var negVal = 0;
      for (i = newEndMth; i > endMth; i--)
        negVal  = mth[i % 12];
      return [negVal];
    }
  });
}

Output:

Note:

• For the latter custom function, you only need to put this at the first row of your data. Then it will populate all the rows below it.
• I added a sample data on the last row that carry overs to the next year to check if the function properly calculates the total value (It does calculate next year properly, modulo (%) handled this scenario pretty easily)