Google Apps Script: Reading Duration values is off by X minutes

Question

I'm working with duration fields on Google Spreadsheet on my timezone

Here is an example of the data I'm working with and my Spreadsheet configuration

Sample data:

actividadID	destinoID	atractivoID	actividadDescripc	actividadDuracion	actividadTipo
da839da6	ae4f25ff	'46432440	Visita	1:00:00 a. m.	Específica

Script Objective

The idea is to loop through a subset of these fields so for that I'm grabbing all the values of the spreadsheet and filtering them:

function TourDuration(tourID, mySS) {
  const touractivData = mySS.getSheetByName("TourActividad").getDataRange().getValues();
  const activData = mySS.getSheetByName("Actividad").getDataRange().getValues();

  var current_activList = touractivData.filter(function(item){
    return item[1] == tourID; //Match Bot Numbers && Unprocessed trades
  });
  Logger.log("current_activList:");
  Logger.log(current_activList);

When I check the result of the data I'm grabbing I see data that is off, going into negative not only by hours but also by minutes:

[da839da6, ae4f25ff, 46432440, Visita a la Plaza Mayor de Lima, Sat Dec 30 01:08:36 GMT-05:00 1899, Específica]

(This one should be the equivalent to 1 hour, but instead I got a negative time)

My goal is to add all the durations that match my filter into a consolidated one without success.

Here is the full code at the moment:

function TourDuration(tourID, mySS) {
  const touractivData = mySS.getSheetByName("TourActividad").getDataRange().getValues();
  const activData = mySS.getSheetByName("Actividad").getDataRange().getValues();

  var current_activList = touractivData.filter(function(item){
    return item[1] == tourID; //Match Bot Numbers && Unprocessed trades
  });

  var current_activList_length = current_activList.length;
  var total_duration = 0;

  for(var i = 0; i < current_activList_length; i++){

    var activDuration = activData.filter(function(item){
      return item[0] == current_activList[i][2]; //Match Bot Numbers && Unprocessed trades
    });

    var duration = Utilities.formatDate(new Date(activDuration[0][4]), "GTM-5", "dd/MM/yyyy HH:mm");
    var dur2 = new Date(activDuration[0][4]).toString().substr(25,6)+":00";
    dateString = Utilities.formatDate(activDuration[0][4], dur2, "MM/dd/yyyy HH:mm:ss");
    Logger.log(duration)
    Logger.log(dur2)
    Logger.log(dateString)
        
    total_duration = total_duration;
  }
  
  
}

And this is the result of the log for a register with a 45min duration:

Could it be that the App Script interface has a different timezone? Although the minutes difference is hard to explain since usually the differences are in full hours.

Also, I don't plan on using getDisplayValue() since I need to consult the whole sheet and getDataRange() would be more efficient.

Answer 1

Explanation:

Also, I don't plan on using getDisplayValue() since I need to consult the whole sheet and getDataRange() would be more efficient.

getDisplayValues is used instead of getValues() and its purpose is to get the displayed value as it is shown in the sheet.

Please refer to this post for more details:

• Difference between getValue() and getDisplayValue() on google app script

---

On the other hand, getDataRange() is not a sophisticated function.

This expression:

const activSheet = mySS.getSheetByName("Actividad");
const activData = activSheet.getDataRange().getValues();

is identical to this:

const activSheet = mySS.getSheetByName("Actividad");
const activData = activSheet.getRange(1,1,activSheet.getLastRow(),
                  activSheet.getLastColumn()).getValues();

Towards the solution:

By using getDisplayValues you will be able to get the value as it is displayed in your sheet:

function TourDuration() {
  const mySS = SpreadsheetApp.getActive();
  const activSheet = mySS.getSheetByName("Actividad");
  const activData = activSheet.getDataRange().getDisplayValues();
  console.log(activData[1][4]); // 1:00:00
}

However, you can't take the full date from this expression because the date is not specified in the cell. If you expand the change the format of your cells in column E to date:

you will see that you are getting exactly the info that is stored in the cell. So the code works as expected. The issue has to do with the missing information in the sheet itself.

You need to store the correct date in your sheet:

and then if you change the format back to time, the date will still be correct and your current script will be able to get it properly.