google-apps-script spreadsheet get start time and end time from multiple rows

Question

I'm trying to write a script for my spreadsheet that reads 2 columns (A & B) with A: start date + time and B: end Date + time. The dates are the same day but I get it with the export from the program.

what I'm trying to do with this is gather all the rows that are from the first day of the month, get the start time and from the last row the end time. The Amount of rows per day is dynamic. Per day, the start goes in a cell and the end goes in a cell.

Example of sheet

Started with this and got stuck at filtering the values per day:

    function hourCalculator()
{
  var SSA = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = SSA.getSheetByName("Example"),
      data = sheet.getDataRange()
                  .getValues();

  for (var i = 1; i < data.length; ++i) { 
    var day = 1,
        maxDay = 32,
        row = data[i],
        actualSDay = new Date(row[0]),
        actualSDateString = Utilities.formatDate(actualSDay, 'Amsterdam', 'dd-MM-yyyy'),
        actualSDayNumber = Utilities.formatDate(actualSDay, 'Amsterdam', 'd'),
        actualSTimeString = Utilities.formatDate(actualSDay, 'Amsterdam', 'HH:mm'),
        actualEDay = new Date(row[1]),
        actualEDateString = Utilities.formatDate(actualEDay, 'Amsterdam', 'dd-MM-yyyy'),
        actualEDayNumber = Utilities.formatDate(actualEDay, 'Amsterdam', 'd'),
        actualETimeString = Utilities.formatDate(actualEDay, 'Amsterdam', 'HH:mm'),
        startDayNumber = Number(actualSDayNumber),
        endDayNumber = Number(actualEDayNumber),
        writeStart = row[6],
        writeStop = row[7];

      Logger.log(actualSDay); 
  }

}

why a google aps script? this can be achieved just by using built-in sheet (array) functions — user3616725
Yes, you can use, for example, =text(A2; "HH:mm") formula to get the time. — Sangbok Lee
That's pure for the time notation if I'm correct, I agree. What I need is for example in the sheet, A2:B8 is day 1 where A2 is the start time and B8 is the end time. I need the start time written in G2 and the end time in H2. although the values of the A and B column are dynamic meaning this time it's A2:B8, but maybe next time it's A2:B10 or A2:B3. — Maurice

Anees Hameed Anees Hameed · Accepted Answer · 2017-03-22T18:35:56

Below code should work, Test it. If you see different time values then do check time zone of your spreadsheet and you script file.

  function start(){
      var Data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Example').getRange("A:B").getValues();
      var startStopArray = [];
      for(var i=1;i<=31;i++){
        startStopArray.push([getStartTimeOfDay(i, Data), getEdTimeOfDay(i, Data)])
      }
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Example').getRange("G2:H32").setValues(startStopArray);

    }

    function getStartTimeOfDay(day, Data){

      var len = Data.length;
      for(var i=1;i<len;i++){
        if(Data[i][0] == "") break;
        var dateIs = new Date(Data[i][0]);
        if(dateIs.getDate() == day){
          var hrs = dateIs.getHours()%12;
          var mins = dateIs.getMinutes();
          return hrs+":"+mins;
        }
      }
      return "";
    }

    function getEdTimeOfDay(day, Data){

      var len = Data.length;
      var hhmm = "";
      for(var i=1;i<len;i++){
        if(Data[i][1] == "") break;
        var dateIs = new Date(Data[i][1]);
        if(dateIs.getDate() > day) break;
        if(dateIs.getDate() == day){
          var hrs = dateIs.getHours()%12;
          var mins = dateIs.getMinutes();
          hhmm = hrs+":"+mins;
        }
      }
      return hhmm;
    }

google-apps-script spreadsheet get start time and end time from multiple rows

1 Answers