CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-apigoogle-sheets
javag87
javag87

Reputation: 61

Handling time duration in Google Sheets Api

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?

Upvotes: 1

Views: 564

Answers (1)

Serge insas
Serge insas

Reputation: 46792

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.

Upvotes: 1

Related Questions