Google Script to get hour + minute from cellrange

Question

I am trying to create a Google script to get hour and minute from a range of cells and add them up as total of minutes. But none of the examples I have found online matches anything to get the thing done, and I haven't found any help in checking the API.

So lets say that I have some cells like this:

|   |    A     |    B     |    C     |    D     |
| 1 | 08:01:00 | 07:57:00 | 06:33:00 | 08:08:00 |

How do I get the minutes? I'm thinking of giving the range as a parameter to the function, in this case x(A1:D1)

EDIT: For clarify: I want to send a range of cell-coordinates to the function. From each cell I want to get a total sum of duration in minutes. So with two cells '08:00:00' and '07:30:00' I should get 930.

Answer 1

This should be fairly simple using JavaScript date methods, I tried working with getTime() that returns milliseconds and divide the resulting value by 60000 to get minutes but the test wasn't working so I used a different approach.

code below (it works if you don't have values with seconds, only hours and minutes)

function test(){
  var sh = SpreadsheetApp.getActiveSheet();
  var range = sh.getRange('A1:D1');// value used for test, change to your use case
  var minutes = totalMinutes(range);
  Logger.log(minutes);
}

function totalMinutes(range){
  var data = range.getValues();
  var total = 0;
  for(var n in data[0]){
    var min = data[0][n].getMinutes()+60*data[0][n].getHours();
    total+=min
  }
  return total;
}

Using your example

it returns

---

last edit (and follow up question):

You didn't mention you were using this as a custom function... now that I tested it on your sheet I can actually see the issue ! (but have no idea where it comes from...) the "solution" (I should say "the workaround") is simple, just add 1 to minute value and results are right but the issue becomes different :

This custom function does not update results when one of the cells in the source range is modified !!!

for example :

cell A1 = 20, cell A2 = 20, custom function result is 40

now when I change A1 to 10, the result is always 40, no matter what I do...

That's really weird...

For info, here is the code as a custom function :

function myMinutes(cells){
  var range = SpreadsheetApp.getActiveSheet().getRange(cells);
  var data = range.getValues();
  var total = 0;
  for(var cell in data[0]){
    var hourtomin = data[0][cell].getMinutes()+1;
    var min = 60*(data[0][cell].getHours());
    total += hourtomin + min;
  }
  return total;
}

If anyone can explain ??? I don't.

I never use custom functions because I don't like them and this behavior won't make me change my mind... ;-)

Answer 2

I have found another way to get a sum of minutes in a range

=arrayformula(sumproduct(HOUR(A1:D1))*60+sumproduct(minute(A1:D1)))