3

In a google script function I need to get a date value from a google sheets I have done something like this

var csh = SpreadsheetApp.getActiveSheet();
var date = csh.getRange('A1').getValue();
var msg = "date = " + date;

What I get is something like

msg = "date = 42323"

How do I format the date variable to appear as a date

thanks

BarbieBear
  • 175
  • 3
  • 10
  • 1
    What is the format string applied to cell A1? (Try `var fmt = csh.getRange('A1').getNumberFormat();`) – Mogsdad Nov 19 '15 at 19:51
  • The format seemed to be the issue. I was assuming normal british date format (dd/mm/yyyy) but it was either not set or mm/dd/yyyy. The cell in question had a date that works in the former but not the latter - 21/12/2015. When I set the cell to the correct format everything worked as expected. – BarbieBear Nov 20 '15 at 20:02

2 Answers2

4

As jvdh said, a date in a spreadsheet should be automatically converted to a JavaScript date in Google Apps Script. There's something more to your situation that we've heard so far.

On a related question from 2013, AdamL did a great job explaining how dates are represented. He also hinted at a way to convert from the numeric "serial number" (or "Epoch") date value in a spreadsheet to the Unix-style values used in JavaScript.

Here is my version of that utility. To use, just pass in the value read from the spreadsheet, like this:

var csh = SpreadsheetApp.getActiveSheet();
var date = convert2jsDate( csh.getRange('A1').getValue() );
var msg = "date = " + date;

The utility can handle existing dates, "serial numbers", or string formats that are supported by JavaScript's dateString.

/**
 * Convert any spreadsheet value to a date.
 * Assumes that numbers are using Epoch (days since 1 Jan 1900, e.g. Excel, Sheets).
 * 
 * @param {object}  value  (optional) Cell value; a date, a number or a date-string 
 *                         will be converted to a JavaScript date. If missing or
 *                         an unknown type, will be treated as "today".
 *
 * @return {date}          JavaScript Date object representation of input value.
 */
function convert2jsDate( value ) {
  var jsDate = new Date();  // default to now
  if (value) {
    // If we were given a date object, use it as-is
    if (typeof value === 'date') {
      jsDate = value;
    }
    else {
      if (typeof value === 'number') {
        // Assume this is spreadsheet "serial number" date
        var daysSince01Jan1900 = value;
        var daysSince01Jan1970 = daysSince01Jan1900 - 25569 // 25569 = days TO Unix Time Reference
        var msSince01Jan1970 = daysSince01Jan1970 * 24 * 60 * 60 * 1000; // Convert to numeric unix time
        var timezoneOffsetInMs = jsDate.getTimezoneOffset() * 60 * 1000;
        jsDate = new Date( msSince01Jan1970 + timezoneOffsetInMs );
      }
      else if (typeof value === 'string') {
        // Hope the string is formatted as a date string
        jsDate = new Date( value );
      }
    }
  }
  return jsDate;
}
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
1

apps script automatically converts Spreadsheet dates to Javascript dates. when I run your code, msg is equal to "date = Wed Nov 11 2015 00:00:00 GMT+0100 (CET)" for me.

If this doesn't work for you for some reasons you might want to try to use new Date(); e.g. date = new Date(date);

jvdh
  • 612
  • 1
  • 6
  • 19
  • strange. I am definitely getting a number. If I use it to initialize a date object ( i.e dateobj = new Date(date); ) I get Thu, 1 Jan 1970 when the date should be 15 Nov 2015. I confirmed that the cell in the spreadsheet is a date.... – BarbieBear Nov 19 '15 at 19:11
  • 1
    can you test that if you double click this cell you get a calendar ? – Serge insas Nov 19 '15 at 20:04
  • I had already found the problem before I read this. It's a shame because it would have helped a lot. I tried using isDate("A1") on the sheet and that was returning true so I though it was ok. But when I reproduced the problem and double clicked - no calendar... – BarbieBear Nov 20 '15 at 20:05