How to get a numerical value of a date with Google Apps Script?

Question

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 .

Answer 1

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