I have this working script to sent mails with data from a Google sheet:

  function SendEmail() {
  let timestamp = 0
  let poid = 1
  let sku = 2
  let qty = 3
  let description = 4
  let licenseid = 5
  let itcost = 6
  let total = 7
  let company = 8
  let contact = 9
  let contactmail = 10
  let endusermail = 11
  let address = 12
  let country = 13
  let status = 14
  let suppliermail = 15
  let currency = 16
  let otherinfo = 17
  let brand = 18
  let comment = 19
  let cc = 20

  let emailTemp = HtmlService.createTemplateFromFile("MAIL")

  let ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA")
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA2")

  let data = ws.getRange("A2:V"   ws.getLastRow()).getValues()
  let sData = sd.getRange("B2:J"   sd.getLastRow()).getValues()

  let sInfo = sData.map(function (r) { return r[0] })

  data = data.filter(function (r) { return r[14] == 'SENTNOW' })

  if (data.length) {

    let found = false

    data.forEach(function (row) {

      emailTemp.ts = row[timestamp].toLocaleString("da-DK")
      emailTemp.po = row[poid]
      emailTemp.co = row[contact]
      emailTemp.cm = row[company]
      emailTemp.ad = row[address]
      emailTemp.cu = row[country]
      emailTemp.cn = row[contactmail]
      emailTemp.sk = row[sku]
      emailTemp.de = row[description]
      emailTemp.qt = row[qty]
      emailTemp.it = (row[itcost]).toLocaleString("da-DK")
      emailTemp.to = (row[total]).toLocaleString("da-DK")
      emailTemp.ce = row[comment]
      emailTemp.cy = row[currency]
      emailTemp.eu = row[endusermail]
      emailTemp.li = row[licenseid]
      emailTemp.ot = row[otherinfo]

      let indexSupp = sInfo.indexOf(row[15])

      if (indexSupp > -1) {

        //only change status if supplierdata email is found
        found = true

        emailTemp.spname = sData[indexSupp][1]
        emailTemp.saddress1 = sData[indexSupp][2]
        emailTemp.saddress2 = sData[indexSupp][3]
        emailTemp.scountry = sData[indexSupp][4]
        emailTemp.sterms = sData[indexSupp][5]
        emailTemp.scurrency = sData[indexSupp][6]
        emailTemp.sothers = sData[indexSupp][7]
        emailTemp.vat = sData[indexSupp][8] * 100

        emailTemp.totvat = (row[total] * sData[indexSupp][8]).toLocaleString("da-DK")

        emailTemp.totandvat = (row[total]   (row[total] * sData[indexSupp][8])).toLocaleString("da-DK")

        let subjectLine = "Subject line # "   row[poid]

        let htmlMessage = emailTemp.evaluate().getContent()

        //only send email if supplierdata email is found

        try {

          GmailApp.sendEmail(
            row[suppliermail],
            subjectLine,
            "",
            { name: 'Name', htmlBody: htmlMessage, bcc: '[email protected]' })

        }

        catch (err) {

          SpreadsheetApp.getUi().alert(err)

        }

      }

    })

    if (found) {

      let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA")
        .getRange('O2:O')
        .createTextFinder('SENTNOW')
        .replaceAllWith('SENT')

    }

  }
}

Only problem is the date format emailTemp.ts = row[timestamp].toLocaleString("da-DK")

This give output date-format "11.2.2022 06.00.00" within the e-mail sent to the reciever.

What I wish is the date to be just "02/11/2022"

I tried emailTemp.ts = row[timestamp].toLocaleString("da-DK").getDisplayValue() but that was not working.

Any suggestions ?

CodePudding user response：

You're going to have to figure out your time zone but try this.

emailTemp.ts = Utilities.formatDate(row[timestamp],"GMT","dd/MM/yyyy");

Reference

• Utilities.formatDate()