如何从工作表单元格中获取日期格式字符串? [英] How to get a date format string from a sheet cell?
问题描述
在谷歌脚本函数中,我需要从谷歌表中获取日期值,我已经做了这样的事情
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;
我得到的是这样的
msg = "date = 42323"
如何格式化日期变量以显示为日期
How do I format the date variable to appear as a date
谢谢
推荐答案
正如 jvdh 所说,电子表格中的日期应该自动转换为 Google Apps 脚本中的 JavaScript 日期.到目前为止,我们还听说了您的情况.
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.
在 2013 年的相关问题中,AdamL 出色地解释了日期的表示方式.他还暗示了一种将电子表格中的数字序列号"(或纪元")日期值转换为 JavaScript 中使用的 Unix 样式值的方法.
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;
该实用程序可以处理 JavaScript 的 dateString.
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;
}
这篇关于如何从工作表单元格中获取日期格式字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!