Mar
21
2012

Merge info from Google Forms to PDF Document and Send via Email

Today we needed a way to easily take information submitted from Google Form and create a form letter so administrators can sign and send it home with a student. I went through several mail merge scripts but nothing fit exactly what I needed so I took some of the best ideas from all of them (especially the employee of the month script) and created a system that works for us. This method of utilizing Google Apps script can be used for certificates for PD sessions, creating awards, or any other documents in which you need to get information that is submitted via a Google form.

Note, this is an advanced walkthrough but if you need help, please just ask! I am more than willing to work through it with you!

 

This is what our final document looks like:

Here is our workflow:

 

To get started create your Google form and get all of the information that you need to record. It is worth noting that we record more information on our form than actually goes on our final product. You can find a copy of the form we use here: http://bit.ly/mergetutform

 

After you create your form its time to create the template for what your end result will look like. Create a new blank Google doc and setup your letter. For our example, the bus conduct report, we have fields such as “Students Name” “Date of incident” “Explanation” etc. You can find a copy of our template here: http://bit.ly/mergetemplate notice in the area where information from our form needs to be we type the word “key” before it. This is important as our script searches the document for what we define here.

When your template is created its time to add the script to our spreadsheet. When you create a Google form all of  the information that user’s input is saved in a Google Spreadsheet. This spreadsheet is where we are going to add our script.

The way the Google scripts work is based on triggers. Triggers are a way to answer the question “What do I do when X happens?  In our case we want to create a PDF with the information from our form and send it to a pre-defined user (The building Administrator and Transportation Director).

 

To get started click “Tools” and then click “Script Editor”.

 

When you do you will see an untitled project come up. It looks like this:

Delete everything in the editor so we have a blank slate.

Step one is to pull in the template that we created earlier. We do this by defining our template variable:

 var docTemplate = ;

We need to tell the script what document ID is. We find this by opening our template and copying the long code in the url. You only want to copy what is pictured below:

 So your code should look like this:

 

 var docTemplate = "1sbsvGkJCmkN-NDkKLpZcZUy8h9x2YHkVmYOg9jJwYrI";

Next we name our template by entering

 var docName = "BusConductReport";

Now its time to create our trigger for when the user hits submit on the form. For this we create a function or trigger. We do this by entering:

 function onFormSubmit(e) {}

All of our code will go in between the ” { } “.

The first thing we want to do in the trigger is create our variables or holders. These will be the pieces of information that we need to put into our template.

 

We do this by entering:

 var  variablename = "static entry or form value"

Here is an example of the ones I used:

  var email_address = "thouston@huron-city.k12.oh.us";
  var full_name = e.values[2];
  var incident_date = e.values[5];
  var grade_level = e.values[4];
  var bus_number = e.values[6];
  var violations = e.values[10];
  var explanation = e.values[11];
  var drivers_coa = e.values[12];

*Note: e.values[x] is taking the value from the form in numerical order as seen below,

Now we need to grab the template we defined earlier and make a working copy. As you can see in the code we make a new copy and combine our document name and the name from the form:
 var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName+' for '+full_name)
.getId();

Next we open the temporary document that we just created:

var copyDoc = DocumentApp.openById(copyId);

Now we need to tell the script to go into the body of our document :

var copyBody = copyDoc.getActiveSection();

So we can replace our placeholder keys that we put in our template earlier:

copyBody.replaceText('keyFullName', full_name);
copyBody.replaceText('keyTodaysDate', incident_date);
copyBody.replaceText('keyGradeLevel', grade_level);
copyBody.replaceText('keyBusNumber', bus_number);
copyBody.replaceText('keyViolations', violations);
copyBody.replaceText('keyExplanation',explanation);
copyBody.replaceText('keyDriverCOA', drivers_coa);
 After we replace our variables we need to save and close our document:
copyDoc.saveAndClose();

Next, create a PDF of our temporary document:

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

Than, send an email to our recipient we defined earlier (remember this does not have to be a static entry like our example, you could pull from your form if you are creating certificates etc) :

var subject = "Bus Conduct Report";
var body = "Here is the Bus Conduct form for " + full_name + "";
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

The final step is to get rid of the temporary document we have been working with. If you do not do this your Google Docs account will become filled with your temporary documents.

DocsList.getFileById(copyId).setTrashed(true); }

Whole Script:

// TJ Houston tjhouston.com (tj@tjhouston.com)
// Bus Conduct Report
// Get template from Google Docs and name it
var docTemplate = "1sbsvGkJCmkN-NDkKLpZcZUy8h9x2YHkVmYOg9jJwYrI"; // *** replace with your template ID ***
var docName = "BusConductReport";

// When Form Gets submitted
function onFormSubmit(e) {
//Get information from form and set as variables
var email_address = "tvonthron@huron-city.k12.oh.us, thouston@huron-city.k12.oh.us";
var full_name = e.values[2];
var grade_level = e.values[3];
var incident_date = e.values[4];
var bus_number = e.values[5];
var violations = e.values[9];
var explanation = e.values[10];
var drivers_coa = e.values[11];

// Get document template, copy it as a new temp doc, and save the Doc’s id
var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName+' for '+full_name)
.getId();
// Open the temporary document
var copyDoc = DocumentApp.openById(copyId);
// Get the document’s body section
var copyBody = copyDoc.getActiveSection();

// Replace place holder keys,in our google doc template
copyBody.replaceText('keyFullName', full_name);
copyBody.replaceText('keyTodaysDate', incident_date);
copyBody.replaceText('keyGradeLevel', grade_level);
copyBody.replaceText('keyBusNumber', bus_number);
copyBody.replaceText('keyViolations', violations);
copyBody.replaceText('keyExplanation',explanation);
copyBody.replaceText('keyDriverCOA', drivers_coa);

// Save and close the temporary document
copyDoc.saveAndClose();

// Convert temporary document to PDF
var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

// Attach PDF and send the email
var subject = "Bus Conduct Report";
var body = "Here is the Bus Conduct form for " + full_name + "";
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

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

You can find a copy of our template here: http://bit.ly/mergetemplate

You can find a copy of the spreadsheet that we used here: http://bit.ly/mergetut

You can find a copy of the form we use here: http://bit.ly/mergetutform

You can find the script here: http://pastie.org/3587263 or as text here: http://bit.ly/mergescripttext

Related Posts

About the Author: TJ Houston

