Example: Script Executor Migrates Records from Microsoft Excel to a Collection

Follow an example that uses a Process, a custom Script Executor and a Script to migrate records in a Microsoft Excel spreadsheet to a Collection.

Overview

Intended audience: Process designers, Web designers, graphic designers, software developers, coding engineers

Tags: Script Executor; Collection; administration; Microsoft Excel; record migration; employee data

This example demonstrates how to migrate records in a Microsoft Excel spreadsheet to an existing Collection. In this example, the Collection stores information about each company employee, such as each employee's first and last name, hire date, salary, and how many vacation days used. These employee records already exist in an Excel spreadsheet, but must be migrated to the Collection to save time and prevent human error. The Collection must already exist with its Screens that correspond with the fields in the Excel spreadsheet records.

While it would be easier to export a Comma-Separated Values (CSV) file from the Excel spreadsheet and then import that CSV file into the Collection to automatically create multiple records, this use case and its accompanying ProcessMaker Platform assets benefits those organizations where business stakeholders do not have access to Collections or the appropriate permissions to edit them. Any User may benefit from this use case.

This example uses the following ProcessMaker Platform assets:

• Process: This example uses a Process named "Excel Spreadsheet Records to Collection Records Example," which may be downloaded for this example and then imported. Except for the Script Executor and Collection, the remaining ProcessMaker Platform assets in this example import with the Process and are already referenced in appropriate Process model nodes. This Process manages the migration process as summarized below:

  • A specified Request participant uploads the Excel file from which to migrate its records to the Collection.

  • A Script reads the Excel file records and stores them in that Request's JSON data model.

  • A specified Request participant reviews the migrated Excel file records.

  • If that Request participant indicates that the Excel file records read correctly, save those records to an existing Collection; otherwise, workflow returns to the same Request participant who uploaded the Excel file to do so again.

• Script Executor: This example uses a custom Script Executor based on the default PHP Script Executor, but requires the PHPSpreadsheet package. The PHPSpreadsheet package is an open-source PHP library that reads and writes spreadsheet files. Note that after creating this Script Executor, it may be used for any Script that uses PHP and requires the PHPSpreadsheet package.

• Scripts: This example uses the following Scripts:

  • Read Excel File to JSON: This Script reads the records from the Excel file, then stores the Excel spreadsheet records in that Request's JSON data model. This Script runs from the custom PHP Script Executor to read the records from the Excel spreadsheet. By default, this Script stores the Excel spreadsheet records in a Request variable named persons.

  • Save Records to Collection: This Script writes the records stored in the Request variable persons to the Collection.

• Screens: This example uses the following Screens:

  • Upload Person Records: This Screen allows a specified Request participant to upload the Excel file from which to migrate its records.

  • Review Uploaded Person Records: This Screen allows a specified Request participant to review the migrated Excel file records and then indicate if those records migrated correctly.

  The Collection uses its own Screens which are not relevant to how this example functions.

• Collection: This example uses an existing Collection to save the read spreadsheet records that the Save Records to Collection Script writes to create the Collection records. This Collection contains 18 records already to demonstrate the content of each record and how fields in the Excel spreadsheet must correspond with the Collection content. Note that the Collections package must be installed.

Click the video below to watch a demonstration of this example.

Video demonstration how to migrate Excel spreadsheet records to a Collection

This example contains the following procedures in this order:

1. Import the Collection: Since the purpose of this example is to demonstrate how to use a custom Script Executor, this example provides the Collection that the example uses. See Import the Collection.

2. Import the Process: Import the Process that this example uses. The Process contains the two Scripts and Screens this example uses. See Import the Process.

3. Create the custom Script Executor: Create the Script Executor that builds the PHPSpreadsheet package into its Docker container. See Create the Custom Script Executor.

4. Configure the Script that reads the Excel file: Configure the Script named "Read Excel File to JSON" to run using the custom Script Executor. See Configure the Script That Reads the Excel File.

5. Download the Excel file: Download the Excel file that contains sample records to be migrated to the Collection. See Download the Excel File.

6. Start a Request: Start a Request for the Process this example uses. See Start a Request for the Example Process.

Import the Collection

Package Required

The Collections package must be installed.

Permission Required

Your user account or group membership must have the "Collections: View Collections" permission to view the list of Collections unless your user account has the Make this user a Super Admin setting selected. This permission is different than record permissions in a Collection that allow you to view the records in that Collection.

