0

I have a value 0.000347222222222222 in a cell, which when formatted as time, looks like 30/12/1899 00:00:30 .

How to get it's numerical value with Google Apps Script?

sheet.getRange('A1').getValue() returns Fri Dec 29 14:09:51 GMT+00:09 1899 .

0

The answer is to do with formatting. When you get the value from Cell A1, it will carry the format with it. So if A1 is formatted as "date", "time" or "date time", then so will the cell that you copy it into. On the other hand, if you format cell A1 as "Scientific", then the format of the target cell will also be "Scientific".

Unfortunately the term "Scientific" isn't recognised as a number format, so a creative work around is required.

In order to do this programmatically, try this:


function myFunction2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // setup cell A1
    // value = 0.000347222222222222
    // formatting is irrelvant
  var rangea1 = sheet.getRange('A1');
  var valuea1 = rangea1.getValue();
  
  //setup target cell = Cell C1
  var rangec1 = sheet.getRange('C1');
  // assign content of A1 to C1
  rangec1.setValue(valuea1);

  // format the target cell as scientific 
  rangec1.setNumberFormat('0.###############')
  
  return;
}   

setNumberFormat - Doc Ref. Number formatting is described in the API doc ref

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.