我是编码新手,希望自动发送带有附件的电子邮件。基本上,我想做的是向电子表格中的特定电子邮件地址发送一条消息和Google Drive附件。每个附件(谷歌驱动器链接(对于每个电子邮件地址都是不同的,我在电子表格上列出了所有内容。
电子表格由三张表组成,我只想从第一张表中获得信息(称为完整数据(。柱子的结构如下:
- A1全名
- B1名字
- C1姓氏
- D1电子邮件地址
- E1文件附件(谷歌驱动器链接(
- F1日期
- G1状态
- H1消息
- I1是否发送电子邮件
我想发送一封带有模板的电子邮件,模板以"Hello{Full Name}"开头,并在下面的行中启动消息。
这是我现在的代码:
// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';
/**
* Sends non-duplicate emails with data from the current spreadsheet.
*/
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var numRows = sheet.getLastRow() - startRow + 1; // Number of rows to process
var startCol = 1;
var numCols = 4; // Number of columns to process (should include column D)
// Fetch the range of cells A2:D
var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[3]; // Third column
var message = row[7]; // Eigth column
var emailSent = row[8]; // Ninth column
if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
var subject = 'Sending emails from a Spreadsheet';
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 8).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
我意识到我没有为附件设置变量。附件应取自E列(对应于行(。例如,A2中的John Apple在E2中有附件X,而A3中的Mick Orange在E3中有附件Y,等等……数据的范围一直下降到130行,但如果有数据(无论行数如何(之前总是能得到范围,那就太好了。
有人知道怎么做吗?
提前感谢!!
如果我理解正确,您当前的问题与之前的问题不同之处在于:
- 消息正文以"Hello{Full Name}"开头
- 您要查找的不是与图纸中名称相同的文件,而是具有特定URL的文件
- 要从中提取数据的列不相同
考虑到所有这些,我重新设计了我在您上一个问题中提出的解决方案,使其达到您现在所假装的效果(阅读内联注释,一步一步地查看代码在做什么(:
var EMAIL_SENT = "EMAIL_SENT";
function sendEmails2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Full Data");
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow() - startRow + 1; // Number of rows to process
var startCol = 1;
var numCols = 9; // Number of columns to process (should include column D)
// Fetch the range of cells A2:D
var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; i++) {
var row = data[i];
var emailAddress = row[3];
var fullname = row[0];
var messageBody = row[7];
var message = "Hello ".concat(fullname, "n", messageBody); // Creating message body
var emailSent = row[8];
var fileURL = row[4];
var fileId = fileURL.match(/[-w]{25,}/); // Extracts file id from file url
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Sending emails from a Spreadsheet";
var file = DriveApp.getFileById(fileId);
MailApp.sendEmail(emailAddress, subject, message, {
attachments: [file], // No need to use getAs if your file is already a PDF
name: 'Custom email Team'
});
sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
DriveApp没有getFileByUrl
方法,因此要获取文件,代码首先使用regex从url中提取文件id,然后调用getFileById。
如果这对你有用,请告诉我。