How to validate the Excel file data in service catalog before submitting the request?

ServiceNow_Boy
Tera Contributor

‎11-27-2024 08:33 PM

How to validate the Excel file data in the service catalog before submitting the request?

0 Helpfuls
  • 428 Views
1 REPLY 1

Community Alums
Not applicable

‎11-27-2024 09:13 PM

Hi @ServiceNow_Boy ,

Write a client script to call a scripted REST API or a server-side script to validate the Excel file

.

 

function onSubmit() {
    var attachmentSysId = g_form.getValue('attachment_variable'); // Replace with your variable name
    
    if (!attachmentSysId) {
        alert("Please attach an Excel file.");
        return false;
    }

    var ga = new GlideAjax('ExcelValidation');
    ga.addParam('sys_id', attachmentSysId);
    ga.getXMLAnswer(function(response) {
        var result = JSON.parse(response);
        if (result.status === 'error') {
            alert("Validation Error: " + result.message);
            return false; // Prevent submission
        } else {
            return true; // Allow submission
        }
    });

    return false; // Wait for the async response
}

 

.The Script Include ExcelValidation will handle the validation logic

 

var ExcelValidation = Class.create();
ExcelValidation.prototype = {
    initialize: function() {},

    validateExcel: function(sysId) {
        if (!sysId) {
            return JSON.stringify({ status: 'error', message: 'No file attached' });
        }

        var attachment = new GlideSysAttachment();
        var excelData = attachment.getContentStream(sysId);
        var workbook = new sn_impex.ExcelParser().parse(excelData);

        // Assume the first sheet contains data
        var sheet = workbook.getSheet(0);
        for (var i = 1; i < sheet.getRowCount(); i++) {
            var row = sheet.getRow(i);
            var value = row.getCell(0).toString();

            if (!value || value.length < 5) {
                return JSON.stringify({ status: 'error', message: 'Row ' + i + ': Invalid data' });
            }
        }

        return JSON.stringify({ status: 'success', message: 'Validation passed' });
    },

    type: 'ExcelValidation'
};

 

.

If using a scripted REST API to validate data:

  1. Create a new Scripted REST API with an endpoint.
  2. Call the ExcelValidation script from the endpoint.

 

Thank You @ServiceNow_Boy .....!.

 

0 Helpfuls