Our company offers soft loans to employees. I am trying to write some code that will set up a loan deduction schedule once a loan is approved. This is all done on google sheets. The schedule then can be linked to payroll etc.

The approved loans will appear in a format like this:- Loans Approved - [Serial, Employee ID,Amount, Monthly Deductions,Requested Date,Deduction Start Date]

I am looking to build an array that will have the first 4 elements that repeat and the deduction month to increase by 1

So far this is my code

    function myFunction() {
      var ss = SpreadsheetApp.getActive();
      var sheet = ss.getSheetByName("Loans");
      var range = sheet.getDataRange();
      var data = range.getValues()
      var lastRow = range.getLastRow()
      var scheduleSheet = ss.getSheetByName("Schedule")
      var scheuduleLastRow = scheduleSheet.getDataRange().getLastRow;
      
       for(let i=1;i<lastRow;i  ){

        var serial = data [i][0]
        var id = data [i][1]
        var amount = data[i][2]
        var monthlyRepayment = data [i][3]
        var startDate = new Date (data [i][5])
        var markScheduleDone = sheet.getRange(i 1,7)
             
        var fullMonths = Math.floor(amount/monthlyRepayment)
        var remainderMonth = (amount/monthlyRepayment)-fullMonths
        var remainderAmount = Math.round(remainderMonth*monthlyRepayment)
        
        for (let j=1;j<=fullMonths 1;j  ){
          
           var incrementalMonths = new Date(startDate.setMonth(startDate.getMonth() 1)) ;
             
        }

        
        var newArray = [serial,id,monthlyRepayment]; 
        var remainderArray = [serial,id,remainderAmount];
        var reptArray = Array(fullMonths).fill(newArray);  
        var finalArray = [...reptArray,remainderArray]
        
        Logger.log(finalArray)
      
        var toPasteto = scheduleSheet.getRange(scheuduleLastRow 1,1,finalArray.length,3) 
        toPasteto.setValues(finalArray)
        markScheduleDone.setValue ("Done")
        
         }
      }

I am close but I cant figure out how to join the incrementalMonths to the finalarray.

This is the first time im using a loop within a loop

Also any guidance if I could have done this better?

Kinldy requesting some guidance

CodePudding user response：

I'm not sure if this is exactly what you are looking for but try this.

Notice I fill the array finalArray with all the newArrays so I only have to setValues() once. Same with markDone.

I increment the month but if the day happens to fall outside the number of days in a month it will increment to another day. So for any 28 or 30 day months there should be another check but I didn't do that.

My particular style of coding is to always use a try {} catch() {} block, always terminate a line with semicolon ;, and to use let instead of var whenever possible.

function myFunction() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Loans");
    var range = sheet.getDataRange();
    var data = range.getValues()
    var lastRow = range.getLastRow()
    var scheduleSheet = ss.getSheetByName("Schedule")
    var scheuduleLastRow = scheduleSheet.getDataRange().getLastRow();
    let finalArray = [];
    let markDone = [];
      
    for(let i=1;i<lastRow;i  ){

      var serial = data [i][0];
      var id = data [i][1];
      var amount = data[i][2];
      var monthlyRepayment = data [i][3];
      var startDate = new Date (data [i][5]);
             
      var fullMonths = Math.floor(amount/monthlyRepayment);
      var remainderMonth = (amount/monthlyRepayment)-fullMonths;
      var remainderAmount = Math.round(remainderMonth*monthlyRepayment);
      let day = startDate.getDate();
      if( day > 28 ) throw "This function has not adjusted for short months"
      let month = startDate.getMonth();
      let year = startDate.getFullYear();
      let newArray = [];
        
      for (let j=1;j<=fullMonths 1;j  ){
        month  ;
        if( month > 11 ) {
          month = 0;
          year  ;
        }
        var date = new Date(year,month,day);
        newArray.push([serial,id,monthlyRepayment,date])
      }
      newArray.push([serial,id,remainderAmount,date]);
      finalArray = finalArray.concat(newArray);
      Logger.log(newArray);
      markDone.push(["Done"]);
    }
    sheet.getRange(2,7,markDone.length,1).setValues(markDone);
    scheduleSheet.getRange(scheuduleLastRow 1,1,finalArray.length,4).setValues(finalArray);
  }
  catch(err) {
    Logger.log(err)
  }
}