如何将Google表格中的唯一值传递给HTML电子邮件模板 [英] How do I pass unique values in Google Sheet to HTML Email template

问题描述

我有一个Google表格，其中包含项目数据(项目代码，名称，项目负责人的日期等)，在这种情况下，我试图获取唯一值的行数据.例如，如果电子邮件 [e-mail] 出现x次，则应捕获行数据并将其传递到html文件，然后通过相同的情况通过电子邮件发送给 [e-mail] 到其他电子邮件.因此，特定的项目负责人应该只收到有关他们领导的项目的电子邮件.我是JavaScript/GA的新手，已经做了大量研究，但未能成功.感谢您的帮助.

I have a google sheet that has project data(project code, name, dates project leaders et.c) and I am trying to get row data for unique values in this case emails. If for example email [e-mail] appear x number of times, the row data should be captured and passed to html file then emailed to [e-mail] same case to other emails. So specific project leaders should receive emails on only the projects they lead. I have am newbie in JavaScript/GAs, and have done tonnes of research but could not get it to work. I will appreciate help with this.

读取工作表的电子邮件脚本

Email script that reads the sheet

function Getdata(){

const ss= SpreadsheetApp.getActiveSpreadsheet();
const ws= ss.getSheetByName("Projects");
const headers= ws.getRange("A1:J").getValues();
const Project_Code = headers[0][0];
const End_Date = headers[0][1];
const Project_Name = headers[0][2];
const New_End_Date = headers[0][3];
const Contact_Finance = headers[0][5];
const Update_client = headers[0][6];
const Project_Leader = headers[0][7];
const Email = headers[0][11];

const lr = ws.getLastRow();
const tableRangeValues=ws.getRange(2,1, lr-1,9).getDisplayValues();

const htmlTemplate = HtmlService.createTemplateFromFile("Notify") 
htmlTemplate.Project_Code = Project_Code;
htmlTemplate.End_Date= End_Date;
htmlTemplate.Project_Name= Project_Name;
htmlTemplate.New_End_Date=Complete;
htmlTemplate.Contact_Finance=Contact_Finance;
htmlTemplate.Update_client=Upadate_client;
htmlTemplate.Project_Leader=Project_Leader;
htmlTemplate.tableRangeValues=tableRangeValues;

const htmlForEmail = htmlTemplate.evaluate().getContent();


// This is the part where email loop should happen? and only send emails to individual project leaders listing only the projects they work on.

MailApp.sendEmail({
to: // emails based on loops?
subject: 'Projects Ending',
htmlBody: htmlForEmail,
//Logger.log(tableRangeValues);
 });
}

推荐答案

如何通过键值将1个数组分成多个数组

我建议使用 Array.prototype. reduce 将行数组组织成一个对象，该对象以电子邮件作为键，而行数组作为值(以下示例). 您最终会得到

How to separate 1 array into multiple arrays by a key value

I suggest using Array.prototype.reduce to organize your array of rows into an object that has emails as keys and arrays of rows as values (example below). You'll end up with

{
 "[e-mail]": [[value1],[value2],[value3]],
 "[e-mail]": [[value1],[value2],[value3]],
 // etc.
}    

请参见下文，了解适合您的需求的减速器，但总的来说，思路是

See below for a reducer that is specific to your needs, but in general the idea is

function reducer(object, arrayOfValues) {
  const key = arrayOfValues[INDEX_OF_KEY];
  if (!(key in object)) object[key] = [];
  object[key].push(arrayOfValues);
  return object;
}

const groupedByKey = valuesFromSheet.reduce(reducer, {});

您可以设置一个对象循环，并在全局Object:Object.entries()中添加相对较新的对象.这将返回一个数组数组，因此您可以使用Array.prototype.forEach(下面的示例)发送电子邮件.

You can setup a loop through an Object with a relatively recent addition to the global Object: Object.entries(). This will return an array of arrays so you can send your email with Array.prototype.forEach (example below).

