Tera Contributor

[Saipriya Thokal]

Hi,

Can anyone help me how can I convert Json into excel.
For example, I have the below Json like below:
[
{
"name": "John Doe",
"age": 30,
"email": "john.doe@example.com"
},
{
"name": "Jane Smith",
"age": 25,
"email": "jane.smith@example.com"
}
]

And want to convert that into excel like below:

Any suggestion would be appreciate, Thanks in advance.

Regards,

Saipriya.

[Deepak Shaerma]

Hi @Saipriya Thokal 
Try below code: 

(function() {
    // Sample JSON data
    var jsonData = '[{"name": "John Doe", "age": 30, "email": "john.doe@example.com"}, {"name": "Jane Smith", "age": 25, "email": "jane.smith@example.com"}]';
    var parsedData = JSON.parse(jsonData);

    // Initialize GlideExcel
    var GlideExcel = GlideExcelFactory.create(); // This is a pseudo code to reflect GlideExcel functionality
    var sheet = GlideExcel.createSheet("Sheet1");

    // Create headers
    var headers = ["Name", "Age", "Email"];
    var row = sheet.createRow();
    for (var h = 0; h < headers.length; h++) {
        row.createCell(h).setValue(headers[h]);
    }

    // Populate rows from JSON data
    for (var i = 0; i < parsedData.length; i++) {
        var rowData = parsedData[i];
        row = sheet.createRow();
        row.createCell(0).setValue(rowData.name);
        row.createCell(1).setValue(rowData.age);
        row.createCell(2).setValue(rowData.email);
    }

    // Convert the sheet to a byte array
    var excelBytes = GlideExcel.toBytes(); // GlideExcel.toBytes() is pseudo code

    // Save the byte array as an attachment
    var attachment = new GlideSysAttachment();
    var excelFileSysID = attachment.write('x_scope_table', 'record_sys_id', 'example.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', excelBytes);

    gs.info('Excel file created with sys_id: ' + excelFileSysID);

    // Optionally, you could allow user to download
    gs.addErrorMessage('Download your file: <a href="/sys_attachment.do?sys_id=' + excelFileSysID + '">example.xlsx</a>');
})();

Please Mark this Helpful and Accepted Solution. If this Helps you to understand. This will help both the community and me..
- Keep Learning ‌‌
Thanks & Regards
Deepak Sharma 

[Amit Verma]

Hi @Saipriya Thokal 

Below post could be helpful :

https://www.servicenow.com/community/itsm-forum/converting-json-payload-to-csv-attachment/m-p/439107

Thanks and Regards
Amit Verma