App Script - Populate Google docs Template via Google forms and send as pdf via Email

0
votes

I am trying to use app script with a google forms sheet to populate a docs template which I then send as pdf attachment via Email. I have used the initial template from here: http://www.tjhouston.com/2012/03/merge-info-from-google-forms-to-pdf-document-and-send-via-email/

and made all suggested changes from here: Generate and send pdf through Google Forms to my email address - doesn't send, debugging to no assitance

However, I cannot get it to work. I would highly appreciate any input as i am at my wits end. Weirdly, the logger does not log anything. 

var docTemplate = "documentIDhere"; // *** change template ID if new google doc is used***
var docName = "Recruiting Requirement Profile -";

    function onFormSubmit(e) {
     //Get information from form and set as variables
    Logger.log(e)

    var Q1 = e.values[2];
    var Q2 = e.values[3];
    var Q3 = e.values[4];

    // Get document template, copy it as a new temp doc, and save the Doc’s id
    var copyId = DriveApp.getFileById(docTemplate).makeCopy(docName).getId();
    var copyDoc = DocumentApp.openById(copyId);
    var copyBody = copyDoc.getBody();

    copyBody.replaceText('keyQ1', Q1);
    copyBody.replaceText('keyQ2', Q2);
    copyBody.replaceText('keyQ3', Q3);

    copyDoc.saveAndClose();

    var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

    var subject = DocName+Q3+"-"+Q1;
    var body = "Here is your "+docName+Q3+"-"+Q1;
      MailApp.sendEmail("[email protected]", subject, body, {htmlBody: body, attachments: pdf});

    // Delete temp file
    DriveApp.getFileById(copyId).setTrashed(true);
    }
1
google-apps-scriptgoogle-docsgoogle-forms
In your script, there are several incorrect spelling. For example, those are MailApp and DriveApp. Please confirm it again. Then, in order to correctly understand your situation, can you provide more information of I cannot get it to work. After you modify spelling, if error occurs, please show it. By the way, how do you run your script? – Tanaike
Thanks Tainaike, I have corrected all the spelling errors already. I have tried to debug it on a testfile and it seems that as soon as I use e.values[] it stops working. Meaning, if I define the variables as strings e.g. 'test' the pdf file arrives but as soon as i try to get the form/ sheets values via e.values[] I do not receive an email anymore. I runthe script by entering data in the form as the e.values does not work via debugging. I debugged it with strings as placeholders for the e.values[] and it worked. I also removed the brackets around the attachments: pdf. – Wencke Harder
@Tanaike the log just tells me undefined. – Wencke Harder
Thank you for replying and updating it. How do you run your script? From the log just tells me undefined., I think that you might directly run the function of onFormSubmit() at the script editor. If it's so, such error occurs. e of onFormSubmit(e) is an event object. So please install the function as a trigger. By this, when the form is submitted, the function is run and the object e is given. – Tanaike
@Tanaike thanks so much!! now it works - had to set up the function as a trigger. – Wencke Harder

1 Answers

1
votes

So the correct code can be found below. Important is to run the function as a trigger. To do this follow these steps:

  1. From the script editor, choose Edit > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger.
  4. Under Events, select either Time-driven or the Google App that the script is bound to (From spreadsheet). Select and configure the type of trigger you want to create (On open trigger).
  5. Click Save.

Here is the code:

var docTemplate = "documentIDhere"; // *** change template ID if new google doc is used***
var docName = "Recruiting Requirement Profile -";

    function onFormSubmit(e) {
     //Get information from form and set as variables
    Logger.log(e)

    var Q1 = e.values[1];
    var Q2 = e.values[2];
    var Q3 = e.values[3];

    // Get document template, copy it as a new temp doc, and save the Doc’s id
    var copyId = DriveApp.getFileById(docTemplate).makeCopy(docName).getId();
    var copyDoc = DocumentApp.openById(copyId);
    var copyBody = copyDoc.getBody();

    copyBody.replaceText('keyQ1', Q1);
    copyBody.replaceText('keyQ2', Q2);
    copyBody.replaceText('keyQ3', Q3);

    copyDoc.saveAndClose();

    var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

    var subject = DocName+Q3+"-"+Q1;
    var body = "Here is your "+docName+Q3+"-"+Q1;
      MailApp.sendEmail("[email protected]", subject, body, {htmlBody: body, attachments: pdf});

    // Delete temp file
    DriveApp.getFileById(copyId).setTrashed(true);
    }