Data Validation

[William08]

Hi all,

Need a help in validating the excel sheet so i need to check whether the mandatory columns are filed but the problem is i have to fetch the column header from row 2 and check if all the mandatory columns are filled. And also it should contain the data

(function executeRule(current, previous /*null when async*/ ) {

    var attachmentSysID = current.variables.upload_the_manager_approval_for_this_request;
    var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentSysID);
var parser = new sn_impex.GlideExcelParser();
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();

var mandatoryHeaders = ["User ID*", "Name"];
var rowNum = 0;
while (parser.next()) {
	rowNum = rowNum+1;
    var row = parser.getRow();

 gs.addInfoMessage("yesh "+ parser.secondRow.getColumnHeaders());
  for (var header in mandatoryHeaders) {

        var currentHeader = mandatoryHeaders[header];

        if (JSUtil.nil(row[currentHeader])) {
		
         gs.addErrorMessage("invalid data, fill all the mandatory columns");
        }
    }
}
 

})(current, previous);

 This is the code but it is fetching the column header from row 1[highlighted in yellow] it should fetch from row 2 [highlighted in green]

Thanks in advance

[Ankur Bawiskar]

@William08 

try this once

(function executeRule(current, previous /*null when async*/ ) {

var attachmentSysID = current.variables.upload_the_manager_approval_for_this_request;
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentSysID);
var parser = new sn_impex.GlideExcelParser();
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();

var mandatoryHeaders = ["User ID*", "Name"];
var rowNum = 0;
parser.next(); // try to use this to skip 1st row
while (parser.next()) {
rowNum = rowNum+1;
var row = parser.getRow();

gs.addInfoMessage("yesh "+ parser.secondRow.getColumnHeaders());
for (var header in mandatoryHeaders) {

var currentHeader = mandatoryHeaders[header];

if (JSUtil.nil(row[currentHeader])) {

gs.addErrorMessage("invalid data, fill all the mandatory columns");
}
}
}

})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[William08]

Hi @Ankur Bawiskar 

Thanks for the reply but if i use parser.next(); its not throwing any error if we haven't entered anything below those columns

[Sohithanjan G · Accepted solution]

hey @William08 

It seems like you're trying to validate an Excel sheet attachment to ensure that all mandatory columns are filled. Your script is on the right track, but there are a few issues that need to be addressed. Here's a corrected version of your script:

(function executeRule(current, previous /*null when async*/) {
var attachmentSysID = current.variables.upload_the_manager_approval_for_this_request;
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentSysID);
var parser = new sn_impex.GlideExcelParser();
parser.parse(attachmentStream);

var headers = parser.getColumnHeaders();
var mandatoryHeaders = ["User ID*", "Name"];

var isValid = true;

while (parser.next()) {
var row = parser.getRow();

for (var header in mandatoryHeaders) {
var currentHeader = mandatoryHeaders[header];

// Check if the header is present in the column headers
if (headers.indexOf(currentHeader) === -1) {
isValid = false;
gs.addErrorMessage("Missing mandatory column: " + currentHeader);
}

// Check if the value in the mandatory column is empty
if (JSUtil.nil(row[currentHeader])) {
isValid = false;
gs.addErrorMessage("Invalid data, fill all the mandatory columns for row " + (parser.getRowNumber() + 2));
}
}
}

if (isValid) {
gs.addInfoMessage("All mandatory columns are filled.");
}
})(current, previous);

Here are the key changes:

1. We check if each mandatory column header is present in the `headers` array, which contains the column headers obtained from the Excel file.

2. We check if the value in the mandatory column is empty for each row and keep track of whether all checks pass using the `isValid` variable.

3. We provide a user-friendly error message that specifies which mandatory column is missing or empty, along with the row number.

4. We add an info message to indicate that all mandatory columns are filled if `isValid` remains `true`.

This revised script should better validate the Excel sheet and provide more informative error messages for missing or empty mandatory columns.

Mark as Accepted solution & hit helpful if it suffice your requirement


BR, Sohith

[William08]

Hi Sohith,

Thanks for the help