Google apps script getting wrong duration values

1
votes

friends! In my sheet I have a column with duration format.

http://dl1.joxi.net/drive/0007/2131/485459/150902/fc427ebb50.jpg

I'm trying to get values of this column with this function:

function getSheetValues(startRow, startCmn, maxColumn) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheets()[0];
var range = mainSheet.getRange(startRow, startCmn, mainSheet.getMaxRows(),   maxColumn);
return range.getValues();
}

var mainSheetData = getSheetByNameValues(2, 1, 9);
var test =  mainSheetData[2][6];
Logger.log(test);

As the result I get wrong values. Example: for third row (00:23:00.000) I get this: Sat Dec 30 02:13:47 GMT+05:53 1899. Probably due to date auto formatting. How can I avoid this? is there any ways to get value (00:23:00.000) as planar text, without changing format of the column itself?

Hope for your help, friends.

2
javascriptgoogle-apps-scriptgoogle-sheets
I think this would be helpful: stackoverflow.com/questions/17715841/… – jeremyasnyder
Upvote. Can't find a way to work with Google Script duration type field. It's niether timestamp nor Date. Does someone know what is it? – NikitOn

2 Answers

0
votes

Assuming you have full control over the spreadsheet...If you truly just want the string representation of '00:20:00.000' the easiest thing to do is to set the format of that column in the actual spreadsheet itself, to be plain text, i.e.

  1. Select the column
  2. Format -> Number -> Plain Text

Then it won't be converted to a date and you should get the raw string value you're after.

If you can't control (or otherwise guarantee) what the format of that column is, you're going to have to end up doing something like what jeremy has suggested in his comment.

0
votes

You could also do it all programatically, copying the range to another cell, set numberFormat to text, then delete this copied range (or leave it):

function go(){
  var ss = SpreadsheetApp.getActiveSheet(), rang1 = ss.getRange('A11'), rang2 = ss.getRange('A12');

  rang1.copyTo(rang2);
  Logger.log(rang2.setNumberFormat('@STRING@').getValue());
  rang2.clear();
};

This of course can be done to entire ranges at once.