Search code examples
google-sheetsgoogle-apps-script

How to send pdf email attachment by an Event trigger in Google Apps Script?


Overview: I have implemented a Google Forms that tracks a user's responses and the responses are stored in an Google Sheets spreadsheet.

I have also implemented a function in Apps script, with the following goals:

  • After Google Forms submission, an Event trigger is initiated to convert the user results to a PDF
  • The PDF file (Google Docs with variables that align to the Google Sheets columns) is sent to a Temp Folder in Google Drive
  • Email the user their responses that includes a PDF attachment

Current issue: The user responses are successfully sent to the Google Sheets as anticipated, and their responses are converted to a Pdf file on my Google Drive, but the emails are not being sent.

function afterFormSubmit(e) {

    const info = e.namedValues;
    const pdfFile = createPDF(info);

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

function sendEmail(email,pdfFile){
  GmailApp.sendEmail(email,"Here is Your PDF","Your PDF is Attached.",{
    attachments: [pdfFile],
    name: 'Sent From Example'   

  });

}


function createPDF(info,filename){


const pdfFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const tempFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const templateDoc = DriveApp.getFileById("18zVh4aL2Dbt76l6XqwIh2KHeJf6JkY-lMU63MRVOQUM");

const newTempFile = templateDoc.makeCopy(tempFolder);

const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
body.replaceText("{date}", info['Timestamp'][0]);
body.replaceText("{Email_Address}", info['Email Address'][0]);
body.replaceText("{selected_responses}", info['Which of these Positive Attitudes do you struggle with?'][0]);

body.replaceText("{Post-Att-01}", info['Choose a positive attitude that you find difficult to maintain.'][0]);
body.replaceText("{Happens-When-01}", info['IT HAPPENS WHEN'][0]);
body.replaceText("{Triggered-By-01}", info['TRIGGERED BY'][0]);
body.replaceText("{RC-Thought #1-01}", info['Rational Counter-Thought #1'][0]);
body.replaceText("{RC-Thought #2-01}", info['Rational Counter-Thought #2'][0]);
body.replaceText("{RC-Thought #3-01}", info['Rational Counter-Thought #3'][0]);
body.replaceText("{Account-Partner-01}", info['Accountability Partner'][0]);
body.replaceText("{Post-Att-02}", info['Select another positive attitude that you find difficult to maintain.'][0]);



openDoc.saveAndClose();


const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
tempFolder.removeFile(newTempFile);

}

Dummy Spreadsheet


Solution

  • Modification points:

    • In your function createPDF, no value is returned. I guessed that this might be the reason for your current issue.
    • Also, in the current stage, removeFile has been deprecated. Ref At tempFolder.removeFile(newTempFile);, you might want to remove newTempFile?

    When these points are reflected in your script, how about the following modification?

    Modified script:

    In this modification, the function createPDF is modified.

    From:

    const blobPDF = newTempFile.getAs(MimeType.PDF);
    const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
    tempFolder.removeFile(newTempFile);
    
    }
    

    To:

      const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
      newTempFile.setTrashed(true);
      return blobPDF; // or return pdfFile;
    }
    

    The whole modified function is as follows. By the way, it seems that filename is not used.

    function createPDF(info, filename) {
      const pdfFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
      const tempFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
      const templateDoc = DriveApp.getFileById("18zVh4aL2Dbt76l6XqwIh2KHeJf6JkY-lMU63MRVOQUM");
      const newTempFile = templateDoc.makeCopy(tempFolder);
      const openDoc = DocumentApp.openById(newTempFile.getId());
      const body = openDoc.getBody();
      body.replaceText("{date}", info['Timestamp'][0]);
      body.replaceText("{Email_Address}", info['Email Address'][0]);
      body.replaceText("{selected_responses}", info['Which of these Positive Attitudes do you struggle with?'][0]);
      body.replaceText("{Post-Att-01}", info['Choose a positive attitude that you find difficult to maintain.'][0]);
      body.replaceText("{Happens-When-01}", info['IT HAPPENS WHEN'][0]);
      body.replaceText("{Triggered-By-01}", info['TRIGGERED BY'][0]);
      body.replaceText("{RC-Thought #1-01}", info['Rational Counter-Thought #1'][0]);
      body.replaceText("{RC-Thought #2-01}", info['Rational Counter-Thought #2'][0]);
      body.replaceText("{RC-Thought #3-01}", info['Rational Counter-Thought #3'][0]);
      body.replaceText("{Account-Partner-01}", info['Accountability Partner'][0]);
      body.replaceText("{Post-Att-02}", info['Select another positive attitude that you find difficult to maintain.'][0]);
      openDoc.saveAndClose();
      const blobPDF = newTempFile.getAs(MimeType.PDF);
      const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
      newTempFile.setTrashed(true);
      return blobPDF; // or return pdfFile;
    }
    
    • By this modification, when the form is submitted, when the function afterFormSubmit(e) of the container-bound script in Google Spreadsheet is installed as the OnSubmit trigger, the function is automatically run and the email is sent with the attachment file.

    Note:

    • If you want to permanently delete the file newTempFile, you can also use Drive.Files.remove(newTempFile.getId()) instead of newTempFile.setTrashed(true). But, at that time, please enable Drive API at Advanced Google services.