See the Collections permissions or ask your Administrator for assistance.

Prior to importing the Collection, download the Collection this example uses named people.json:

50KB

people.json

Follow these steps to import the Collection as described in this example:

1. View your Collections. The Collections page displays.

2. Click the Import button. The Import Collection screen displays.
3. Click Browse to locate the Collection you downloaded named people.json.

4. Click Import. The Import Collection screen displays to indicate that the Collection imported correctly.
5. Click List Collections. The Collections page displays.

6. Make note of the imported Collection's ID as displayed in the Collection ID column. This ID is required to specify to which Collection to write the read Excel spreadsheet records that are stored in the persons Request variable during a Request. You will revise the Collection ID after you import the Process for this example.

7. View the records for the Collection named People that this example uses.

8. View a record in this Collection to see an example of the information to be migrated from the Excel spreadsheet.

Import the Process

Your user account or group membership must have the following permissions to import a Process unless your user account has the Make this user a Super Admin setting selected:

• Processes: Import Processes

• Processes: View Processes

See the Processes permissions or ask your Administrator for assistance.

Prior to importing the Process, download the Process model this example uses named Process - Excel Spreadsheet Records to Collection Records Example.json:

80KB

Process - Excel Spreadsheet Records to Collection Records Example.json

Below is the Process model after the Process described in this example is imported and edited in Process Modeler.

Process model of the "Process - Excel Spreadsheet Records to Collection Records Example.json"

Follow these steps to import the Process:

1. View your active Processes. The Processes tab displays.

2. Click the Import button. The following message displays: You are about to import a Process. After importing, you can reassign users and groups to your process.

   
3. Click Browse to locate the Process model you downloaded named Process - Excel Spreadsheet Records to Collection Records Example.json.

4. Click Import. The Import Process screen displays.

5. From the Import Process screen, locate the Configuration section below the Status section.
6. From the Configuration section, assign the Request participants and user accounts for this example. Follow these guidelines:

   • Assign the Start Event element named "Start Event" to the user and/or group

     Assign which user and/or group can start a Request of your imported Process. Type into the Assign Start Event setting to filter users and/or groups that display in that setting's drop-down menu. If you will start a Request for this example, then assign the Start Event element to your user.

   • Assign the Form Task element named "Upload File" to the user and/or group

     Assign which user and/or group uploads the Excel file to the Process from which its records migrate to the Collection. Type into its Assign Task setting to filter users and/or groups that display in that setting's drop-down menu. Optionally, use the Requester option to assign that Task to the user that started that Request.

   • Assign the Form Task element named "Review Upload" to the user and/or group

     Assign which user and/or group reviews the records after they have been read from the Excel file. Type into its Assign Task setting to filter users and/or groups that display in that setting's drop-down menu. Optionally, use the Previous Task Assignee option to assign the Task to the assignee of the "Upload File" Form Task.

   • Select which user to run the "Read Excel File to JSON" Script

     Select which user account to run the "Read Excel File to JSON" Script, which is the Script that reads the Excel spreadsheet records and stores them to that Request's JSON data model. This Script runs when the Process File Script Task element triggers. Type into its Run script setting to filter users and/or groups that display in that setting's drop-down menu. Ensure that the selected user or group members have appropriate permissions to run Scripts.

   • Select which user to run the "Save Records to Collection" Script

     Select which user account to run the "Save Records to Collection" Script, which is the Script that writes the records stored in that Request's JSON data model to the Collection. This Script runs when the Save to Collection Script Task element triggers. Type into its Run script setting to filter users and/or groups that display in that setting's drop-down menu. Ensure that the selected user or group members have appropriate permissions to run Scripts.

   • Assign which user and/or group can cancel Requests

     Assign which user and/or group can cancel Requests for your imported Process. If no user or group are selected, no one can cancel a Request from this Process. Type into the Assign Cancel Request setting to filter users and/or groups that display in that setting's drop-down menu.

   • Assign which user and/or group can edit Request data

     Assign which user or group has permission to edit Request data from this Process. By editing Request data, these users and group members can adjust the data that Request participants have submitted during a Request. If no user or group are selected, no one can edit Request data from this Process. Type into the Assign Edit Data setting to filter users and/or groups that display in that setting's drop-down menu.

7. Click Save. The Processes page displays the imported Process.

8. View and then edit the imported Process. The Process model displays.

