How can I turn a UNIX timestamp into a Google Sheets date value?

Question

I'm using Google Apps Script to retrieve some data through an API. The date that is given to me is a standard ISO 8601 string, and I need to convert that to a date number that will be understood by Google Sheets when I display it (i.e. as the Google Sheets / Excel standard date value)

Currently I'm using a workaround which is passing a string in my cells then converting it through the DATEVALUE() function but it is not optimal, since I cant use that function natively in my script (from my research on the topic).

I want to create a function directly in Google Apps script that converts the Javascript ol' Date() object into a readable value for my spreadsheet.

I've made a few attempts by converting the seconds since 1970 to the number of days since 1900 but I always have a two-hour gap that I can't explain. I suspect it has to do with the fact that I am in UTC+2, but can't really solve it, and adding two hours sounds like a bad fix.

The baseline of what I've gotten to so far is this :

function toDateNum(string) {

  //Parsing to Unix Timestamp
  date = new Date(string).getTime() / 1000

  //Here I need convert to GSheets date value, add the number of days between 1900-01-01 and 1970-01-01


  return date ;

}

I'm looking either for an arithmetic solve for this (converting and adding dates) or for a function integrated in the Google Sheets app that would allow me to work with this. Any suggestions ?

Thanks a lot !

EDIT (with additional clarification)

One of my issues is that the toISOString format returned is not supported by google sheets directly. It will only be read as a string, not as a date value. Currently, I'm working around this by sending the toISOString value in the cells and reformat using DateValue() directly in the table. I'm trying to do that natively in the script.

To the best of my knowledge, the date value supported by Google Sheets (and Excel) is the number of days since 1900-01-01. A simple arithmetic solution should be found (computing a unix timestamp from seconds then to days then add the difference between 1900-01-01 to 1970-01-01) but, then again, I get my two-hour gap that messes everything up.

Answer 1

In the question linked below, the response by user79865 explains how to manage dates and their formats within sheets. The Sheets API documentation also specifies all the different formats you can use as well as how to create your own when working with dates and times. The Utilities.formatDate() will help you change the date format to whatever you need.

Question URL: https://webapps.stackexchange.com/questions/88621/basic-date-manipulation-in-google-script?newreg=7c66fdcf156f4ff5a30eb5fa4153b243

Sheets Documentation URL: https://developers.google.com/sheets/api/guides/formats

Answer 2

You can achieve this by using new Date(string*1000), then passing the result to Utilities.formatDate(). Multiplying the Unix timestamp by 1000 means your argument is in milliseconds, rather than seconds. This Stackoverflow Question has a lot of good info for formatting dates using js.

function toDateNum(string) {
  //convert unix timestamp to milliseconds rather than seconds
  var d = new Date(string*1000);

  //get timezone of spreadsheet
  var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

  //format date to readable format
  var date = Utilities.formatDate(d, tz, 'dd-MM-yyyy hh:mm:ss a');

  return date;
}

This script will use the timezone setting of your spreadsheet, but you can change this to use the time zone of the script or enter one yourself, examples below if you'd like to use these instead:

//use spreadsheet timezone setting
var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

//use script timezone setting
var tz = Session.getScriptTimeZone();

//enter your own time zone
var tz = 'GMT+0';