我必须发送电子邮件以获取某些列中的数据.如果重复发送电子邮件，我只想发送一封包含其他单元格值的电子邮件 [英] I have to send emails taking data from some columns. If the email is repeated I would like to send only one email with the values from the other cells

问题描述

注意:我的脚本使用的是Google Apps脚本.

Note: I am using Google Apps Script for my script.

我有A，B和C列，其中包含要从D列(逐行)发送到电子邮件的数据.我已按字母顺序对列表进行了排序，并且某些地址重复了.

I have columns A, B, and C with data to send to the emails from Column D (row by row). I have sorted the list alphabetically and some of the addresses are repeated.

现在我的代码在D列中的每个地址发送一封电子邮件，但这意味着如果重复该值，此人将收到多封电子邮件.

Right now my code sends one email per each address in column D but this means that if the value is repeated, the person will get multiple emails.

如果记录重复，我如何只发送一条消息，其中包含A，B和C列中的相应值?

If the record is repeated, How do I send only one message containing the respective values from columns A, B, and C?

名字   姓氏         用户电子邮件                 经理电子邮件

First Name     Last Name         User Email                    Manager Email

姓名1        姓氏1        [e-mail]      [e-mail]

Name 1         Last Name 1       [e-mail]      [e-mail]

姓名2      姓氏2     [e-mail]    [e-mail]

姓名3      姓氏3     [e-mail]    [e-mail]

姓名4      姓氏4     [e-mail]    [e-mail]

姓名5        姓氏5        [e-mail]       [e-mail]

Name 5         Last Name 5       [e-mail]      [e-mail]

上表显示 [e-mail] 在列表上重复了3次.我想避免向同一位经理发送多封电子邮件，而只发送一条包含其他列中的值的消息.

The above table shows that [e-mail] is repeated three times on the list. I would like to avoid sending multiple emails to the same manager and just send one message containing the values from the other columns.

例如

收件人:[e-mail]
主题:报告

To: [e-mail]
Subject: Report

--------------------------------------------------- ---

--------------------------------------------------

美好的一天，

以下人员...

名称2姓氏2，电子邮件为[e-mail]

Name 2 Last Name 2 with email [e-mail]

名称3姓氏3，电子邮件为[e-mail]

Name 3 Last Name 3 with email [e-mail]

名称4姓氏4，电子邮件为[e-mail]

Name 4 Last Name 4 with email [e-mail]

...

--------------------------------------------------- ---

--------------------------------------------------

谢谢

 

推荐答案

尝试一下:

我发现使用对象是对信息进行分组的好方法.

I find that using objects is a great way to group information.

function concatColumn() {
  var mgrcol=4;
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var mgrA=[];
  var html='';
  for(var i=1;i<vA.length;i++) {
    if(mgrA.indexOf(vA[i][3])==-1) {
      mgrA.push(vA[i][3]); 
    }
  }
  var mgrObj={}
  for(var i=0;i<mgrA.length;i++) {
    for(var j=0;j<vA.length;j++) {
      if(mgrA[i]==vA[j][3]){
        if(mgrObj.hasOwnProperty(mgrA[i])) {
          mgrObj[mgrA[i]]+=Utilities.formatString('~~~FirstName: %s<br />LastName: %s<br />UserEmail: %s',vA[j][0],vA[j][1],vA[j][2]);
        }else{
          mgrObj[mgrA[i]]=Utilities.formatString('FirstName: %s<br />LastName: %s<br />UserEmail: %s',vA[j][0],vA[j][1],vA[j][2]);
        }                                               
      } 
    }
  }
  for(var i=0;i<mgrA.length;i++) {
    var tA=mgrObj[mgrA[i]].split('~~~');
    var s='User Information<br /></br />';
    for(var j=0;j<tA.length;j++) {
      s+=tA[j].toString() + '<br />'; 
    }
    s+='<hr widht="100" />';
    GmailApp.sendEmail(mgrA[i], 'User Names and Emails', null, {htmlBody:s})
    //html+=Utilities.formatString('Email Recipient: <strong>%s</strong><br />',mgrA[i]) + s;//debug 
  }
  //var ui=HtmlService.createHtmlOutput(html);//debug
  //SpreadsheetApp.getUi().showModelessDialog(ui, 'Emails');//debug
}

最后几行注释掉的行用于创建电子邮件的副本以显示在对话框上，以调试脚本.希望这可以帮助.

The last few commented out lines are used for creating a replica of the email to display on a dialog in order to debug the script. Hope this helps.

这篇关于我必须发送电子邮件以获取某些列中的数据.如果重复发送电子邮件，我只想发送一封包含其他单元格值的电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！