Trying to streamline multiple for loops to reduce the execution time in Google Script

Question

I am in the process of writing a back-end to a spreadsheet we use to work our equipment allocation.

One of the ways we are doing this is using a google form which outputs data to a spreadsheet in the format:

Timestamp | Job Description | Laptops | Printers | Start date | End date | Months involved | Job duration.

I have written two functions to calculate the duration of the jobs and the months involved, as all of the other fields are inputted by the user (and validated by the input form).

The only issue is that when I call these two functions, they take approximately 15-20 seconds to execute. I feel this is likely due to me iterating through every row for the data, but I am new enough to Jscript that I can't get my head around how to make it more efficient. Could I get some suggestions? Not necessarily looking for code perfect responses, as I am using this to learn, but some points in the right direction would be appreciated.

Code below:

// Calculates the duration of the job in days (including weekends / bank holidays).

function dateCalc() {

    var ss = SpreadsheetApp.getActive()
    var sh = ss.getSheetByName('Form responses')
    var selection = sh.getDataRange()

  // Declares the relevant columns

    var columnResult = 8
    var columnConditional1 = 5
    var columnConditional2 = 6
    var rows = selection.getNumRows()

  // Initiates a loop statement to iterate through the rows. On each row, provided there is a start date and an end date present and the difference hasn't already been calculated,
  // calculates difference and outputs it to the relevant column.

    for (var row=2; row <= rows; row++){

           if (sh.getRange(row,5).getValue() != '' && sh.getRange(row,6).getValue() != '' && sh.getRange (row,8).getValue() == "") {
            var startDate = sh.getRange(row,5).getValue()
            var endDate = sh.getRange(row,6).getValue()
            var dateDiff = Math.floor((endDate-startDate)/(24*3600000))
            sh.getRange(row,columnResult).setValue(dateDiff)
           }
    }
}

function DateMonths(){

  // Pulls in the "Moment.Js" library which allows heavy date manipulation. every mention of "moment" from here on is calling this library.
  // Also sets the moment language to english so dates are read "dd/mm/yyyy" instead of "mm/dd/yyyy"

  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.0/moment-with-locales.js').getContentText());
  moment.locale('en')

  //Declaring the range of the active data and assigning it to the "rows" variable.

  var ss = SpreadsheetApp.getActive()
  var sh = ss.getSheetByName('Form responses')
  var selection = sh.getDataRange()
  var rows = selection.getNumRows()

  //Same conditional as previous function, but streamlined into a single line using && to denote AND.

  for (var row=2; row <= rows; row++){
    if (sh.getRange(row,5).getValue() != '' && sh.getRange(row,6).getValue() != '' && sh.getRange (row,8).getValue() == "") {

         var startDate = sh.getRange(row,5).getValue()
         var endDate = sh.getRange(row,6).getValue()
         var dateStart = moment(startDate, 'DD-MM-YYYY')
         var dateEnd = moment(endDate, 'DD-MM-YYYY')


         // Creates a blank array timeValues

         var timeValues = [];

         //initiates a while loop under the condition that the start month is less than the end month, and outputs the month in plain english.

         while (dateEnd > dateStart || dateStart.format('M') === dateEnd.format('M')) {
           timeValues.push(dateStart.format('MMMM'));
           dateStart.add(1,'month');

           }
         // outputs the array of months between start and end to the cell in the row concerned. Note that this is OUTSIDE of the while loop, but INSIDE of the for loop to ensure it 
         // only prints the finished array for every row.

         sh.getRange(row,7).setValue(timeValues.join(', '))
     }
  }
}

}

Answer 1

There are two major improvements you can make and one that depends a bit on your data:

1. Use getValues() to loop through an Array, rather than the range. Accessing the range is very slow.
2. Use built in functions to manipulate the dates, rather than using Moment.js. Utilities.formatDate(), getMonth(), setMonth()
3. If you're going to be printing a lot of data each time you run the script, then consider using setValues(), as setValue() takes much more time. (I think you'll probably only be printing a few rows at a time, though, so I left this for you to implement if you need it.)

Please make sure that the outputs are all going into the correct columns, but otherwise, this should run much, much faster.

// Calculates the duration of the job in days (including weekends / bank holidays).

function dateCalc() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Form responses');
  var selection = sh.getRange("A:H"); // Define your range a little more to limit the amount of work done
  var rows = selection.getValues(); // Get the values as an Array[][], rather than having to access the range. MUCH FASTER!!!!
  var columnResult = 8;
  // Initiates a loop statement to iterate through the rows. On each row, provided there is a start date and an end date present and the difference hasn't already been calculated,
  // calculates difference and outputs it to the relevant column.

  for (var i=1; i < rows.length; i++){
    var timestamp = rows[i][0];
    if (timestamp == "") {
      // Because the data is submitted in a form, there should be no empty rows. 
      // If we find a row to be empty, as judged by the automatically filled timestamp, 
      // then we will stop looping through the data. No need to look at empty values ¯\_(ツ)_/¯ 
      // If you're not comfortable with this, you can use "continue;" instead. 
      break; 
    }
    var startDate = rows[i][4]; // Arrays are 0-indexed, so we use the Column # - 1 = 4
    var endDate = rows[i][5];
    var dateDiff = rows[i][7];
    // All the sh.getRange().getValue() functions take forever!
    if (startDate != "" && endDate != "" && dateDiff == "") {
      dateDiff = Math.floor((endDate-startDate)/(24*3600000))
      sh.getRange(i+1,columnResult).setValue(dateDiff)
    }
  }
}

function DateMonths(){  
  //Declaring the range of the active data and assigning it to the "rows" variable.
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Form responses');
  var selection = sh.getRange("A:H");
  var rows = selection.getValues();
  var columnResult = 7;
  //Same conditional as previous function, but streamlined into a single line using && to denote AND.
  for (var i=1; i < rows.length; i++){
    var timestamp = rows[i][0];
    if (timestamp == "") {
      break;
    }
    var startDate = rows[i][4];
    var endDate = rows[i][5];
    var dateDiff = rows[i][7];
    if (startDate != "" && endDate != "" && dateDiff == "") {
      startDate = sh.getRange(i+1,5).getValue()
      endDate = sh.getRange(i+1,6).getValue()
      var dateStart = new Date(startDate);
      var dateEnd = new Date(endDate);

      // Creates a blank array timeValues
      var timeValues = [];

      //initiates a while loop under the condition that the start month is less than the end month, and outputs the month in plain english.
      while (dateEnd > dateStart || dateStart.getMonth() === dateEnd.getMonth()) {
        timeValues.push(Utilities.formatDate(dateStart, "GMT", "MMMMM")); 
        dateStart.setMonth(dateStart.getMonth() + 1);
      }
      // outputs the array of months between start and end to the cell in the row concerned. Note that this is OUTSIDE of the while loop, but INSIDE of the for loop to ensure it 
      // only prints the finished array for every row.

      sh.getRange(i+1,columnResult).setValue(timeValues.join(', '));
    }
  }
}