如果单元格与今天的日期匹配，如何通过电子邮件发送几行Google工作表 [英] How to send several rows of google sheet table via email if cell match today's date
本文介绍了如果单元格与今天的日期匹配，如何通过电子邮件发送几行Google工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
每天我必须通过电子邮件向其他收件人发送不同数量的Google工作表表格行(具体取决于出发的卡车数量).
daily I have to send different qty of google sheet table rows (it depends on how many trucks were departed) via e-mail to a few recipients.
请帮助我，举例说明如何在单元格与当前日期匹配的情况下收集几行google表，然后通过电子邮件发送它.
Could you help me, please, with an example of how to collect several rows of google table if cell match today's date and then send it via email?
例如，我需要获取具有今天日期的所有行，并通过电子邮件从A，B，C，E列发送数据.
For example, I need to grab all rows with today's date and send data from columns A, B, C, E via e-mail.
非常感谢您的帮助:)
推荐答案
让我们一步一步走.我假设A列包含日期.如果不正确，请更改它:
Let's go step by step. I'm supposing the column A contains the dates. Change it if it's not correct:
- 声明我们将为此使用的变量
function main() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = sheet.getActiveSheet();
var row_count = sheet.getLastRow() - 1;
var today = Utilities.formatDate(new Date(), "UTC", "dd.MM.yyyy"); //You can use the date format you prefer
var data = sheet.getRange("A2:E"+ (row_count + 1)).getValues(); //We get all the values from A to E and from row 2 to the last one.
countToday(sheet, today, row_count, data);
sendEmails(data);
}
- 由于我们在数据的每个位置都有每一行，因此我们将进行比较每个带有"today"的日期，我们将从中删除(拼接)行其他日子.具有有效日期的行将保留在变量中:
function countToday(sheet,today, row, data){
var lastrow = "A" + (row + 1);
var col_A = sheet.getRange('A2:'+lastrow).getValues();
for (var i = row; i >= 0; i--){
if (col_A[i] != today){
data.splice(i, 1); //
}
}
}
- 现在我们已经获得了每一行元素，我们可以发送电子邮件.我们将创建一个html表，以便于理解.Mailapp函数使其非常简单:
function sendEmails(data){
MailApp.sendEmail({
to: "example1@mail.com" + "example2@mail.com", //You can put as many emails you want
subject: "Example",
htmlBody:"<html><body>" + createTable(data)+ "</body></html>"});
}
- 要创建html表，我们只需创建一个用html编码的字符串即可，工作表中各列的名称.然后我们制作一个表数组，在这里，我们将从逗号分隔的
data中分离出每个元素(即每个工作表单元格)，然后将其添加到变量的末尾.</td>将为我们创建行.
- To create the html table, we just make a string coded in html with
the names of the columns from the Sheet. Then we make a table array,
where we will split each element from
dataseparated by coma (that's each sheet cell) and just add it to the end of the variable. < /td> will create the rows for us.
function createTable(data){
var cells = [];
var table = "<html><body><br><table border=1><tr><th>Date</th><th>Column B</th><th>Column C</th><th>Column D</th><th>Column E</tr></br>";
for (var i = 0; i < data.length; i++){
cells = data[i].toString().split(",");
table = table + "<tr></tr>";
for (var u = 0; u < cells.length; u++){
table = table + "<td>"+ cells[u] +"</td>";
}
}
table=table+"</table></body></html>";
return table;
}
这篇关于如果单元格与今天的日期匹配，如何通过电子邮件发送几行Google工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
