Some PDIs are currently unavailable, and PDI actions are paused. View the latest updates here. Read More

Validate the number of rows in an attachment variable

jkelvynsant
Tera Contributor

3 hours ago

Hello, is it possible to validate the number of rows in an Excel file attached through an attachment variable? For example, if the file contains more than 10 rows, prevent the request from being submitted. 

var ExcelAttachmentValidator = Class.create();
ExcelAttachmentValidator.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    validate: function() {

        // receberá o sys_id do attachment
        var attachmentSysId = this.getParameter('sysparm_attachment');

        if (!attachmentSysId)
            return JSON.stringify({
                valid: false,
                message: 'Nenhum arquivo encontrado.'
            });

        var parser = new sn_impex.GlideExcelParser();
        var stream = new GlideSysAttachment().getContentStream(attachmentSysId);
        parser.parse(stream);
        var rowNumber = 0;
        var total = 0;

        while (parser.next()) {
            rowNumber++;
            if (rowNumber < 4)
                continue;

            var row = parser.getRow();
            var cnpj = row.B;

            if (cnpj && cnpj.toString().trim() != "")
                total++;
        }

        if (total > 10) {
            return JSON.stringify({
                valid: false,
                total: total,
                message: "A planilha possui " + total + " CNPJs. O limite permitido é 10."
            });
        }

        return JSON.stringify({
            valid: true,
            total: total
        });
    },
    type: 'ExcelAttachmentValidator'
});
0 Helpfuls
  • 90 Views
1 REPLY 1

Abhishek Pal
Mega Guru

3 hours ago

Hi @jkelvynsant ,

Yes, this is possible using GlideExcelParser, but there are two important issues in the current approach:

1. row.B is not a reliable way to read Excel column B.

GlideExcelParser returns an object whose property names are the Excel column headers. Therefore, use the actual header name, for example:

row['CNPJ']

2. Do not call asynchronous GlideAjax only from an onSubmit script.

The request can submit before the server response returns. Validate the attachment when it changes, store the result in a hidden variable, and let onSubmit check that result.

Recommended implementation:

Create a hidden catalog variable:

Name:
excel_validation_status

Create a client-callable Script Include:

var ExcelAttachmentValidator = Class.create();
ExcelAttachmentValidator.prototype = Object.extendsObject(AbstractAjaxProcessor, {

validate: function() {
var result = {
valid: false,
total: 0,
message: ''
};

var attachmentSysId =
(this.getParameter('sysparm_attachment') || '').toString();

if (!/^[0-9a-f]{32}$/.test(attachmentSysId)) {
result.message = 'No valid attachment was found.';
return JSON.stringify(result);
}

var attachmentGR = new GlideRecordSecure('sys_attachment');

if (!attachmentGR.get(attachmentSysId)) {
result.message = 'The attachment could not be accessed.';
return JSON.stringify(result);
}

var fileName = attachmentGR.getValue('file_name') || '';

if (!/\.xlsx$/i.test(fileName)) {
result.message = 'Please upload a valid XLSX file.';
return JSON.stringify(result);
}

var parser = new sn_impex.GlideExcelParser();

try {
var stream =
new GlideSysAttachment().getContentStream(attachmentSysId);

// Use 2 when the Excel header is located on row 3.
// GlideExcelParser header row numbering starts from 0.
parser.setHeaderRowNumber(2);
parser.setNullToEmpty(true);

if (!parser.parse(stream)) {
result.message =
'The Excel file could not be parsed: ' +
parser.getErrorMessage();

return JSON.stringify(result);
}

var headers = parser.getColumnHeaders();
var requiredHeader = 'CNPJ';

if (headers.indexOf(requiredHeader) == -1) {
result.message =
'The required CNPJ column was not found in the Excel file.';

return JSON.stringify(result);
}

while (parser.next()) {
var row = parser.getRow();
var cnpj = (row[requiredHeader] || '').toString().trim();

if (!cnpj)
continue;

result.total++;

// Stop early because additional rows are not required.
if (result.total > 10)
break;
}

if (result.total > 10) {
result.message =
'The spreadsheet contains more than 10 CNPJ records. The maximum allowed is 10.';

return JSON.stringify(result);
}

result.valid = true;
result.message =
'The spreadsheet was validated successfully. Total records: ' +
result.total;

return JSON.stringify(result);

} catch (ex) {
gs.error(
'ExcelAttachmentValidator failed for attachment ' +
attachmentSysId + ': ' + ex.message
);

result.message =
'An error occurred while validating the Excel file.';

return JSON.stringify(result);

} finally {
try {
parser.close();
} catch (ignore) {
}
}
},

type: 'ExcelAttachmentValidator'
});

Create an onChange Catalog Client Script on the attachment variable.

Replace excel_file with the actual attachment variable name:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading)
return;

g_form.setValue('excel_validation_status', '');
g_form.hideFieldMsg('excel_file', true);

if (!newValue)
return;

g_form.setValue('excel_validation_status', 'pending');

var ga = new GlideAjax('ExcelAttachmentValidator');
ga.addParam('sysparm_name', 'validate');
ga.addParam('sysparm_attachment', newValue);

ga.getXMLAnswer(function(answer) {
var result;

try {
result = JSON.parse(answer || '{}');
} catch (ex) {
g_form.setValue('excel_validation_status', 'invalid');

g_form.showFieldMsg(
'excel_file',
'An unexpected response was received while validating the file.',
'error'
);

return;
}

if (result.valid) {
g_form.setValue('excel_validation_status', 'valid');

g_form.showFieldMsg(
'excel_file',
result.message,
'info'
);
} else {
g_form.setValue('excel_validation_status', 'invalid');

g_form.showFieldMsg(
'excel_file',
result.message || 'The Excel file is not valid.',
'error'
);
}
});
}

Create an onSubmit Catalog Client Script:

function onSubmit() {
var attachmentSysId = g_form.getValue('excel_file');

if (!attachmentSysId)
return true;

var status = g_form.getValue('excel_validation_status');

if (status == 'pending') {
g_form.addErrorMessage(
'Please wait for the Excel file validation to complete.'
);

return false;
}

if (status != 'valid') {
g_form.addErrorMessage(
'Please upload and validate an Excel file containing no more than 10 records.'
);

return false;
}

return true;
}

Also configure the attachment variable with:

allowed_extensions=xlsx

The script above counts rows where the CNPJ column contains a value. This is safer than counting every parsed row because Excel files can contain formatted but empty rows.

If the header is not on Excel row 3, change:

parser.setHeaderRowNumber(2);

to the correct header-row index.

For strict data integrity, repeat the validation in the fulfillment Flow or server-side processing before importing the file, because client-side validation should not be the only control protecting downstream processing.

Official references:

https://www.servicenow.com/docs/r/zurich/api-reference/server-api-reference/GlideExcelParserScopedAP...

https://www.servicenow.com/docs/r/servicenow-platform/service-catalog/attachment.html

Hope this helps!

If this response helped, please mark it as Helpful.
If it resolves your issue, please Accept it as Solution.

Kind Regards,
Abhishek Pal

0 Helpfuls