How do I format the date in HTML table from dataGrid columns-CodePudding

I need help with Google Apps Script for formatting the HTML output in the email to Currency for dataGrid[i][6] and date (mm-dd-yyy) for dataGrid[i][9], dataGrid[i][11] and dataGrid[i][12]. The date currently outputs to Fri Apr 22 2022 00:00:00 GMT-0700 (Pacific Daylight Time). Full code w/comments below:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('reports_send')
      .addItem('LOB (Report)', 'sendLob')
      .addToUi();
}


function sendLob() 
{
  
  //capture the sheet with this identifier
  var sheet = SpreadsheetApp.getActive().getSheetByName("report_lob");
  
  //get the email address of the recipient
  var email = sheet.getRange(2, 14).getValue(); //loads the current email address into variable "email". Notice columns are referred to as numbers.

  //get data for this range
  var dataGrid = sheet.getRange(6, 1, sheet.getLastRow(), 14).getValues(); //loads the data in this range at the time

  var message = ""; //container to hold final email content
 
  //set up the HTML to be rendered by sendEmail eventually.
  //adding a table for the column headings and content inside it.
  
  message = "  <table width='100%' border='1' cellpadding='5' style='border:1px #999999; border-collapse:collapse'>";
  message  = "<tr>"
  message  = "<td><b>Supplier</td>"
  message  = "<td><b>PO</td>"
  message  = "<td><b>PO Line#</td>"
  message  = "<td><b>Line Item</td>"
  message  = "<td><b>Buyer</td>"
  message  = "<td><b>Line Value</td>"
  message  = "<td><b>Line Order Qty</td>"
  message  = "<td><b>Open Qty</td>"
  message  = "<td><b>Contract Date</td>"
  message  = "<td><b>Organization</td>"
  message  = "<td><b>Anticipated Date</td>"
  message  = "<td><b>Updated Date</td>"
  message  = "<td><b>Justification</td>"
  message  = "</b> </tr>"
  
  //Loop through the content in data grid and set them up in each column with new row for each.
  for(var i=1;i<dataGrid.length; i  )
  {
    message = "<tr>"
    message = "<td>"   dataGrid[i][1]   "</td>"
    message = "<td>"   dataGrid[i][2]   "</td>"
    message = "<td>"   dataGrid[i][3]   "</td>"
    message = "<td>"   dataGrid[i][4]   "</td>"
    message = "<td>"   dataGrid[i][5]   "</td>"
    message = "<td>"   dataGrid[i][6]   "</td>"
    message = "<td>"   dataGrid[i][7]   "</td>"
    message = "<td>"   dataGrid[i][8]   "</td>"
    message = "<td>"   dataGrid[i][9]   "</td>"
    message = "<td>"   dataGrid[i][10]   "</td>"
    message = "<td>"   dataGrid[i][11]   "</td>"
    message = "<td>"   dataGrid[i][12]   "</td>"
    message = "<td>"   dataGrid[i][13]   "</td>"
    message = "</tr>"
  }
  //end of table tag
  message  ="</table>"
  
  
  //send email. Done! 
  
MailApp.sendEmail(email, "LOB (Report)", "", {htmlBody:message});
  Browser.msgBox("LOB (Active) report sent! Who took the chocolate?"); 
  }

I tried:

var cell = SpreadsheetApp.getActiveSheet().getRange(1, 1);
cell.setValue(new Date()).setNumberFormat("MM/dd/yyyy");

but it just changed the worksheet cell contents.

CodePudding user response：

Have you tried this? as it seems very logically, you just want the format to change?

cell.setValue(cell.getValue()).setNumberFormat("MM/dd/yyyy");

replace cell with the cell in question.

CodePudding user response：

As answered in the comments section:

"when var dataGrid = sheet.getRange(6, 1, sheet.getLastRow(), 14).getValues(); is modified to var dataGrid = sheet.getRange(6, 1, sheet.getLastRow(), 14).getDisplayValues()"