0

I have a google script to use a form submission to fill out a document, then convert it to a PDF and save the PDF, then email it out to 2 emails. One is to the respondent whos email is on the google worksheet and one to the hard coded email address. The trigger is working, the document gets filled out and save as a PDF to the correct folder. I have verified all the links to the appropriate forms, doc, and cells are correct. But it will not send either email. I am new to Google Script so can use any and all help.

function afterFormSubmit(e) { 
  
    const info = e.namedValues;
    const pdfFile = createPDF(info);

    sendEmail(e.namedValues['Email Address'][0], pdfFile);
    sendEmail2(e.namedValues['Name'][0], pdfFile);
}


function sendEmail(email,pdfFile){

  GmailApp.sendEmail(email,"Cutting Edge Renovation and Painting, LLC - Acknowledgement Form Completed", "Please find a copy of the Cutting Edge Renovation and Painting, LLC Acknowledgement form you completed for your records.",{attachments:[pdfFile],
  name: 'Cutting Edge Renovation and Painting, LLC'
});
}


function sendEmail2(ClientName,pdfFile){

  GmailApp.sendEmail("[email protected]","Cutting Edge Renovation and Painting, LLC - Client Completed the Acknowledgement Form", ClientName & " " & "has completed the Acknowledgement form",{
    attachments:[pdfFile],
  name: 'Cutting Edge Renovation and Painting, LLC'
});
}


function createPDF(info){

const pdfFolder = DriveApp.getFolderById("1N2QTvSJgRMtBx7TzCIKUWc6o3JOhfuGU");
const tempFolder = DriveApp.getFolderById("1WpbEOn_zH1IdvA_PkvxeDPnMXJVVMWQj"); 
const templateDoc = DriveApp.getFileById("1QMHSL1CFb_2srVDd1YZlQEmGMTx0sHm7NGjWBZpnccE");
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();

body.replaceText("{Name}", info['Name'][0]);
body.replaceText("{Address}", info['Job Address'][0]);
body.replaceText("{Photo}", info['Photo Release'][0]);
body.replaceText("{Access}", info['Home Access'][0]);
body.replaceText("{Parking}", info['Parking'][0]);
body.replaceText("{Parking2}", info['Parking Permit'][0]);
body.replaceText("{Signature}", info['Signature'][0]);
body.replaceText("{Phone}", info['Phone Number'][0]);
body.replaceText("{Date}", info['Timestamp'][0]);

openDoc.saveAndClose();

const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Name'][0]);
}
7
  • Did your script send any blank email? Any errors on the logs? I think const pdfFile = createPDF(info); in function afterFormSubmit(e) will only run function createPDF(info) (which creates the files) but will not return the pdf because function createPDF(info) has no return value.
    – PatrickdC
    Commented Jul 3, 2024 at 17:59
  • Can you explain more, I literally open google script for the first time today and don't understand what you mean by no return value. To my understanding the createPDF actually makes the PDF, which it does correctly, but by putting it in the 'const pdfFile = createPDF(info);' it allows me to pass the created PDF to the email function.
    – Hareborn
    Commented Jul 3, 2024 at 18:04
  • can you try and use console.log(pdfFile) after const pdfFile = createPDF(info);? I think it will return undefined.
    – PatrickdC
    Commented Jul 3, 2024 at 18:10
  • I don't know how to accomplish this. Since this is triggered by form submission I can't debug or execute the code because all variables are empty and when I try it by submitting the form it doesn't log anything.
    – Hareborn
    Commented Jul 3, 2024 at 18:18
  • Your function createPDF doesn't return anything. At the last line of that function just type: return pdfFile Commented Jul 3, 2024 at 18:28

2 Answers 2

1

This works for me:

function onMyFormSubmit(e) { 
  GmailApp.sendEmail(e.namedValues['Email Address'][0],e.namedValues["2. Question "][0],"", {attachments:DriveApp.getFileById(gobj.globals.test1id)});
}
2
  • How does {attachments:DriveApp.getFileById(gobj.globals.test1id)} find the correct file?
    – Hareborn
    Commented Jul 3, 2024 at 18:19
  • If finds the file by it's id
    – Cooper
    Commented Jul 3, 2024 at 18:22
0

While it's clear that your main function is a trigger handler function for a form submitted for a spreadsheet, the question doesn't include enough details. Still, I think you might need to improve your script to make it easier to debug.

  1. Create functions that emulate the form submission event object. If you are unsure about how to do this, one way that you can easily do it is to log the event object

    function formSubmissionHandler(e){
      Logger.log(JSON.stringify(e));
    }
    

    Then, change the trigger function handler, fill out a form with representative responses, and make a new submission. From the execution log, grab the logged JSON.

    Then, create a function to emulate the form submission using this JSON.

    function emulateFormSubmission(){
      const json = ""; // put here the JSON logged on the execution logs.
      const e = JSON.parse(json);
      debugger;
      afterFormSubmit(e);
    }
    
  2. Execute emulateFormSubmission using the Google Apps Script debugger. The execution will pause on the debugger statement. As you need it, use the debugger buttons to continue the execution or to execute the next line and so on.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.