Handling time duration in Google Sheets Api

Question

I'm making a basic script that fetches time durations from external sheets, and sums them. What I have so far is:

function getHorasCasoUso() {
  var libros = {
    "key1" : "externalSheetURL1",
    "key2" : "externalSheetURL2",
    ...
  };

  var horas_por_caso_uso = {};

  for (var key in libros) {
    var libro = SpreadsheetApp.openByUrl(libros[key]);
    var sheets = libro.getSheets();
    for (var i = 0; i < sheets.length; i++) {
      var sheet = sheets[i];
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();

      for (var j = 5; j < numRows; j++) {
        var row = values[j];
        var caso_uso = row[6];
        var horas = row[4]; //The cell format is 'Duration'       

        if (!caso_uso)
          continue;

        if (!!horas_por_caso_uso[caso_uso])
          horas_por_caso_uso[caso_uso] += horas;
        else
          horas_por_caso_uso[caso_uso] = horas;
      }
    }
  }

  var ss = SpreadsheetApp.getActiveSheet();
  for (var key in horas_por_caso_uso) {
    ss.appendRow([key, horas_por_caso_uso[key]]);
  }

}

The problem is that the data stored in 'horas' is a string. I want to get the time duration in that cell. How can I do that?

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Your issue seems quite similar to the one in this post but at a larger scale...

You should convert row[4] value to minutes (or seconds if you need this accuracy) before adding that value to the total counter.

If the cells are formatted as duration (as you say it is) it should work without changes.(see code at the end of this post)

If not, ie if these values are returned as strings then a simple string manipulation will do the job for you like this :

example : testString = 12:34

function toMinutes(value){
  var minutes = Number(value.split(':')[0].replace(' ',''))*60+Number(value.split(':')[1].replace(' ',''));
  return minutes;
}

(code working as a function) will return 754 (60*12+34)

usage in your code : var horas = toMinutes(row[4]);

function toMinutes(value){
  var duration = new Date(value);
  var min = duration.getMinutes()+60*duration.getHours();
  return min;
}

You can eventually improve this function with a few error trapping features to handle cases where cell is empty of malformed... Since I don't know what the data look like I prefer let you do it yourself.

Thanks for your reply. In addition to that, I had to be careful with the time zones. For example, one of the spreadsheets has the value "0:45:00", but when I inspect the corresponding value at row[4], I get "Sat Dec 30 1899 02:01:48 GMT-0300 (UYT)". toMinutes returns 121. On the other hand, if I change the cell format in the spreadsheet to Date time, it shows 12/30/1899 0:45:00. Clearly the issue here is that new Date(row[4]) returns a Date in uruguaian time, since I'm from Uruguay. — javag87, Sep 25 '14 at 20:55
That will be more tricky indeed... maybe forcing it to string and using the parsing function I suggest would be more reliable or, at least, much simpler! - btw, thx for accepting. see also this post , it could be helpful if you try using date methods :http://stackoverflow.com/questions/17715841/gas-how-to-read-the-correct-time-values-form-google-spreadsheet/17727300#17727300 — Serge insas, Sep 25 '14 at 20:59

Handling time duration in Google Sheets Api

1 Answers1