Excel to json conversion using APi

[Shubham Deep]

Hi team,

I have got a requirement to convert the contents of an excel attachment(of a record) to json and return back the json object to the third part so that they can consume our environment by giving a record number in the body and they can get the json object of the excel attachment in return. I have got the result using the background script but when tried with scripted rest api, we are getting the response status as "success" but the body is showing as blank. Kindly help us in this regard.

Please refer to the attached excel and the desired output is:

[
    {
        "Scan ID": 1,
        "Database ID": 2,
        "App ID": "2965",
        "Column": abcd
    },
    {
        "Scan ID": 3,
        "Database ID": 4,
        "App ID": "2966",
        "Column": efgh
    },
    {
        "Scan ID": 5,
        "Database ID": 6,
        "App ID": "2967",
        "Column": ijkl
    }
]

My approach:

Background script (using attachment sys_id) ::

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream('sys_id of the attachment');
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();
while(parser.next()) {
for(i in headers){
var row = parser.getRow();
var data = headers[i] + ': ' + row[headers[i]];
var jsonData = JSON.stringify(row);

}
gs.log(jsonData);
}

OUTPUT::

*** Script: {"Scan ID":"1","Database ID":"2","App ID":"2965","Column":"abcd"}
*** Script: {"Scan ID":"3","Database ID":"4","App ID":"2966","Column":"efgh"}
*** Script: {"Scan ID":"5","Database ID":"6","App ID":"2967","Column":"ijkl"}

Scripted rest api code for taking sys_id of the attachment (GET method):

var ritmNumber = request.pathParams.attach_sys_id;
var result = [];

var ritmGr = new GlideRecord("sys_attachment");
ritmGr.addQuery('sys_id', ritmNumber);
ritmGr.query();
if (ritmGr.next()) {

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(ritmGr.sys_id);
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();
while (parser.next()) {

for (var i in headers) {
var row = parser.getRow();
var data = headers[i] + ':' + row[headers[i]];
var jsonData = JSON.stringify(row);

}
result.push(jsonData);

}

}
response.setBody(result);
response.setContentType("application/json");

OUTPUT:

Response: Success

Response Body

{
  "result": [
    "{\"Scan ID\":\"1\",\"Database ID\":\"2\",\"App ID\":\"2965\",\"Column\":\"acbd\"}",
    "{\"Scan ID\":\"3\",\"Database ID\":\"4\",\"App ID\":\"2966\",\"Column\":\"efgh\"}",
    "{\"Scan ID\":\"5\",\"Database ID\":\"6\",\"App ID\":\"2967\",\"Column\":\"ijkl\"}"
  ]
}

Scripted rest api code for taking record number of the attachment (GET method):

var ritmNumber = request.pathParams.ritm;
var result = [];
var ritmGr = new GlideRecord("sc_req_item");
ritmGr.addQuery('number', ritmNumber);
ritmGr.query();

if (!ritmGr.get("number", ritmNumber)) {
return new sn_ws_err.BadRequestError("RITM not found");
} else if (ritmGr.next()) {
var attachmentGr = new GlideRecord("sys_attachment");
attachmentGr.addQuery("table_name", "sc_req_item");
attachmentGr.addQuery("table_sys_id", ritmGr.sys_id);
attachmentGr.addQuery("file_name", "CONTAINS", ".xlsx");
attachmentGr.orderByDesc("sys_created_on");
attachmentGr.setLimit(1);
attachmentGr.query();

if (!attachmentGr.next()) {
return new sn_ws_err.BadRequestError("Excel attachment not found");
}

else if (attachmentGr.next()) {
var attachmentContent = GlideSysAttachment.getContent((attachmentGr.sys_id));
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentGr.sys_id);
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();
while (parser.next()) {

for (var i in headers) {
var row = parser.getRow();
var data = headers[i] + ': ' + row[headers[i]];
var jsonData = JSON.stringify(row);

}
result.push(jsonData);

}

}

}

response.setBody(result);
response.setContentType("application/json");

OUTPUT:

Response: Success

Response Body

{
  "result": []
}