CODEWITHSUNDEEP
javascriptemaildatetimegoogle-apps-scriptgoogle-sheets
Greg
Greg

Reputation: 3

Email table in html but format first column as date

I've got the below code which sends the data from a specific sheet to the email address when there is data on the sheet (A1 = "Date"). Only trouble I'm having is how to format the first column as a date. Currently, it defaults to the full format (e.g. Tue Jul 09 2019 00:00:00 GMT+0100 (BST) ). Would prefer dd/mm/yyyy

function email_table() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('reminders');
  var ob='';
  var kl='';
  var h=[];
  var row1='';
  var data = sheet.getDataRange().getValues();
  Logger.log(data)


 if(data[0][0]=='Date'){
    Logger.log('valid')
    var l = data[0].length
    //Logger.log(l)
    data.forEach(function(row){

      for(var i=0;i<row.length;i++){
        ob=ob + '<td>'+row[i]+'</td>'
        if(i==(l-1)){
          h.push('<tr>'+ob+'</tr>')
          ob=''
        }
      }
    });
     //Logger.log(h)
     h.map(function(v){
       kl=kl+v
     })
     //Logger.log(kl)
     row1='<table style="width:100%;height:300;">'+ kl + '</table>'
     Logger.log('done');

    MailApp.sendEmail({
    to: '[email protected]',
    subject: 'Task',
    htmlBody: row1,
  });
  } else{
  }
}

Upvotes: 0

Views: 161

Answers (2)

Wicket
Wicket

Reputation: 38180

If your dates are already formatted in Google Sheets, instead of getValues() use getDisplayValues().

The above because getValues() returns JavaScript Date objects while getDisplayValues() returns strings the values displayed in Google Sheets as strings.

Upvotes: 1

Cooper
Cooper

Reputation: 64062

I think this will do it:

function email_table() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('reminders');
  var html='<style>td,th{border:1px solid black;}</style><table style="width:100%;height:300;">';
  var data = sheet.getDataRange().getValues();
  if(hdrA[0][0]=='Date'){
    data.forEach(function(r,i){
      if(i==0){
        html+='<tr>';
        r.forEach(function(c){html+=Utilities.formatString('<th>%s</th>',c);});
        html+='</tr>';
      }else {
      r.forEach(function(c,j){
        if(j==0) {
          html+='<tr>';
          html+=Utilities.formatString('<td>%s</td>',Utilities.formatDate(new Date(c), Session.getScriptTimeZone(), "dd/MM/yyyy"));
          html+='</tr>';
        }else {
          html+='<tr>';
          html+=Utilities.formatString('<td>%s</td>',c);
          html+='/<tr>';
        }
        });
      } 
    });
    MailApp.sendEmail({to: '[email protected]',subject: 'Task',htmlBody: html});
  } 
}

Upvotes: 0

Related Questions