Google Scripts Timestamp - Format Output on Email

Question

I'm not sure what I'm doing wrong here, and this is probably really simple... After scouring the net and trying hundreds of different examples found, I've come up empty handed on getting the format needed. My script works, sends the email, all values are there, but the date formats are not what we are after. So, here is what's up:

I've got a basic script that sends an HTML email from a template when a respondent submits a form. The timestamp, start (date and time), end (time only) value is needed, and is printed in the HTML email, but it's showing a full-blown timestamp output such as: "Tue Nov 03 2020 11:39:28 GMT-0700 (Mountain Standard Time)"

What I am trying to do is format the timestamp value shown in the email to this: "Tue Nov 03 2020 HH:mm"

Here is the script I am using:

function onFormSubmit(e) {
  var htmlBody = HtmlService.createTemplateFromFile('email');  
  var rng = SpreadsheetApp.getActiveSheet().getActiveRange();
  var timestamp = Utilities.formatDate(new Date(), "MST" , "MM-dd-yyyy | HH:mm:ss");
  var email = rng.getValues()[0];
  var body = HtmlService.createTemplateFromFile("email");
  var to = 'foo@bar';
  var subject = 'Activity Report ' + email[0] + '';
  
  htmlBody.timestamp = email[0];
  htmlBody.start = email[1];
  htmlBody.end = email[2];
  htmlBody.activityobserved = email[3];
  htmlBody.summary = email[4];
  htmlBody.actiontaken = email[5];
  htmlBody.attachments = email[6];
  
  var email_html = htmlBody.evaluate().getContent();
  
  MailApp.sendEmail({
    to: to,
    subject: subject,
    htmlBody: email_html,
    replyTo:'bar@foo',
  });
}

You're successfully saving a formatted date in timestamp, but you're not using it anywhere. — Diego
– Diego, Commented Nov 3, 2020 at 19:27
Well. I knew I was missing something! so I changed htmlBody.timestamp = email[0]; to htmlBody.timestamp = timestamp; and this seems to work great. — John Spartan
– John Spartan, Commented Nov 3, 2020 at 19:35
Im a little confused on how to format the start and end times though. They are still displaying the full output — John Spartan
– John Spartan, Commented Nov 3, 2020 at 19:38
You can just repeat the Utilities.formatDate() call when setting htmlBody.start and htmlBody.end. — Diego
– Diego, Commented Nov 3, 2020 at 19:40

Diego · Accepted Answer · 2020-11-03 19:37:56Z

3

Don't forget to actually use timestamp. To get the "Tue" part in your date, you can use "EEE". I set the date formatting below as you specified in your question.

function onFormSubmit(e) {
  // ...
  var timestamp = Utilities.formatDate(new Date(), "MST" , "EEE MMM dd yyyy HH:mm");
  // ...
  htmlBody.timestamp = timestamp;
  htmlBody.start = Utilities.formatDate(email[1], "MST" , "EEE MMM dd yyyy HH:mm");
  htmlBody.end = Utilities.formatDate(email[2], "MST" , "EEE MMM dd yyyy HH:mm");
  // ...
}

answered Nov 3, 2020 at 19:37

Diego

9,5812 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diego Over a year ago

@Marios Type less haha... you're probably more correct to use new Date(email[n]) though. I was just assuming his use of getValues() would return a valid date.

Marios · Accepted Answer · 2020-11-03 19:42:48Z

3

Solution:

You don't include variable timestamp in the htmlBody object. Instead you are using the original source value of it.

Replace:

htmlBody.timestamp = email[0];

with:

htmlBody.timestamp = timestamp;

Update based on your comment:

Im a little confused on how to format the start and end times though. They are still displaying the full output.

Assuming that you have date objects in your sheet,

Replace:

 htmlBody.start = email[1];
 htmlBody.end = email[2];

with

htmlBody.start = Utilities.formatDate(new Date(email[1]), "MST" , "EEE MMM dd yyyy HH:mm");
htmlBody.end = Utilities.formatDate(new Date(email[2]), "MST" , "EEE MMM dd yyyy HH:mm");

edited Nov 3, 2020 at 19:42

answered Nov 3, 2020 at 19:36

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Collectives™ on Stack Overflow

Google Scripts Timestamp - Format Output on Email

2 Answers 2

1 Comment

Solution:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Solution:

Comments

Your Answer

Sign up or log in

Post as a guest

Related