为每封电子邮件创建一个新模板.您应该利用Apps Script的模板功能对模板进行模块化.当您添加所有样式，然后开始添加scriplet标签<? ?>时，它可能会变成一团糟.我在下面演示了如何将表格模板与主电子邮件模板分开，最后以嵌套模板结束.这更易于管理和调试/测试.

For each email, create a new template. You should take advantage of Apps Script's templating ability to modularize your templates. When you add all your styling in and then start adding scriplet tags <? ?> it can become a real mess. I have demonstarted below how you can separate the table template from the main email template and end up with nested templates. This is much more manageable and debuggable/testable.

最后，我也总是建议在一个位置定义标题(请参见下面的HEADINGS对象)，以便稍后可以轻松更改标题顺序，而无需在多个位置更新代码(将错误引入您的脚本).

Finally, I also always recommend defining your headings in one place (see the HEADINGS object below) so you can easily change up the heading order later without updating the code in multiple places (an easy way to introduce a bug into your script).

const HEADINGS = {
  PROJECT_CODE: 0,
  END_DATE: 1,
  PROJECT_NAME: 2,
  NEW_END_DATE: 3,
  CONTACT_FINANCE: 5,
  UPDATE_CLIENT: 6,
  PROJECT_LEADER: 7,
  LAST_NAME: 8,
  FULL_NAME: 9,
  EMAIL: 11
};

function Getdata(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Projects");
  const headers = makeHeaders(ws.getRange("A1:J").getValues()[0]);
  const lastRow = ws.getLastRow();
  const tableRangeValues = ws
    .getRange(2, 1, lastRow - 1, HEADINGS.EMAIL + 1)
    .getDisplayValues();
  const groupedByEmail = tableRangeValues.reduce(emailReducer, {});
  Object.entries(groupedByEmail).forEach(function(emailGroup) {
    const [email, values] = emailGroup;
    MailApp.sendEmail({
      to: email,
      subject: "Projects Ending",
      htmlBody: makeHtmlEmail(values[0][HEADINGS.FULL_NAME], headers, values)
    });
  });
}

function emailReducer(groups, row) {
  const email = row[HEADINGS.EMAIL];
  if (!(email in groups)) groups[email] = [];
  groups[email].push(row.slice(HEADINGS.PROJECT_CODE, HEADINGS.FULL_NAME + 1));
  return groups;
}

function makeHtmlEmail(name, header, values) {
  const emailTemplate = HtmlService.createTemplateFromFile("Notify");
  emailTemplate.name = name;
  emailTemplate.header = header;
  emailTemplate.values = values;
  return emailTemplate.evaluate().getContent();
}

function makeTable(header, values) {
  const table = HtmlService.createTemplateFromFile("table");
  table.header = header;
  table.values = values;
  return table.evaluate().getContent();
}

function makeHeaders(headerRow) {
  return [
    headerRow[HEADINGS.PROJECT_CODE],
    headerRow[HEADINGS.END_DATE],
    headerRow[HEADINGS.PROJECT_NAME],
    headerRow[HEADINGS.NEW_END_DATE],
    headerRow[HEADINGS.CONTACT_FINANCE],
    headerRow[HEADINGS.UPDATE_CLIENT],
    headerRow[HEADINGS.PROJECT_LEADER]
  ];
}

Notify.html

<body>
  <head><!-- style, etc... omitted for clarity --></head>
  <body>
    <p>Hi <?= name ?></p>
    <!-- details omitted for clarity -->
    <h4> Project Details </h4>
    <?!= makeTable(header, values) ?>
  </body>
</body>

table.html

<!-- styling omitted for clarity -->
<table>
  <thead>
    <tr>
    <? header.forEach(heading => { ?>
      <th><?= heading ?></th>
    <? }) ?>
    </tr>
  </thead>
  <tbody>
  <? values.forEach(row => { ?>
    <tr>
    <? row.forEach(value => { ?>
      <td><?= value ?></td>
    <? }); ?>
    </tr>
  <? }); ?>
  </tbody>
</table>

这篇关于如何将Google表格中的唯一值传递给HTML电子邮件模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！