Date format in table when running mailapp script

Zukhairah Binte Zahari

unread,

Jan 19, 2023, 5:31:02 AM1/19/23

to Google Apps Script Community

Hi all,

I have a question whereby I need help with the emailapp script.

I have a google sheet table, in which i have created an email script to send out the google sheet table to various emails.

In this google sheet, some cells are formatted as date (dd-MMM-yyyy) format.

However, whenever I run the script, the date format shown in the email will include the time i.e. 00:00:00 format.

I do not want this, i want the date format in the email to be the just the date (dd-MMM-yyy)

Is there way to change the date format? Below screenshot of the email sent. Screenshot 2023-01-19 6.23.56 PM.png

function sendEmail() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var formattedDate = Utilities.formatDate(new Date(), "GMT", "dd-MMM-YYY")

const subject = `Daily RCCA update ${formattedDate} - SAFETY`;

const to = ' email ';

const cc = 'email,';

const numberOfColumns = 10

const lastRow = sheet.getLastRow();

const data = sheet.getRange(1, 1, lastRow, numberOfColumns).getValues();

let htmlBody = `<html>

<body>

Good Afternoon,

<p>Please find the status update for IRCA/RCCA for Safety : </p>

<tr>`;

for (let row = 0; row < data.length; row++) {

for (let col = 0; col < data[0].length; col++) {

if (row === 0) {

htmlBody += `<th style="color: #fff; background: #000">${data[row][col]}</th>`;

} else {

htmlBody += `<td>${data[row][col]}</td>`;

}

htmlBody += `</tr>

<tr>`;

}

htmlBody += `</tr></table></body></html>`;

MailApp.sendEmail({

to,

cc,

subject,

htmlBody,

});

}

laurie.nason

unread,

Jan 19, 2023, 5:33:24 AM1/19/23

to google-apps-sc...@googlegroups.com

The best way I have found to do this is to format the cell in the sheet how you want it to appear, then instead of using getValues() - use getDisplayValues()

------ Original Message ------

From "'Zukhairah Binte Zahari' via Google Apps Script Community" <google-apps-sc...@googlegroups.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 1/19/2023 1:31:02 PM

Subject [Apps-Script] Date format in table when running mailapp script

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/fe8ce989-a76c-4b15-b1b7-4ba51b314d69n%40googlegroups.com.

Zukhairah Binte Zahari

unread,

Jan 19, 2023, 10:29:14 AM1/19/23

to Google Apps Script Community

Perfect, it works now!

Thank you!

I do have another question: i have already indicated <td>${data[row][col]}</td>, how do i centre in the data in the table?

Reply all

Reply to author

Forward