Excel Fill Template Administration

Estimated Reading Time:  9 minutes

Overview

Excel Fill Template Administration enables uploading Excel templates to your environment. For example, if you have a dashboard of submission data that you want to download as an Excel (.xlsx, .xlsm) file using a sheet of your design. You can configure your application to populate your template with submission data. After the template populates, you can download it as an Excel file.

What You'll Learn

After completing this article, you’ll know how to create, upload, download, and delete Excel templates. You'll also learn how to generate excel sheets in the Module Builder.

Creating an Excel Template

When you configure your application to download Excel data, you'll download the entire array of submission data. Along with your data, you'll also download:

  • The module ID.

  • The submission ID.

  • The user ID.

  • The timestamp for the creation of the submission data.

  • The timestamp for the last modification of the submission data.

Here's an example of how submission data looks in an Excel template:

Typically, you don't want all the submission data cluttering your Excel sheet. The easiest way to select the desired information is to create two spreadsheets. You can title these spreadsheets in your template:

  • All Data: This is the spreadsheet you'll configure to collect all the submission data from your application.

  • Selected Data: This is the spreadsheet you'll use to select and populate only the data you want from the All Data spreadsheet. You'll use the spreadsheet's calculations and rules to do this.

Here's how your Selected Data spreadsheet might look after it filters the All Data spreadsheet:

You'll notice that the Selected Data spreadsheet omits the previously mentioned submission data details. After you've created your template, save it on your computer where it's easy to find. Then, upload it to Excel Fill Template Administration.

Uploading an Excel Template

When you upload an Excel template using Excel Fill Template Administration, you can use it anywhere in your environment. We'll talk about how to configure it into your modules later in this article.

NOTE  You can upload any size file to Excel Fill Template Administration. But, this administration page only supports .xlsx and .xlsm file types.

To upload your Excel template to your environment:

1. At the top right of the Unqork Designer Platform, click the Settings ▾ drop-down.
2. Click Administration.
3. Under Integration, select Excel Fill Template Administration.
4. Click the Upload Button (Upload) button.
5. From your computer's folder, select your template file.

6. Click Open.

Your uploaded file displays in the Excel Fill Template Administration list. You'll see that this administration page sorts files alphabetically.

Replacing an Excel Template

If you make any changes to your template, you can easily upload it again. Uploading files with the same name automatically replaces the previous version. After you've uploaded the updated template, you'll see its Last Modified timestamp changes.

To replace your Excel template:

1. Navigate to the Excel Fill Template Administration page.
2. Click the Upload Button (Upload) button.
3. From your computer's folder, select your template file.
4. Click Open.
5. In the File Exists pop-up, click Confirm.

Removing an Excel Template

You can also remove a template from your environment.

To remove your Excel template:

1. Navigate to the Excel Fill Template Administration page.
2. Select the template file to delete.
3. Click the Trash icon (Remove) button in the top-right corner.

4. Click Confirm. The file no longer displays in the Excel Fill Template Administration list.

Downloading an Excel Template

When you promote your applications to another environment level, your Excel templates won't promote with them. This administration page allows you to download your environment's Excel templates. That way, you can access the Excel Fill Template Administration page from other environment levels and upload your template(s) again.

To download an Excel template file from Excel Fill Template Administration:

1. Navigate to the Excel Fill Template Administration page.
2. Select the file to download.
3. Click the Download Icon (Download) button. The file downloads to your computer's Downloads folder.

Referencing an Excel Template

Now that you have an Excel template uploaded, reference it in a module. For this example, use a Plug-In component to get your submission data from a schema module. Then, you'll set up a separate Plug-In to generate the .xlsx file. When you open that file in your spreadsheet program, you'll see the data in your template.

NOTE  The more data you export, the longer it takes to download the .xlsx file.

For this example, use the following module for collecting submission data: https://training.unqork.io/#/form/60f892a5b07b3064183cb94e/edit.

Submissions in this module store in a schema module: https://training.unqork.io/#/form/60f8941bb07b3064183cb994/edit.

Now, create the module that generates your Excel file from the data submissions stored in the schema module.

NOTE  You don't need to do anything with the above links. There is already submission data in the schema module that you can reference in your Excel template.

Here's how the completed use case looks in the Module Builder:

A static image displaying the Module Builder Canvas with an excel template setup.

Here's how the completed use case looks in Express View Express View is how your end-user views you application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View.:

A static image displaying a Submission Dashboard with the Excel Template button.

What You'll Need

To set up this use case, you need:

Configure the Initializer Component

First, set up an Initializer to trigger a Plug-In to get submission data from the schema module.

1. In the Module Builder, drag and drop an Initializer Icon Initializer component onto your canvas.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module., enter initStart.
3. In the Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . field, enter initStart.
4. On the left side of the configuration window, click Actions Icon Actions.
5. From the Trigger Type Deteremines how the component triggers. drop-down, select New Submission The component fires when the page or called module loads without an existing submission ID. Dashboards and remote execute modules are great uses for this trigger..
6. In the Outputs table, enter the following:
Property ID Type Value

pluginGetSubmissions

trigger

GO

A static image displaying the initStart Initalizer's Actions settings.

7. Click Save & Close.

Configure the moduleId Hidden Component

The moduleId Hiden Icon Hidden component holds the module ID of your schema module so your pluginGetSubmissions Plug-In Component Icon Plug-In component can reference the module's submission data.

1. Drag and drop a Hiden Icon Hidden component onto your canvas, placing it below the initStart Initializer Icon Initializer component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter moduleID.
3. In the Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . field, enter, enter moduleId.
4. In the Default Value field, enter 60f8941bb07b3064183cb994. This is the module ID of your schema module.
5. Click Save & Close.

