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:
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)]});
}
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 GmailApp with the edited fields. So I created a new trigger: Edit (from a Spreadsheet). The other trigger is Form submit.
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: Failed to send email: no recipient.
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 cEmail was never edited?
Searchs
I could find some interesting answers:
- Failed to send email: no recipient
- Google scripts “Failed to send email: no recipient” but email arrives
- Failed to send email: no recipient, what is wrong with my code?
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.
