Google Apps Script date format issue (Utilities.formatDate)

by

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.

  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

  2. 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');

More Related Contents:

  1. Format a Google Sheets cell in plaintext via Apps Script
  2. Difference between two dates expressed as years, months, days (in one column)
  3. How to compare dates or date against today with query on google sheets?
  4. Compare two dates Google apps script
  5. Get current date in personalized format in c#
  6. How can I parse / create a date time stamp formatted with fractional seconds UTC timezone (ISO 8601, RFC 3339) in Swift?
  7. Convert column index into corresponding column letter
  8. Create Google Calendar Events from Spreadsheet but prevent duplicates
  9. Zero-based month numbering [closed]
  10. Google Spreadsheet Script – How to Transpose / Rotate Multi-dimensional Array?
  11. Is it always a good idea to store time in UTC or is this the case where storing in local time is better?
  12. How do I add formulas to Google Sheets using Google Apps Script?
  13. Deleting rows in google sheets using Google Apps Script
  14. Form and file upload with htmlService and app script not working
  15. Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank
  16. Send active sheet as PDF to email listed in cell
  17. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  18. Custom function throws a “You do not have the permission required to setValue” error
  19. Margins parameters for Google spreadsheet export as PDF
  20. How to fix ‘TypeError: Cannot call method “getRange” of null’ in Google Apps Script
  21. Script to convert .XLSX to Google Sheet and move converted file
  22. Hidden field in a Google Form
  23. Format date from database?
  24. Java Date Format for Locale
  25. Google Script – convert sheet to XLSX
  26. Use script in all spreadsheets
  27. Same date in different time zone
  28. Converting to date in PHP from yyyymmdd format
  29. Convert decimal day to HH:MM
  30. Custom Functions and Recalculation

Leave a Comment