Google表格内容发送电子邮件（布局） [英] Google Sheets content to email (lay out)
问题描述
我在Google表格中列出了一系列活动。这是一张包含4列数据和几十行的工作表。当活动即将在下周举行时，我想自动发送一封电子邮件。
我在Google表格中为即将到来的活动筛选了一个标签。所以我只需要将这些数据复制到电子邮件中即可。
它适用于以下代码：
var first = ss.getSheetByName（Sheet2）;
first.activate（）;
var sheet = SpreadsheetApp.getActiveSheet（）;
var data = first.getDataRange（）。getValues（）;
//生成内容
var body = [];
for（var x = 0; x
）;
body.push（/ n+这就是所有的下周）;
MailApp.sendEmail（mailaddress，subject，Upcoming events+\\\
+\\\
+ body）
问题在于它看起来很糟糕。在邮件中，它看起来像这样：
CAR，星期四2016年3月24日08:00:00 GMT + 0100（CET）， FY，c
，INBEV，Wed Apr 27 2016 09:00:00 GMT + 0200（CEST），AGM，c
日期信息太长，并且单元格之间没有间隔。我希望看到这样的情况：
Car * Mar 24 * FY（c）
INBEV * Apr 27 * AGM（c）
我对javascript和google-apps脚本有非常基本的了解。将继续的方式是什么？如何调整输出？
编辑
使用Sandy Good的建议后，它可以正常工作。除了第二行以逗号（，）开头。这不是价值的一部分，所以我不能用子字符串将它砍掉。任何人都知道为什么第二行（和第三，第四...）从表格开始以逗号开始？
edit2
这是我目前使用的。它仅从表格中获取最后一行，因为我从数组切换到文本作为输入。
函数myFunction（）{
var mail ='我的邮件地址'
var ss = SpreadsheetApp.openById（'ID code sheet'）;
//首先是活动工作表
var first = ss.getSheetByName（Sheet2）;
first.activate（）;
var sheet = SpreadsheetApp.getActiveSheet（）;
var data = first.getDataRange（）。getValues（）;
//为电子邮件正文生成内容
var body =，
thisRow，
column_1_Value，
column_2_Value，
column_3_Value，
column_4_Value;
for（var x = 0; x
column_1_Value = thisRow [0];
column_2_Value = thisRow [1];
column_3_Value = thisRow [2];
column_4_Value = thisRow [3];
column_2_Value = Utilities.formatDate（新日期（column_2_Value），GMT，MMMMM dd）;
body = ** body ** + column_1_Value +*+
column_2_Value +*+
column_3_Value +（+
column_4_Value +） +\\\
}
MailApp.sendEmail（接收邮件的邮件地址，即将发生的事件，即将发生的事件+\ n +\\\
+ body +\\\
+这就是所有的下周）
}
您可以使用 Utilities.formatDate（）方法格式化日期。您可以使用JavaScript字符串方法来更改大小写。这是一个例子，它可能不是一个完整的解决方案，但希望它显示了你如何能够完成你所需要的：
函数test（）{
var body =，
allRowsData =，
thisRow，
column_1_Value，
column_2_Value，
column_3_Value，
column_4_Value;
for（var x = 0; x
column_1_Value = thisRow [0];
column_2_Value = thisRow [1];
column_3_Value = thisRow [2];
column_4_Value = thisRow [3];
column_1_Value = column_1_Value.slice（0,1）+ column_1_Value.slice（1）.toLowerCase（）;; //更改为大写和小写
column_2_Value = Utilities.formatDate（new日期（column_2_Value），时区，MMM dd）;
body =; //在每个循环中重置
body = column_1_Value +*+
column_2_Value +*+
column_3_Value +* +
column_4_Value +*+
\\\
+/ n+下周就是这样;
allRowsData = allRowsData + body;
};
};
I have a list of events in a google sheet. It's a sheet with 4 columns of data, and a few dozen rows. When events are upcoming in the next week, I want to send out an email automatically.
I have a tab in the google sheet which filters for upcoming events. So I only need to copy this data to an e-mail.
It works with this code :
var first = ss.getSheetByName("Sheet2");
first.activate();
var sheet = SpreadsheetApp.getActiveSheet();
var data = first.getDataRange().getValues();
// Generate content
var body = [];
for (var x = 0; x <data.length; x++)
body.push(data[x] + "\n");
body.push("/n" + "That's all for next week");
MailApp.sendEmail(mailaddress, subject, "Upcoming events" +"\n" +"\n" + body)
The problem is that it looks terrible. In the mail it looks like this:
CAR,Thu Mar 24 2016 08:00:00 GMT+0100 (CET),FY,c
,INBEV,Wed Apr 27 2016 09:00:00 GMT+0200 (CEST),AGM,c
The date information is way too long, and the cells are not with spacing between. I would like to see something like this:
Car * Mar 24 * FY (c)
INBEV * Apr 27 * AGM (c)
I have very basic knowledge of javascript and google-apps script. What would be the way to proceed? How to I adjust the output?
Edit It works fine after using the suggestion by Sandy Good. Except for the second row to start with a comma (,). It's not part of the value, so I can't chop it off with substring. Anybody knows why a second (and third, fourth...) row from a sheet starts wih a comma?
edit2
This is what I am currently using. It only gets the last line from the sheet, as I switched from array to text as input. The body is not really assembled, the content is replaced all the time before it finally sends.
function myFunction() {
var mail = 'my mail address'
var ss = SpreadsheetApp.openById('ID code sheet');
// first is the active sheet
var first = ss.getSheetByName("Sheet2");
first.activate();
var sheet = SpreadsheetApp.getActiveSheet();
var data = first.getDataRange().getValues();
// Generate content for body of email
var body = "",
thisRow,
column_1_Value,
column_2_Value,
column_3_Value,
column_4_Value;
for (var x = 0; x <data.length; x++) {
thisRow = data[x];
column_1_Value = thisRow[0];
column_2_Value = thisRow[1];
column_3_Value = thisRow[2];
column_4_Value = thisRow[3];
column_2_Value = Utilities.formatDate(new Date(column_2_Value), "GMT", "MMMMM dd");
body = **body** + column_1_Value + " * " +
column_2_Value + " * " +
column_3_Value + " (" +
column_4_Value + ")" + "\n"
}
MailApp.sendEmail("mail address to receive mail", "upcoming events", "Upcoming events" +"\n" +"\n" + body + "\n" + "That's all for next week")
}
You can use the Utilities.formatDate() method to format the date. You can use JavaScript string methods, to change the case. Here is an example, it may not be a complete solution, but hopefully it shows how you can accomplish what you need:
function test(){
var body = "",
allRowsData = "",
thisRow,
column_1_Value,
column_2_Value,
column_3_Value,
column_4_Value;
for (var x = 0; x <data.length; x++) {
thisRow = data[x];
column_1_Value = thisRow[0];
column_2_Value = thisRow[1];
column_3_Value = thisRow[2];
column_4_Value = thisRow[3];
column_1_Value = column_1_Value.slice(0,1) + column_1_Value.slice(1).toLowerCase();;//Change to Upper and lower case
column_2_Value = Utilities.formatDate(new Date(column_2_Value), timeZone, "MMM dd");
body = "";//Reset on every loop
body = column_1_Value + " * " +
column_2_Value + " * " +
column_3_Value + " * " +
column_4_Value + " * " +
"\n" + "/n" + "That's all for next week";
allRowsData = allRowsData + body;
};
};
这篇关于Google表格内容发送电子邮件（布局）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
