CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Atif Qayyum
Atif Qayyum

Reputation: 57

GoogleSheet Facing Formatting Issues while Sending Email of Range copied to Email Body

I am working on a atomized email report pasted in Gmail body and sending emails. All things working good expect the format on email Body is showing like Image 1.

Last column is only the time gap difference which is formatted on Google Sheet as Duration - HH:MM:SS

How data is Being reflected in Email body: enter image description here

Actual Image from Google Sheet - From where Email is being Generated: enter image description here

Script for Sending emails & Fetching data:

  var stockData = getData();
  var body = getEmailText(stockData);
  var htmlBody = getEmailHtml(stockData); 
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Operations - Today's Job Details",
    body: body,
    htmlBody: htmlBody
  });
}
function getEmailText(stockData) {
  var text = "";
  stockData.forEach(function(stock) {
    text = text + stock.name + "\n" + stock.ticker + "\n" + stock.price + "\n-----------------------\n\n";
  });
  return text;
}
/**
 * @OnlyCurrentDoc
 */
function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("Email").getRange("Stocks").getValues();
  values.shift(); //remove headers
  var stocks = [];
  values.forEach(function(value) {
    var stock = {};
    stock.sr = value[0];
    stock.job = value[1];
    stock.work = value[2];
    stock.worked = value[3];
    stock.time = value[4];
    stocks.push(stock);
  })
  //Logger.log(JSON.stringify(stocks));
  return stocks;
}

Followed by Html code:

    <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
        <colgroup>
            <col width="45">
                <col width="588">
                    <col width="57">
                        <col width="90">
                            <col width="103">
        </colgroup>
        <tbody>
            <tr style="height:21px">
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:rgb(252,229,205);font-weight:bold;text-align:center;border:1px solid rgb(204,204,204)">SR</td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:rgb(252,229,205);font-weight:bold;text-align:center;border:1px solid rgb(204,204,204)">JOB DETAIL</td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:rgb(252,229,205);font-weight:bold;text-align:center;border:1px solid rgb(204,204,204)">WORK TODAY?</td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:rgb(252,229,205);font-weight:bold;text-align:center;border:1px solid rgb(204,204,204)">WORKED X TIMES</td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;background-color:rgb(252,229,205);font-weight:bold;text-align:center;border:1px solid rgb(204,204,204)">TIME SPENT ON JOB</td>
            </tr>
            <? for(var i = 0; i < stocks.length; i++) { ?>
            <tr style="height:21px">
                <td style="overflow:hidden;padding:2px 3px;vertical-align:middle;text-align:center;border:1px solid rgb(204,204,204)"><?= stocks[i].sr ?></td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)"><?= stocks[i].job ?></td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)"><?= stocks[i].work ?></td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)"><?= stocks[i].worked ?></td>
                <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)"><?= stocks[i].time ?></td>
            </tr>
            <? } ?>
        </tbody>
    </table>
</div>

Upvotes: 0

Views: 182

Answers (1)

Marios
Marios

Reputation: 27390

Change:

var values = SpreadsheetApp.getActive().getSheetByName("Email").getRange("Stocks").getValues();

to:

var values = SpreadsheetApp.getActive().getSheetByName("Email").getRange("Stocks").getDisplayValues();

to get the date as it is displayed in the sheet.

Upvotes: 3

Related Questions