- Set Up an Excel Template
Note: This feature is restricted to users with V2 forms. For more information on how to enable V2 forms, please read: Enabling Access to V2 Forms.
ProntoForms allows you to design and build a custom document entirely in Excel. When a form submission is made, it will generate an Excel file with your desired formatting and send it to the people and places configured with data destinations.
By adding simple references to your form questions right in the Excel file, the answers to those questions can appear exactly where you need them to. All conditional formatting and formulae will continue to work within the Excel file, allowing you to continue to manipulate data in a familiar way.
Custom documents allow you to:
- easily highlight your own brand in fully custom documents
- build familiarity: collect data in the ProntoForms app, but display the data in ways your stakeholders are familiar with,
- meet industry or government regulations for document formatting
Below are a couple of examples of Excel documents that can be generated by a form submission when using this feature: a simple Invoice, and a Vehicle Inspection.
When you set up the Excel Custom Template in ProntoForms and fill it with DREL references, you are creating the pattern for future documents. The DREL references will pull information from form submissions based in questions' Unique IDs, and put it into a document based on your template. As shown in the GIF below:
- Set up the Excel document with DREL references.
- Fill out a form in ProntoForms and submit it.
- ProntoForms will create a document based on the template and your form submission.
When you create an Excel Document, you're laying the foundation for how your data will show up following a form submission. In your custom Excel Document, any answer from your form submission can appear in a cell in your sheet -- you just need to add the correct DREL reference to that cell, or, if it's a cell with a dropdown or similar special configuration, map it in the web portal.
First, you'll want to set up your Excel document how you want it to appear, then you'll need to upload it to ProntoForms for use with your form submissions.
This document type will always generate an Excel file. If you need another format, check out the links below:
- Read about PDF, Word or HTML documents with no-code configuration options.
- Read about fully custom PDF, Word, and HTML documents (HTML & CSS required).
- Build other fully custom document types with DREL, Handlebars, or Freemarker.
The file must be configured correctly for ProntoForms to work with Excel. This section will touch upon how to set up an Excel spreadsheet to work with ProntoForms.
Named Ranges: Ensure that there are no named ranges in your document. Named ranges are meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to understand at first glance. As they do not have consistent cell references between named ranges, they cause errors when generating documents.
Subtotal Function: Not all functions of this syntax are supported. Please use the fully-supported SUM function instead.
Once you have your custom document set up in Excel, it's time to map your form questions to the appropriate locations in your sheet. ProntoForms uses Data Record Expression Language (DREL) to take answers from form submissions and insert them into the desired cells in the Excel file. For more information on DREL, please read:
An example of a simple Invoice form built in Excel, and with DREL added is shown below:
- Using DREL References in individual cells is the most common use, as shown above: any question that shows up in a form submission can be put into a cell with the correct DREL string. The most common DREL string in an Excel document is:
%a[unique ID]This string will give the form submission's answer to that question. For example, if we plan to put the answer to Customer Name in cell B8, we select B8 in Excel and enter %a[Customer Name].
- Cells may have multiple DREL strings. For example, while your form may have separate questions for City, State, and Zip/Postal Code, you can set up a single cell using a string like:
%a[City], %a[State] %a[ZipCode]
Setting up the Excel Document
- Ensure that there are no named ranges in your document. Named ranges are meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to understand at first glance. As they do not have consistent cell references between named ranges, they cause errors when generating documents.
- Cells must be formatted as General or Numeric in order to have information from answers pushed to them.
- When setting up tables to contain Repeatable Section data, please keep in mind that you cannot dynamically map the data -- i.e., you cannot map the question once and have it create as many rows as needed. Create a maximum number of rows and map the data into them as shown below.
- Be careful when using the SUBTOTAL function: not all functions of this syntax are currently supported. If possible, use the fully-supported SUM function instead.
Repeatable sections allow users to enter a variable number of entries for the same set of questions -- typically used for entering things like parts, labor, or material line items, such as those shown in this invoice form on an Android device:
When using data from repeatable sections in your Excel document, you must use the correct DREL. Unlike standard unique IDs (using %a[unique ID]) which only have one answer to one question, repeatable sections have multiple answers to the same question. Using %a[Repeatable Section Unique ID] will give you all the answers to that question. In most Excel documents, we only want one answer per cell.
This is done by DREL and indexing. The format for calling a single answer to a repeatable section question is:
%a[Unique ID][index number]
Repeatable section index numbers start at 0. Therefore, the first answer to a repeatable section question would be %a[Unique ID]. In our document example, we use this format to put single answers into the table:
Which results in a table that looks like this in a form submission:
For more information, read: DREL for Repeatable Sections.
- Please note that ProntoForms cannot dynamically add rows to the Excel document based on the number of repeatable rows. To ensure you have enough rows to display all the data, please set up your document to have the maximum number of rows you believe will be used. Any rows not entered in the form submission will be left blank in the Excel document.
To add a Custom Excel Document to your account:
- Create and name the document as normal, selecting Excel Template from the list.
- On the Configuration tab, select Choose File and select your Excel document.
- Configure any manual mapping and images (see below) and select Save.
Some cells cannot contain DREL inside your Excel file, such as dropdowns or cells containing validation. If you wish to push data from your form submission to these cells, you can manually map them in our Manual Cells section of the document configuration:
Cell Reference: This uses standard Excel cell references, e.g. C2.
Worksheet/Tab Reference: To refer to different sheets inside a single file, use the standard format of [Sheet Name]![Cell Reference].
- This applies to mapping to specific worksheets. For example, to refer to Cell B2 on Sheet 2, it would be: Sheet2!B2
- Please note that if you have a leading or trailing space in your tab/worksheet name, ProntoForms will create a duplicate tab when creating the document. Please ensure your worksheet name does not have leading or trailing spaces.
Cell Type: There are three options:
- Text: This is "General" formatting in Excel.
Manually Mapping Images
You can add reference images to your Excel document, using the photos from Image, Sketchpad, or Signature questions. Rather than setting this up in the Excel file itself, this is set up in the Configuration tab of the document in the web portal.
Cell Range Reference: These are the cells that will hold the image file.
Cell Anchor: These options determine what happens within the cell(s) you set for the picture.
- Move and Resize: Your image file will be anchored to the top left and bottom right cell(s) defined, and will be resized to fit.
- Move, no Resize: Your image file will be anchored to the top left only, and not resized.
- No Move, No Resize: Your image file will center on the cell(s) defined and will not be resized.
- Mapping a Question that Collects a Single Image: simply insert the question's unique ID.
- Mapping Multiple Images from a single question -- i.e., when an image or sketchpad question is allowed to collect multiple images -- is done in much the same way as mapping repeatable section data: using index numbers starting at . These must each be manually mapped. As an example:
- This example has two images attached to the same question: TakePhotoWorksite. The first image would be TakePhotoWorksite, and the second would be TakePhotoWorksite. if the question is allowed to collect up to 10 images, be sure to map all of them.