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

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

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. 

This looks very helpful.  Thank you!

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?

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?

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

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

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

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);

}