收到表单回复后，Google表格脚本即可通过电子邮件发送 [英] Google sheets script to email when form response received
本文介绍了收到表单回复后，Google表格脚本即可通过电子邮件发送的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张 https://docs.google.com /spreadsheets/d/1NcCZ5Y7YrL63e2i6osQC28MOG3_Mq4fLPUANFrxxYNE/edit#gid = 725463476 有多种形式供稿.我需要一个脚本，当收到新的响应时，它将从另一个工作表中提取数据并通过电子邮件发送.我将需要一个脚本，可以针对每个不同的表单响应进行修改.因此，如果可能的话，可以在脚本中调用一个表来观察和提取数据，以便在脚本中为每个不同的表更改表名.我的实际工作表中大约有7或8个.我可能还需要一些html吗?也许它可以从纸张上拉出颜色/尺寸/粗体?如果不是的话，我将需要知道如何添加字体大小/颜色/粗体，如果它不能从工作表中拉出字体，甚至会更加棘手，那么我将需要基于通过和失败字样的条件颜色.绿色表示通过，红色表示失败.我将进进出出的工作表随意留在工作表等上.只是一个示例，所以输入.希望与可以帮助我的人在工作表中聊天！
I have a sheet https://docs.google.com/spreadsheets/d/1NcCZ5Y7YrL63e2i6osQC28MOG3_Mq4fLPUANFrxxYNE/edit#gid=725463476 with multiple forms feeding it. I need a script that when a new response is received it will pull the data from a different sheet and email it. I will need a script that I can modify for each different form response. So if possible have a sheet to watch and sheet where the data is pulled form called out in the script so I can just change the sheet name in the script for each different one. I have about 7 or 8 of these in my real sheet. I will also need some html probably in it? Maybe it can pull the colors/size/bold ect from the sheet? If not I will need to know how to add font size/color/bold and even trickier if it cant pull this from the sheet I will need conditional colors based on the words pass and fail. Green for pass and red for fail. I will be in and out of the sheet fell free to leave notes on the sheet ect. It's just a sample so type away. Hope to chat in the sheet with someone who can help me out!
谢谢！ 斯蒂芬
推荐答案
从Google表单发送电子邮件提交
这是一个您可以用来创建触发器的菜单，它仅允许您一次创建一个触发器.
Sending Emails from Google Forms Submit
This is a menu that you can use to create your trigger it only allows you to create one trigger at a time.
function coopermenu() {
SpreadsheetApp.getUi().createMenu('Cooper Menu')
.addItem('Create Trigger', 'createFormSubmitTrigger')
.addToUi();
}
这将设置您的触发器
function createFormSubmitTrigger(funcname) {
var funcname=funcname||'form1Submit';
if(!isTrigger(funcname)) {
ScriptApp.newTrigger(funcname).forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
}
}
这是触发器与升级请求一起运行的功能:
And this is the function that the trigger runs along with your upgrade requests:
function form1Submit(e) {
Logger.log(JSON.stringify(e));
Logger.log('Sheet: %s',e.range.getSheet().getName());
var sheetname=e.range.getSheet().getName();
switch(sheetname) {
case 'Form Responses 1':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','To:',getGlobal('form1Email'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','Subject:',getGlobal('form1Subject'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','TimeStamp:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Location:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[4]),RoG(e.values[4]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[7]),RoG(e.values[7]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form1Email'), getGlobal('form1Subject'), '', {htmlBody:html});
}
break;
case 'Form Responses 2':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','To:',getGlobal('form2Email'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','Subject:',getGlobal('form2Subject'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','TimeStamp:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Location:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form2Email'), getGlobal('form2Subject'), '', {htmlBody:html});
}
break;
}
}
function RoG(s) {
if(s) {
if(s=='Pass') {
return '<span style="color:#00ff00;font-weight:bold">Pass</span';
}else if(s=='Fail') {
return '<span style="color:#ff0000;font-weight:bold">Fail</span>';
}
}
return s;
}
function RoGpf(s) {
if(s) {
if(s=='Pass') {
return '<span style="color:#00ff00;font-weight:bold">Pass/Fail</span';
}else if(s=='Fail') {
return '<span style="color:#ff0000;font-weight:bold">Pass/Fail</span>';
}
}
return s;
}
这些是我使用的一些实用程序:
These are some utilities that I used:
triggers.gs:
triggers.gs:
//Filename: triggers.gs
function deleteTrigger(triggerName){
var triggers=ScriptApp.getProjectTriggers();
for (var i=0;i<triggers.length;i++){
if (triggerName==triggers[i].getHandlerFunction()){
ScriptApp.deleteTrigger(triggers[i]);
}
}
}
function isTrigger(funcName){
var r=false;
if(funcName){
var allTriggers=ScriptApp.getProjectTriggers();
for(var i=0;i<allTriggers.length;i++){
if(funcName==allTriggers[i].getHandlerFunction()){
r=true;
break;
}
}
}
return r;
}
function deleteAllTriggers(){
var triggers=ScriptApp.getProjectTriggers();
for (var i=0;i<triggers.length; i++){
ScriptApp.deleteTrigger(triggers[i]);
}
}
function showMeProjectTriggers(){
var html='';
var br='<br />';
var triggers=ScriptApp.getProjectTriggers();
for(var i=0;i<triggers.length;i++){
var name=triggers[i].getHandlerFunction();
html+=br + 'triggers[' + i + '] = ' + name;
}
html+=br + '<input type="button" value="Close" onClick="google.script.host.close();" />';
var userInterface=HtmlService.createHtmlOutput(html).setWidth(800).setHeight(450);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Project Handler Functions');
}
globals.gs
globals.gs
function getGlobals(){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
var rg=sh.getRange(1,1,sh.getLastRow(),2);
var vA=rg.getValues();
var g={};
for(var i=0;i<vA.length;i++){
g[vA[i][0]]=vA[i][1];
}
return g;
}
function setGlobals(dfltObj){
var dfltH=Object.keys(dfltObj).length;
if(dfltObj){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
var rg=sh.getRange(1,1,dfltH,2);
var vA=rg.getValues();
for(var i=0;i<dfltH;i++){
vA[i][1]=dfltObj[vA[i][0]];
}
rg.setValues(vA);
}
}
function getGlobal(name){
return getGlobals()[name];
}
function setGlobal(name,value){
var curObj=getGlobals();
if(!curObj.hasOwnProperty(name)) {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
sh.appendRow([name,value])
}else{
curObj[name]=value;
setGlobals(curObj);
}
}
function cleanGlobals() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
if(sh.getLastColumn()>2) {
sh.getRange(1,3,sh.getLastRow(),sh.getLastColumn()-2).clearContent();
}
var rg=sh.getRange(1,1,sh.getLastRow(),2);
var vA=rg.getValues();
for(var i=0;i<vA.length;i++) {
if(!vA[i][0] || !vA[i][1]) {
var userInterface=HtmlService.createHtmlOutput('Globals Sheet Requires Maintenance...Do it know.' + ' Check Row ' + Number(i + 1));
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Global Maintenance Required');
break;
}
}
}
- 触发器生成器
- 表单事件对象
- Trigger Builder
- Form Event Object
- 创建globals.gs并粘贴全局脚本.
- 创建triggers.gs并粘贴触发器脚本.
- 创建cooper.gs并粘贴其他脚本.我这样做是因为其他人正在进入代码，他们只是删除了我的脚本并过度编写了他们的脚本.我心里不是很友善.
- 创建全局表. A1 ="form1Subject"，A2 ="form1Email"，B1 =您的主题"和B2 =您的电子邮件".
- 进入脚本编辑器并运行coopermenu()，这将为您提供一个权限对话框.您需要授权才能运行脚本.它会为您找出所有范围.
- 然后转到新菜单，然后按创建触发器.一次只能创建一个.
- 转到实时表单并填写一个表单
- 阅读您的电子邮件，如下图所示.
- 由于我们在使用伪造的formSubmit时遇到了问题，因此我使用了以下逻辑来阻止它们
if(e.values && e.values[1] && e.values[2]) {，这意味着必须填写您的姓名和位置问题才能接收电子邮件. - Create globals.gs and paste globals script.
- Create triggers.gs and paste triggers scripts.
- Create cooper.gs and paste in the other scripts. I did this because other people were getting into code and they just deleted my scripts and over wrote theirs. Not very kind in my mind.
- Create a Globals Sheet. A1='form1Subject', A2='form1Email', B1='Your Subject', and B2='Your email'.
- The go into the script editor and run coopermenu() and this will give your a permissions dialog. You need to authorize inorder to run the script. It figures out all of the scopes for you.
- Then go to the new menu and press create trigger. It will allow you to create only one at a time.
- Go to the live form and fill one out
- Read your email it will look something like this image below.
- Since we have been having problems with spurious formSubmits I used the follow logic to prevent them
if(e.values && e.values[1] && e.values[2]) {this means that your Name and Location question must be filled in in order to receive an email.
说明
我已更改了您要求的间距，这是现在电子邮件的外观.
I made the spacing changes you requested and here's what the emails look like now.
这篇关于收到表单回复后，Google表格脚本即可通过电子邮件发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
