如何使用应用程序脚本向谷歌工作表的编辑发送电子邮件

Question

我正试图编写一个函数，根据单元格的值向谷歌工作表的编辑发送电子邮件。我在实现这一点时遇到了一些问题，我很乐意提供一些建议。在某些情况下，表格是表格的后端，我们询问填写表格的人在我们的活动之前是否需要任何特殊的住宿。

我成功地设置了一个向所有者发送电子邮件的功能。问题在于；所有者；也就是创建表单的人，只会制作表单，而不是自己运行活动。

他们增加了管理活动的人员作为编辑。因此，我希望当与会者需要特殊住宿时，脚本能通过电子邮件发送给编辑。

这是我给所有者发电子邮件的内容：

function accommodateNotify() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Form Responses 1');
var ownerEmail = ss.getOwner().getEmail();
var startingRow = 6;
Logger.log(ownerEmail);
var name = ss.getName();
Logger.log(name);
var url = ss.getUrl();
Logger.log(url);

var lastRow = getLastNonEmptyRow(sheet.getRange(1, 83, sheet.getLastRow(), 1));
Logger.log(lastRow);
var needsAccommodation = sheet.getRange(lastRow, 83).getValue();
Logger.log(needsAccommodation);
var requiredAccommodation = sheet.getRange(lastRow, 85).getValue();

if (needsAccommodation == "Yes") {
var message = "An attendee for " + name + " " + " has stated they need accommodations. Please review: " +url;
var subject = "Accommodation Request Alert";
Logger.log("sending email...n" + "  subject: " + subject + "n  message: " + message);
GmailApp.sendEmail(ownerEmail, subject, message);
}
}

使用getEditors的问题是它返回一个数组(因为它们可能是多个编辑器(。你有什么指导吗？我会如何把它发给编辑？

我设法把这些放在一起，但出于某种原因，它给编辑发了两次电子邮件。。。

function accommodateNotify() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Form Responses 1');
var editors = ss.getEditors();
for (var i = 0; i < editors.length; i++) {
Logger.log(editors[i].getEmail());
//var ownerEmail = ss.getOwner().getEmail();
var startingRow = 2;
var name = ss.getName();
Logger.log(name);
var url = ss.getUrl();
Logger.log(url);
var lastRow = getLastNonEmptyRow(sheet.getRange(1, 29, sheet.getLastRow(), 1));
Logger.log(lastRow);
var needsAccommodation = sheet.getRange(lastRow, 29).getValue();
Logger.log(needsAccommodation);
var requiredAccommodation = sheet.getRange(lastRow, 85).getValue();
if (needsAccommodation == "Yes") {
var message = "A student for " + name + " " + " has stated they need accommodations. Please review: " +url;
var subject = "Accommodation Request Alert";
Logger.log("sending email...n" + "  subject: " + subject + "n  message: " + message);
GmailApp.sendEmail(editors, subject, message);
}
}
}

Answer 1

根据我从你的问题中了解到的情况，你的问题是你想向编辑器发送一封电子邮件，editors是一个包含所有编辑器的数组。此外，请注意GmailApp.sendEmail((一次只能向单个收件人发送电子邮件。

下面，我建议对您的脚本进行更改，对所有编辑器进行迭代，以便能够向每个编辑器发送电子邮件。该代码具有不言自明的注释。

还要注意，getEditors返回的是User对象，而不是电子邮件，因此必须使用getEmail((获取这些对象。

if (needsAccommodation == "Yes") {
var message = "A student for " + name + " " + " has stated they need accommodations. Please review: " +url;
var subject = "Accommodation Request Alert";
Logger.log("sending email...n" + "  subject: " + subject + "n  message: " + message);

// iterate over all the editors that are in the array
for(i=0;i<editors.length;i++){
// send an email with the same message and subject to each of the editors
GmailApp.sendEmail(editors[i].getEmail(), subject, message);
}
}