9. Select the Save to Collection Script Task element that runs a Script to write the Excel spreadsheet records stored in that Request's JSON data model in the persons Request variable.

   The settings for this Script Task element display.

10. From the Configuration panel, locate the Script Configuration setting, and then click theicon to edit this Script's configuration. The Script Config screen displays with configuration settings the Request sends to the Script when the Save to Collection Script runs.

    {

    "recordsVariableName": "persons",

    "collectionId": 18

    }

11. Change the value of the collectionID key name to the Collection ID you imported for this example.

12. Save the Process model.

Would you prefer to save the Excel spreadsheet records to a different Request variable than persons when the Read Excel File to JSON Script that run when the Process File Script Task element triggers? Locate the Script Configuration setting for the Process File Script Task element as described above, and then change the JSON key name recordsVariableName's value to the Request variable name you want: { "fileVariableName": "recordsFile", "recordsVariableName": "persons" }

Ensure to change the JSON key name recordsVariableName's value for the Save to Collection Script's configuration to the same value.

Create the Custom Script Executor

Follow these steps to create the custom Script Executor as described in this example:

1. View your Script Executors. The Script Executors page displays.

2. Click the +Script Executor button. The Add New Script Executor screen displays.
3. In the Name setting, enter the unique name for the Script Executor. This name displays from Script configuration settings, so enter a descriptive name that designers configuring their Scripts understand what customization this Script Executor provides. This example uses the name PHP with PHPSpreadsheet package.

4. In the Description setting, enter a description for the Script Executor.

5. From the Language setting, select the PHP option. The default Dockerfile content to run PHP Scripts displays in the Dockerfile setting. The Dockerfile content includes the SDK for that language.

6. From the Dockerfile setting, add the following commands that contain the dependencies to use the PHPSpreadsheet package and integrate this package into this Docker container:

   RUN apt-get update -y \

   && apt-get install -y \

   libpng-dev \

   && apt-get clean -y \

   && docker-php-ext-install gd zip \

   && docker-php-ext-enable gd zip

   RUN composer require phpoffice/phpspreadsheet

7. Click Save and Build to build the Docker container from which the Script Executor runs Scripts. The Build Command Output setting displays below the Dockerfile setting as the Script Executor builds the Docker container in real-time. If the Docker container builds successfully, the following message displays: Executor Successfully Built. You can now close this window. If building the Docker container is unsuccessful, the following message displays: Error Building Executor. See Output Above.. The Build Command Output setting displays the Dockerfile error.

8. Click Close.

Configure the Script That Reads the Excel File

Your user account or group membership must have the following permissions to configure a Script unless your user account has the Make this user a Super Admin setting selected:

• Scripts: Edit Scripts

• Scripts: View Scripts

See the Scripts permissions or ask your Administrator for assistance.

Follow these steps to configure the Scripts as described in this example:

1. View your Scripts. The Scripts page displays.

2. Click the Configure iconfor the Script named Read Excel File to JSON. The Edit Configuration page displays.
3. From the Script Executor drop-down menu, select the Script Executor you created for this example to run this Script.

4. Click Save.

The second Script this example uses, Save Records to Collection, by default configures to use the Script Executor named PHP Executor when it is imported.

Download the Excel File

Download the Excel file that this example uses to migrate its records to an existing Collection.

9KB

Persons.xlsx

Start a Request for the Example Process

The example is ready to demonstrate. To demonstrate this example, start a Request of this Process.

Follow these steps to start a Request of this Process as described in this example:

1. Log on as a user you configured from the Start Event element that can start Requests for this Process.

2. Start a Request of this Process. Remember that the Process name is Excel Spreadsheet Records to Collection Records Example.

3. Open the Upload File Task from your To Do Tasks. After opening the Task, the Screen to upload the Excel file in the example displays. Ensure that you have downloaded the Excel file.

4. From the Upload File Task, click the Select File button, locate the Excel file you downloaded, and then click the Upload File button.

   The Review Upload Task automatically opens because the Upload File Form Task element in the Process model uses the Display Next Task to Task Assignee option. The Read Excel File to JSON Script read the records from the Excel spreadsheet, stored them into the persons Request variable, and now displays their contents in the Review Upload Task.

5. From the Review Upload Task, click the Save File button.

6. View the imported Collection records to see two additional records in this Collection that have been migrated from the Excel spreadsheet.