How to make a script that sends via Email a PDF when the user sends or edits

Question

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.

Answer 1

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.