google-apps-scriptgoogle-sheetsgoogle-email-settings-api

Line Breaks - Gmail Apps Script

I have made some code to automate my email from a spreadsheet and want to expand it across my team but I would like to add a footer/signature to it and also not have to include the recipient and subject in the code itself but rather pull it from the spreadsheet. I have it laid out in the spreadsheet as;

enter image description here

I would like the email footer to look like that but when I run the script it brings back "Range"

function EODReportEmail() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Tasks");;        // Use data from the active sheet
  var startRow = 2;                                                                            // First row of data to process
  var numRows = sheet.getLastRow();                                                            // Number of rows to process
  var lastColumn = sheet.getLastColumn();                                                      // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn)                             // Fetch the data range of the active sheet
  var data = dataRange.getValues();                                                            // Fetch values for each row in the range

  var recipient = "shea.a.murphy@icloud.com"                                                                              // Email address report will be sent to
  var subject = "Shea Murphy - EOD Email"                                                                              // Subject heading of email e.g. Shea Murphy - EOD Email
  var footer = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("C2");
  Logger.log(footer);

  // Work through each row in the spreadsheet
  for (var i = 0; i < data.length; ++i) {
    var row = data[i]; 

 // Assign each column a variable
    var Point1 = row[0];              // Col A: 1st point to be included in email
    var Point2 = row[1];              // Col B: 2nd point to be included in email
    var Point3 = row[2];              // Col C: 3rd point to be included in email
    var Point4 = row[3];              // Col D: 4th point to be included in email
    var Point5 = row[4];              // Col E: 5th point to be included in email

    var Num1 = "<b>1. </b>"           // Number variables for email points
    var Num2 = "<b>2. </b>"
    var Num3 = "<b>3. </b>"
    var Num4 = "<b>4. </b>"
    var Num5 = "<b>5. </b>"

    var Task1 = Num1 + Point1         // Note 1 to be inlcuded in the email
    var Task2 = Num2 + Point2         // Note 2 to be inlcuded in the email
    var Task3 = Num3 + Point3         // Note 3 to be inlcuded in the email
    var Task4 = Num4 + Point4         // Note 4 to be inlcuded in the email
    var Task5 = Num5 + Point5         // Note 5 to be inlcuded in the email

    if (Point1 == undefined) {Task1 = " "};
    if (Point2 == undefined) {Task2 = " "};
    if (Point3 == undefined) {Task3 = " "};
    if (Point4 == undefined) {Task4 = " "};
    if (Point5 == undefined) {Task5 = " "};

  // Build the email message
      var emailBody =  '<b style="font-family:georgia;font-size:18px;font-style:italic; color: #D04A02";>EOD Report</b>';
          emailBody += '<p>Please see what I have worked on today below:<p>';
          emailBody += '<dl><dd>'+ Task1 +'</dd>';
          emailBody += '<dl><dd>'+ Task2 +'</dd>';
          emailBody += '<dl><dd>'+ Task3 +'</dd>';
          emailBody += '<dl><dd>'+ Task4 +'</dd>';
          emailBody += '<dl><dd>'+ Task5 +'</dd>';

          emailBody += '<p>Let me know if you have any questions.<p>';
          emailBody += footer

    // Create the email draft
      GmailApp.sendEmail(
        recipient,          // Recipients
        subject,            // Subject
        ' ',                // Body
        {
        htmlBody: emailBody,    // Options: Body (HTML)


        }
      )

  }
}

Basically, is there any way of bringing the code across the two pages for the recipient, subject and include the signature at the end with all correct formatting rather than writing it out line by line in the code itself?

EOD Email Information enter image description here

EOD Email Tasks enter image description here

Email Itself

Solution

  • I believe your goal as follows.

    For this, how about this answer?

    Modification points:

    When I saw the image of your question, I could confirm that your rich text put to the cell "C2" uses "Bold", "Italic", "ForegroundColor" and the hyperlink. Using this, I would like to propose the following modified script.

    When your script is modified, please modify as follows.

    Modified script:

    From: 
    var footer = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("C2");
    To: 
    var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOD Email Information").getRange("C2");
var footer = range.getRichTextValue().getRuns().reduce((s, r) => {
  let text = r.getText().replace(/\n/g, "<br>").replace(/ /g, "&nbsp;");
  if (r.getLinkUrl()) text = `<a href="${r.getLinkUrl()}">${text}<\/a>`;
  const style = r.getTextStyle();
  const obj = {
    fontFamily: style.getFontFamily(),
    fontSize: style.getFontSize(),
    foregroundColor: style.getForegroundColor(),
    bold: style.isBold(),
    italic: style.isItalic(),
    strikethrough: style.isStrikethrough(),
    underline: style.isUnderline(),
  };
  const fontFamily = obj.fontFamily ? `font-family: '${obj.fontFamily}';` : "";
  const fontSize = obj.fontSize ? `font-size: ${obj.fontSize * 1.333}px;` : "";
  const foregroundColor = obj.foregroundColor ? `color: ${obj.foregroundColor};` : "";
  const bold = obj.bold ? 'font-weight: bold;' : "";
  const italic = obj.italic ? 'font-style: italic;' : "";
  const strikethrough = obj.strikethrough ? 'text-decoration: line-through;' : "";
  const underline = obj.underline ? 'text-decoration: underline;' : "";
  const keys = [fontFamily, fontSize, foregroundColor, bold, italic, strikethrough, underline];
  if (keys.some(e => e != "")) {
    s += `${keys.reduce((str, e) => str += e, '<span style="')}">${text}</span>`;
  } else {
    s += text;
  }
  return s;
}, "");

    Result:

    When the value of your image is used for this modified script, the following HTML is retrieved. (The email address was changed to a sample value.)

    <span style="font-family: 'Arial';font-size: 13.33px;color: #000000;">Kind&nbsp;regards&nbsp;<br>&nbsp;&nbsp;--<br></span><span style="font-family: 'Arial';font-size: 13.33px;color: #000000;font-weight: bold;">Shea&nbsp;Murphy</span><span style="font-family: 'Arial';font-size: 13.33px;color: #000000;"><br>Mobile:&nbsp;074xxxxxx93<br>Email:&nbsp;</span><span style="font-family: 'Arial';font-size: 13.33px;color: #1155cc;text-decoration: underline;"><a href="mailto:sample@email.com">sample@email.com</a></span><span style="font-family: 'Arial';font-size: 13.33px;color: #000000;"><br><br></span><span style="font-family: 'Arial';font-size: 13.33px;color: #990000;font-weight: bold;font-style: italic;">For&nbsp;further&nbsp;information&nbsp;-&nbsp;please&nbsp;contact</span>

    References: