How to create excel from JSON

[tomas (Tomas Belak)]

Hi,

In Integray we have possibility to create excel from JSON but in this case you need to provide quite specific format of JSON which contains definition of the:

• Sheets
• Columns
• Data rows

More details on how this output should looks like are here: JSON to Excel converter - Help center

Here is simple JS that can format the data for me:

var rowData = []
inputData[0].JSON.forEach((inElement, index) => {
  
  rowData.push(
    [
      inElement.UserName,
      inElement.Email,
      inElement.UserCreated,
      inElement.CompanyName,
      inElement.Active,
      inElement.CompanyCreated,
      inElement.ActiveTasks,
      inElement.ActiveEndpoints,
      inElement.RunnableTasks,
      inElement.SucessRuns,
      inElement.FailedRuns,
      inElement.EndpointData,
    ]
  )

})

const outData = {
  "Sheets": [
    {
      "Name": "Trial data",
      "DisplayHeader": true,
      "Columns": [
        {
          "Name":"User Name",
          "DataType": "string"
        },
        {
          "Name":"Email",
          "DataType": "string"
        },
        {
          "Name":"User Created",
          "DataType": "datetime",
          "Format": "yyyy-mm-dd hh:mm:ss"
        },
        {
          "Name":"Company Name",
          "DataType": "string"
        },
        {
          "Name":"Active",
          "DataType": "string"
        },
        {
          "Name":"Company Created",
          "DataType": "datetime",
          "Format": "yyyy-mm-dd hh:mm:ss"
        },
        {
          "Name":"Active Tasks",
          "DataType": "int"
        },
        {
          "Name":"Active Endpoints",
          "DataType": "int"
        },
        {
          "Name":"Runnable Tasks",
          "DataType": "int"
        },
        {
          "Name":"Sucess Runs",
          "DataType": "int"
        },
        {
          "Name":"Failed Runs",
          "DataType": "int"
        },
        {
          "Name":"Endpoint Data",
          "DataType": "int"
        }                      
      ],
      "Rows": rowData
    }
  ]
}

//log.warn(JSON.stringify(outData))

return [{"JSON": outData}];

This example create first the array of rows from provided data. Then in creates that structure required for JSON to Excel connector.

Then you just have to add the filename and directory and you save the file or you just add the name into one of the email sender connector and you can send the file as attachment.

return [
  {
    "Subject": "Some nice subject",
    "From": [
      {
        "Name": "MyIntegray",
        "Address": "my.integray@integray.app"
      }
    ],    
    "To": [
      {
        "Name": "Jan Novak",
        "Address": "jan.novak@domain"
      }
    ],
    "BodyType": "text",
    "Body": "See attachment.",
    "Attachment": [{"Name": "filename.xlsx", "Content": inputData[0].Data}]
  }
]

Input and output schema:

Enjoy, Tomas.