Date format error in Google Apps Script (Utilities.formatDate)

Question

I'm new to Google Apps Script, so please bear with me.

I collect daily interest rates from a bank in Google Sheets, and I use the following code to add new rows for bets 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]); }; 

My problem with this is that although I specified the date format as "yyyy-MM-dd", the dates in column A in my new rows are created in this format "M / dd / yyyy".

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

As if my code completely ignores Utilities.formatDate. FYI I got the above code here .

Answer 1

Utilities.formatDate() formats javascript String. However, when writing to a spreadsheet, a new row of data is interpreted by Google Sheets to determine the data types and the corresponding formatting, as if you entered it through the user interface.

Since you have a recognizable date string, Google Sheets decides its Date, saves it as such, and applies the default date format.

You have two options so that the date in the spreadsheet matches your formatting requirements.

• Makes it be interpreted as a String, even if it looks 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 cell value as a date, which is useful for calculations.

  The date format conforms to the SimpleDateFormat specification.

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

Answer 2

Decision:

 values[0][0] = Utilities.formatDate(new Date(), "GMT+10:00", "yyyy-MM-dd").setNumberFormat('yyyy-mm-dd');