1

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.

Smorkster
  • 322
  • 3
  • 10
  • If there is a reason for requiring Google Apps Script to do this (ie actual use case is more complicated) then I would suggest a. include the `google-apps-script` tag and b. probably a duplicate of this thread: http://stackoverflow.com/questions/17715841/gas-how-to-read-the-correct-time-values-form-google-spreadsheet/17727300#17727300 ... this explains and works around the issues of different epochs for Sheets and Javascript, different time zone treatment, etc. – AdamL Sep 23 '14 at 01:29
  • pnuts: Unfortunately that doesn't give the correct value AdamL: I tried that function, but it did not work. Manly 'cus the argument i send "A1:D1" is read as values of time and date, but I want it to be used as A1-notation coordinates. Maybe I should 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. – Smorkster Sep 23 '14 at 06:02

2 Answers2

0

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

enter image description here

it returns

enter image description here

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... ;-)

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • This works almost perfectly. However, for some reason it returns the a value one less than the actual sum for each cell. So instead of getting 1839, I get 1835. Four less for four cells. – Smorkster Sep 23 '14 at 08:36
  • As you can see in the screen capture I get 1839... have you checked your range settings ? I suggest you add a Logger.log in the for loop to check each iteration value – Serge insas Sep 23 '14 at 08:39
  • I couldn't get the logger to work, but I tried sending just one cell, returning after first cell, only returning minutes (not converting hours), reading from other cells, reading from cells with timestamp manually written. But nothing. It always returns the cells minutes-1. P.S. I am using your exact code. P.P.S. I noticed that the cellvalue read, contains i.e. '08:00:46'. Worth noting here is that the time is calculated from a starttime and a stop time. This goes for all times in the spreadsheet. – Smorkster Sep 23 '14 at 09:55
  • '08:00:46' is 8 hours and 46 seconds... you didn't mention you needed to get seconds so it will return 8*60=480 minutes without seconds. could you share a copy of your sheet please ? About the logger : from th script editor > view > Logs my test sheet (see test3 : https://docs.google.com/spreadsheets/d/1Y196LYNTsLWgKdz9pc9FzhFtgy3lAhVvhl5qxC2WSL8/edit?usp=sharing make a copy to use) – Serge insas Sep 23 '14 at 10:21
  • Thou, I don't want seconds. The timestamp I enter to the cells are just hour and minute. But for some reason, when I send the cell coordinates to the function, the time is always 14 seconds earlier; i.e. 07:00:46 instead of 08:00:00 which is what I send. – Smorkster Sep 23 '14 at 10:28
  • you'll have to solve that 14 secs issue before anything can work... that's simply why you are getting minutes -1 (I take only integer part of minutes.) have you checked your script and SS timezone settings ? have you tried my test sheet ? – Serge insas Sep 23 '14 at 11:36
  • Good point @pnuts :-) this 14 secs seem really strange. The OP doesn'tseem to want sharing a copy of his sheet.... I'm just curious. – Serge insas Sep 23 '14 at 17:49
  • 1
    Mmmmh...strange was probably too weak, how about weird? :-) I try to imagine what can go wrong in his sheet but I admit I'm stuck... – Serge insas Sep 23 '14 at 18:24
  • interesting ! thanks. We should know the OP time zone to check this... setting date is a clever solution (if the issue comes from there) I hope he/she will come back and give more details. – Serge insas Sep 23 '14 at 19:34
  • Sorry for late answer. Here is a link to the spreadsheet https://docs.google.com/spreadsheet/ccc?key=0AuKMHv0A3A7vdGFULU1ZQzNqVkZqajZ4d0ZkREh5MVE&usp=sharing Sorry for the confusion in earlier post. It should say 07:59:46, not 07:00:46. – Smorkster Sep 24 '14 at 06:20
  • Yeah, I thought of simply adding that to. But it aint pretty :) The weird thing is that this problem occurs in all spreadsheets I have. – Smorkster Sep 24 '14 at 12:58
  • I sent you an email too... as you can see in my last edit it becomes quite terrible ! I should say unusable (!)... maybe someone will give an explanation... I can't, sorry. Btw, I can't even suggest another approach for your use case (except native spreadsheet functions that you already use) because the structure of the sheet would make it hard to use a "normal" script. – Serge insas Sep 24 '14 at 13:08
0

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

=arrayformula(sumproduct(HOUR(A1:D1))*60+sumproduct(minute(A1:D1)))
josliber
  • 43,891
  • 12
  • 98
  • 133
Smorkster
  • 322
  • 3
  • 10