分享程序员开发的那些事...
更新时间：2023-11-15 16:09:04
由于jvdh表示，电子表格中的日期应自动转换为Google Apps脚本中的JavaScript日期。我们目前听到的情况还有更多。
在相关来自2013年的问题，AdamL在解释日期如何表现方面做得很出色。他还暗示将电子表格中的数字序列号（或Epoch）日期值转换为JavaScript中使用的Unix样式值。
这是我的版本的实用程序。要使用，只需传入从电子表格中读取的值即可： code> var csh = SpreadsheetApp.getActiveSheet（）;
var date = convert2jsDate（csh.getRange（'A1'）。getValue（））;
var msg =date =+ date;
该实用程序可以处理现有日期，序列号或JavaScript支持的字符串格式 dateString 。
/ **
*将任何电子表格值转换为日期。
*假设数字正在使用Epoch（1900年1月1日以来的天数，例如Excel，表格）。
*
* @param {object} value（可选）单元格值;日期，数字或日期字符串
*将被转换为JavaScript日期。如果缺少或
*为未知类型，则将被视为今天。
*
* @return {date}输入值的JavaScript日期对象表示。
* /
函数convert2jsDate（value）{
var jsDate = new Date（）; //默认为现在
if（value）{
//如果我们被赋予了一个日期对象，那么按原样使用它
if（typeof value ==='date'）{
jsDate = value;
else {
if（typeof value ==='number'）{
//假定这是电子表格序列号日期
var daysSince01Jan1900 = value ;
var daysSince01Jan1970 = daysSince01Jan1900 - 25569 // 25569 =天到Unix时间参考
var msSince01Jan1970 = daysSince01Jan1970 * 24 * 60 * 60 * 1000; //转换为数字unix时间
var timezoneOffsetInMs = jsDate.getTimezoneOffset（）* 60 * 1000;
jsDate = new Date（msSince01Jan1970 + timezoneOffsetInMs）;
}
else if（typeof value ==='string'）{
//希望字符串被格式化为日期字符串
jsDate = new Date（value）;
}
}
}
return jsDate;
}
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
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;
}