CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
user2320271
user2320271

Reputation: 97

Map or export Google Form responses to PDF Form fields

I want to implement a workflow where users submit data via a Google Form, and their responses are automatically posted to a pre-existing PDF form.

I have a couple of PDF forms that I use frequently. I have created a Google Form to gather all of the information necessary to complete the PDF. Now I am trying to find a way to map the answers to the original PDF.

How can I accomplish that?

Upvotes: 5

Views: 12280

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

There is an article on patentbytes.com, Automatically Completing Your PDF Forms, which goes into some detail about this topic, and which is the inspiration for this answer.

With Google Apps Script, you cannot modify existing PDF forms. However, you can leverage the existing Import capabilities in applications such as Adobe Acrobat and Acrobat Reader, by scripting the generation of form data in a way that is easily imported.

Here's the idea:

  1. Have users fill out Google Form. Use the form settings to ensure required questions are answered.
  2. On form submission, have a spreadsheet-contained trigger function generate an XML file with the user's answers. It's simple to use text-substitution to complete this, but we'll use an alternative approach using Google's XmlService.
  3. Transmit the XML-formatted answers to the administrator. This may be done via email, or by placing a file in Google Drive.
  4. Use Adobe Acrobat or Reader to import the XML file into the pdf form. (If needed.)

Before proceeding, your PDF form needs to have specific characteristics:

  • It must be a fillable form.
  • It must include support for Export and Import of form data. (In Adobe Reader, you will see an "Extended" menu, as in this screen shot.)

  • We'll use the example form from the previously mentioned article as an example. It works, although it needs cleaning up.

    Screenshot

Export a sample of XML form data

Using Acrobat Reader (or similar), fill out a sample form completely, and Export the form data as XML. (It simplifies future steps if you fill the form out using camelCase terms that will be used for the HTML template - for example inventionTitle.)

Create HTML template from exported XML

In the Apps Script editor, use File-New to create a new html file in the project, and name it "formXml". Paste the text content of the exported xml file into formXml.html.

  • The XML declaration needs to be in our final output, but will cause the HtmlService to crash if left in the template. Remove it from the template; we'll add it back in our code.

    <?xml version="1.0" encoding="UTF-8"?>

  • (Optional) Prettify the XML, to make it easier to avoid introducing errors. (freeformatter.com has a useful xml formatter. I found that a first pass with "compact mode" followed by a second pass at 2 spaces per indent produced good results.

  • (Optional) Reorder the fields logically.

  • For each input field, replace the example text with valid printing scriplets.

    inventionTitle    becomes   <?= inventionTitle ?>

formXml.html

Here is what your form XML template should end up like. We have only 5 of the form fields represented in this example.

<fields xmlns:xfdf="http://ns.adobe.com/xfdf-transition/">
  <TitleofInvention xfdf:original="Title of Invention"><?= inventionTitle ?></TitleofInvention>
  <Inventorone xfdf:original="Inventor one"><?= inventor1name ?></Inventorone>
  <Citizenof xfdf:original="Citizen of"><?= inventor1citizenship ?></Citizenof>
  <Inventortwo xfdf:original="Inventor two"><?= inventor2name ?></Inventortwo>
  <Citizenof_2 xfdf:original="Citizen of_2"><?= inventor2citizenship ?></Citizenof_2>
</fields>

Code.gs

Here's the form submission trigger function. Each time a user fills out your on-line google form, this spreadsheet form submission trigger will process their responses, and save a timestamped xml file on your google drive, ready to be imported into a PDF form.

function formSubmission(eventData) {
  // Get a handle on the xml template
  var formXml = HtmlService.createTemplateFromFile('formXml');

  // Replace templated values with user's input
  formXml.inventionTitle = eventData.namedValues['Invention Title'];
  formXml.inventor1name = eventData.namedValues['Inventor 1 Name'];
  formXml.inventor1citizenship = eventData.namedValues['Inventor 1 Citizenship'];
  formXml.inventor2name = eventData.namedValues['Inventor 2 Name'];
  formXml.inventor2citizenship = eventData.namedValues['Inventor 2 Citizenship'];

  // Evaluate the template with substitutions
  var xml = formXml.evaluate();

  // Get the evaluated template as text, prepend the XML Declaration
  var formXmlText = '<?xml version="1.0" encoding="UTF-8"?>' 
                  + xml.getContent();

  // Save user's input as an xml file on our Google Drive
  var fileName = 'Form ' + eventData.namedValues['Timestamp'];
  var xmlFile = DriveApp.createFile(fileName, formXmlText, MimeType.XML);
}

Form & Spreadsheet

The namedValues in the eventData object processed by the trigger function come from the Form Responses sheet, which are directly from the Form questions. (That's a good reason to keep the questions short, and rely on the Help Text to elaborate!)

Spreadsheet screenshot

Upvotes: 2

Related Questions