Changing date format in script

[Amber Marchant]

I have the following script which is generating an email based on a trigger in Google Sheets. It's working fine but the date format in the email is not ideal. I would love some help on how to fix this.  The date fields appear like this -  Deposit Date: Sat Feb 25 2023 07:00:00 GMT+0000 (GMT).  But I'd really like it to look like this MM/DD/YYYY.

Thank you

(Newbie)

function sendMailEdit(e){

 if (e.range.columnStart != 5 || e.value != "Yes - Send") return;

 const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues();

 let date = rData[0][2];

 let email = rData[0][0];

 let team = rData[0][1];

 let amount = rData[0][3];

let msg = "This is an automated message from XXX Accounting. We are writing to inform you of a recent deposit received for " + team + ". \n\nDeposit Amount:$ " + amount + " \n\nDeposit Date: " + date + " \n\nCustomer Reference Number: " + date + " \n\nDeposit Date: " + date + " \n\n\nIf you have not already submitted the CDA deposit, please send it not to XXXX. \n\nThank You - Your XXX Accounting Team"

 

 MailApp.sendEmail(email, "XXX Deposit Notification", msg)

}

[Scott Bennett]

Look up Utilities.formatDate()

Scott Bennett

Sent from my iPhone 

On Feb 25, 2023, at 8:19 PM, Amber Marchant <amber.m...@place.com> wrote:

I have the following script which is generating an email based on a trigger in Google Sheets. It's working fine but the date format in the email is not ideal. I would love some help on how to fix this.  The date fields appear like this -  Deposit Date: Sat Feb 25 2023 07:00:00 GMT+0000 (GMT).  But I'd really like it to look like this MM/DD/YYYY.

--
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/a6843b92-9344-4e81-9257-7a4dc28d345en%40googlegroups.com.

[laurie.nason]

Alternatively, look at using getDisplayValues() rather than getValues() - this returns the values displayed in the cells which if your date cell is formatted as you talked about will return that.

Laurie

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

From "'Scott Bennett' via Google Apps Script Community" <google-apps-sc...@googlegroups.com>

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

Date 2/26/2023 5:46:08 AM

Subject Re: [Apps-Script] Changing date format in script

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CB7537DC-CDF9-40FD-88BE-FA7C663AD633%40bbchs.org.

[DME]

hi 

let msg = "This is an automated message from XXX Accounting. We are writing to inform you of a recent deposit received for " + team + ". \n\nDeposit Amount:$ " + amount + " \n\nDeposit Date: " + date + " \n\nCustomer Reference Number: " + date + " \n\n\nIf you have not already submitted the CDA deposit, please send it not to XXXX. \n\nThank You - Your XXX Accounting Team"

This code replaces the original msg string with the updated version that includes the formatted date string. Now, the date will appear in the format MM/DD/YYYY in the email message.

I hope this helps! Let me know if you have any further questions.