Google Apps 脚本日期格式问题 (Utilities.formatDate) [英] Google Apps Script date format issue (Utilities.formatDate)

问题描述

我对 Google Apps 脚本还很陌生，请耐心等待.

I am pretty new to Google Apps Script, so please bear with me.

我正在 Google 表格上从银行收集每日利率，我正在使用以下代码为 A5:F5 中包含的利率附加新行，其中 A 列包含日期.

I am collecting daily interest rates from a bank on Google Sheets, and I am using the following code to append new rows for the rates contained in A5:F5, with column A containing dates.

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Interest Rates");
  var source = sheet.getRange("A5:F5");
  var values = source.getValues();
  values[0][0] = Utilities.formatDate(new Date(), "GMT+10:00", "yyyy-MM-dd");
  sheet.appendRow(values[0]);

};

我的问题是 - 尽管我已将日期格式指定为yyyy-MM-dd"，但我的新行中 A 列中的日期是以M/dd/yyyy"格式创建的.

My issue is this - although I have specified the date format to be "yyyy-MM-dd" the dates in column A in my new rows are created in this format "M/dd/yyyy".

我已经尝试使用 Google 表格中的下拉格式菜单使用yyyy-MM-dd"预先格式化整个 A 列，但是如果我运行上面的代码，我的新行仍然在M/dd/yyyy"中.

I have tried pre-formatting entire column A with "yyyy-MM-dd" using the drop down Format menu in Google Sheets, but if I run the code above my new row is still in "M/dd/yyyy".

就好像我的代码完全忽略了 Utilities.formatDate.仅供参考，我从 此处 获得了上述代码.

It's as if my code ignores Utilities.formatDate completely. FYI I got the above code from here.

推荐答案

Utilities.formatDate() 实用程序格式化 javascript 字符串.但是，当写入电子表格时，新的数据行会被 Google 表格解释以确定数据类型和适当的格式，就像您通过用户界面输入一样.

The Utilities.formatDate() utility formats a javascript String. However, when written out to the spreadsheet, the new row of data is interpreted by Google Sheets to determine data types and appropriate formatting, just as if you'd typed it in through the user interface.

由于您有一个可识别的日期字符串，Google 表格会确定它是一个日期，将其存储为日期，并应用默认日期格式.

Since you've got a recognizable date string, Google Sheets decides it's a Date, stores it as such, and applies the default date format.

您有两个选项可以使电子表格中的日期满足您的格式需求.

You've got two options for making the date in the spreadsheet meet your formatting needs.

1. 强制将其解释为字符串，即使它看起来像日期.

1. Force it to be interpreted as a String, even if it does look like a date.

cell.setValue(Utilities.formatDate(new Date(), "GMT+10:00", "''yyyy-MM-dd"));
//                                                           ^^ force string literal

设置单元格的日期格式.这会将单元格的值保留为日期，这对计算很有用.

Set the date format for the cell. This will leave the value of the cell as a date, which is useful for calculations.

日期格式遵循 SimpleDateFormat 规范.

Date formats follow the SimpleDateFormat specification.

// Cell A1 contains a date
var cell = SpreadsheetApp.getActiveSheet().getRange("A1");
cell.setNumberFormat('yyyy-mm-dd');

这篇关于Google Apps 脚本日期格式问题 (Utilities.formatDate)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！