How to Format the Email Body Properly When Creating Gmail Drafts from Google Sheets Data in Google Apps Script?

Question

I'm developing an app script program in which the user selects a checkbox in a Google Sheet and when I press the appscript button, it creates an email draft in Gmail. Currently, it works properly. However, the outcome did not match my expectations.

I have this sample Google sheet table.

PICName	TRADE	LANE	VNAME
MF	IMPORT	L1	Samplevn23
DH	TP	L2	VN1 V2
MG	SR JAZZ DX	L3 L4 L5	SAMPLE ONE SAMPLE TWO SAMPLE 3

My current code generates a draft based on the selected checkbox and reflects the data accordingly; however, if the cell contains a lot of varied data (like in row 3), the email display format does not update properly.

I want to display them in the email draft as: (if I selected row 3 in google sheet)

 Dear All,

 Good day!

 Here is the data.
 
 MG, SR, L3, SAMPLE ONE
 MG, JAZZ, L4, SAMPLE TWO
 MG, DX, L5, SAMPLE 3 "

but the result I'm getting is:

"Dear All,

 Good day!

 MG
 SR
 JAZZ
 DX
 L3, 
 L4
 L5
 SAMPLE ONE
 SAMPLE TWO
 SAMPLE 3 " 

Here is my code. Thank you!

function createDraftEmail() {
  var ui = Browser.msgBox('Confirm Draft Creation', 'Are you sure you want to create the email draft?', Browser.Buttons.YES_NO);

  // Process the user's response
  if (ui == 'yes') {
    try {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Main");

      if (!sheet) {
        throw new Error("Sheet 'Main' not found.");
      }

      var data = sheet.getDataRange().getValues();
      var emailSubject = "Notification"; // Customize the email subject
      var emailBodyTemplate = "Dear All Concerned,<br /><br />Please be informed that we have completed all necessary processes for sending as below.<br /><br />"; // Customized message
      var emailBody = emailBodyTemplate;
      var checkboxSelected = false;

      for (var i = 1; i < data.length; i++) {
        if (data[i][0] === true) { // Assuming the checkbox is in the first column
          Logger.log("Checkbox selected in row " + (i + 1));

          sheet.getRange(i + 1, 1, 1, sheet.getLastColumn()).setBackground("#d9ead3"); // Highlight processed row
          sheet.getRange(i + 1, 1).setValue(false); // Uncheck the box
          checkboxSelected = true;

          var PICName = data[i][23]; // Get the name from column 23 of the current row
          var Trade = data[i][2];
          var Lane = data[i][3].toString().split("<br>");
          var VName = data[i][4].toString().split("<br>");
          Logger.log(VName);
          for (var j = 0; j < VName.length; j++) {
            var Lanes = Lane[0];
            var Trades = Trade;
            var PICNames = PICName;
            var VNames = VName[j] || '';

            Logger.log(`Adding line: ${PICNames}, ${Trades}, ${Lanes}, ${VNames}`);
            emailBody += `${PICNames}, ${Trades}, ${Lanes}, ${VNames}<br /><br />`; // Add break line between entries
          }
        }
      }

      if (checkboxSelected) {
        var recipient = ""; // Customize the recipient

        emailBody =
        `<br /><pre><b><span style="color: red;">Details</span></b><br />` +
        `<b>PICName  Trade  Lanes  VNames</b><br />` +
        `------------------------------------------------------------------------------------------------------<br />` +

        emailBody + 
        "<br /><br />Best Regards,<br />" + PICName

        GmailApp.createDraft(recipient, emailSubject, "", {htmlBody: emailBody});
        Logger.log("Draft email created successfully.");
        // Show UI alert
        Browser.msgBox('Success!', 'Draft email created successfully.', Browser.Buttons.OK);
      } else {
        Logger.log("No rows with checked checkboxes found.");
        Browser.msgBox('Failed', 'No rows with checked checkboxes found. No draft created.', Browser.Buttons.OK);
      }
    } catch (error) {
      Logger.log("Error: " + error.message);
      // Show UI alert for error
      Browser.msgBox('Error: ' + error.message);
    }
  } else {
    Browser.msgBox('Cancelled!', 'Draft creation canceled.', Browser.Buttons.OK);
  }
}

Answer 1

getValues() returns data as text and not as html. To split, use split("\n") instead of split("<br>")