CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Artem
Artem

Reputation: 2500

How to get display/text value of a cell?

I have a duration column, to which I programmatically insert values like 12:16 meaning 12 hours 16 minutes. The values are displayed as 12:16, but when I click on it the actual value is 12:16:00.

Now, when I try to get the values(I need to sum all durations) using the following code 

let values = statsSheet.getDataRange().getValues();
for(let rowIndex = 0; rowIndex < values.length; rowIndex++) {
  let duration = values[rowIndex];
}

I'm getting the Date object, which is Sat Dec 30 1899 11:58:20 GMT+0144 for a column with display value 12:16. How can I get text/display value of such cells instead of sheets automatically convert it to date?

Upvotes: 1

Views: 1204

Answers (1)

Cooper
Cooper

Reputation: 64042

This is the way I do it.

function sumOfDurations() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rg=sh.getRange(1,2,4);//four values in column B
  rg.setNumberFormat("#,##0.00");//number format
  const vA=rg.getValues();
  rg.setNumberFormat("[h]:mm:ss");//duration format
  var td=0;
  vA.forEach(function(r){
    td+=Number(r[0]);//sum each value as a number
  });
  sh.getRange('B5').setValue(td).setNumberFormat("[h]:mm:ss");//load final value and set duration format
  //var end="is near";
}

Image:

enter image description here

Upvotes: 2

Related Questions