formatDate() gives correct date -1 day (Google Apps Script)

Question

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.

Serge insas Serge insas · Accepted Answer · 2016-02-18T17:57:39

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() gives correct date -1 day (Google Apps Script)

1 Answers