TJ Houston is the Director of Technology at Huron City Schools. TJ leads professional development in all areas of technology and loves developing ways to make teachers, students, and staff's lives easier with the use of technology. TJ also loves photography and his portfolio can be found at http://www.tjshots.com. All of the views on this site are his own.

  • Keith Gonzalez

    This worked great, but I noticed one quirk when the PDF is created. It makes a double-space in the replacement when there’s just a carriage return in the form field and also shows carriage return in the Google spreadsheet. Is there a way around this? Thanks!!!

    • http://www.tjhouston.com/ TJ Houston

      Not as far as I know. What I would do in this case is put directions in the help field that tell users not to hit enter. Does that answer your question? Let me know!

      Thanks
      TJ

  • Balanand

    TypeError:
    Cannot read property “Values” from undefined. (line 10).. this is error I am facing.. Please help

    • http://www.tjhouston.com/ TJ Houston

      Are you still having this issue?

      • Matthew Newberry

        I’m getting this error. 

        • http://www.tjhouston.com/ TJ Houston

          Please send a copy of your code to tj at tjhouston.com and we can chat. Its much easier to converse via email than in comments :) 

      • Linda

        I am getting the same error. ? please help

      • Kalyn Brewer

        I am getting the error also.

  • Richard Forster

    Thanks TJ,

    A very good tutorial.  I needed a mail merge to generate snail mail for those members of our club who don’t use email. Using the various Document API’s in the way you describe  has given this to me.  Much appreciated.

    • http://www.tjhouston.com/ TJ Houston

      No problem! Let me know if you need anything else!

      Thanks
      TJ

  • plnaugle

    Hi TJ. Thanks for sharing the link to this post in the backchannel of the Simple K12 webinar today. Great to learn about this option. 

    • http://www.tjhouston.com/ TJ Houston

      Not a problem! Let me know if you or any of the educators you work with need help setting it up 

      Thanks
      TJ

  • Pingback: Merge info from Google Forms to PDF Document and Send via Email | Using Google Docs in the classroom | Scoop.it

  • Pingback: Mail Merge from Google Forms to PDF Document and Send via Email | iGeneration - 21st Century Education | Scoop.it

  • Ed Neuhaus

    Hi Tj,  Great instructions, This is very useful.  I would like to expand on the email that is sent and add in another attachment.  It is already a PDF and loaded into google docs.  I tried a few different ways of adding it as an attachment with no luck.  Any ideas how I could do that?

    • http://www.tjhouston.com/ TJ Houston

      I havent tried it but you should be able to create another PDF Variable

      var pdf2 = DocsList.getFileById(2ndGoogleAppID).getAs(“application/pdf”);And than add it to the attachements call  “attachments: pdf,pdf2″I will test when I get some time. Let me know if you are successful. ThanksTJ

      • Ed Neuhaus

        JT, I can get the document set up as a variable and get it to out put correctly.  The problem I am having is getting both document to show up as attachments.  I tried various ways to reverence both files but can’t figure out the correct way.  Any ideas?

        • http://www.tjhouston.com/ TJ Houston

          So only one shows? Or none show?

      • Ed Neuhaus

        So I figured it out and thought I would share incase anyone else has the same problem.  If you make an array of the files that need to be sent it works well.

        // Convert temporary document to PDFvar pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);var pdf2 = DocsList.getFileById(docId).getAs(“application/pdf”);  // Create an Array for attachmentsvar pdfArray=new Array( pdf, pdf2);

        // Then call the array in the mail app function

        MailApp.sendEmail(myEmail, subject, body, {htmlBody: body, attachments: pdfArray });

  • Andy MALO

    Hi ! Really good tuto ! But I got this error :
    Cannot read property “Values” from undefined. (line 7) :(

    It’s on this line : var full_name = e.values[2];
    I don’t know how to resolve it :/ Can you help me please ?

    • http://www.tjhouston.com/ TJ Houston

      Did you setup your triggers for “On Form Submit”? Here is the link to do that: http://tjhouston.com/2012/07/setting-up-triggers-in-google-apps-for-debugging/

      Let me know!
      Thanks
      TJ

      • Andy MALO

        Well I did what you said :) I already setup the triggers and the notifications :

        Début

        Fonction

        Message d’erreur

        Déclencheur

        Fin

        01/08/12 13:21

        onFormSubmit

        TypeError: Cannot read property “3″ from undefined. (ligne 21)

        formSubmit

        01/08/12 13:21

        That’s what it reported me I think the script doesn’t want to initialize my var with the datas from the form :/

        • Andy MALO

          Well I resolved my problem :
          I didn’t use = e.values[x] but e.namedValues["x"]
          It works great :)

          Thank you for your help

          Andy MALO

  • Pingback: Setting up Triggers in Google Apps for Debugging « TJ Houston.com

  • Lori

    I am getting the following error.

    TypeError:
    Cannot read property “Values” from undefined. (line 5)

    I see that other people have had the same issue.  I made sure my triggers were set up and tried the change that Andy made, but still no luck.  Any ideas?

    TIA,
    Lori Klooz

  • Eric Pabst

    This looks very helpful.  Thank you!

  • Maia

    I am getting the same error messages telling me that “values” is undefined.  I have tried everything that has been suggested in the comments below, but have not been able to fix the problems.  I would appreciate any advice to get this code working! 

    • http://www.tjhouston.com/ TJ Houston

      Are you getting the errors when you hit play in the code editor or when you submit? Make sure you set your triggers to get debug info: http://tjhouston.com/2012/07/setting-up-triggers-in-google-apps-for-debugging/

      Let me know!
      Thanks
      TJ

    • Lila

      I am also facing same problem, TypeError: Cannot read property “namedValues” from undefined. I tried all solutions below, is it any way to contact you via email for help? Thanks in advance. /Lila

      • http://www.tjhouston.com/ TJ Houston

        sure! Email is tj at tjhouston.com

      • Tfoley1

        I don’t know a lot about scripts, but I changed the number in the e.values[1] and e.values[2], etc… and it worked great for me.

        TJ, this is awesome and definitely helped me solve a huge issue at my school with event registration.

        • Tfoley1

           Ah, value [1] in TJ’s example is the user ID, since his form automatically collects it.  If your form is not set up to do that, then your first question’s value whoudl be [1].

          • http://www.tjhouston.com/ TJ Houston

            Thanks for pointing that out. Work is super busy havent had time to respond.

  • Pedro Rodrigues

    Another challenge: I’d like to have 2 fields appearing on the generated PDF, but not exactly submitted by the form, just with the form.

    i) Issue date: I’d like to generate the current date (no time) and insert it on the copyDoc PDF
    ii) Expiry date: I’d like to sum one year to the Issue Date and add this as well to the PDF (e.g. if current date, i.e., issue_date is 30-Aug-2012, expiry date should be 30-Aug-2013).
    Currently, I have both variables being manually submitted by users, which often leads to errors.
    var issue_date = e.values[5];var expiry_date = e.values[6];

    How could the script be edited so that I get those dates instantly?
    And could they be added to the spreadsheet as well?

    • Pedro Rodrigues

      Check follow-up here: https://productforums.google.com/forum/#!msg/docs/-PKHodzUDn8/90omfIQ8uYgJ

  • Vbignacio

    thanks for this. what should i change if i want to attach a doc copy instead of a pdf?

    • http://www.tjhouston.com/ TJ Houston

      At this time I do not believe it is possible :( You could however not delete the doc in your docs account and export it that way.

      Thanks
      TJ

  • Jonathan Braley

    Hey TJ not sure if Google Docs has this functionality, but I have a .pdf that has several checkboxes in it.  I’m trying to implement this functionality on a Google form, but have not found a solution.  Any ideas?

    • http://www.tjhouston.com/ TJ Houston

      Are you looking to have the checkboxes in the final PDF or the form?

      • Jonathan Braley

         We already have them in the form, but are looking to see how those checkboxes on the form get dumped into the .pdf as a checked box on the .pdf.  Would you suggest using drop downs instead?

        • http://www.tjhouston.com/ TJ Houston

          I think dropdowns would be much easier.

          • Alina

            Hey TJ I’m also utilizing the check boxes as I need multiple lines of answer of the same question… any comments as to how to account for this? (Great script btw it worked perfectly!)

  • Edd

    Thank you – this is exactly what I’ve been looking for – however, nothing seems to be happening, and I don’t know where to start tweaking.

    Would you be able to advise?

    At the moment I set up template and form exactly as I like them, the script reports no bugs, but nothing seems to happen – no mails get sent anywhere…

    • Sanjay Mathew

      You might have made the same mistake I made, I copied his script and changed this line
      var full_name = e.values[2];to var empname = e.values[2];However I forgot to change it here:makeCopy(docName+’ for ‘+full_name)so what happens is that the file is never created and no email is sent

      • Edd

        This isn’t making sense to me at all. Let me try and then get back to you.

      • Nicole

        HI Edd, 
        I actually made the exact same mistake that you mentioned above. I went and changed that line but I still did not receive an email with a pdf!  Thanks for the comment – it was helpful!

        • Nicole

          Sorry, Thanks so much Sanjay!

        • Sanjay Mathew

          Here is another reason why it could fail. After saving the
          script, Click on Run and Choose onFormSubmit. It will ask you to authorize the
          script. After you click Authorize, it will say:

          Authorization
          Status

          Now
          you can run the script.

          Click
          the Close button

          Back
          in the Script Windows. Click on Resources and Choose Current Project’s triggers

          It
          will show a link

          No triggers set up. Click here to add one now.

          Click on the link and then choose onFormSubmit, from spreadsheet,
          on form submit

          Click Save

          Click Authorize

          Click Close.

      • Edd Turner

        So I’m back now and I think my questions are clear:

        1. I’m making this for someone else – they need a Google account, right, & access to the spreadsheet – correct?

        2. The line which says ‘MailApp.sendEmail’ – does that mean Mail.app on OS X? Am I only going to have success if the user’s on a Mac?

        More questions to come!

        • http://www.tjhouston.com TJ Houston

          It has been best to create the script on the users account also mail.app is referring to the gmail app. Anyone else had success sharing scripts with others? It seems like permissions are locked down to original creator.

          Thanks
          TJ

        • melequa1

          Edd, you was able to transfer it to someone else? I have a problem related to that..

  • Sanjay Mathew

    Awesome post. Worked perfectly. Is there a way to tell the field numbers? I had to guesstimate it.

  • Nicole

    Hi TJ, 
    I built the script step by step. It saved without error (I think because I didn’t get an error message) but when I did a sample form, I did not receive a copy of my document as a pdf.  Can you suggest some troubleshooting ideas so I can try again?  Any help that you can provide (or the community for that matter) is greatly appreciated!

    Nicole

  • Pingback: Llenar plantilla en pdf a partir de correo entrante en gmail y más.. | Stringmander – Tips computines

  • http://webloggerz.com/ Webloggerz

    i was looking for this from last 2 days.. thnx mate…much acknowledged tut frm ur end…

  • mail

    Thank you so much for this! This is exactly what I was looking for! This script it amazing, can be customized and it WORKS!

  • raju

    Thanks for the script. However i keep getting following error at the code:

    I see document exists and it copies perfectly fine, however for editing
    var copyId = DocsList.getFileById(docTemplate).makeCopy(docName).getId();

    “Document is missing (perhaps it was deleted?) ”

    Any help would be appriciated

  • Sona Kashyap

    Thanks for the script. However i keep getting following error at the code:

    I see document exists and it copies perfectly fine, however for editing
    var copyId = DocsList.getFileById(docTemplate).makeCopy(docName).getId();

    “Document is missing (perhaps it was deleted?) ”
    Any help that you can provide (or the community for that matter) is greatly appreciated!

    • http://www.tjhouston.com/ TJ Houston

      Are you trying to edit the document after it is created?

  • http://twitter.com/ro_betzy B de la Betzyshor

    is it possible to make two pdf”s and mail them at once to the same email ?

    • http://www.tjhouston.com/ TJ Houston

      You would be able to copy the code and paste it again with a different variable name for the title of the attachement. What is your end goal?

      Thanks
      TJ

      • http://twitter.com/ro_betzy B de la Betzyshor

        i want to take data from one form (excel) an complete two different doc template, export them as pdf an mail them togheter to the same email

      • http://twitter.com/ro_betzy B de la Betzyshor

        well, i did what u`ve said and it`s working only half way, now it generates the second pdf and send it to email, but it doesn`t send the first :((( any others sugestions ?

        • http://www.tjhouston.com/ TJ Houston

          Did you also add your second PDF to the send mail like so:
          MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf,pdf2});

          Let me know
          Thanks
          TJ

  • http://twitter.com/ro_betzy B de la Betzyshor

    well my new code looks like this

    // Get template from Google Docs and name it

    var docTemplate = “1HEhRZUmdWhMzqpg5cvb2g1B5q-0vNFS2ctU6hA5PFJw”;

    var docName = “TemplateCerereLegitimareClub”;

    // When Form Gets submitted

    function onFormSubmit(e) {

    //Get information from form and set as variables

    var email_address = “mailu_lu_betzy@yahoo.com”;

    var nume_prenume = e.values[1];

    var data_nasterii = e.values[2];

    var cnp = e.values[3];

    var nationalitate = e.values[9];

    var serie_nr_buletin = e.values[10];

    var adresa = e.values[4];

    var localitate = e.values[5];

    var judet = e.values[6];

    var nr_telefon = e.values[7];

    var email = e.values[8];

    // Get document template, copy it as a new temp doc, and save the Doc’s id

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+nume_prenume)

    .getId();

    // Open the temporary document

    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section

    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template

    copyBody.replaceText(‘keyNumePrenume’, nume_prenume);

    copyBody.replaceText(‘keyDataNasterii’, data_nasterii);

    copyBody.replaceText(‘keyCNP’, cnp);

    copyBody.replaceText(‘keyNationalitate’, nationalitate);

    copyBody.replaceText(‘keySerieNrBuletin’, serie_nr_buletin);

    copyBody.replaceText(‘keyAdresa’, adresa);

    copyBody.replaceText(‘keyLocalitate’, localitate);

    copyBody.replaceText(‘keyJudet’, judet);

    copyBody.replaceText(‘keyTelefon’, nr_telefon);

    copyBody.replaceText(‘keyEmail’, email);

    // Save and close the temporary document

    copyDoc.saveAndClose();

    // Convert temporary document to PDF

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    //al doilea pdf

    // Get template from Google Docs and name it

    var docTemplate = “1Ocvf9YRip-PWv3dSAaB7p3Y2P0r-NpBtbvztkypywqU”;

    var docName = “TemplateAdeziune”;

    // Get document template, copy it as a new temp doc, and save the Doc’s id

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+nume_prenume)

    .getId();

    // Open the temporary document

    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section

    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template

    copyBody.replaceText(‘keyNumePrenume’, nume_prenume);

    copyBody.replaceText(‘keyCNP’, cnp);

    // Attach PDF and send the email

    var subject = “Cerere Legitimare Club”;

    var body = “Atasamentul contine cererea de legitimare pentru ” + nume_prenume + “”;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf, pdf2});

    // Delete temp file

    DocsList.getFileById(copyId).setTrashed(true); }

    i get: missing: after property ID. (line 76, fille “code”), the line 76 is the one with

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf, pdf2});

    and it`s not sending mail

    • D

      Is there anyway to make it read and email only the latest data in spreadsheet? or everytime the form is submitted?

  • http://www.facebook.com/cavedonzinho Felipe Eduardo Cavedon

    Hey, guys. I’ve managed to write something with a similar result as TJ’s code by changing the approach in his code.

    As you can figure it out, I’m not form USA or some other native english spoken language, but I don’t think that a couple of names might get you lost. Hope it helps you!

    //================================================================================================

    //

    // I must give the credits of this development to TJ Huston, from the site

    // http://tjhouston.com/2012/03/merge-info-from-google-forms-to-pdf-document-and-send-via-email/

    // His work has been the core of this script and I’m sure it shall help many, many others!

    //

    // So I got this code from TJ Huston. I’ve managed to find another way to

    // do the same, but instead using the e.values[x], I’ve used an specific cell to read and copy to

    // template. However, the trick is the “delete line” at the end of this code. It’s the soul of

    // this entire code. As the response comes to the spreadsheet, it is read and the deleted, so all

    // the other responses will be lifted up by the the spread sheet. Hope it helps you.

    //

    //================================================================================================

    // Get template from Google Docs and name it

    var docTemplate = “1w3wPMJ8_qbwkDuZbRdWq1xWzyvWNigw5eBHmZQ9Tycc”; // this is what you want to change for your version

    var docName = “Solicitação para “;

    var ss = SpreadsheetApp.getActiveSheet();

    // For future uses

    var cell = ss.getDataRange();

    // When Form Gets submitted

    function onFormSubmit(e) {

    //Get information from form and set our variable

    var email_address = “felipe.cavedon@laboris.com.br, cavedon.felipe@gmail.com, denise.bersot@laboris.com.br“; // People who should get PDF

    var cargo = ss.getRange(“B2:B2″).getValues(); //=====================================================================================================

    var idade = ss.getRange(“C2:C2″).getValues(); // For each cell, a field from the form. Couldn’t manage to find another way to get this thing working.

    var sexo = ss.getRange(“D2:D2″).getValues(); //=====================================================================================================

    var nivel = ss.getRange(“E2:E2″).getValues();

    var inicio = ss.getRange(“F2:F2″).getValues();

    var situacao = ss.getRange(“G2:G2″).getValues();

    var contrato = ss.getRange(“H2:H2″).getValues();

    var salario = ss.getRange(“I2:I2″).getValues();

    var duracao = ss.getRange(“J2:J2″).getValues();

    var tempo = ss.getRange(“K2:K2″).getValues();

    var cliente = ss.getRange(“L2:L2″).getValues();

    var projeto = ss.getRange(“M2:M2″).getValues();

    var status = ss.getRange(“N2:N2″).getValues();

    var curso = ss.getRange(“O2:O2″).getValues();

    var necessidade = ss.getRange(“P2:P2″).getValues();

    var substituicao = ss.getRange(“Q2:Q2″).getValues();

    var atividades = ss.getRange(“R2:R2″).getValues();

    var dominio = ss.getRange(“S2:S2″).getValues();

    var desejaveis = ss.getRange(“T2:T2″).getValues();

    var experiencia = ss.getRange(“U2:U2″).getValues();

    var caracteristicas = ss.getRange(“V2:V2″).getValues();

    var equipamentos = ss.getRange(“W2:W2″).getValues();

    var observacoes = ss.getRange(“X2:X2″).getValues();

    var diretor = ss.getRange(“Y2:Y2″).getValues();

    var data = ss.getRange(“Z2:Z2″).getValues();

    // Get document template, copy it as a new temp doc, and save the Doc’s id

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+cargo)

    .getId();

    // Open the temporary document

    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section

    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template

    copyBody.replaceText(‘keyCargo’, cargo);

    copyBody.replaceText(‘keyIdade’, idade);

    copyBody.replaceText(‘keySexo’, sexo);

    copyBody.replaceText(‘keyNivel’, nivel);

    copyBody.replaceText(‘keyIni’, inicio);

    copyBody.replaceText(‘keySituacao’, situacao);

    copyBody.replaceText(‘keyContrato’, contrato);

    copyBody.replaceText(‘keySalario’, salario);

    copyBody.replaceText(‘keyDuracao’, duracao);

    copyBody.replaceText(‘keyTempo’, tempo);

    copyBody.replaceText(‘keyProjeto’, projeto);

    copyBody.replaceText(‘keyCliente’, cliente);

    copyBody.replaceText(‘keyStatus’, status);

    copyBody.replaceText(‘keyCurso’, curso);

    copyBody.replaceText(‘keyNecessidade’, necessidade);

    copyBody.replaceText(‘keySubstituicao’, substituicao);

    copyBody.replaceText(‘keyAtividades’, atividades);

    copyBody.replaceText(‘keyDominio’, dominio);

    copyBody.replaceText(‘keyDesejaveis’, desejaveis);

    copyBody.replaceText(‘keyExperiencia’, experiencia);

    copyBody.replaceText(‘keyCaracteristicas’, caracteristicas);

    copyBody.replaceText(‘keyObservacoes’, observacoes);

    copyBody.replaceText(‘keyEquipamentos’, equipamentos);

    copyBody.replaceText(‘keyData’, data);

    copyBody.replaceText(‘keyDiretor’, diretor);

    // Save and close the temporary document

    copyDoc.saveAndClose();

    // Convert document to PDF

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    // Attach PDF and send the email and name it

    var docName = “Solicitação para ” + cargo;

    var subject = “Nova Solicitação de Vaga: ” + cargo;

    var body = “Solicitação do cargo ” + cargo + ” para o(a) diretor(a) ” + diretor;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

    // Delete temp file

    DocsList.getFileById(copyId).setTrashed(true);

    // Ok, this line is used to prevent the code from getting stuck,

    // as deleting the second row will bring up all the rows bellow,

    // forcing it to read and execute the other lines as new docs.

    // Again, I couldn’t find another way to solve this, as e.values[x]

    // returned “undefined” each time that I’ve tried to run;

    ss.deleteRow(2);

    }

    • D

      I tried to do this but for some reason it pulls some data from the spreadsheet but not when the form is submitted. Can you please let me know how i can change that.

  • Sergio Pena

    Mr. Houston,

    I would like some guidance regarding this script of yours. I am trying to make a registration form for a church seminary (in Spanish). Please email me at sergio[at]penatechnologies[.com] at your earliest convenience.

    Much obliged!

    Sergio

  • D

    Hi, I’m trying to do exactly what you’ve described here..Collect data from form and email it as attached PDF file.

    I can’t seem to move past
    var prname = e.values[2];
    var desc = e.values[3];

    In the template I have it as;

    Project Name: keyPrName
    Project Description : keyDesc

    I’m getting the following error

    TypeError: Cannot read property “values” from undefined. (line 14, file “generateScript”)

    Please help! :(

  • Bhaskar M

    Hi Houston,

    I am trying something exactly like what u showed in the example. But i am not able to succeed.

    When i submit the form i am getting the data into the spreadsheet. But i dont receive any email. :( I suspect that i am doing something wrong with the templates part and i am badly struck, I am trying to resolve it since last night.

    Plz help me out. Thanks in advance.

    Regards,
    Bhaskar M.

    • http://www.tjhouston.com/ TJ Houston

      I believe we talked via email. Are you still having issues?

  • Dors

    Amazing code, and amazing that you have shared it. It has made my life so much simpler. Thank-you.

  • Adrian

    This code is JUST what I was looking for – so glad you shared it :-)

    I just have one extra requirement I need to find a solution for… How can I allow the user to select the style of PDF that gets created via the form?

    My end goal is to create PDF birthday party invites from the form submission but allow the user to choose the preferred party template of their choice (blue for boy, pink for girl for example)

    Any help on this would be greatly appreciated!

    Thanks

    Adrian

    • http://www.tjhouston.com/ TJ Houston

      You would need to use a check box and set the document id by the option they choose.

  • http://www.trendmatcher.nl/ trendmatcher

    Thanks for this great script! I’ve modified it a bit so I can send two PDF’s, add cc and bcc

    • http://www.tjhouston.com/ TJ Houston

      Care to share?

      • http://www.trendmatcher.nl/ trendmatcher

        I will! I translated everything to Dutch so now I have to translate it back to English…

        • http://www.tjhouston.com/ TJ Houston

          Wanna do a guest post?

          Thanks
          TJ

      • http://www.trendmatcher.nl/ trendmatcher

        I’ve made a blogpost in Dutch with the code. I think you will understand the changes I have made:
        http://www.trendmatcher.nl/2013/06/voeg-velden-uit-een-formulier-samen-in.html

        If not, please let me know!

  • Ben S

    First, THANK YOU for posting this. It is perfect. Much easier to understand then Google.

    I need some help and can’t find your email.

    I am creating a script to take a form for my schools SIT process and create a summary form. I need to know what to put for variables that are grids. I want the object selected from the grid to be put in to different parts of the summary form.

    Any ideas?

    Thanks!

    • http://www.tjhouston.com/ TJ Houston

      tj@tjhouston. Shoot me an email!

      Thanks
      TJ

  • http://www.trendmatcher.nl/ trendmatcher

    Do you think it is possible to generate a unique barcode for the PDF file, using Google Chart API? Then people can take the printed PDF and it can be scanned at the entrance for example.

  • Frustrated

    This script is perfect for what I need. The problem is that it never sends the email. It populates the spreadsheet perfectly, but never sends anything. Any ideas? I’ve copied the script exactly and have a trigger set for it.

    • http://www.tjhouston.com/ TJ Houston

      Have you set up a diagnostic trigger to see where an error would be.

      Thanks
      TJ

  • Gabrielle

    TJ,
    I’m trying to just convert the forms I’ve created in Google Drive to PDF documents that I can print to have my clients fill out in person. Probably much simpler than the process you’ve outlined here — but do you have any idea how? Thanks,

    • http://www.tjhouston.com/ TJ Houston

      There really isnt a way other then print and print as pdf.

      Sorry
      TJ

  • Darth Mole

    Hello, I just wanted to post something I came across. I was receiving the “TypeError: Cannot read property “1″ from undefined. (line 11, file “Code”)” error. The new google forms don’t, by default, create a spreadsheet for the results so I didn’t realize I was applying the script from this site directly to the form, not the spreadsheet. After I created a spreadsheet for the results of the form, applied the script to the spreadsheet and not the form and then adjusted the trigger (it wasn’t on the submit option by default. I’m not sure if that was an isolated instance or not) there. After that it works as intended. I’m not sure if this is related to the issues the others were experiencing or not. Thank you very much, TJ, for making this amazing script!

  • Jose

    what if instead of sending the document to the person, I want to display the PDF in a new page. Just like when you make a link to a PDF file.

    • http://www.tjhouston.com/ TJ Houston

      well the variable pdf has the file. You would have to have something in the script that would open that file. Ill be honest I dont know how to make this happen.

      Thanks
      TJ

  • Jim

    Thank you for this! I just have one additional thing I was looking to do: In addition to e-mail, I want to save a copy of the Doc file in a folder I’ve created in my Google Drive. I have already stopped the script from deleting the temporary file, but how could I then move the file into a specific folder? Thank you!

    • http://www.tjhouston.com/ TJ Houston

      You should be able to just remove the line of code that deletes the doc…

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

      Let me know!

      Thanks
      TJ

  • Nathan Bartell

    TJ – this tutorial that you put together has me really excited! I have a couple of PDF’s that have check boxes and text fields and I would like to be able to populate with a Google Form. I’ve been researching for a while, and your example is the closest I have been able to find. Do you think it is possible to take Google Form answers and populate an existing PDF document?

    • http://www.tjhouston.com/ TJ Houston

      Not to my knowledge.

      Sorry,
      TJ

  • Franck Ruault

    Thank you for this! I don’t succed to launch this code. I always have an error:
    I don’t understand why.

    (in french sorry)
    TypeError: Impossible de lire la propriété “values” depuis undefined. (ligne 10, fichier “Code”)

    var full_name = e.values[2];

  • Adam

    I’m having a problem with the script. It keeps giving the error TypeError: Cannot read property “values” from undefined. (line 15, file “Code”) :
    Below is a copy of the code. Thanks for your help.

    var docTemplate = “1ZSqmId2BBjtz6PmgQEmusjnkHGsFKD1CBSq0rrQk6Kc”;

    var docName = “TestCertificate”;

    // When Form Gets submitted

    function onFormSubmit(e) {

    //Get information from form and set our variables

    var email_address = “email@k12.nc.us”; // People who should get PDF

    var full_name = e.values[0];

    // Get document template, copy it as a new temp doc, and save the Doc’s id

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+full_name)

    .getId();

    // Open the temporary document

    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section

    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template

    copyBody.replaceText(‘keyFullName’, full_name);

    // Save and close the temporary document

    copyDoc.saveAndClose();

    // Convert document to PDF

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    // Attach PDF and send the email

    var subject = “Form”;

    var body = “Here is the Form ” + full_name + “”;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

    // Delete temp file

    DocsList.getFileById(copyId).setTrashed(true);

    }

    • Adam

      Thanks for the great original code btw TJ. With the following tweaks, it should work for all. I think Google changed some formatting recently to cause it to not work as is.

      I got the answer I asked from Stack Overflow

      Here are the links to the threads:

      (http://stackoverflow.com/questions/17984230/getting-typeerror-in-onformsubmit-trigger)

      (http://stackoverflow.com/questions/17992718/how-to-get-onformsubmit-to-trigger-automatically)

      • Georel Ulangkaya

        adam, please share. how did you fix it? can you please explain it in details. thanks

        • http://www.tjhouston.com/ TJ Houston

          All who are getting errors how are you testing? You can’t hit the play button on the script editor page. IF you do you are passing empty variables (e) to the script.

          This code is meant to be used when a user submits a form. This is why you setup your trigger as “onFormSubmit”.

          To test, save the code and enter data in your form.

          There is nothing wrong with the above code. I have been using it everyday since I posted this. If anyone has any questions feel free to post here, email me at tj@tjhouston.com or call/text me 4199759749.

          Cheers!
          TJ

          • Georel Ulangkaya

            That was a quick reply TJ. first of all, thanks for the quick reply. I tested it by doing both. Clicking the play botton, where error comes out, and inputting on the fields. the spreadsheet was updated but no emails at all. I will try it again. i’ll update you when i find my mistake :)

          • Adam

            Thanks TJ! Your original code worked. I guess anytime I see a play button, I have to press it and there was my problem. Turning form data into a pdf has helped us out a lot!

          • beanor

            Im having the same issues as other when hitting the play button and am not seeing anything at the designated email address when I submit responses.

            from what I have gathered here, the problem is the trigger of submitting the form. is there an alternate trigger you can use with the current spreadsheet data to troubleshoot?

        • Jason

          I was having the exact same issue. When I created my Form, I just created it from drive, then added the spreadsheet. I tried the script on both the spreadsheet and the form, and I got the same error. Eventually, I decided to create a new Spreadsheet, and make a Form from that spreadsheet, then put the script on the spreadsheet. And it worked!

          So in short, If anyone is still having this issue, make a new Spreadsheet, and create a Form using the menu on that spreadsheet.

  • casey

    TJ I am having an issue with this. Right now the error I get is “8/5/13 12:38 PMonFormSubmitYou do not have permission to call openByIdformEdit8/5/13 12:38 PM.

    Any idea why I can’t use the openById?

    • http://www.tjhouston.com/ TJ Houston

      Did you copy the code over into a new script?

      • casey

        I just tried that. I haven’t gotten any error messages OR any submissions. Not really sure what to try.

        • http://www.tjhouston.com/ TJ Houston

          Did you setup a diagnostic trigger?

          • casey

            Going to be honest here and say I have no idea what that is. I have a trigger to send the email on form submit.

  • andre

    Hey I have a question. is there a way to make the name of the document that gets created by information that was submitted. For example what i would like to do is have the name setup like this “Value 1 – Value 2 – Value 3″

    • http://www.tjhouston.com/ TJ Houston

      I guess it may be possible. You would have to have something like var docname = e.values[2]; or whatever value you wanted it to be. I haven’t tried it but that should work.
      Thanks
      TJ

  • Daniel Jensen

    HI TJ. Thanks for the code, it works well. I have a google form used by technicians on-site and they click on checkboxes to indicate the tech on site. This all works well with the form, spreadsheet and template, but I would like to send the email based on the response in the check box.

    Code:

    var email_address = e.value[6];{

    if (e.value[6]=”A”,”email@1gmail.com”);

    if (e.value[6]=”B”, “email2@gmail.com”);

    if (e.value[6]=”C”, “email3@gmail.com”);

    and so on, but I can’t get the email to send. Any ideas?

  • Pingback: Merge info from Google Forms to PDF Document an...

  • Pingback: Medicare fee » My Google Number

  • ash

    hi Guys i am receiving this error msg Missing ] in index expression. (line 15, file “Code”)

    below is what i have written can someone please help?
    var timestamp = e.values[2a];

    var full_name = e.values[2b];

    var address = e.values[2c];

    var date = e.values[2d];

    • http://www.tjhouston.com/ TJ Houston

      Try getting rid of the letters after the numbers.

  • Jag

    Hi TJ Houston,

    Thanks a bunch for the sharing the code,appreciate it,was really helpful.and works great,

    Thx
    Jag
    http://www.staritconsulting.com

  • Ravim

    Thanks for the detailed instructions. This is very close to what I wanted to create.

    I have a query and it would be great if you could help me out with it. I already have a filled sheet with many different columns one of which is email address.

    The form that I want to create will just have the email address field. When the user submits his/her email address two things should happen -

    a) If the email address entered is found in my sheet, it should create a customized pdf and send it to the email address entered.

    b) If the email is not there, it should display en error message or else send a customised text e.g. “your email is not registered”.

    How can I do it. Would really appreciate your help. Please elaborate well as I am very new to coding. Thanks!

  • Amanda

    I am trying to use this code and keep recieiving and error message on line 7

    “TypeError: Cannot read property”values” from undefinded. (line 7, file” Code”)”

    Below is what I have surrounf this line… Line 7 is ‘var your_name…

    var docTemplate = “13SQ9gq4t4HxVhGZ1jmAoL8kvBXh1z5b-rwLoLSLhzYU/edit?usp=drive_web”;
    var docName= “VolunteerApplicationSample”;
    function onFormSubmit(e)
    {
    var variablename = “static entry or form value”
    var email_address = “pause4change@gmail.com”;
    var your_name = e.values[1];
    var current_address = e.values[2];
    var mobile_contact_number = e.values[3];
    var daytime_contact_number = e.values[4];
    var evening_contact_number = e.values[5];
    var email_address = e.values[6];

    Any suggestions?

    • Brian Oconnell

      I’m getting the same error.

      • beanor

        I think the values start at 2 because the 1st value is the timestamp. but I changed the numbers and I get the same error….different situation.

        • http://www.tjhouston.com/ TJ Houston

          You can’t hit the play button on the script editor page. IF you do you are passing empty variables (e) to the script.

          This code is meant to be used when a user submits a form. This is why you setup your trigger as “onFormSubmit”.

          To test, save the code and enter data in your form. Also it might help if you setup a diagnostic trigger: http://tjhouston.com/2012/07/setting-up-triggers-in-google-apps-for-debugging/

  • Skipper

    I really like this code, it’s almost exactly what I’m looking for to use for my Scout Group. Only one thing I need to change though; I already have all the data I want to use in a spread sheet. So can I change the trigger from ‘onSubmit’? can I run the code manually to send out emails and documents based on my existing spreadsheet?

    many thanks for your help

  • Oli1

    Hello TJ.

    Thanks so much for this tutorial. It is greatly appreciated and works for me. Please pardon my possible ignorant question here. I need to insert a line break in the email message: body var body = “text text text (Needed line break) text text text text”

    My searches for a solution haven’t worked.

    Any input/suggestions?

    Thanks in advance.

  • Kevin

    I like this script but I’m a little confused on how to pull the email from the form and send to the person based off of that. Can you elaborate how to do that?

  • Dean Stewart

    Hi TJ, this is excellent Thank you. Is there a way that certain fields in the spreadsheet (eg: surname of client, job number), can form the filename of the exported PDF? eg: if surname was Smith and job number was 1234,t he PDF name would then be smith_1234.pdf. I am asking this as I would like to use the filename as index fields into my ECM. Cheers if you can assist! Dean Stewart

  • Josh

    TJ, this was extremely helpful and useful. You did a great job explaining the approach and purpose (with effective comments in your code). I appreciate it.

  • John

    Great script! This is extremely useful for a number of applications. Thank you so much!

  • Juuso

    Great script TJ! Do you think it would be possible to use this script and add an approval flow using mail merge prior to the document being created?
    I.e. if the information on the form that is submitted needs to be approved by A and B prior to the document being created? I would like to log the approvals and the time required for the approval flow to finish.

    • http://www.tjhouston.com/ TJ Houston

      I am unsure of the best way to do this… it may be better to go with a canned tool like kissflow may be better.

      Thanks
      TJ

    • Phill

      There’s actually a bunch of tutorials on the Google Developers site that explain how to roll an approval flow. I created a pretty comprehensive system for authorising various HR workflow using the HtmlService with this helpdesk workflow document as a guide: https://developers.google.com/apps-script/articles/helpdesk_tutorial

  • Ryan Martin

    First of all, thank you so much for posting this. It will be such a help in making all kinds of mail merges in the future. I have a suggestion and a question.

    For what it’s worth, I didn’t want to email, but save the file in another directory (for printing later). I added this at the bottom my code (after I took out the line about deleting the file):

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    var doc = DocsList.getFileById(copyId);

    var folders = doc.getParents()

    var newFolder=DocsList.getFolder({Here I put the name of the desired folder, not in quotes})

    doc.addToFolder(newFolder);

    var docParentFolder=folders[0];

    doc.removeFromFolder(docParentFolder);

    }

    Now, here’s my question, for TJ (or anyone else). I’d like to try to format the date and some of the other numbers (as dollars, specifically). How can I take the “replaceText” function and change the text into a format that I specify?

    Thanks again!

  • Rob

    This was exactly what I was looking for! You’re the man TJ. Made my life a heck of a lot easier.

    Thank you,

    Rob
    http://www.sapbwtraining.net

  • Crystal

    Absolutely Great! Thanks so much

  • marionix

    Hi!! Thanks for this great script. I’m wondering if the recipient of the email were the own user that submits the form what would be the code in var email address. Thanks a lot!!!

    • http://www.tjhouston.com/ TJ Houston

      You could just have the user enter the email address in one of the fields.

      • melequa

        Hello, I would like to ask you for an advice…

        ….but first of all I would like to tell you TJ that you made an amazing job and it’s so nice of you that not only you shared this with us but also you are here, helping to solve our problems. I really admire it.

        The problem I have is actually the same as marionix’s – that the PDF file would be sent also to the person that submitted the form.

        I was thinking about the solution you have suggested but maybe there is another way, that the address would be taken automatically (since the person that is submitting the form is logged in) so we could avoid forcing him/her to fill another form field?

        Thank you.

  • Duncan Cunningham

    Do you have to use ‘Google Apps’ account or can this be for a normal gmail account? I setup everything the best I could but I get no resultant email so far.

    • http://www.tjhouston.com/ TJ Houston

      Still having issues? Best way to get help is to shoot me an email. Tj@tjhouston.com

  • Matt

    Great tutorial TJ

    Would it be possible to just load the pdf in the browser instead of having to e-mail?
    So when the user hits submit on the form, the PDF loads in a new tab?

    I’m creating a webpage that will take form data, run calculations, then present in a PDF of a google spreadsheet, but people are very hesitant to share e-mail address.

  • dcd

    Thanks! I was able to fixed a major error based on these instructions. Appreciate your efforts.

  • Deepak

    Really in urgent need of help.

    I have created a ms-word document with a tabular structure in it to collect data.

    I uploaded the ms-word document to the google drive.

    And followed with the code given by you. But it doesn’t work for me :(

    When I see the Execution trascript it shows

    “Execution failed: Service unavailable”

    and this is when the code says :
    var copyDoc = DocumentApp.openById(copyId);

    Please help me asap. I really need this now in a few mins :(

  • Joe

    Great script Tj. Thank you again.

    I wanted to save the doc so had to change a true value to (false):

    DocsList.getFileById(copyId).setTrashed(false);

    Then I wanted to email a link to the doc for further editing, so I modified the email body with a URL and copyId:

    var body = “Proposal for ” + full_name + ” ” + “https://docs.google.com/document/d/” + copyId;

  • Shelley

    TJ, this is a great script and exactly what I need! I can’t get it to work though (which I’m sure is because I’m a newbee at scripts). You mentioned you were willing to help work through the script. What is the best way to contact you and show you what I’ve done so far, and need help with?

  • Sasha

    Thank you so much for this script and tutorial! It worked perfectly for my needs!

    I modified the script so that it would send the email to an address entered by a user of the form, and then CC a static email address of someone in our department. In case others are interested, here’s what that looks like:

    var email_address = e.values[2];

    var static_copy = “example@example.com”;

    then…

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf, cc: static_copy});

    • WS

      Thanks for sharing. I also need the pdf forwarded to a static email account, as well as a designated email, but the script is telling me the error occurs on the static copy line. error reads ‘unterminated string literal’. any help on this would be greatly appreciated. thanks!

  • Matt

    what are the possible causes that it isn’t emailing the PDF? I set up a simple form, sent the responses to the spreadsheet where they are being recorded, I have the script set up and it does appear in the script manager, I made sure the template id was correct but I’m not getting an email when the form is submitted. Thanks for any help!

    • Matt

      Never mind, I found it out

  • Matt

    to anyone who can’t get the form to be emailed, I had the same problem and it was driving me nuts. I found this, https://developers.google.com/apps-script/understanding_triggers look under Using Container-Specific Installable Triggers and it solved my problem

  • Mharo

    Nice Script but I am having a problem while running script. I am using the same script with minor change and form as givn in the template sheet. It is showing error as:

    “TypeError: Cannot read property “values” from undefined. (line 12, file “Code”)”

    & I am using the following script

    10 function onFormSubmit(e) {
    11 //Get information from form and set as variables
    12 var email_address = e.values[1];
    13 var static_copy = “email@email.com”;
    14 var timestamp = e.values[0];
    15 var full_name = e.values[2];

    likewise….
    pls help me executing this successfully

    • Matt

      Mharo,

      I had the same problem, but it isn’t actually a problem at all. The script is set to run when the Form is submitted, so that’s when the values will be defined. The debug won’t give you a true result. You need to set up a trigger that tells you if there is a problem with the code after it’s run, then submit test forms to see where the problems are.

      To set up the triggers go to the ‘Resources’ drop down and click ‘Add New Trigger’.

      For this to run correctly, the trigger needs to be set up correctly. Make sure that the trigger reads:
      onFormSubmit>From Spreadsheet>On Form Submit

      Click on ‘Notifications’ and change to ‘Daily’ to ‘Immediately’.

      This will get you going, and you will receive email notifications if there are errors.

      Hope this helps.

      • Mharo

        Hi Matt,
        It is working perfectly now.
        Also I need – the person who is submitting should also gets a copy of this. what should i do.

        • Mharo

          Can you help me:
          I want minor changes in the script.
          I also want to send data which is there in another spreadsheet. I want to put in pdf which then sends as an email.
          i.e. what should be the script to pull the data from another spreadsheet.

      • felipe

        Hi can you help me, I did as you describes and returns the following : “Cannot read property “0″ from undefined. (línea 10, archivo “Código”)”

        and i have
        // Felipe Toledo – ftoledo@postec.cl
        // Carta Envio Planos
        // Get template from Google Docs and name it
        var docTemplate = “1DecAyu0Z_4O_itZHwYv2agqISlvqzNQAVrKFXQr3Jzo”; // *** replace with your template ID ***
        var docName = “Envio Planos”;

        // When Form Gets submitted
        function onFormSubmit(e) {
        //Get information from form and set as variables
        var id_date= e.values[0]
        var email_address = “ftoledo@postec.cl”;
        var id_owner = e.values[2];
        var id_manager = e.values[3];
        var id_pid = e.values[4];
        var id_proyect = e.values[5];
        var id_fid = e.values[6]
        var id_level = e.values[7];
        var id_copies = e.values[8];
        var id_plan0 = e.values[9];
        var id_rev0 = e.values[10];
        var id_plan1 = e.values[11];
        var id_rev1 = e.values[12];
        var id_plan2 = e.values[13];
        var id_rev2 = e.values[14];
        var id_plan3 = e.values[15];
        var id_rev3 = e.values[16];
        var id_plan4 = e.values[17];
        var id_rev5 = e.values[18];
        var id_engineer = e.values[19];

        // Get document template, copy it as a new temp doc, and save the Doc’s id
        var copyId = DocsList.getFileById(docTemplate)
        .makeCopy(docName+’ – ‘+id_proyect + ‘ – ‘+id_fid)
        .getId();
        // Open the temporary document
        var copyDoc = DocumentApp.openById(copyId);
        // Get the document’s body section
        var copyBody = copyDoc.getActiveSection();

        // Replace place holder keys,in our google doc template
        copyBody.replaceText(‘id_owner’, id_owner);
        copyBody.replaceText(‘id_manager’, id_manager);
        //copyBody.replaceText(‘id_date’, id_date);
        copyBody.replaceText(‘id_copies’, id_copies);
        copyBody.replaceText(‘id_pid’, id_pid);
        copyBody.replaceText(‘id_proyect’, id_proyect);

        // Save and close the temporary document
        copyDoc.saveAndClose();

        // Convert temporary document to PDF
        var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

        // Attach PDF and send the email
        var subject = “Envio de Planos ” + ‘ – ‘+ id_proyect + ‘ – ‘+ id_fid + “”;
        var body = “Adjunto la carta de envio de planos ” + ‘ – ‘+ id_proyect + ‘ – ‘+ id_fid + “”;
        GmailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

        // Delete temp file
        DocsList.getFileById(copyId).setTrashed(true);
        }
        thanks in advance

  • Matias

    Hi, I have a problem, when I try to run this script

    function onFormSubmit (e) {

    var DocTemplate = “142JdcSno6ywvNliAO-nnbLbB9paaaznQennj8rdGMis” ;

    var DOC = “Orden de Compra” ;

    var timestamp = e.values[0];

    var name = e.values[1];

    var nombrecliente = e.values[2];

    var gecomcliente = e.values[3];

    var mail = ‘ftsrs@gmail.com’;

    var producto = e.values[4];

    var cantidad = e.values[5];

    var gecom = e.values[6];

    var precio = e.values[7];

    var pago = e.values[8];

    var envio = e.values[9];

    var plazo = e.values[10];

    var producto2 = e.values[11];

    var gecom2 = e.values[12];

    var precio2 = e.values[13];

    var cantidad2 = e.values[14];

    var producto3 = e.values[15];

    var gecom3 = e.values[16];

    var precio3 = e.values[17];

    var cantidad3 = e.values[18];

    var copyId = DocsList.getFileById(DocTemplate)

    .makeCopy(DOC + ‘ for ‘+ nombrecliente)

    .getId();

    var copyBody = DOC.getActiveSection ();

    copyBody . replaceText ( ‘keyNombre’ , name );

    copyBody . replaceText ( ‘keyCliente’ , nombrecliente );

    copyBody . replaceText ( ‘KeyGecomC’ , gecomcliente );

    copyBody . replaceText ( ‘KeyProducto1′ , producto );

    copyBody . replaceText ( ‘KeyCantidad1′ , cantidad );

    copyBody . replaceText ( ‘KeyGecomP1′ , gecom );

    copyBody . replaceText ( ‘KeyPrecio1′ , precio );

    copyBody . replaceText ( ‘KeyProducto2′ , producto2 );

    copyBody . replaceText ( ‘KeyCantidad2′ , cantidad2 );

    copyBody . replaceText ( ‘KeyGecomP2′ , gecom2 );

    copyBody . replaceText ( ‘KeyPrecio2′ , precio2 );

    copyBody . replaceText ( ‘KeyProducto3′ , producto3 );

    copyBody . replaceText ( ‘KeyCantidad3′ , cantidad3 );

    copyBody . replaceText ( ‘KeyGecomP3′ , gecom3 );

    copyBody . replaceText ( ‘KeyPrecio3′ , precio3 );

    copyBody . replaceText ( ‘KeyFormadePago’ , pago );

    copyBody . replaceText ( ‘KeyFormadeEnvio’ , envio );

    copyBody . replaceText ( ‘KeyPlazodeEntrega’ , plazo );

    copyDoc . saveAndClose ();

    var pdf = DocsList.getFileById ( copyId ). GetAs ( “application / pdf” );

    var subject = “Solicitud de orden de compra de ” + name;

    //var body =”orgen de compra”+ nombre_completo + “”;

    // Attach PDF and send the email

    MailApp.sendEmail(mail, subject, body,{htmlBody: body, attachments: pdf});

    DocsList.getFileById(copyId).setTrashed(true);

    }

    I get this report:

    InicioFunciónMensaje de errorActivaciónFin29/01/14 9:57onFormSubmitTypeError: No se puede encontrar la función getActiveSection en el objeto Orden de Compra. (línea 29, archivo “Código”)formSubmit29/01/14 9:58

    The problem is on getActiveSlection,

    Can someone help me?

    • http://www.tjhouston.com/ TJ Houston

      Best way to get help is to shoot me an email. Tj@tjhouston.com

      Thanks
      TJ

  • Ray Washington

    Hi TJ,

    Thanks for making this script available to the world! It was there waiting for me just when I needed it most. After cleaning up my typo’s everything worked virtually as advertised. The only issue that I have encountered is that the script send 2 copies of the email everything the Submit button is clicked. It sends 2 copies to the To: address, as well as 2 to each cc: and bcc:.

    Anyone else encountered this? I’m pretty much out of ideas … even considered that maybe fast double-clicking on Submit might be the cause. Not so … happens if Enter key is pressed too.

    The code below is what initiates the send is pretty unremarkable, and only appears once in the code, and is not in a loop nor complex If statement.

    MailApp.sendEmail(contact_email, subject, body, {htmlBody: body, attachments: pdf, bcc: coord_copy});

    Am I missing something obvious?

    Thanks,
    Ray Washington

    • Ray Washington

      Oops …
      The double emails were caused because I somehow creates 2 onFormSubmit triggers for the same gScript. Pretty dumb!

      Ray Washington

  • Tara Parr

    I have never written a script…but I am trying. I followed your directions the best I could…here is my final script. Sadly, I am not receiving emails. Thoughts?

    var docTemplate = “1KwDTN-EyUxa31HCtcsuWW8YhbNFw3GQa5-eu_rB1Fng”;

    var docName = “Pep Individual Forms”;

    function onFormSubmit(e) { var email_address = “tparr@pvsd.org”;

    var student_name = e.values[2];

    var date = e.values[3];

    var person_referring = e.values[4];

    var concerns = e.values[5];

    var comments = e.values[6];

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+full_name)

    .getId();

    var copyDoc = DocumentApp.openById(copyId);

    var copyBody = copyDoc.getActiveSection();

    copyBody.replaceText(‘keystudent_name’, student_name);

    copyBody.replaceText(‘keydate’, date);

    copyBody.replaceText(‘keyperson_referring’, person_referring);

    copyBody.replaceText(‘keyconcerns’, concerns);

    copyBody.replaceText(‘keycomments’, comments);

    copyDoc.saveAndClose();

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    var subject = “New Pep Referral Form – Received”;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

    DocsList.getFileById(copyId).setTrashed(true); }

    • http://www.tjhouston.com/ TJ Houston

      Best way to get help is to shoot me an email. Tj@tjhouston.com

  • Jo Burbidge

    This is brilliant but I really need the final document to be editable (ie not pdf). Is there any way it can either be sent as a doc/docx/rtf or saved in senders Google Drive?

    • melequa

      Jo Burbidge, did u find a solution? I am having the same problem. Thank you!

  • teacherB

    Hello TJ. I’ve been trying your Script with “New Google Sheets” which was rolled out by Google very recently. I can’t seem to get it to work. Do you know if there are any compatibility issues between your script and the “New Google Sheets”? Thank you.

  • teacherb

    Can’t get my version to work. Just started using “New Google Sheets”. Could that be a problem? I’ve read through all suggestions posted in here. No luck.

  • http://easterngreenetechnology.weebly.com/ Elisabeth

    I am not too familiar with script as it’s been a long time since I’ve had to use it. I followed this very easily, except, when I try to run it, I get an error message. I have attached a screen shot of what I’m seeing. Can you help me?

  • Annie Cates

    This is great but I’m trying to find a way to merge google spreadsheet data with a stack of pdf forms. I can add the field names to the pdf document and using a third party tool I can merge them but I’d like to be able to merge it without paying 150.00 for the tool.

    I’d like to merge the pdf document with the data and output a finished pdf file. Any pointers on this? if you know of any tools or online documentation, let me know. I’m searching the web but not really finding anything.

  • NJ

    Mahalo for your script. It is exactly what I was looking for as a foundation to get started.

  • David

    Hi, thank you for this script example. I’m trying to do something like this using a spreadsheet rather than document template. I don’t need to send an email, create a pdf or delete the file — I’d like to keep the spreadsheets created. Any ideas what would be different? (Can’t get it to work.)

  • Pingback: Web tools | Pearltrees

  • Jacob Standish

    I’ve applied this to several applications since I discovered it last month. Thank you for sharing. It’s simply BRILLIANT!

  • Kevin Forte

    TJ This is a great script. I have not even tried to do it yet but this is exactly what I am looking for. Strange that most pdf generator libraries on the market want to charge $2500+ and the pdf creation is so hard. Well done Google and well done TJ for the great walk through!

  • deepee01

    Hello Tj,
    Thanks you so much for this script but i will like to know how i can use it with my own user interface ( html form on my page ) instead of using google forms.

    I was able to send inputs from my html form to Google Spreadsheet but i am having problem mailing it to myself using your code when submitted.

  • joeventures

    Thank you for this tutorial! It was exactly what I was looking for. I was able to make it work, but with one issue that I’m sure can easily be resolved. I just can’t figure out where to look to resolve this.

    Some of the fields on my form are either dates or times. When copied over to the Google Doc, the dates format as mm/dd/yyyy 4/10/2014 and the times format in the 24-hour format 12:00:00.

    I’d like to change those formats to “April 10, 2014″ and “12:00 PM”. I looked around various forums and found information on date formats in JavaScript, Java and Google Script. But none of them seem to have accurate information. It’s hard to figure out even where to look.

    I’m used to using the date() function in php, so I’m hoping there’s a simple solution here as well. Any ideas?

    • Evy

      This frustrated me so much! I did a google helpout with Bryan, and he showed me a script that solved all my date time entry issues for the spreadsheet. Here is the code:

      var ss = SpreadsheetApp.getActiveSpreadsheet();

      var sheet = ss.getSheets()[0]

      sheet.getRange(‘B2:B1000′).setNumberFormat(“h:mm:ss am/pm”);

      Seems sooo simple, but i searched for this answer for hours.

      Anyway, with this script the spreadsheet looks great, but when i emai lit as a pdf, it goes back to the 24hour clock and wrong time zone. Anyone know how to fix this??

      • joeventures

        Hey Evy — See above!

    • joeventures

      Okay… I think I figured it out. It’s getting the number format from the form submission, not from how the spreadsheet cells are formatted. It would be so nice and easy if all it took was reformatting the spreadsheet. But no…. So…

      In the script, after all the variables are defined, there are a few extra lines of code to use for each date and time field. For each time field, use this code:

      vTimeField = new Date(‘January 25, 2014 ‘ + vTimeField );
      vTimeField = Utilities.formatDate(vTimeField, “Etc/GMT+4″, “h:mm a”);

      Substitute vTimeField for the name of the variable. You’ll notice I put a date in there. It doesn’t matter what date you put in there because you’re not going to use it. I also used the time zone “Etc/GMT+4″ because if I used “GMT,” the time I would get back would be 4 hours off. I have no idea why. If this time zone doesn’t work for you, use this list of timezones to figure out what should go in place of the one I used: http://joda-time.sourceforge.net/timezones.html

      Also, if you want to use a different time format than the one I used, this handy reference will help: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

      What’s happening here? First, we have to define our variable as a Date object. Then we can use the formatDate method to then format that date however we want. More info about that here: https://developers.google.com/apps-script/reference/utilities/utilities#formatDate%28Date,String,String%29

      Here’s the code to format a date field:

      var parts = vDateField.split(‘/’);
      vDateField = new Date(parts[2], parts[0]-1, parts[1] );
      vDateField = Utilities.formatDate(vDateField, “GMT”, ‘MMMMM d, yyyy’);

      Substitute vDateField as the name of your variable. You can use the same reference from Oracle, above, to help you format your date as you want it.

      There may be better ways to skin this cat, but this is what I was able to figure out. I hope my explanation was clear!

  • RabaiM

    That’s so great, i edited it and mine is not working, any help please?

    Here is my script:

    var docTemplate = “1CXed-yL-2TS9RoOvvSxdV4090IuZBj0SvHrV5yL6zek”;

    var docName = “Report”;

    function onFormSubmit(e) {

    var email_address = “malrabi@ndi.org”;

    var Month = e.values[2];

    var University = e.values[4];

    var Highlights = e.values[5];

    var activies = e.values[6];

    var upcoming = e.values[7];

    var plus_highlights = e.values[9];

    var plus_Activities = e.values[10];

    var plus_upcoming = e.values[11];

    //var explanation = e.values[11];

    //var date = e.values[12];

    var copyId = DocsList.getFileById(docTemplate).makeCopy(docName+’ for ‘+University)

    .getId();

    var copyDoc = DocumentApp.openById(copyId);

    var copyBody = copyDoc.getActiveSection();

    copyBody.replaceText(‘keyUniversity’, University);

    copyBody.replaceText(‘KeyMonth’, Month);

    copyBody.replaceText(‘keyUsharek Highlights’, Highlights);

    copyBody.replaceText(‘keyAnaUsharekactivitiesduringtheMonth’, activies);

    copyBody.replaceText(‘keyupcomingactivities’, upcoming);

    copyBody.replaceText(‘keyHighlights’, plus_highlights);

    copyBody.replaceText(‘keyactivies’, plus_Activities);

    copyBody.replaceText(‘keyupcoming’, plus_upcoming);

    copyDoc.saveAndClose();

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    var subject = “Monthly Report”;

    var body = “attached is the mothnly report for ” + University + “”;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

    DocsList.getFileById(copyId).setTrashed(true); }

  • Miguel

    HEEELP!

    I create the script and I can run smootly, but when I receive the email with pdf, the pdf came “empty”…

  • Troy

    Any chance I could get access to the form, it seems to be protected?

  • 1800 Reverse

    Hi,

    I can manage to get everything to work exsept the copy on the template is not replaced with the copy from the form. please help!

    here is my code:

    // 1800 MT Refund

    // Get template from Google Docs and name it

    var docTemplate = “18HShsMmT4zZoZY4NzNOGm-mVZHcOEIn7h298ftM0aJo”; // *** replace with your template ID ***

    var docName = “1800 Reverse MT Refund”;

    // When Form Gets submitted

    function onFormSubmit(e) {

    //Get information from form and set as variables

    var email_address = “supperstar0@gmail.com, cs@1800reverse:disqus .com.au”;

    var date = e.values[2];

    var address = e.values[3];

    var b_party_number = e.values[4];

    var refund_amount = e.values[9];

    var operators_name = e.values[10];

    var customers_full_name = e.values[11];

    // Get document template, copy it as a new temp doc, and save the Doc’s id

    var copyId = DocsList.getFileById(docTemplate)

    .makeCopy(docName+’ for ‘+customers_full_name)

    .getId();

    // Open the temporary document

    var copyDoc = DocumentApp.openById(copyId);

    // Get the document’s body section

    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template

    copyBody.replaceText(“keycustomers_full_name”,customers_full_name);

    copyBody.replaceText(‘keydate’, date);

    copyBody.replaceText(‘keyaddress’, address);

    copyBody.replaceText(‘keyb_party_number’, b_party_number);

    copyBody.replaceText(‘keyrefund_amount’, refund_amount);

    copyBody.replaceText(‘keyoperators_name’,operators_name);

    // Save and close the temporary document

    copyDoc.saveAndClose();

    // Convert temporary document to PDF

    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    // Attach PDF and send the email

    var subject = “1800 Reverse MT Refund”;

    var body = “Here is the 1800 Reverse MT Refund form for ” + customers_full_name + “”;

    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

    // Delete temp file

    DocsList.getFileById(copyId).setTrashed(true);

    }

    • Brian

      Blank responses seem to be left out of the array, thus messing up the value assignments. I found using ‘e.namedValues’ as a way to ensure assignment of form submission fields aligned with my template keys.

      • Jessica

        I think I have that same problem. I am able to get the PDF by email when all fields are answered, but if there is a blank field I get no email. I changed the e.values to the e.namedValues as you suggested, but I don’t know if I did it correctly.

        For example, first I just tried this:
        var employee_number = e.namedValues[1];

        then I tried with the apostrophes and it still didn’t work.
        var employee_number = ‘e.namedValues[1]‘;

        Please understand I know nothing about scripting until I used this one…Thanks!

        • meleqau

          Jessica, did you get it working? i have the same problem :(

          • Gary

            I need help with this problem DESPERATELY! Any resolutions?

  • Craig

    Im using the new sheets to run my script…Everything seems to work fine except the PDF does not attach….?

  • Kevin

    Thanks, very good article! Exactly the solution I was looking for!

  • artur

    is possible to make that with spreadsheet? would like to make an invoice from form answer… I highly appreciate your help

  • Leon

    Great script, it works well and thanks for the tutorial.

    I just have one query – regardless of who submits the form, it always comes from my email. Is there a way around that?

  • melequa

    TJ Houston, this is an amazing tutorial, thank you!

    I just have one concern and I really hope you or someone else could help me find a solution for this.. when the website form is submited, the data is added to my template file, but.. that’s all. There is no e-mail sent with the PDF. I only get this msg:

    Your script, Project, has recently failed to finish successfully. A summary of the failure(s) is shown below (…):

    The script is used by the document Form (Responses).
    Details:
    * Function: onFormSubmit
    * Error Message: ReferenceError: “full_name” is not defined. (line 17, file “Code”)
    * Tigger: formSubmit

    On the begining I was recieving the msg that was already mentioned in someone’s comment:

    “TypeError: Cannot read property “values” from undefined. (line 11, file “Code”)”

    and I did as Matt suggested in his comment reply.

  • PDF Dummie

    Hello,

    I am wondering if it is possible to extract information into a Google doc from a fillable pdf?

    Thanks,
    PDF Dummie

  • Nashphil

    I need to save a form, after parents fill it out, as a .pdf and I want it to save into a folder not a spreadsheet.
    Is this possible??
    I have researched till I have a headache.

  • Adrian

    Hello, this tutorial was very helpful and i must thank you a lot for that. Great job. I have a little problem, I created 3 forms, that have this script but only the first form i did is sending the e-mail after i submit, the other two give no error but i receive no e-mail as well. It supports only 1 form/e-mail address?

  • Karzan Slemani

    Dear TJ

    can you clear this tutorial by video? thanks…

  • melequa

    Once again, thanks for the great tutorial. Everything works perfectly under one condition… if all the fields in Google Form had been filled.

    The problem starts when there is one or more fields not filled on the Google Form… What happens in this case? Excel File is filled correctly, but all the answers on the template.pdf are misplaced (they go 1 or more up – according to the amount of empty fields). Do you have any suggestions?

    // Replace place holder keys,in our google doc template
    copyBody.replaceText(‘keyProjectName’, project_name);
    copyBody.replaceText(‘keyOwner’, owner);
    copyBody.replaceText(‘keyTeamMembers’, team_members);
    copyBody.replaceText(‘keyProjectInitiator’, project_initiator);
    copyBody.replaceText(‘keyStakeholders’, stakeholders);
    copyBody.replaceText(‘keyDeadline’, deadline);
    copyBody.replaceText(‘keyRelatedProjects’, related_projects);
    copyBody.replaceText(‘keyProjectSummary’, project_summary);
    copyBody.replaceText(‘keyContribution’, contribution);

    My template: http://zapodaj.net/images/ce3895cc6614f.png

    (Let’s say I don’t fill the field “Project Name”, than the answer for “Owner” will be placed in “Project Name” field in the template).

    Thank you for any suggestions…

    • middlekj

      Hi melequa, did you find a solution for this? I have the same problem.

      • melequa

        Yes and no. Since my project was very urgent I have decided to make all the fields mandatory :)

        But this may help you:

        I posted the question on other forum and i got very good answer that can be helpful for you

        “e.values stores form values in the form of number => value array (e.values[1]), they’re packed, blank field treated as if it doesn’t existed.

        e.namedValues stores form values in the form of string (field id) => value array (or dictionary), for example e.namedValues['project_name'], blank field existed”.

        So according to my understanding, instead of

        var project_name = e.Values["1"];

        should be

        var project_name = e.namedValues["project name"];

        I didn’t have a chance to test it to be honest, but hopefully it can be at least a lead for you :)

        • middlekj

          Thanks for the reply – this has been driving me nuts! – will give it a go and let you know

          • middlekj

            Yes, that works! inserts “undefined” if the field is blank. Thanks a bunch. Now if I can only work out why my email is now not sending. Once I have got the whole thing working I’ll paste the script here.

          • melequa

            I’m glad I could help. With small steps you will reach the point you are aiming for, I am sure :)

          • middlekj

            My Code! Enjoy and I hope someone looking doesnt have to go through what I went through! :-) feel free to improve

            //commons errors –
            //Triggers are not set
            //spaces after Form questions
            //e.values dont work when fields are not mandatory and left blank
            //e.namedValues dont work for sending emails use e.values[#]
            //place holder keys in template dont match
            //spelling errors
            //Note expect undefined error when de-bugging as values are not defined until form completed and submitted – run first with a small test form as per below

            // Get Template
            //from Google Docs and name it
            var docTemplate = ” “; // *** replace with new templae ID if new Template created***
            var docName = “Test Script”; //replace with document name

            // When Form Gets submitted
            function onFormSubmit(e) {
            //Get information from the form and set as variables
            //var variablename = “static entry or form value”
            //Note: var Variablename = e.namedValues["X"]; is taking the value from the spreadsheet by column name so update if spreadsheet or form questions change
            //Additions to the form will be added to the end of the spreadsheet regardless of their position in the form
            var Timestamp = e.namedValues["Timestamp"];
            var full_name = e.namedValues["Name"];
            var position = e.namedValues["Position"]
            var contact_email = e.namedValues["Contact Email"];
            var phone_number = e.namedValues["Telephone Number"];

            // Get document template, copy it as a new doc with Name and email, and save the id
            var copyId = DocsList.getFileById(docTemplate)
            .makeCopy(full_name+’ ‘+docName+’ for ‘ +contact_email+’ ‘+Timestamp)//Update or remove Variablename to create full doc Name
            .getId();
            // Open the temporary document
            var copyDoc = DocumentApp.openById(copyId);
            // Get the documents body section
            var copyBody = copyDoc.getActiveSection();

            // Replace place holder keys <> in template
            //copyBody.replaceText(‘<>’, Variablename); Variables from above
            //***Update if template is changed***
            copyBody.replaceText(‘<>’, Timestamp);
            copyBody.replaceText(‘<>’, full_name);
            copyBody.replaceText(‘<>’, position);
            copyBody.replaceText(‘<>’, contact_email);
            copyBody.replaceText(‘<>’, phone_number);

            // Save and close the temporary document
            copyDoc.saveAndClose();

            // Convert temporary document to PDF by using the getAs blob conversion
            var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

            {
            // Add the data fields to the message

            var s = SpreadsheetApp.getActiveSheet();
            var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
            var message = ” “;

            // Only include form fields that are not blank
            for ( var keys in columns ) {
            var key = columns[keys];
            if ( e.namedValues[key] && (e.namedValues[key] != “”) ) {
            message += key+ ‘ : ‘+ e.namedValues[key] + “”;
            }
            }}

            // Attach PDF and send the email
            //***Change the “To” email address when to form goes live***
            var to = “youremail@gmail.com”;
            var senders_name = e.values[1]
            var contact_email = e.values[3]
            var subject = “Test”;
            var htmlbody = “text goes here”+
            ” “+message+
            ” Submitted By:”+
            ” “+full_name+
            “”+position+
            “”+contact_email+
            “”+phone_number+
            ” Generated by Hansmoleman for Compu-Global-Hyper-Mega-Net”;

            MailApp.sendEmail({
            name: senders_name,
            to: to,
            cc: contact_email,
            replyTo: contact_email,
            subject: subject,
            htmlBody: htmlbody,
            attachments: pdf,
            });

            }

  • middlekj

    This scripts works great and is perfect for my needs…except when a field is left blank then the e.values no longer match and the merged document is miss- matched. Do you have a solution for fields that are not required and left blank?

  • Cait

    Hi TJ! Thanks so much for this. It is exactly what I need. So my script is working perfectly. Except, when I leave some fields blank in the form, it causes all the responses to shift up on the PDF, instead of leaving them as ‘undefined.’ Which means that many of my answers on the PDF are not attached to the correct headings/questions. Anyone know of a way to stop this happening? Thank you!

    • disqus_e1pWivS5rg

      I had the same problem! I fixed it by using e.namedValues instead of e.values. This also streamlines the script a bit.

      In TJ’s code, he defines the variables as e.values (example: var full_name = e.value[2];), then later in the code replaces the placeholders in the PDF document with those variables (example: copyBody.replaceText(‘keyFullName’, full_name);).

      To solve this problem, I eliminated defining the variables altogether. Then, in the copyBody.replaceText section of the code, I used e.namedValues to replace the placeholders in the PDF directly from the spreadsheet. For example: copyBody.replaceText(‘keyFullName, e.namedValues["Students Full Name"]); Just fill the brackets after e.namedValues with the exact heading of the spreadsheet column.

      I hope this helps!

  • melequa1

    I have one question, maybe you can help me.. I made a Google Form acording to the tutorial and everything was working perfectly. The problem is that I was doing it for someone else, so I needed to pass the rights to this person.

    What i did:
    I have replaced my e-mail address in the script with an address of my colleague. I also gave him ownership of all the files and I excluded myself (no access at all).

    Result:
    And now it’s not working any more… The answers are added correctly to the spreadsheet but the person doesn’t receive any e-mail. Instead I keep receiving this: “Authorization is required to perform that action.”.

    Do you have any tips ?

  • Tabs

    Thank you so much for the script, I had to figure out something for work and with your help and a lot of google I wrote my first script. You saved me :)

  • disqus_e1pWivS5rg

    Thanks for the great, functional script and the easy-to-follow directions! This has been working well for my form, but when the user does not give an answer on the form, I would like for the PDF placeholder to be made blank rather than being replaced with “undefined.” Is there any way to do this?

    • Ben

      I need this too!

    • h2o

      Any solutions for replace the “undefined” printed at PDF ?

    • h2o

      Hey, I found the solution !

      // After these…
      var Variablename = e.namedValues["X"];

      // Then…
      if (typeof Variablename === ‘undefined’) {Variablename = ‘ ‘;}

      Please refer to this…
      http://blog.milescatlett.com/google-form-emails-attached-document/

  • felipe toledo

    Hi my name is felipe an am trying to make the script you wrote with some modification, but i keep having the same error
    “TypeError: Cannot read property “0″ from undefined. (line 10, file “Code”)”

    I have set the trigger because i receive this notification via email so it means it’s running but for some reason the script is not working, I will appreciate very much if you can help me somehow.
    Thanks in Advance
    Felipe
    http://tjhouston.com/2012/03/merge-info-from-google-forms-to-pdf-document-and-send-via-email/

    // Felipe Toledo – ftoledo@postec.cl
    // Carta Envio Planos
    // Get template from Google Docs and name it
    var docTemplate = “1DecAyu0Z_4O_itZHwYv2agqISlvqzNQAVrKFXQr3Jzo”; // *** replace with your template ID ***
    var docName = “Envio Planos”;

    // When Form Gets submitted
    function onFormSubmit(e) {
    //Get information from form and set as variables
    var id_date= e.values[0];
    var email_address = e.values[1];
    var id_owner = e.values[2];
    var id_manager = e.values[3];
    var id_pid = e.values[4];
    var id_proyect = e.values[5];
    var id_fid = e.values[6]
    var id_level = e.values[7];
    var id_copies = e.values[8];
    var id_plan0 = e.values[9];
    var id_rev0 = e.values[10];
    var id_plan1 = e.values[11];
    var id_rev1 = e.values[12];
    var id_plan2 = e.values[13];
    var id_rev2 = e.values[14];
    var id_plan3 = e.values[15];
    var id_rev3 = e.values[16];
    var id_plan4 = e.values[17];
    var id_rev4 = e.values[18];
    var id_engineer = e.values[19];

    // Get document template, copy it as a new temp doc, and save the Doc’s id
    var copyId = DocsList.getFileById(docTemplate)
    .makeCopy(docName+’ – ‘+id_proyect + ‘ – ‘+id_fid)
    .getId();
    // Open the temporary document
    var copyDoc = DocumentApp.openById(copyId);
    // Get the document’s body section
    var copyBody = copyDoc.getActiveSection();

    // Replace place holder keys,in our google doc template
    copyBody.replaceText(‘id_owner’, id_owner);
    copyBody.replaceText(‘id_manager’, id_manager);
    copyBody.replaceText(‘id_date’, id_date);
    copyBody.replaceText(‘id_copies’, id_copies);
    copyBody.replaceText(‘id_pid’, id_pid);
    copyBody.replaceText(‘id_proyect’, id_proyect);
    copyBody.replaceText(‘id_fid’, id_fid);
    copyBody.replaceText(‘id_plan0′, id_plan0);
    copyBody.replaceText(‘id_rev0′, id_rev0);
    copyBody.replaceText(‘id_plan1′, id_plan1);
    copyBody.replaceText(‘id_rev1′, id_rev1);
    copyBody.replaceText(‘id_plan2′, id_plan2);
    copyBody.replaceText(‘id_rev2′, id_rev2);
    copyBody.replaceText(‘id_plan3′, id_plan3);
    copyBody.replaceText(‘id_rev3′, id_rev3);
    copyBody.replaceText(‘id_plan4′, id_plan4);
    copyBody.replaceText(‘id_rev4′, id_rev4);
    copyBody.replaceText(‘id_engineer’, id_engineer);
    // Save and close the temporary document
    copyDoc.saveAndClose();

    // Convert temporary document to PDF
    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);

    // Attach PDF and send the email
    var subject = “Envio de Planos ” + ‘ – ‘+ id_proyect + ‘ – ‘+ id_fid + “”;
    var body = “Adjunto la carta de envio de planos ” + ‘ – ‘+ id_proyect + ‘ – ‘+ id_fid + “”;
    GmailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

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

  • Ben

    I need help. I have the script working great, but my problem is questions on the form left blank.

    Ex) Address
    Address 2 (Apt. or Building #)

    When there’s no Address 2, it returns “undefined”. To clarify, I have used e.namedValues in the script, so it’s not a counting issue.
    I need it to respond with ” “. That way I don’t have ‘undefined’ on various locations of my pdf. It just looks bad on a letter.

  • Daniel Eng

    Great script. tried to make my own first but didnt send any email or create any pdf. than i tried the original and it worked. than i tried to change that but didnt work. tried to go back to the original but now i cant get that to work either. Hopefully i figure it out soon.

  • Mark

    HI TJ…Nice Script!

    I want a similar script that will just email the word doc (no need to convert to pdf) and then SAVE it to our network drive. Thanks in advance! Maybe the form could have a “SAVE AS” button that will default to our L: drive so we can save the document first before clicking “SEND”. We would also need to include more email addresses as a CC to others.

    Think you can help?

  • Mark

    Line 9 – var Todays_Date = e.values[2];

    Line 30 – copyBody.replaceText(‘keyTodaysDate’, Todays_Date);

    ERROR:
    TypeError: Cannot read property “values” from undefined. (line 9, file “Code”)

  • Daniel Eng

    Got it working once like its supposed to sending the email and all when i copied the files you had and run them but than i changed the files and now even though i tried to again copy the original files you made i still cant get it to send the email. Another thing i noticed was that i now have like four copies of the script when in the beginning i think it was only one. i changed the email address and the link in all four copies . Any ideas?

    • Daniel Eng

      Found another copy of the script where the id wasnt changed so now it runs with original files. now to try some modifications. Thanks

  • Mark

    Hello….anybody there???

  • Mark

    Script is not working for me TJ. I copied yours and changed the email address just to see if the original worked and it doesn’t. I don’t even need it to convert to pdf. Just the doc attached to an email and sent. I get an error on this line:

    var full_name = e.values[2];

  • Victoria

    Thanks for the article. Works like a charm.

  • Toni O.

    TJ, this was great and worked perfectly. I’m going to try to replicate it when the response destination is kept in form format only and not sent to a spreadsheet. I have no idea if this is possible. Also hope to send to print and email rather than just email, Wish me luck!

  • Brent

    Have you had any issues lately with this working? Is there a way to see where an error might be occurring?

  • Brent

    Have you had any issues lately with this working? Is there a way to see where an error might be occurring?

  • Jedidiah Rex

    Does this script work with the new Google Sheets/Forms?

  • http://www.brianbennett.org/blog Brian Bennett

    This is awesome, and I’m wondering if you can give guidance on expanding it: Is there a way to modify the template/script to give one PDF with multiple reports on it? So, rather than running each time it’s fired, a simpler submission would fill a spreadsheet and then compile those into a document with multiple entries.

    I’m trying to see if this could be used to gather feedback or ideas without emailing myself a ton of PDFs.

  • Joaquin Davila

    I am getting this message please help

    ReferenceError: “Agent_Writing_Number” is not defined. (line 14, file “Code”)

    var docTemplate = “1tCt6Oz9vP9EXJdysVRsLCGnFF7LPMvwu7-ivPgLGZAA”;
    var docName = “EverwellSitCode”;
    function onFormSubmit(e) { var variablename = “static entry or form value”
    var email_address = “joaquin@thergvdistrict.com”;
    var Agent_Name = e.values[1];
    var Agent_Writing_Number = e.values[2];
    var Street_Address = e.values[3];
    var City = e.values[4];
    var State_,_Zip = e.values[5];
    var Dsc_Name = e.values[6];
    var Dsc_Writing_Number = e.values[7];
    var Rsc_Name = e.values[8];
    var Rsc_Writing_Number = e.values[9];}
    var copyId = DocsList.getFileById(docTemplate)
    .makeCopy(docName+’ for ‘+Agent_Writing_Number)
    .getId();
    var copyDoc = DocumentApp.openById(copyId);
    var copyBody = copyDoc.getActiveSection();
    copyBody.replaceText(‘KEYNAME’, Agent_Name);
    copyBody.replaceText(‘KEYWRITINGNUM’, Agent_Writing_Number);
    copyBody.replaceText(‘KEYMAILINGADDRESS1′, Street_Address);
    copyBody.replaceText(‘KEYCITY ‘, City);
    copyBody.replaceText(‘KEYSTATEZIP’, State_,_Zip);
    copyBody.replaceText(‘KEYDSC’,Dsc_Name);
    copyBody.replaceText(‘KEYDSC#’, Dsc_Writing_Number);
    copyBody.replaceText(‘KEYRSC’, Rsc_Name);
    copyBody.replaceText(‘KEYRSC#’, Rsc_Writing_Number);
    copyDoc.saveAndClose();
    var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);
    var subject = “Ever Well Sit for”+ Agent_Name + “”;
    var body = “Here is the Bus Conduct form for ” + Agent_Name + “”;
    MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});
    DocsList.getFileById(copyId).setTrashed(true);

  • Question4U

    I am creating a doc for the faculty to do online discipline reports. I want to use this process so the administrators decide the consequences and put them in the spreadsheet after referrals have been submitted. The admins then need to print and mail forms to the parents. Hence I want my form to complete and email once the admin has clicked “complete” in a designated column. I know this is an OnEdit function, but this is all very new to me. I don’t even know WHERE to put that. Can you offer any advice?

Find Me Online:

About Me:

My name is TJ Houston.
I am the Director of Technology at Huron City Schools. I love integrating technology into the classroom and helping teachers find the way on the information super highway. Things I love: My family, My awesome Girlfriend, Technology, Education, and Photography.

I run PD sessions twice a week and run community training the first Saturday of the month. I love teaching people new things.

Best School Admin Blog

Nominated for Best School Admin Blog

Facebook Friends

Sponsers

Classroom 2.0

Presentations

Presenter Badge
diigo education pioneer

Friends