使用Google Apps脚本将电子邮件发送到其他工作表中列出的特定地址 [英] Send email to specific address listed on another sheet using Google Apps Script

问题描述

我在某些代码中遇到了一些问题，需要一些专家帮助。我想在表单表单响应中引用列E，它将返回一个人的姓名。相同的名称可以在表格电子邮件的栏A中找到。在表格B栏中，电子邮件将是我想要发送数据的电子邮件地址。我被困在如何产生这个电子邮件地址。这是我到目前为止...
$ b $ pre $ function emailData（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（ ）;
var responses = ss.getSheetByName（Form Responses）;
var lastRow = respond.getLastRow（）;
var values = respond.getRange（A+（lastRow）+：AK+（lastRow））。getValues（）; //获取范围和值一步
var headers = response.getRange（A1：AK1）。getValues（）; //对标题
做同样的事情var recipient = respond.getRange（E+（lastRow））。getValues（）;

var emailSheet = ss.getSheetByName（Email）;
var names = emailSheet.getRange（A2：A20）。getValues（）;
var emails = emailSheet.getRange（B2：B20）。getValues（）;


var subject =容量营销表格;
var message = composeMessage（headers，values）; //以2个数组作为参数调用函数

Logger.log（message）; //检查结果然后发送电子邮件给消息作为文本正文
MailApp.sendEmail（收件人，主题，消息）;


函数composeMessage（headers，values）{
var message ='这里是表单提交的数据：'
for（var c = 0; c< values [0] .length; ++ c）{
message + ='\ n'+ headers [0] [c] +'：'+ values [0] [c]
}
返回消息;
}


我必须为@Serge提供道具来帮助我处理数组。唉，任何你可以提供的帮助都会很棒！

你有搜索所需的姓名和电子邮件地址，在两个二维数组中。每个数组都是一个行数组。一个简单的搜索将是这样的：

  var email =''; （如果我们没有找到匹配，我们将失败发送
 for（var row = 0; row< names.length; row ++）{
 if（names [row] [0 ] ==收件人）{
电子邮件=电子邮件[行] [0]; 
休息; //结束搜索，我们完成
} 
} 
 
 ... 
 MailApp.sendEmail（email，subject，message）; 
  

有一些更优雅的方法可以做到这一点，我敢肯定，但这应该适合您。

I am stuck at a point in some code and need some expert help. I want to reference column "E" on the sheet "Form Responses", which will return a name of a person. The same name can be found in column "A" of sheet "Email". In column "B" of sheet "Email" will be the email address that I want to send data to. I am stuck at how to produce this email address. Here is what I have so far...

function emailData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responses = ss.getSheetByName("Form Responses");
var lastRow = responses.getLastRow();
var values = responses.getRange("A"+(lastRow)+":AK"+(lastRow)).getValues();// get the range and values in one step
var headers = responses.getRange("A1:AK1").getValues();// do the same for headers
var recipient = responses.getRange("E"+(lastRow)).getValues();

var emailSheet = ss.getSheetByName("Email");
var names = emailSheet.getRange("A2:A20").getValues();
var emails = emailSheet.getRange("B2:B20").getValues();


var subject = "Capacity Campaign Form";
var message = composeMessage(headers,values);// call the function with 2 arrays as arguments

Logger.log(message);// check the result and then send the email with message as text body
MailApp.sendEmail(recipient,subject,message);
}

function composeMessage(headers,values){
var message = 'Here is the data from the form submission:'
for(var c=0;c<values[0].length;++c){
message+='\n'+headers[0][c]+' : '+values[0][c]
}
return message;
}

I must give props to @Serge for helping me with the array. Alas, any help that you could provide would be awesome!

解决方案

You've got the names and email addresses you need to search, in two 2-dimensional arrays. Each of those arrays is an array of rows. A simple search would be this:

var email = ''; // If we don't find a match, we'll fail the send
for (var row=0; row < names.length; row++) {
  if (names[row][0] == recipient) {
    email = emails[row][0];
    break; // end the search, we're done
  }
}

...
MailApp.sendEmail(email,subject,message);

There are more elegant ways to do this, I'm sure, but this should work for you.

这篇关于使用Google Apps脚本将电子邮件发送到其他工作表中列出的特定地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！