当用户发送或编辑时，如何制作通过电子邮件发送PDF的脚本 [英] How to make a script that sends via Email a PDF when the user sends or edits

查看：48
本文介绍了当用户发送或编辑时，如何制作通过电子邮件发送PDF的脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是上一个问题的附录:

这是电子表格的外观(我为列枚举添加了第二行):

<身体>
时间戳Direcciónde correoelectrorónico姓氏名字 Year 型号生日(1)汽车(1)添加新行?生日(2)汽车(2)添加新行?生日(3)汽车(3)何时
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14
2/04/2021 20:30:53 example1@gmail.com A F1 2005 Z 15/03/2008 MM Y 12/12/1996 - N 3/05/2009
2/04/2021 20:31:05 example2@gmail.com B F2 2006 X 3/02/2005 WW N 24/08/2000
3/04/2021 21:40:04 example3@gmail.com C F3 2018 Y - TT Y 3/03/2004 54 Y - 43 24/12/2019

感谢鲁宾的答案我可以处理一些字段.到目前为止，这是我的代码:

  function onSubmit(e){//函数取自https://medium.com/swlh/hacking-it-generate-pdfs-from-google-forms-3ca4fcc5a0aaconst rg = e.range;const sh = rg.getSheet();const值= e.range.getValues().flat();const cEmail = e.namedValues ['Direcciónde correoelectrorónico'] [0]吗?e.namedValues ['Direcciónde correoelectrorónico'] [0]:值[1]；const cSurname = e.namedValues ['Surname'] [0]吗?e.namedValues ['Surname'] [0]:值[2]；const cFirstName = e.namedValues ['名字'] [0]?e.namedValues ['名字'] [0]:值[3]；const cYear = e.namedValues ['Year'] [0]吗?e.namedValues ['Year'] [0]:值[4]；const cModel = e.namedValues ['Model'] [0]吗?e.namedValues ['Model'] [0]:值[5]；//从https://stackoverflow.com/a/66909780/11617040提取日期，月份和年份const monthNames = ["1月"，"2月"，"3月"，"4月"，"5月"，"6月"，"7月"，"8月"，"9月"，"；十月"，十一月"，十二月"]；//生日1var dateStrBirthday1 = e.namedValues ['Birthday(1)'] [0]吗?e.namedValues ['Birthday(1)'] [0]:values [6];var dateArrBirthday1 = dateStrBirthday1.split("/");var newDateBirthday1 = dateArrBirthday1 [1] +"/"；+ dateArrBirthday1 [0] +"/"+ dateArrBirthday1 [2];var dateBirthday1 =新的Date(newDateBirthday1);var cBDay1 = dateBirthday1.getDate();var cBMonth1 = dateBirthday1.getMonth();//要使用带字母的月份，请写monthNames [cMonth]var cBYear1 = dateBirthday1.getFullYear();//生日2var dateStrBirthday2 = e.namedValues ['Birthday(2)'] [0]吗?e.namedValues ['Birthday(2)'] [0]:values [9];var dateArrBirthday2 = dateStrBirthday2.split("/");var newDateBirthday2 = dateArrBirthday2 [1] +"/"+ dateArrBirthday2 [0] +"/"+ dateArrBirthday2 [2];var dateBirthday2 =新的Date(newDateBirthday2);var cBDay2 = dateBirthday2.getDate();var cBMonth2 = dateBirthday2.getMonth();//要使用带字母的月份，请写monthNames [cMonth]var cBYear2 = dateBirthday2.getFullYear();//生日3var dateStrBirthday3 = e.namedValues ['Birthday(3)'] [0]吗?e.namedValues ['Birthday(3)'] [0]:values [12];var dateArrBirthday3 = dateStrBirthday3.split("/");var newDateBirthday3 = dateArrBirthday3 [1] +"/"；+ dateArrBirthday3 [0] +"/"+ dateArrBirthday3 [2];var dateBirthday3 =新的Date(newDateBirthday3);var cBDay3 = dateBirthday3.getDate();var cBMonth3 = dateBirthday3.getMonth();//要使用带字母的月份，请写monthNames [cMonth]var cBYear3 = dateBirthday3.getFullYear();//^此代码应通过for循环完成const cCar = [];对于(var i = 1; i< = 3; i ++){//在这里，我应该做同样的事情，但是生日(i)cCar.push(e.namedValues ['Car('+ i +')'] [0]?e.namedValues ['Car('+ i +')'] [0]:values [7+(i-1)* 3])；}const cWhen = e.namedValues ['When'] [0]吗?e.namedValues ['When'] [0]:values [14];//根据文件生成新的发票//文件夹和文件IDconst folderAnswersID ='........';const folderAnswers = DriveApp.getFolderById(folderAnswersID);//将实际时间添加到文件名今天的var =新的Date();const newFileName ='示例-'+ cSurname +''+ cFirstName +''+ Today.getDate()+''+(today.getMonth()+ 1)+''+ today.getFullYear()+''+今天.getHours()+'_'+今天.getMinutes();const temapleFileID ='...';//复制模板文件const newAnswersFileID = DriveApp.getFileById(temapleFileID).makeCopy(newFileName，folderAnswers).getId();//将发票正文放入变量中var document = DocumentApp.openById(newAnswersFileID);var body = document.getBody();//替换所有<>发票正文中的文字body.replaceText('< Surname>'，cSurname);body.replaceText('< FirstName>'，cFirstName);body.replaceText('< Year>'，cYear);body.replaceText('< Model>'，cModel);body.replaceText('< BDay1>'，cBDay1);body.replaceText('< BDay2>'，cBDay2);body.replaceText('< BDay3>'，cBDay3);body.replaceText('< BMonth1>'，cBMonth1);body.replaceText('< BMonth2>'，cBMonth2);body.replaceText('< BMonth3>'，cBMonth3);body.replaceText('< BYear1>'，cBYear1);body.replaceText('< BYear2>'，cBYear2);body.replaceText('< BYear3>'，cBYear3);对于(var i = 1; i< = 3; i ++){body.replaceText('< Car'+ i +'>'，cCar [i-1]);}body.replaceText('< When>'，cWhen);document.saveAndClose();//来自https://stackoverflow.com/a/66862676/11617040var docblob = document.getAs('application/pdf').setName(newFileName +'.pdf');var dupdocs = folderAnswers.getFilesByName(newFileName);while(dupdocs.hasNext()){dupdocs.next().setTrashed(true);}var file = folderAnswers.createFile(docblob);var emailTitle ='您的答案已提交！';var emailBody ='亲爱的< b>'+ cFirstName +'</b>:< br>< br>文档< b>'+ newFileName +'</b>已正确提交.请参阅附件.';var附件= DriveApp.getFileById(newAnswersFileID);GmailApp.sendEmail(电子邮件，emailTitle，"，{htmlBody:emailBody，附件:[attachment.getAs(MimeType.PDF)]});}

我也有2个触发器:

  • 提交时:当用户按下发送表单"时.
  • 当用户在按下发送表单"后编辑其表单时.

我在代码上标记了2个问题.存在两种类型的字段".我需要关心的:

  1. 非重复字段":姓氏，名字，年份，型号，时间.这些字段不需要不需要 for 循环，因此代码不会太长:
    • 非日期字段":姓氏，名字，年份.它们并不特殊.
    • 日期字段":时间.这是Google表单上的日期字段，但没什么特别的.
  2. 重复字段":日，月，年，汽车.这些字段要做需要 for 循环，因此代码不会太长:
    • 非日期字段": Car .我为此字段创建了一个 for 循环.
    • 日期字段":日，月，年.这些字段是日期"字段，但是需要将它们分开，所以我分别有几天，几个月和几年.在上一个问题中解决了这个问题，但是我不知道如何为此循环创建一个 for 循环.

我的问题是:我们如何处理用户点击发送表单"后可以编辑其提交的表单?还不对列标题进行硬编码并关心分解Date字段并以一种奇特的方式使用 for 循环吗?

如果 for 循环问题很难回答，您可以忘掉它，我需要尽快实施用户所做的修改.

解决方案

方法1

要让接收PDF的用户可以编辑以前的表单提交，可以使用 getEditResponseUrl()方法包括为其表单响应生成的编辑URL.

例如，如果您要修改文档以包含标签，则可以按如下所示插入URL:

 函数onSubmit(e){/*剪断*/var myFormId ="1234567890abcdefghijklmn";//替换为您的表单IDvar allResponses = FormApp.openById(myFormId).getResponses();var lastResponse = allResps [allResps.length-1];var editUrl = lastResponse.getEditResponseUrl();body.replaceText('< editURL>'，editUrl);/*剪断*/}

该代码故意冗长，以使步骤清晰明了；您可以将其合并为两行.

上面的代码获得了最后响应的编辑URL ，这可能是一个问题...

方法2

如果您在短时间内有大量提交，则最后一个提交可能不是触发代码的提交.那么方法1不是推荐的方法.

相反，您将必须使用一些标识符(例如，时间戳记甚至是用户名)来获取与事件对象中的信息相关的特定响应.

您可能还需要对格式化时间戳进行一些工作，以便进行比较.

例如，

 函数onSubmit(e){/*剪断*/var myFormId ="1234567890abcdefghijklmn";//替换为您的表单ID//处理时间戳var timestampParts = e.namedValues.Timestamp.toString().split(");var tParts = timestampParts [1] .split(:"));var dParts = timestampParts [0] .split("/");var tstamp =新日期(dParts [2]，dParts [1] -1，dParts [0]，tParts [0]，tParts [1]，tParts [2])；var tEarlier = new Date(tstamp);//提前3秒获取时间tEarlier.setSeconds(tstamp.getSeconds()-3);//调整"3"达到适当的价值//从tEarlier获得所有响应var allResponses = FormApp.openById(myFormId).getResponses(tEarlier);//循环响应，并检查事件对象的时间戳和用户名与响应项的时间戳和用户名是否匹配for(让i = 0; i< allResponses.length; i ++){if(Date.parse(allResponses [i] .getTimestamp())=== Date.parse(tstamp)&&e.namedValues [电子邮件地址"] === allResponses [i] .getRespondentEmail()){//比赛！抓住编辑URLvar editUrl = allResps [i] .getEditResponseUrl();}别的{//不匹配！发生错误(例如事件物件遗失，被触发两次等)var editUrl =未找到";//登录某处进行调查}}body.replaceText('< editURL>'，editUrl);/*剪断*/

注意事项:

  • 如果不小心，时间戳可能会很麻烦.我必须执行上述操作，因为时间戳来自Forms，格式为dd/mm/yy，但是JS使用yy/mm/dd(至少对我来说-它可能会因您的语言环境而异).
  • 不是必需的，但建议使用第二个标识符，例如电子邮件地址.我以前曾见过Forms的奇怪行为，即事件触发两次或被延迟等，因此进行检查对于确保某人不会得到别人的编辑URL非常重要，以防万一您无法控制某些问题.

一些参考

有从表单提交中获取最新回复的替代方法

有关详细信息，请参见 FormResponse API参考(以及示例).

This is an addenum of a previous question: Problems generating PDFs from Google Forms after it is submitted

I am creating a Google Script that fills a Google Doc template based on the fields of a Google Forms and sends the generated PDF via email to the user. Now I need to implement the possibility that the user can edit its submit after he press on "Send Form".

This is the Doc template example:

This is how the Spreadsheet looks like (2nd row is added by me for columns enumeration):

Time stamp Dirección de correo electrónico Surname First name Year Model Birthday (1) Car (1) Add a new row? Birthday (2) Car (2) Add a new row? Birthday (3) Car (3) When
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14
2/04/2021 20:30:53 example1@gmail.com A F1 2005 Z 15/03/2008 MM Y 12/12/1996 - N 3/05/2009
2/04/2021 20:31:05 example2@gmail.com B F2 2006 X 3/02/2005 WW N 24/08/2000
3/04/2021 21:40:04 example3@gmail.com C F3 2018 Y - TT Y 3/03/2004 54 Y - 43 24/12/2019

Thanks to Rubén's answer I could handle some fields. This is my code so far:

function onSubmit(e) { // Function taken from https://medium.com/swlh/hacking-it-generate-pdfs-from-google-forms-3ca4fcc5a0aa
  const rg = e.range;
  const sh = rg.getSheet();

  const values =  e.range.getValues().flat();

  const cEmail = e.namedValues['Dirección de correo electrónico'][0] ? e.namedValues['Dirección de correo electrónico'][0] : values[1];
  const cSurname = e.namedValues['Surname'][0] ? e.namedValues['Surname'][0] : values[2];
  const cFirstName = e.namedValues['First name'][0] ? e.namedValues['First name'][0] : values[3];
  const cYear = e.namedValues['Year'][0] ? e.namedValues['Year'][0] : values[4];
  const cModel = e.namedValues['Model'][0] ? e.namedValues['Model'][0] : values[5];

  // EXTRACT DAY, MONTH AND YEAR from https://stackoverflow.com/a/66909780/11617040

  const monthNames = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];

  // Birthday 1
  var dateStrBirthday1 = e.namedValues['Birthday (1)'][0] ? e.namedValues['Birthday (1)'][0] : values[6];
  var dateArrBirthday1 = dateStrBirthday1.split("/");
  var newDateBirthday1 = dateArrBirthday1[1] + "/" + dateArrBirthday1[0] + "/" + dateArrBirthday1[2];
  var dateBirthday1 = new Date(newDateBirthday1);
  var cBDay1 = dateBirthday1.getDate();
  var cBMonth1 = dateBirthday1.getMonth(); // To use month with letters write monthNames[cMonth]
  var cBYear1 = dateBirthday1.getFullYear();
  // Birthday 2
  var dateStrBirthday2 = e.namedValues['Birthday (2)'][0] ? e.namedValues['Birthday (2)'][0] : values[9];
  var dateArrBirthday2 = dateStrBirthday2.split("/");
  var newDateBirthday2 = dateArrBirthday2[1] + "/" + dateArrBirthday2[0] + "/" + dateArrBirthday2[2];
  var dateBirthday2 = new Date(newDateBirthday2);
  var cBDay2 = dateBirthday2.getDate();
  var cBMonth2 = dateBirthday2.getMonth(); // To use month with letters write monthNames[cMonth]
  var cBYear2 = dateBirthday2.getFullYear();
  // Birthday 3
  var dateStrBirthday3 = e.namedValues['Birthday (3)'][0] ? e.namedValues['Birthday (3)'][0] : values[12];
  var dateArrBirthday3 = dateStrBirthday3.split("/");
  var newDateBirthday3 = dateArrBirthday3[1] + "/" + dateArrBirthday3[0] + "/" + dateArrBirthday3[2];
  var dateBirthday3 = new Date(newDateBirthday3);
  var cBDay3 = dateBirthday3.getDate();
  var cBMonth3 = dateBirthday3.getMonth(); // To use month with letters write monthNames[cMonth]
  var cBYear3 = dateBirthday3.getFullYear();
  // ^ This code should be done with a for loop


  const cCar = [];

  for (var i=1; i<=3; i++) {
    // Here I should do the same but for Birthday (i)
    cCar.push(e.namedValues['Car (' + i + ')'][0] ? e.namedValues['Car (' + i + ')'][0] : values[7+(i-1)*3]);
  }

  const cWhen = e.namedValues['When'][0] ? e.namedValues['When'][0] : values[14];

  //Build a new invoice from the file
  //Folder and file IDs
  const folderAnswersID = '........';
  const folderAnswers = DriveApp.getFolderById(folderAnswersID);

  // Add actual time to the file name
  var today = new Date();
  const newFileName = 'Example - ' + cSurname + ' ' + cFirstName + ' ' + today.getDate() + ' ' + (today.getMonth()+1) + ' ' + today.getFullYear() + ' ' + today.getHours() + '_' + today.getMinutes();

  const temapleFileID = '...';
  
  //Make a copy of the template file
  const newAnswersFileID = DriveApp.getFileById(temapleFileID).makeCopy(newFileName, folderAnswers).getId();
  
  //Get the invoice body into a variable
  var document = DocumentApp.openById(newAnswersFileID);
  var body = document.getBody();

  //Replace all the < > text in the invoice body
  body.replaceText('<Surname>', cSurname);
  body.replaceText('<FirstName>', cFirstName);
  body.replaceText('<Year>', cYear);
  body.replaceText('<Model>', cModel);

  body.replaceText('<BDay1>', cBDay1);
  body.replaceText('<BDay2>', cBDay2);
  body.replaceText('<BDay3>', cBDay3);
  body.replaceText('<BMonth1>', cBMonth1);
  body.replaceText('<BMonth2>', cBMonth2);
  body.replaceText('<BMonth3>', cBMonth3);
  body.replaceText('<BYear1>', cBYear1);
  body.replaceText('<BYear2>', cBYear2);
  body.replaceText('<BYear3>', cBYear3);

  for (var i=1; i<=3; i++) {
    body.replaceText('<Car' + i + '>', cCar[i-1]);
  }
  
  body.replaceText('<When>', cWhen);

  document.saveAndClose();
  
  // From https://stackoverflow.com/a/66862676/11617040
  var docblob=document.getAs('application/pdf').setName(newFileName + '.pdf');
  var dupdocs=folderAnswers.getFilesByName(newFileName);
  while(dupdocs.hasNext()) {
    dupdocs.next().setTrashed(true);
  }
  var file=folderAnswers.createFile(docblob);

  var emailTitle = 'Your answer was submitted!';
  var emailBody = 'Dear <b>' + cFirstName + '</b>:<br><br>The document <b>' + newFileName + '</b> was submitted correctly. Please see the attachment.';

  var attachment = DriveApp.getFileById(newAnswersFileID);
  GmailApp.sendEmail(
    cEmail,
    emailTitle,
    "",
    {
      htmlBody: emailBody,
      attachments: [attachment.getAs(MimeType.PDF)]
    }
  );
}

I also have 2 triggers:

  • On submit: When the user press "Send Form".
  • Edit: When the user edits its Form after he press "Send Form".

I have marked 2 problems on the code. There are 2 types of "Fields" that I need to care about:

  1. "Non-repetitive fields": Surname, First name, Year, Model, WHEN. These fields do not require a for loop so the code is not so long:
    • "Non-date fields": Surname, First name, Year. They aren't special.
    • "Date fields": WHEN. This is a Date field on Google Forms but nothing special.
  2. "Repetitive fields": Day, Month, Year, Car. These fields do require a for loop so the code is not so long:
    • "Non-date fields": Car. I created a for loop for this field.
    • "Date fields": Day, Month, Year. These fields were Date fields but they need to be separated so I have days, months and years separately. This was solved in a previous question but I don't know how to make a for loop for this ones.

My question is: How can we work with a Form where the user can edit its submit after he press on "Send Form" and also not hardcoding the column headers and caring about descomposing Date fields and using for loops in a fancy way?

If the for loops question is too difficult to answer, you can forget about it, I need to implement the edits made by the user ASAP.

解决方案

Approach 1

To allow the user receiving the PDF the possibility to edit their previous Form submission, you can include the edit URL generated for their form response using the getEditResponseUrl() method.

For example, if you were to modify your document to include a tag then you could insert the URL as follows:

function onSubmit(e){
  /*
  snip
  */

  var myFormId = "1234567890abcdefghijklmn"; // replace with your Form ID
  var allResponses = FormApp.openById(myFormId).getResponses();
  var lastResponse = allResps[allResps.length-1];

  var editUrl = lastResponse.getEditResponseUrl();
  
  body.replaceText('<editURL>', editUrl);
  
  /*
   snip
  */    

}

The code is deliberately verbose to make the steps clear; you could combine it into 2 lines.

This code above gets the last response's edit URL which might be an issue...

Approach 2

If you have a lot of submissions in a short time, it's possible the last submission is not the submission that triggered the code. Then Approach 1 is not a recommended approach.

Instead, you would have to get the specific response related the information in the event object, e, using some identifiers such as the timestamp and perhaps even a username.

You may also have to put some work into formatting timestamps in order to compare them.

For example,

function onSubmit(e){
  /*
  snip
  */

  var myFormId = "1234567890abcdefghijklmn"; // replace with your Form ID

  // handle the timestamp
  var timestampParts = e.namedValues.Timestamp.toString().split(" ");
  var tParts = timestampParts[1].split(":");
  var dParts = timestampParts[0].split("/");
  var tstamp = new Date(dParts[2], dParts[1]-1, dParts[0], tParts[0], tParts[1], tParts[2]);
  var tEarlier = new Date(tstamp);
  
  // get the time 3 seconds earlier, tEarlier
  tEarlier.setSeconds(tstamp.getSeconds() - 3); // tune "3" to appropriate value

  // get all responses since tEarlier
  var allResponses = FormApp.openById(myFormId).getResponses(tEarlier);

  // loop over the responses and check the event object timestamp and username match the response item timestamp and username
  for (let i=0;i<allResponses.length;i++){
    if( Date.parse(allResponses[i].getTimestamp()) === Date.parse(tstamp) 
        && e.namedValues["Email address"] === allResponses[i].getRespondentEmail()
    ){
      // Match! Grab the edit URL
      var editUrl = allResps[i].getEditResponseUrl();    
    }else{
      // Not a match! Something went wrong (e.g. event object missing, triggered twice, etc.)
      var editUrl = "Not found";
      // log somewhere and investigate 
    } 
  }

body.replaceText('<editURL>', editUrl);

/*
snip
*/

Points of consideration:

  • Timestamps can be troublesome if you are not careful. I had to do the above because the timestamp comes from Forms in the format dd/mm/yy but JS uses yy/mm/dd (for me at least - it may vary based on your locale).
  • A second identifier like email address is not necessary but recommended. I've seen strange behaviour with Forms previously where events triggered twice, or were delayed etc., so having this check can be important to ensure the someone doesn't get someone else's edit URL in case there is some issue beyond your control.

Some Refs

There are alternative approaches to getting the last response from a form submission.

See FormResponse API reference for more information (and examples) regarding the methods.

这篇关于当用户发送或编辑时，如何制作通过电子邮件发送PDF的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