Configure the pluginGetSubmissions Component

Next, set up a Plug-In component to retrieve the submission data from your schema module. You'll also configure the component to display the submission data in a dashboard.

1. Drag and drop a Plug-In Component Icon Plug-In component onto the canvas, placing it below the moduleId Hiden Icon Hidden component.
2. In the Property ID and Canvas Label Text fields, enter pluginGetSubmissions.
3. From the Internal Services drop-down, select List Submissions for Dashboard.
4. In the Inputs table, enter the following:
Property ID Mapping

moduleId

moduleId

'firstName,lastName,phoneNumber,stateOfResidence,typeOfInsurance,insuranceAmount'

fields

A static image displaying the pluginGetSubmissions Plugin Component's Data settings.

5. Click Save.

Configure the ViewGrid Component

Now, set up a dashboard to view your submission data. When configured, you'll see the same information in the dashboard as your Excel file.

1. Drag and drop a View Grid component ViewGrid component onto your canvas, placing it below your pluginGetSubmissions Plug-In Component Icon Plug-In component.
2. In the Label field, enter Submission Data Dashboard.
3. In the Property Name field, enter vgSubmissions.
4. In the Action field, enter null.
5. In the Inputs table, enter the following:
ID Required

pluginGetSubmissions

Checked Box (checked)
6. In the Outputs table, enter the following:
ID Mapping

submissions

id
7. In the Display table, enter the following:
ID Formula Heading Type CSS Button

firstName

 

First Name

 

 

☐ (Unchecked)

lastName

 

Last Name

 

 

☐ (Unchecked)

phoneNumber

 

Phone Number

 

 

☐ (Unchecked)

stateOfResidence

 

State of Residence

 

 

☐ (Unchecked)

typeOfInsurance

 

Type of Insurance

 

 

☐ (Unchecked)

insuranceAmount

 

Insurance Amount

 

 

☐ (Unchecked)

A static image displaying the vgSubmissions View Grid component's Action, Input, Output, and Display settings.

8. Click Save.

Configure the pluginExcelTemplate Component

Next, set up a Plug-In to insert your submission data into your Excel template. You'll configure it to place the data into a specific spreadsheet.

Recall our previous Excel file example. We created an Excel template with All Data and Selected Data spreadsheets. You'll configure the Plug-In component to add submission data to the All Data spreadsheet. The Selected Data spreadsheet automatically populates based on the spreadsheet's calculations. We also titled the template file as Excel Fill Example.xlsx. You'll input this template into the Plug-In and the Plug-In outputs the submission data into a new Hidden component called url.

1. Drag and drop a Plug-In Component Icon Plug-In component onto your canvas, placing it below your vgSubmissions View Grid component ViewGrid component.
2. In the Property ID and Canvas Label Text fields, enter pluginExcelTemplate.
3. From the Internal Services drop-down, select Upload Excel Template.

A static image displaying the pluginExcelTemplate Plug-In Component's Data Settings.

4. In the Inputs table, enter the following:
Property ID Mapping

'Excel Fill Example.xlsx'

templateName

pluginGetSubmissions

data["All Data"]
5. In the Outputs table, enter the following:
Property ID Mapping

url

url

A static image displaying the pluginExcelTemplate Plug-In Component's Data Input and Output settings.

6. Click Save.

Configure the url Hidden Component

This Hidden component holds the generated Excel file.

1. Drag and drop a Hiden Icon Hidden component onto your canvas, placing it below your pluginExcelTemplate Plug-In Component Icon Plug-In component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter url.
3. In the Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . field, enter url .
4. Click Save & Close.

Configure the Decisions Component

Configure a Decisions component to automatically download the Excel file. After pluginExcelTemplate Plug-In Component Icon Plug-In generates the Excel file, the Decisions component outputs a pageOpen function. The file downloads to your default browser.

1. Drag and drop a Decisions Component Decisions component onto your canvas, placing it below your url Hiden Icon Hidden component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter ruleExcelTemplate.
3. In the Canvas Label Text * field, enter ruleExcelTemplate.
4. On the left side of the configuration window, click Actions Icon Actions.
5. Under Trigger Type Deteremines how the component triggers., select Watch.
6. In the Inputs table, enter the following:
Property ID Alias Type Required Silent

url

 

 

Checked Box (checked)

☐ (Unchecked)
7. In the Outputs table, enter the following:
Property ID Type

url

pageOpen
8. In the Micro Decisions table, enter the following:
Input Values Output Values
url url_pageOpen

 

=url

A static image displaying the ruleExcelTemplate Decisions Component's Actions settings.

9. Click Save & Close.

Configure the Button Component

Lastly, you’ll configure a Button to trigger the whole process.

1. Drag and drop a Button Component Button component onto your canvas, placing it below your ruleExcelTemplate Decisions Component Decisions component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter btnExcelTemplate.
3. In the Canvas Label Text * field, enter Generate Excel.
4. On the left side of the configuration window, click Actions Icon Actions.
5. Under Action Type The action performed by the button when selected., select Event.
6. Under Triggers, in the On Click field, enter pluginExcelTemplate.

A static image displaying the btnExcelTemplate Button Component's Action settings. The Action Type is set to event and the On Click field is set to "pluginExcelTemplate".

7. Click Save & Close.
8. Save your module.

Now, test your configuration. Preview your module in Express View Express View is how your end-user views you application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View.. Submission data displays on the dashboard. Click Generate Template, and an Excel file displays in your browser. Open the Excel file and view the details. Using our example, all submission details display in the All Data spreadsheet. Only the specific information from your dashboard displays in your template's Selected Data spreadsheet.

Resources