collection from power apps to json to excel

[jfperalta]

Hi,

I'm currently working in a flow to export a collection from power apps to a worksheet in excel. But when power automate is recieving the collection in json format, it doesn´t recognize as an array but it takes it as a string. 

Power automate recieves the json as following:

{

    "variables": [

        {

            "name": "Datos_Json",

            "type": "String",

            "value": "[{\"Costo_por_Perfil_pesos\":573,\"Días\":23,\"Esfuerzo_Total_horas\":184,\"Esfuerzo_por_Perfil_horas\":184,\"Fecha_Fin\":\"2022-08-31\",\"Fecha_Inicio\":\"2022-08-01\",\"Monto_venta_Total_MN\":105432,\"Numero_Propuesta\":\"BMXPP0000001\",\"Número_de_entregable\":1,\"Perfil\":\"BI platforms-Other & Solutioning\",\"Producto\":\"prueba\"},{\"Costo_por_Perfil_pesos\":425,\"Días\":4,\"Esfuerzo_Total_horas\":32,\"Esfuerzo_por_Perfil_horas\":32,\"Fecha_Fin\":\"2022-09-07\",\"Fecha_Inicio\":\"2022-09-01\",\"Monto_venta_Total_MN\":13600,\"Numero_Propuesta\":\"BMXPP0000001\",\"Número_de_entregable\":2,\"Perfil\":\"CS Legacy-Release Support\",\"Producto\":\"prueba2\"},{\"Costo_por_Perfil_pesos\":396.01,\"Días\":9,\"Esfuerzo_Total_horas\":72,\"Esfuerzo_por_Perfil_horas\":72,\"Fecha_Fin\":\"2022-09-21\",\"Fecha_Inicio\":\"2022-09-08\",\"Monto_venta_Total_MN\":28512.72,\"Numero_Propuesta\":\"BMXPP0000001\",\"Número_de_entregable\":3,\"Perfil\":\"Mainframe Legacy-Development\",\"Producto\":\"prueba3\"}]"

        }

    ]

}

I was following the post json to excel, but it is not working for me. ¿Is the any other way in which I can export the collection to a template in excel?

Regards,

JFM

[takolota · Accepted solution]

@jfperalta 

Looks like there are a couple levels to this, you have JSON inside an array, inside a long string.

try the array or create array expression on the string of the array to get a regular array of JSON strings.
If that doesn’t work, use the replace expression on the starting & ending array brackets & a split on the },{ followed by a join }###{ followed by a split ###

Then if you need to convert the stringified JSON item into regular JSON, use the JSON( ) expression on the string. Then parse in a Parse JSON action.

[Gherry]

Hi,

I followed your instructions

But still have a problem with the JSON

Do you have any recommendations or solutions?

Thank you for your time.

[takolota]

What is the Analisis JSON action that is erroring? Do you have a picture of what is in that item?

[takolota]

@Gherry @jfperalta 

I edited my answer as I later found the JSON expression can handle a JSON array & not just individual JSON objects.

So the extra Select shouldn’t be necessary, just a regular JSON( ) expression.