Get dat column in App Script without time zone

user_4202449 0
Viewed 64

I am trying to get the date into App Scripts but eventually, it is getting a date with time and time zone which I really don't want to. here is what I code.

function sendMail(){
var first = 0;
var order = 1;
var date = 2;
var item = 3;
var email = 4;
var pesa = 5;
var aliases = GmailApp.getAliases();

var emailTemp = HtmlService.createTemplateFromFile("email");

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NAJ");

var data = ws.getRange("A2:F" + ws.getLastRow()).getValues();

data.forEach(function(row){
  emailTemp.fn = row[first]
  emailTemp.oid = row[order]
  emailTemp.odate = row[date]
  emailTemp.itm = row[item]
  emailTemp.amt = row[pesa]
  var htmlMessage = emailTemp.evaluate().getContent();
  GmailApp.sendEmail(
    row[email],
    "Your Order Recently Canceled",
    "Your email doesn't support HTML",
    {'from':aliases[4], name: "My company name", htmlBody: htmlMessage}
    );
  });
}

this showing the date in below format

Thu Feb 25 2021 14:00:00 GMT-0500 (Eastern Standard Time)

i only want it to be

25/01/2021
or
01/25/2021

Here is the HTML

<p>Hi <?= fn ?>,</p>
  <p>I hope you are well. We notices you recently tried to place an order on <a href="www.xyz.com">My Company Name</a>, and it got canceled before completion.</p>
  <p>Let me know if you need any assistance with placing an order; I will be delighted to help you.</p>

  <p>Below is your canceled order details:</p>

  <p><strong>Order# <?= oid ?></strong></p>
  <p><strong>Date: <?= odate ?></strong></p>
  <p><strong>Item: <?= itm ?></strong></p>
  <p><strong>Total Amount: $<?= amt ?></strong></p>

any help will highly be appreciated

google-apps-script google-sheets javascript
1 Answers

It's not clear where you get the date in the code you shared, but you can format the date as you wish by using the formatDate() method from the Utilities class.

Try somehting like this:

let formattedDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy")
Logger.log(formattedDate)

and see if that works for you?

EDIT: Here's the complete code for you to try. Note: The html-body is constructed inside the code. No need for a seperate HTML-file.

function sendMail() {
  let [first, order, date, item, email, pesa, aliases] = [0, 1, 2, 3, 4, 5, GmailApp.getAliases()]
  let data = SpreadsheetApp.getActive().getSheetByName("NAJ").getDataRange().offset(1, 0).getValues();

  data.forEach(row => {

    let formattedDate = isDate_(row[date]) ? Utilities.formatDate(row[date], Session.getScriptTimeZone(), "dd/MM/yyyy") : "";
    let htmlMessage = getHTMLBody_(row[first], row[order], formattedDate, row[item], row[pesa])

    if (row[email]) {
      GmailApp.sendEmail(row[email], "Your Order Recently Canceled", "",
        {'from':aliases[4], name: "My company name", htmlBody: htmlMessage }
      );
    }
  });
}

function getHTMLBody_(fn, oid, odate, itm, amt) {

  return '<HTML><p>Hi ' + fn + ',</p>' +
    '<p>I hope you are well. We notices you recently tried to place an order on <a href="www.xyz.com">My Company Name</a> ' +
    'and it got canceled before completion.</p>' +
    '<p>Let me know if you need any assistance with placing an order; I will be delighted to help you.</p>' +
    '<p>Below is your canceled order details:</p>' +
    '<p><strong>Order# ' + oid + '</strong></p>' +
    '<p><strong>Date: ' + odate + '</strong></p>' +
    '<p><strong>Item: ' + itm + '</strong></p>' +
    '<p><strong>Total Amount: ' + amt + '</strong></p>' +
    '</HTML>';
}

function isDate_(d) {
  return (Object.prototype.toString.call(d) !== "[object Date]") ? false : (isNaN(d.getTime())) ? false : true;
}

user_38894230
Related