英文:
Send one email to include one of the repeated columns
问题
我改编了这段代码，但我需要帮忙提取另外两列的数据而不重复。
目前我的最终结果确实会发送两封不同的电子邮件消息（列B），其中包含产品详细信息的清单，但是用户名和临时密码只获取最后一行的信息，并发送给两个收件人。
如果这被视为重复问题，我很抱歉。我不确定我是否应该依赖原始问题。谢谢！
英文:
I repurposed this code: https://stackoverflow.com/questions/54740760/i-have-to-send-emails-taking-data-from-some-columns-if-the-email-is-repeated-i/54741922#54741922 but I need help on how to grab the two other columns with data without repeats.
My end result currently does send two different email messages (column B) with the listing of the Product details but the Username and TempPassword, it only takes the last row information and send to both recipients.
If this is considered as duplicate question, I apologize. I am not sure if I should piggy back on the original question. TIA!
答案1
得分: 2
我相信您的目标如下。
- 您希望从电子表格的列“A”到“D”中检索值。
- 当在列“B”中找到相同的电子邮件地址时，您希望仅使用该电子邮件地址发送一封电子邮件。
在这种情况下，以下是一个示例脚本：
示例脚本：
请复制并粘贴以下脚本到电子表格的脚本编辑器中。并且，请设置sheetName和subject的变量。
function myFunction() {
const sheetName = "Sheet1"; // 请设置您的表格名称。
const subject = "示例主题"; // 请设置您期望的电子邮件主题。
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const values = sheet.getRange("A2:D" + lastRow).getDisplayValues();
const obj = [...values.reduce((m, r) => m.set(r[1], m.has(r[1]) ? [...m.get(r[1]), r] : [r]), new Map()).values()];
const messages = obj.map(r => ({
to: r[0][1],
body: [
"您的信息：",
`用户名：${r[0][2]}`,
`密码：${r[0][3]}`,
"",
"产品：",
...r.map(([a]) => `- ${a}`),
].join("\n"),
subject,
}));
messages.forEach(o => MailApp.sendEmail(o));
}
- 运行此脚本时，将从表格的“A2:D”中检索值。然后，使用每个电子邮件地址发送电子邮件。
- 文本正文的格式来自您的问题。
注意：
- 此示例脚本是为了显示示例电子表格图像而提供的。如果您更改电子表格，此脚本可能无法使用。请注意此点。
参考资料：
英文:
I believe your goal is as follows.
- You want to retrieve the values from the columns "A" to "D" of a Spreadsheet.
- When the same email addresses are found in column "B", you want to send only one email using the email address.
In this case, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Spreadsheet. And, please set the variables of sheetName and subject.
function myFunction() {
const sheetName = "Sheet1"; // Please set your sheet name.
const subject = "sample subject"; // Please set your expected subject of the email.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const values = sheet.getRange("A2:D" + lastRow).getDisplayValues();
const obj = [...values.reduce((m, r) => m.set(r[1], m.has(r[1]) ? [...m.get(r[1]), r] : [r]), new Map()).values()];
const messages = obj.map(r => ({
to: r[0][1],
body: [
"Your information:",
`UserName: ${r[0][2]}`,
`Password: ${r[0][3]}`,
"",
"Product:",
...r.map(([a]) => `- ${a}`),
].join("\n"),
subject,
}));
messages.forEach(o => MailApp.sendEmail(o));
}
- When this script is run, the values are retrieved from "A2:D" of the sheet. And, the emails are sent using each email address.
- The format of the text body is from your question.
Note:
- This sample script is for your showing sample Spreadsheet image. If you change the Spreadsheet, this script might not be able to be used. Please be careful about this.
References:
答案2
得分: 1
以下是已翻译的内容：
注意：此答案旨在作为项目的起点或参考。请注意，社区成员不提供编码服务。
您可以尝试使用JavaScript数组方法 reduce、map、filter 和 forEach 的组合来实现您的目标。以下是一个示例。
脚本
function main() {
let rawData = SpreadsheetApp.getActive().getActiveSheet().getRange('A:D').getValues().filter(row => row.some(cell => cell !== ''));
processData(rawData).forEach(d => {
let body =
`<body>
<p>[电子邮件正文]</p>
<p>您的信息：</p>
<p>用户名：${d[2]}</p>
<p>密码：${d[3]}</p>
<p>产品：</p>
<p>${d[1].split(', ').map(item => `•${item}`).join('<br>')}</p>
</body>`;
MailApp.sendEmail(d[0], '用户姓名和电子邮件', null, { htmlBody: body });
console.log(`已发送电子邮件至 ${d[0]} \n${body}`);
});
}
function processData(data) {
const res = Object.values(data.slice(1).reduce((acc, row) => {
const [product, emailAddress, userName, tempPassword] = row;
if (!acc[emailAddress]) {
acc[emailAddress] = { products: [product], emailAddress, userName, tempPassword };
} else {
acc[emailAddress].products.push(product);
}
return acc;
}, {})).map(({ products, emailAddress, ...rest }) => [emailAddress, products.join(', '), ...Object.values(rest)]);
return res;
}
演示
- 示例表格
- 示例结果消息
英文:
Suggestion
> NOTE: This answer is intended to serve as a starting point or reference for your project. It's important to note that the community members do not provide coding services.
You could try achieving your goal by using a combination of the JavaScript array methods reduce, map, filter, and forEach. Here's an example of how you can do it.
Script
function main() {
let rawData = SpreadsheetApp.getActive().getActiveSheet().getRange('A:D').getValues().filter(row => row.some(cell => cell !== ''));
processData(rawData).forEach(d => {
let body =
`<body>
<p>[Body of the email]</p>
<p>Your information:</p>
<p>UserName: ${d[2]}</p>
<p>Password: ${d[3]}</p>
<p>Product:</p>
<p>${d[1].split(', ').map(item => `•${item}`).join('<br>')}</p>
</body>`;
MailApp.sendEmail(d[0], 'User Names and Emails', null, { htmlBody: body });
console.log(`Email sent to ${d[0]} \n${body}`);
});
}
function processData(data) {
const res = Object.values(data.slice(1).reduce((acc, row) => {
const [product, emailAddress, userName, tempPassword] = row;
if (!acc[emailAddress]) {
acc[emailAddress] = { products: [product], emailAddress, userName, tempPassword };
} else {
acc[emailAddress].products.push(product);
}
return acc;
}, {})).map(({ products, emailAddress, ...rest }) => [emailAddress, products.join(', '), ...Object.values(rest)]);
return res;
}
Demo
- Sample Sheet
- Sample result message
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论