title: How can I turn a UNIX timestamp into a Google Sheets date value?
body: <p>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)</p>
  
  <p>Currently I'm using a workaround which is passing a string in my cells then converting it through the <code>DATEVALUE()</code> function but it is not optimal, since I cant use that function natively in my script (from my research on the topic).</p>
  
  <p>I want to create a function directly in Google Apps script that converts the Javascript ol' <code>Date()</code> object into a readable value for my spreadsheet. </p>
  
  <p>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. </p>
  
  <p>The baseline of what I've gotten to so far is this :</p>
  
  <pre><code>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 ;
  
  }
  </code></pre>
  
  <p>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 ? </p>
  
  <p>Thanks a lot !</p>
  
  <p>EDIT (with additional clarification)</p>
  
  <p>One of my issues is that the <code>toISOString</code> 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 <code>toISOString</code> value in the cells and reformat using DateValue() directly in the table. I'm trying to do that natively in the script.</p>
  
  <p>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.</p>