cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jfperalta
Frequent Visitor

collection from power apps to json to excel

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
1 ACCEPTED SOLUTION

Accepted Solutions
takolota
Super User
Super User

@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.

View solution in original post

4 REPLIES 4
takolota
Super User
Super User

@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.

Hi,

I followed your instructions

asd1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

But still have a problem with the JSON

asd.png

 

 

 

 

 

 

 

 

 

 

 

Do you have any recommendations or solutions?

Thank you for your time.

takolota
Super User
Super User

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

takolota
Super User
Super User

@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.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (6,513)