如何将UNIX时间戳转换为Google Sheets日期值? [英] How can I turn a UNIX timestamp into a Google Sheets date value?
问题描述
我正在使用Google Apps脚本通过API检索一些数据.给我的日期是标准的ISO 8601字符串，我需要将其转换为显示时Google表格可以理解的日期数字(即Google表格/Excel标准日期值)
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)
当前，我正在使用一种变通方法，该方法是在单元格中传递字符串，然后通过DATEVALUE()函数将其转换，但这不是最佳方法，因为我无法在脚本中本地使用该函数(来自我对主题的研究) ).
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).
我想直接在Google Apps脚本中创建一个函数，将Javascript或Date()对象转换为电子表格的可读值.
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.
我做了一些尝试，将1970年以来的秒数转换为1900年以来的天数，但是我总是有两个小时的时间间隔，无法解释.我怀疑这与我在UTC + 2中的事实有关，但是并不能真正解决问题，而增加两个小时的声音听起来像是一个不好的解决方法.
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 ;
}
我正在寻找对此的算术求解(转换和添加日期)，或者正在Google表格应用程序中集成的函数允许我使用此函数.有什么建议 ?
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 ?
非常感谢！
编辑(需要进一步说明)
EDIT (with additional clarification)
我的问题之一是Google表格不直接支持返回的toISOString格式.它将仅作为字符串读取，而不作为日期值读取.当前，我正在通过在单元格中发送toISOString值并直接在表中使用DateValue()重新格式化来解决此问题.我正在尝试在脚本中本地执行此操作.
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.
据我所知，Google表格(和Excel)支持的日期值为自1900-01-01以来的天数.应该找到一个简单的算术解决方案(计算从几秒钟到几天的unix时间戳，然后加上1900-01-01和1970-01-01之间的差)，但是，我又得到了两个小时的时间间隔，这使所有事情搞砸了
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.
推荐答案
在下面链接的问题中，user79865的响应说明了如何在工作表中管理日期及其格式. Sheets API文档还指定了您可以使用的所有不同格式，以及在处理日期和时间时如何创建自己的格式. Utilities.formatDate()将帮助您将日期格式更改为所需的任何格式.
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.
表格文档的网址: https://developers.google.com/sheets/api /guides/formats
这篇关于如何将UNIX时间戳转换为Google Sheets日期值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
