CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsdate-formatting
Miyer
Miyer

Reputation: 365

Need To Change The Date Format

I need to change the date format in the A Column, where I need to get something like JULY 21 2020 but what I'm getting on the email body is something like this DATE - Thu Jul 23 2020 00:00:00 GMT+0530 (India Standard Time)

Can you help me to change date format in the below script

function sendEmail(e) {
    var thisSheet = e.source.getActiveSheet();
    if (thisSheet.getName() !== 'ARTWORK' || e.range.columnStart !== 5 || e.range.rowStart == 1 || e.value !== 'NO-Materials') return;
    var body, headers = thisSheet.getRange(1, 1, 1, 3).getValues()[0],
        thisRow = thisSheet.getRange(e.range.rowStart, 1, 2, 3).getValues()[0],
        ref = thisRow[1]


    recipients = "[email protected]",
        subject = "⚫ Art Work No-Images ► " + ref,
        body = "Require Vehicle images for Social Media Campaign\n\n",
        i = 0;
    while (i < 3) {
        body += headers[i] + ' - ' + thisRow[i] + '\n';
        i++;
    }

    MailApp.sendEmail(recipients, subject, body, { name: "AutoDirect" });
}

enter image description here

Upvotes: 1

Views: 134

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Applying getValues() on cells with dates will return JavaScript Date objects. That's the behaviour you're getting. To get the dates formatted as you want, you have two options, depending on whether you want to get the displayed format or apply different one:

Use getDisplayValues:

The method getDisplayValues() returns the values with the exact same format as in the spreadsheet. Use this instead of getValues() if you want to retrieve the dates with the same format as they are displayed:

Returns a two-dimensional array of displayed values, indexed by row, then by column. The values are String objects. The displayed value takes into account date, time and currency formatting, including formats applied automatically by the spreadsheet's locale setting.

Use formatDate:

If you want to apply a format that is different than the one displayed on the spreadsheet, you can keep using getValues() and then format the returned date values with Utilities.formatDate(date, timeZone, format) according to this specification. For example:

Utilities.formatDate(new Date(), Session.getScriptTimezone(), "MMMM d yyyy");

Upvotes: 1

Related Questions