CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
lululola58
lululola58

Reputation: 45

Format Date in AppScript - Google Sheets to Google Doc conversion

I have a Google Sheet that collects Google Form responses. Two of the questions collect dates. I'm running a script that collects the information from the form responses sheet and generates a Google Doc. I've formatted the date various ways, but when it gets pulled into the Google Doc, it reverts back to Tue Apr 27 2021 05:00:00 GMT+0100 (British Summer Time). I want it just to say 'Month Day, Year' when pulled into the Google Doc.

I've been able to format the cells with the dates using, but this formatting is only reflected in the Google Sheet:

datasheet.getRange(row + 1, 14).setNumberFormat("mmmm d, yyyy")

I've attempted creating a variable containing the date and passing it through formateDate(), but I get an error saying: Exception: The parameters (number[], String, String) don't match the method signature for Utilities.formatDate.

var timeZone = Session.getScriptTimeZone()
var arrivalDate = datasheet.getRange(row + 1, 13).getValues()
var formatedArrivalDate = Utilities.formatDate(arrivalDate, timeZone, 'mmmm d, yyyy')
datasheet.getRange(row + 1, 13).setValue(formattedArrivalDate)

And the correct parameters for Utilities.formateDate() are: Date, String, String

The 'merge' portion of the code reads:

for (var col = 3; col < 100; ++col) {
    body.replace('{_' + data[0][col] + '_}', data[row][col]
}

(The data is defined previously and is not where my issue lies.)

Upvotes: 1

Views: 296

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Issue:

You are providing a two-dimensional array as the first parameter in formatDate; you should provide a Date instead.

Explanation and solution:

getValues() returns a 2D array. If you want to retrieve the value of a single cell, you should use getValue() instead:

var arrivalDate = datasheet.getRange(row + 1, 13).getValue();

Other issue:

In SimpleDateFormat, Month is identified by capital M, not m (which refers to minutes).

Therefore, you should format the date like this instead:

var formatedArrivalDate = Utilities.formatDate(arrivalDate, timeZone, 'MMMM d, yyyy')

Upvotes: 2

Related Questions