Google Apps Script date format issue (Utilities.formatDate)

Question

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

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]);

};

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".

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".

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

Removed that part, cheers

sactyr
– sactyr

2015-06-02 23:34:50 +00:00
Commented Jun 2, 2015 at 23:34 — sactyr
– sactyr, Commented Jun 2, 2015 at 23:34

Mogsdad · Accepted Answer · 2015-04-13 20:15:19Z

15

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.

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.

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.

Date formats follow the SimpleDateFormat specification.
```
// Cell A1 contains a date
var cell = SpreadsheetApp.getActiveSheet().getRange("A1");
cell.setNumberFormat('yyyy-mm-dd');
```

answered Apr 13, 2015 at 20:15

Mogsdad

46k21 gold badges167 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sactyr Over a year ago

Thank you and apologies for the delay in getting back to the forum. I would prefer the second solution but unfortunately it doesn't work, I am still getting a new row with the date in the form of 'code "M/dd/yyyy"'. I have edited the OP with the updated code.

Mogsdad Over a year ago

You didn't change your code according to the explanation and advice. When you call appendRow, the new ddTa gets interpreted as a date. You need to adjust the format AFTER the row is added.

sactyr Over a year ago

Yea I didn't understand when to insert the code - but all good now, I inserted the code after appenRow with getRange("A9:A") so that it formats column A starting from cell A9, and it seems to do the trick. Thank you

thor · Accepted Answer · 2016-04-07 00:24:36Z

0

Solution :

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

edited Apr 7, 2016 at 0:24

thor

22.7k33 gold badges108 silver badges201 bronze badges

answered Apr 7, 2016 at 0:04

Cory Kapty

91 bronze badge

Collectives™ on Stack Overflow

Google Apps Script date format issue (Utilities.formatDate)

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related