Custom Excel Document

 

This feature is available on the following tiers: Enterprise, Advanced.

 

About

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

Sample Outputs

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.

2018-09-17-InvoiceSample671px.png 2018-09-17-VehicleInspectionSample628px.png

 

Note:
Need to generate custom documents that can't be edited by your customers or employees? ProntoForms can write into "protected" sheets in Excel. Read more about protecting a worksheet.

 

How it Works

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:

  1. Set up the Excel document with DREL references to any questions that produce text as an answer.
  2. Map any images into the document using the ProntoForms web portal.
  3. Fill out a form in ProntoForms and submit it.
  4. ProntoForms will create a document based on the template and your form submission.

2018-08-14-ExcelGif.gif

[ top ]

 

Set up an Excel Document

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:

Tip: If you want to take a look at a form configured with the correct Excel document, you can import the Vehicle Inspection Form pictured above from the Form Library: Simple Vehicle Inspection Form (with Custom Excel Output)

 

Unsupported Excel Features

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.

Data Types other than General or Numeric: Cells must be formatted as General or Numeric in order to have information from answers pushed to them.

[ top ]

 

 

Mapping Text-Based Answers

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.

Note: Mapping with DREL only supports answers with text (freetext, numbers, etc) as the answer.   Read below on how to map images.

For more information on DREL, please read:

An example of a simple Invoice form built in Excel, and with DREL added is shown below:

2018-07-20-ExcelDocument01.png

  • 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]

[ top ]

 

Manually Mapping Cells (for Cells with Validation or Dropdowns)

Some cells in Excel cannot contain DREL, such as dropdowns or cells containing validation.  Even though the answer that will be mapped may pass that validation, the DREL expression will not, which will prevent you from saving your template.

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:

2018-07-24-ExcelDocument03.png

  • 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].
    • 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.
    • Number
    • Boolean

[ top ]

 

Mapping Images

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.


Image Aspect Ratio

The aspect ratio (height vs. width) will automatically be retained.    


Image Size

The size the images are displayed in the Excel document is controlled by the cell range they are mapped into, as the entire image is fully contained within the range.

  • If the cell range is a single cell, the image will be contained in a single cell
  • If the cell range covers many cells, the image will span many cells 

The document will always prioritize the maintenance of the aspect ratio.  If your cell range does not match that aspect ratio, the image will be resized so that the longest edge of the image fits within your cell range. 

Example:
In the example below, the mapped Cell range is A2:D14, but the image is quite tall and narrow, so the image is sized so that the long edge (height) fits within the cell range, and it does not take up the full width.
mceclip0.png

Mapping Images

2018-07-24-ExcelDocument05.png

Cell Range Reference: These are the cells that will hold the image file.

Cell Anchor: These options do NOT impact how the images appear in the document when it is generated.  They DO impact how the images are tied to cells if you manually manipulate the document after it is edited.

Note: See the Cell Anchor example images in the section below.

  • Options:
    • Move and Resize
    • Move, No Resize
    • No Move, No Resize

 

Form Label: 

  • 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 [0]. These must each be manually mapped. As an example:
    2018-07-25-ExcelDocument09.png
    • This example has two images attached to the same question: TakePhotoWorksite. The first image would be TakePhotoWorksite[0], and the second would be TakePhotoWorksite[1].  if the question is allowed to collect up to 10 images, be sure to map all of them.

 

Cell Anchor Examples

Move:

A move is considered inserting a new column or row into the generated document, so that the column or row the image currently appears in shifts over/down.

If MOVE is enabled, and the document is manually manipulated as described above, the image will stay anchored to the cells it was originally mapped to, and stay aligned with other content.mceclip5.png

If MOVE is not enabled, the image will stay in its previous visual location in the document, rather than shifting to make room for the inserted column.
mceclip8.png



Resize:

Stretching the cell, column or row the image appears in so that it is larger or smaller.

If RESIZE is enabled, the image will shrink or stretch to fit the new range.   This may distort the aspect ratio. 
mceclip6.png

If RESIZE is not enabled, the image will stay the same size as before. 

mceclip7.png

[ top ]

 

 

Mapping Repeatable Sections

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:

2018-07-24-ExcelDocument06.png

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][0]. In our document example, we use this format to put single answers into the table:

2018-07-24-ExcelDocument07.png

Which results in a table that looks like this in a form submission:

2018-07-24-ExcelDocument08.png

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.

[ top ]

 

In the ProntoForms Web Portal

To add a Custom Excel Document to your account:

  1. Create and name the document as normal, selecting Excel Template from the list.
  2. On the Configuration tab, select Choose File and select your Excel document.
  3. Configure any manual mapping and images (see below) and select Save.

[ top ]

 

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments