Creating a BeanShell Spreadsheet Validator

Introduction

This article demonstrates how to create a BeanShell Spreadsheet Validator. The validator in the sample app ensures that all fields are populated and that there are no duplicate email addresses or phone numbers.

App Structure

The image below displays the Child Form.

The image below displays the Spreadsheet Field.

The image below displays the BeanShell Validator.

Implementation

Here is what the Beanshell script inside the spreadsheet looks like:

import java.util.HashSet;
import java.util.Set;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.service.FormUtil;

public boolean validate(Element element, FormRowSet rows, FormData formData) {
    boolean result = true;
    String elementId = element.getPropertyString("id"); // Get the element ID

    if (rows != null && !rows.isEmpty()) {
        Set emailSet = new HashSet();
        Set phoneSet = new HashSet();

        // Iterate over rows using a standard for loop for BeanShell compatibility
        for (int i = 0; i < rows.size(); i++) {
            FormRow row = rows.get(i);
            String email = row.getProperty("email");
            String phone = row.getProperty("phone");
            String firstName = row.getProperty("first_name");
            String lastName = row.getProperty("last_name");

            // Check if required fields are empty
            if (firstName == null || firstName.equals("") ||
                lastName == null || lastName.equals("") ||
                email == null || email.equals("")) {
                result = false;
            }

            // Check for duplicate emails
            if (email != null && !email.equals("") && !emailSet.add(email)) {
                result = false;
            }

            // Check for duplicate phone numbers, ignoring empty values
            if (phone != null && !phone.equals("") && !phoneSet.add(phone)) {
                result = false;
            }
        }
    }

    if (!result) {
        formData.addFormError(elementId, "Ensure that the first name, last name, and email fields are filled out, and verify that there are no duplicate email addresses or phone numbers.");
    }

    return result;
}

// Call validate method with injected variables
return validate(element, rows, formData);

Runtime

The image below displays Invalid Input (Duplicate Phone).

The image below displays Invalid Input (Missing Field Input)

Download the Sample App