Receive the data, per submission, in Excel format! Configure an email with an Excel file that you’ll receive after sending a submission.
No MoreApp account yet? Sign up here.
This feature is available in the Branch, Tree and Forest plan. Visit our Pricing page.
1. Create the Excel template
Open Excel and design the template that will show your data. Use placeholders in your Excel template in order to know where the form entries need to be placed. You will know which are the correct placeholders to use by checking the data names of the widgets.
Note: Data names of the widgets can be found when activating the advanced mode of the widget's properties panel.
When creating the Excel template, most of the widgets need to be configured using the placeholder {dataname}. For example, a Text-widget named ‘Company’ will have the placeholder {company} in the template.
However, some other widgets use a different placeholder: {%dataname}
This placeholder can be used for the Photo- and Signature-widgets.
Check the following image as an example of how the placeholders will look in your template.
Notes:
-
You can add data in multiple tabs if needed.
-
Video-, Catalogue-, Zip Code NL- and Pin-widgets won't be visible in the export.
-
The map of the Location-widget is not displayed in the report. However, you can add the latitude and longitude by selecting the appropriate placeholders from the dropdown menu in the Email tab.
1.1 Subform-widget
A Subform-widget has to be opened with a # and closed in the Excel template with a / as shown in the example below.
All information in the loop (between the # and / ) will be copied each time the subform is filled out.
Note: It is important to use unique data names in the Subform. Avoid using the same name for the Subform-widget and the widgets inside of the subform.
It is also possible to have a subform inside of a subform. To configure this in your Excel template, you will need to open and close both of them as shown in the example below.
{#subform}
{widget}, {widget}
{#subform1}
{%photo}
{/subform1}
{/subform}
Take the example, change the word ‘subform’ to the data name from your Subform-widget. Do the same for the widgets inside the subform.
Find an overview of how to use different ways of looping with the output examples below.
Template:
Output:
Note: Avoid adding extra data in the same row as the subform loop. The loop applies to the entire row, including all placeholders between # and / across rows from left to right.
1.2 Radio- and Lookup-widgets
These widgets use the standard placeholder {dataname}. However, when using these widgets, keep in mind that the value appearing in the Excel report will be the one added in the Value field.
In order to check this value, open the Advanced mode of the widget’s Properties. Besides changing the name of the Options, you can also change the name of the Value.
Note: Value in PDF does not apply to the Excel integration.
For example, the placeholder {meeting} will show either Sales or Support in the report, depending on the selection in the form.
You can also rename the Value field. This does not have to be the same as the Options field. For example, the user will need to select Sales or Support in the form. The output, however, can show something else (like an emoji).
1.3 Hide fields without data
If you use optional fields or rules hiding certain widgets, you need to configure this in the Excel template as well.
Make sure to indicate an opening and closing of the fields (similar to how Subform loops are structured with # and /). As a result, the field will only appear if data is entered.
Follow this format: {#dataname} {dataname} {/}
1.4 Checkbox-widget
If you are using a Checkbox-widget, the placeholder {dataname} will show TRUE or FALSE in the output.
Do you want to represent the answer differently, like Correct / Incorrect, or ❌ / ✅ ? Then you can use this configuration:
{#dataname}Correct{/dataname}{^dataname}Incorrect{/dataname}
1.5 Formulas
You can also add formulas to your Excel template to create conditions for the output.
You will need to add a separate tab to your template. You could call this tab Raw Values. This is where you will receive the answers from the form. Therefore, make sure you add the correct data names as placeholders.
For example:
-
If you want to show an “X” in a cell if a certain value is set to “Yes” you can use the following formula in that cell = IF('Raw Values'!$B2 = "Yes", "X", "")
-
Raw Values = the name of the separate tab
B2 = the cell you are referring to from the separate tab. Make sure to refer to the correct cell.
If the value shown in this cell = Yes, then an X will be shown. Otherwise, it stays empty. -
Replace the ‘Yes’ for a ‘No’ in the formulas of column C.
Here you can see an example of the tab where the formulas are added:
Here you can see an example of the tab 'Raw Values':
This combination will create the following output:
Notes:
-
This separate tab is needed because you can’t use a placeholder directly in a formula. This would not work: IF({question1}="Yes", "X", "")
-
You can even make the second tab invisible once you are done with the configuration to avoid confusion.
-
This is just one example of how to work with formulas in your Excel template. There are many more possibilities. However, creating advanced Excel reports depends on your skills and advanced knowledge of Excel.
-
If you want to add colours to your answer selection, we recommend using Conditional Formatting.
2. Configure the integration
Now that you have successfully created your template, go to the MoreApp Platform, select a form and click on Edit Form. Access the Integrations tab and click on Add Integration. Select the Excel integration and click on Add.
Note: This integration will trigger an external system on each submission. You will receive an Excel file every time a form is sent.
You can now start configuring the Excel integration.
First of all, you will need to upload an Excel template. Click on Select file and choose the template you have just created.
Note: Make sure the template has a .xlsx or .xlsm extension.
-
Check the box: Use legacy delimiters if you use a placeholder with a dollar sign ($) in the subject, body, filename and the Excel template.
-
In the Excel file name field, write the name of the export file that will be attached to your email.
-
In the Recipients field, type in the email address of the person who will receive the file. Use a comma to separate the emails if there is more than one.
-
In the Dynamic recipient field, you will have the possibility to send the file to a different person each time. This option uses the Email-widget in your form, as a recipient.
-
Select Copy to user if you want to send a copy of the Excel to the user who filled in the form.
Note: If your email is bigger than 19 MB, your Excel file and images will be added to the body of the email via a download link. The download link is valid for 7 days.
You can also select the quality of the images you will receive. The section Image Quality is set to Original, by default, but you can select the option you prefer: Original, High, Medium or Low.
To finish the configuration, type a Subject line and Body for the email. You can also use placeholders.
Note: Some widgets are not supported in the email body of the Excel integration. Here is the list of widgets you can not use as placeholders: Subform, Photo, Signature, Pin, Location, File, Catalogue, Header, HTML, Label, Image and Help-widgets.
Click on Validate.
Note: If an error occurs after validating or your template is not working well, this means that there are wrong placeholders used in the Excel file.
Once you are finished editing the form, click on Save & Publish.
3. Download the Report
It is possible to download the Excel report on the Submissions tab, by clicking on the green checkmark of each submission. A new section appears and you need to click on Download report.
4. Congratulations!
You will receive an Excel file with your form's data after a new submission is submitted.
Here is an example of the Excel file:
Tip: If you don't want to receive two emails, you can remove the email that MoreApp generates, under the Email tab, by clicking on the trash icon.
Note: You can use this Excel template to see what the report would look like with the Excel integration. To test the Excel template, you can select the predefined "Work Order” template in the Platform.
Keep in mind that this is just an example. Get in touch if you want a customised report and request a quotation.
Want to stay up to date with our latest tutorials? Go to YouTube and hit the Subscribe button!