Problems generating PDFs from Google Forms after it is submitted

Question

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.

Answer 1

When a form response is edited the on form submit event object properties values and namedValues only include values for those questions that were edited.

To fix the error Failed to send email: no recipient, replace

 GmailApp.sendEmail(cEmail, '<subject>, 
                     '<body>', 
                     {attachments: [attachment.getAs(MimeType.PDF)]});

by

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)]});

P.S. Instead of hardcoding the value assigned to recipientIdx you might use some code to get it based on the column headers.

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 e.range.getValues().flat().

Related

• How to check if current submission is editing response or a new response
• How to check if current form submission is editing response