提交Google表单后从PDF生成PDF时出现问题 [英] Problems generating PDFs from Google Forms after it is submitted
问题描述
我正在创建一个Google脚本，该脚本基于Google表单的字段填充Google Doc模板，并通过电子邮件将生成的PDF发送给用户.
在此详细说明了我执行的所有步骤: 代码工作正常.现在我需要用户在按发送表单"后，可以编辑其响应.因此，我决定选中受访者可以在提交后进行编辑".然后，我需要通过带有编辑字段的 但是我有问题.当用户编辑字段并再次按下发送表格"时，触发编辑"按钮将被取消.已激活，并显示以下错误: 如果我转到电子表格答复，则可以看到已编辑的行(因为单元格的注释为答复的人已更新此值")，并且列邮件未编辑，但仍然抛出异常. 如果从未编辑过 我可以找到一些有趣的答案: 他们似乎描述了当触发编辑"时，可以生成空白行.被激活.但是，我不知道为什么会发生这种情况，以及如何解决它，因为新用户提交答案后会自动编辑电子表格响应. 在编辑表单响应时，表单提交事件对象属性 要纠正错误 作者 P.S.代替对分配给 注意:以上内容只能防止问题中提到的错误.为了使脚本起作用，您将必须对所有字段应用相同的想法:使用 相关 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. All of the steps that I followed are explained in detail here: Hacking it: Generate PDFs from Google Forms The script (obtained from the article) is: The code works fine. Now I need that the user can edit its response after he press "Send Form" on Google Forms. So I decided to check "Respondents can edit after submit". Then I need to send the document again via However I have a problem. When the user edits a field and press, again, "Send Form", the trigger "Edit" is activated with the following error: If I go to the Spreadsheet responses I can see the edited row (because the cell has a comment "The person who responded has updated this value"), and the column mail is not edited but it stills throwing the exception. How can we solve this problem if I could find some interesting answers: They seem to describe that a blank row can be generated when the trigger "Edit" is activated. However I don't see why this could happen, and how I can solve it since the Spreadsheet Responses is automatically edited after a new user submit an answer. When a form response is edited the on form submit event object properties To fix the error by P.S. Instead of hardcoding the value assigned to NOTE: The above only will prevent the error mentioned in the question. In order to make the script work you will have to apply the same idea for all the fields: Read the missing values from the spreadsheet by using the Related 这篇关于提交Google表单后从PDF生成PDF时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ GmailApp 重新发送文档.因此，我创建了一个新触发器: Edit (来自电子表格).另一个触发器是表单提交.无法发送电子邮件:没有收件人. cEmail ，我们如何解决此问题?搜索
values 和 namedValues 仅包含值对于那些已编辑的问题.无法发送电子邮件:没有收件人，请替换
GmailApp.sendEmail(cEmail，'< subject> ;,'< body>'，{attachments:[attachment.getAs(MimeType.PDF)]})；
const receiverIdx = 1;//这是具有收件人电子邮件地址的列的从0开始的索引const收件人= cEmail?cEmail:e.range.getValues().flat()[recipientIdx]GmailApp.sendEmail(收件人，'< subject>'，'< body>'，{attachments:[attachment.getAs(MimeType.PDF)]})； recipientIdx 的值进行硬编码，您可以使用一些代码基于列标题来获取它. e.range.getValues().flat()从电子表格中读取缺少的值./p>function onSubmit(e) {
const rg = e.range;
const sh = rg.getSheet();
//Get all the form submitted data
//Note: This data is dependent on the headers. If headers, are changed update these as well.
const cName = e.namedValues['Client Name'][0];
const cEmail = e.namedValues['Client Email'][0];
const cAddress = e.namedValues['Client Address'][0];
const cMobile = e.namedValues['Client Mobile'][0];
const sendCopy = e.namedValues['Send client a copy?'][0];
const paymentType = e.namedValues['What is your agreed upon payment schedule?'][0];
const fixedCost = e.namedValues['What was your agreed upon cost for the project?'][0];
const hourlyRate = e.namedValues['Hourly Rate'][0];
const manHours = e.namedValues['Total man hours'][0];
const services = e.namedValues['Select the services'][0];
//Consequential Data
const tax = 18.5
var subtotal = 0;
var taxAmt = 0;
var payableAmt = 0;
//if the user has selected hourly payment model
//Note: Be careful that the responses match the elements on the actual form
switch (paymentType ){
case 'Hourly Rate':
subtotal = hourlyRate*manHours;
taxAmt = subtotal * (tax/100);
payableAmt = +subtotal + +taxAmt;
break;
case 'Fixed Cost':
subtotal = fixedCost;
taxAmt = fixedCost * (tax/100)
payableAmt = +fixedCost + +taxAmt;
break;
}
const invoiceID = 'IN' + Math.random().toString().substr(2, 9);
var formattedDate = Utilities.formatDate(new Date(), "IST", "dd-MMM-yyyy");
//Set the consequential data in the columns of the spreadsheet for record keeping
//Note: These variable are dependent on the sheet's columns so if that changes, please update.
const row = rg.getRow();
const payableAmtCol = 2; //B
const invoiceIDCol = 3; //C
sh.getRange(row,payableAmtCol).setValue(payableAmt);
sh.getRange(row,invoiceIDCol).setValue(invoiceID);
//Build a new invoice from the file
//Folder and file IDs
const invoiceFolderID = '<invoice-folder-id>';
const invoiceFolder = DriveApp.getFolderById(invoiceFolderID);
const templateFileID = '<template-id>';
const newFilename = 'Invoice_' + invoiceID;
//Make a copy of the template file
const newInvoiceFileID = DriveApp.getFileById(templateFileID).makeCopy(newFilename, invoiceFolder).getId();;
//Get the invoice body into a variable
var document = DocumentApp.openById(newInvoiceFileID);
var body = document.getBody();
//Replace all the {{ }} text in the invoice body
body.replaceText('{{Invoice num}}', invoiceID);
body.replaceText('{{Date}}', formattedDate);
body.replaceText('{{Client Name}}', cName);
body.replaceText('{{Client Address}}', cAddress);
body.replaceText('{{Client Mobile}}', cMobile);
body.replaceText('{{Client Email}}', cEmail);
body.replaceText('{{Services}}', services.split(', ').join('\n'));
body.replaceText('{{Subtotal}}', subtotal);
body.replaceText('{{Tax Value}}', taxAmt);
body.replaceText('{{Total}}', payableAmt);
//In the case of hourly rate payment type, let's add an additional message giving the rate and the man hours.
if(paymentType.includes('Hourly Rate')){
//It should look something like this on the invoice
//Hourly Rate
//Rate of Rs.1200/hour
//Completed 50 man hours
const message = paymentType + '\nRate of Rs.' + hourlyRate + '/hour\nCompleted ' + manHours + ' man hours';
body.replaceText('{{Payment Type}}', message);
} else {
body.replaceText('{{Payment Type}}', paymentType);
}
document.saveAndClose();
//send email with the file
var attachment = DriveApp.getFileById(newInvoiceFileID);
GmailApp.sendEmail(cEmail, '<subject>,
'<body>',
{attachments: [attachment.getAs(MimeType.PDF)]});
}
GmailApp with the edited fields. So I created a new trigger: Edit (from a Spreadsheet). The other trigger is Form submit.Failed to send email: no recipient.cEmail was never edited?Searchs
values and namedValues only include values for those questions that were edited.Failed to send email: no recipient, replace GmailApp.sendEmail(cEmail, '<subject>,
'<body>',
{attachments: [attachment.getAs(MimeType.PDF)]});
const recipientIdx = 1; // This is the 0 based index of the column having the recipient email address
const recipient = cEmail ? cEmail : e.range.getValues().flat()[recipientIdx]
GmailApp.sendEmail(recipient , '<subject>',
'<body>',
{attachments: [attachment.getAs(MimeType.PDF)]});
recipientIdx you might use some code to get it based on the column headers.e.range.getValues().flat().
