在将范围内的电子邮件复制到电子邮件正文时，GoogleSheet面临格式问题 [英] GoogleSheet Facing Formatting Issues while Sending Email of Range copied to Email Body

问题描述

我正在处理粘贴在Gmail正文中的雾化电子邮件报告并发送电子邮件.一切正常，希望电子邮件正文上的格式如图1所示.

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.

最后一栏只是时间差，在Google表格中以时长-HH:MM:SS

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:

Google表格中的实际图片-生成电子邮件的位置:

Actual Image from Google Sheet - From where Email is being Generated:

用于发送电子邮件和脚本的脚本提取数据:

  var stockData = getData();
  var body = getEmailText(stockData);
  var htmlBody = getEmailHtml(stockData); 
  MailApp.sendEmail({
    to: "[e-mail]",
    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;
}

后跟HTML代码:

    <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>

推荐答案

更改:

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

收件人:

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

获取工作表中显示的日期.

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

这篇关于在将范围内的电子邮件复制到电子邮件正文时，GoogleSheet面临格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！