Step 1:
Create a catalog item and make the attachment mandatory.
Step 2:
After the request is created, get the attachment sys_id.
Step 3:
Create a Flow Action :
Input:
attachment_sys_id (String)
Output:
excel_parse_output (JSON)
total_records (Int)
Flow Action Script:
(function execute(inputs, outputs) {
var attachmntSysId = inputs.attachment_sys_id;
var excelParseOutPut = [];
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// Use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(attachmntSysId);
parser.parse(attachmentStream);
//retrieve the column headers
var headers = parser.getColumnHeaders();
//headers[0], headers[1], headers[2], headers[3],
var TotalHoursRawEx = headers[7]; //Total Hours
while(parser.next()) {
objct = {};
row = {};
var row = parser.getRow();
//Direct from Excel
var total_hours = '';
if(row[TotalHoursRawEx]){
total_hours = row[TotalHoursRawEx];
}
objct.total_hours = total_hours;
excelParseOutPut.push(objct);
}
outputs.total_records = excelParseOutPut.length;
outputs.excel_parse_output = JSON.stringify(excelParseOutPut);
})(inputs, outputs);
Step 4:
Now you have the JSON, You can proceed with adding to the Servicenow Table
(function execute(inputs, outputs) {
var JsonArray = [];
if(inputs. JsonData){
JsonArray = JSON.parse(inputs.JsonData);
}
//Loop
for (var i = 0; i < JsonArray.length; i++) {
var item = JsonArray[i];
var total_hours = item.total_hours;
//Script Include
var createFields = {
total_hours: total_hours
};
//gs.info('Created record sys_id: ' + createdSysID);
}
})(inputs, outputs);
Thank you.