ServiceNow Process an excel file attached to a fix script

 Process an excel file attached to a fix script

main();

function main() {

var excelDataJSON = this.getData();

if (excelDataJSON == null) {

gs.info("No data in the excel file");

return;

}

this.processData(excelDataJSON);

}

function processData(excelDataJSON) {

excelDataJSON.forEach(function(row) {

var approle = row.application_role;

var upn = row.upn

}

}

function getDataFromAttachedExcelFile(grAttachmentSysId) {

var myobjArray = [];

var attachmentSID = grAttachmentSysId; // pass your excel attachment sys_id

var parser = new sn_impex.GlideExcelParser();

var attachment = new GlideSysAttachment(); // use attachment sys id of an excel file

var attachmentStream = attachment.getContentStream(attachmentSID);

parser.parse(attachmentStream);

while (parser.next()) {

var row = parser.getRow();

var myData = {};

myData.upn = row['UPN'];

myData.application_role = row['Application Role'];

myobjArray.push(myData);

}

return myobjArray;

}

function getData() { // Get the Excel file, that is attached to this Fix Script

//var encodedQuery = "table_sys_id=f2deb6a587651e90868c0f230cbb3543^ORDERBYDESCsys_created_on";

var encodedQuery = "table_sys_id=ca231dc1c3a612100001554d05013114^ORDERBYDESCsys_created_on";

var grAttachment = new GlideRecord("sys_attachment");

grAttachment.addEncodedQuery(encodedQuery);

grAttachment.setLimit(1);

grAttachment.query();

if (grAttachment.next()) { // Transform the Excel file data in JSON format

return this.getDataFromAttachedExcelFile(grAttachment.getValue("sys_id"));

}

return null;

}