formatDate（）给出正确的日期-1天（Google Apps脚本） [英] formatDate() gives correct date -1 day (Google Apps Script)

问题描述

我使用Google Spreadsheet每天记录一些事情。为了方便用户使用我的同事，我将电子表格尽可能地设计为界面，基本上它就像一个表格。

这个表格有一个提交按钮，用于保存工作表并创建一个新工作表（模板副本）。

问题是保存的工作表应保存为细胞。 但是它保存了实际日期前一天的日期......（！）我试图找出原因。

以下是我按下提交按钮时调用的Google Apps脚本中的代码：

  function renameSheet（）{
 var ShootName = SpreadsheetApp.getActiveSheet（）.getRange（G8）。getValue（）; 
 var DateName1 = SpreadsheetApp.getActiveSheet（）.getRange（A8）。getValue（）; 
 var newdate = new Date（SpreadsheetApp.getActiveSheet（）.getRange（A8）。getValue（））; 
 var Datename2 = Utilities.formatDate（newdate，PST，yyyy-MM-dd）; 
 var NewName = Datename2 + - + ShootName; 
 SpreadsheetApp.getActiveSpreadsheet（）。renameActiveSheet（NewName）; 
 var oldSheet = ss.getActiveSheet（）; 
 //创建模板图的副本
 ss.setActiveSheet（ss.getSheetByName（Original0））; 
 var newSheet = ss.duplicateActiveSheet（）; 
 newSheet.activate（）; 
 ss.moveActiveSheet（1）; 
 newSheet.setName（NewLog）; 
 
 
 
 
 
 
 $ b如果单元格A8的值为12.25.16 - 该表将被命名为12.24.16。
 
 
 
如果有人有适当的甚至是脏的quickfix，我很乐意听到它。
解决方案
您正在对这一行的时区进行硬编码：     
 code> var Datename2 = Utilities.formatDate（newdate，PST，yyyy-MM-dd）; 
  

但这并没有考虑到夏令时和电子表格中的日期值始终为00： 00:00，所以一小时的班次可以改变日期......

替换为这样的自动化值：

  var Datename2 = Utilities.formatDate（newdate，Session.getScriptTimeZone（），yyyy-MM-dd）; 
  

I'm using a Google Spreadsheet to log some things on a day-to-day basis. To make it user-friendly to my colleagues I've made the spreadsheet as "interface-ish" as possible, basically it resembles a form.

This "form" has a submit button that saves the sheet and creates a new sheet (copy of template).

The problem is that the sheet that is saved should be saved with the date from a cell. BUT it saves with the date one day before the actual date... (!) I'm going nuts trying to figure out why.

Here's the code from the Google Apps Script I'm calling when the submit button is pressed:

function renameSheet() {
var ShootName = SpreadsheetApp.getActiveSheet( ).getRange("G8").getValue();
var DateName1 = SpreadsheetApp.getActiveSheet( ).getRange("A8").getValue();
var newdate = new Date(SpreadsheetApp.getActiveSheet( ).getRange("A8").getValue());
var Datename2 = Utilities.formatDate(newdate, "PST", "yyyy-MM-dd");
var NewName = Datename2 + " - " + ShootName;
SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(NewName);
var oldSheet = ss.getActiveSheet();
// create a duplicate of the template sheet
ss.setActiveSheet(ss.getSheetByName("Original0"));
var newSheet = ss.duplicateActiveSheet();
newSheet.activate();
ss.moveActiveSheet(1);
newSheet.setName("NewLog");

}

If cell A8 has the value "12.25.16" - the sheet will be named "12.24.16".

If anyone has a proper or even a dirty quickfix to this, I'd love to hear it.

解决方案

You are hardcoding the timezone in this line :

var Datename2 = Utilities.formatDate(newdate, "PST", "yyyy-MM-dd");

but this does not take into account the daylight saving and date only values in spreadsheets are always at 00:00:00 hours so one hour shift can change the date...

Replace with an automated value like this :

var Datename2 = Utilities.formatDate(newdate, Session.getScriptTimeZone(), "yyyy-MM-dd");

这篇关于formatDate（）给出正确的日期-1天（Google Apps脚本）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！