cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wsalling
Helper III
Helper III

Size Limit on JSON?

Is there a size limit when using the function JSON?  i have large collection that i turn into a JSON object to send to a flow. if i select the first 4000 records and create the JSON it takes a couple of seconds.  But if i try 6000 or more it seems to stop working without an error.  Ideas?

 

14 REPLIES 14

Hi @markfleet,

 

I use your code to collect more than 5000 records in JSON it got collected but its throwing SCHEMA error in ParseJSON step in Powerautomate "Expected Array but got String". Am I missing something in Powerapps code or any step before ParseJSON.

 

This is powerapps code generating & passing json

Clear(repodatajsoncol);
With({BatchSize: 1000, RowCount: CountRows(repodata)},
	With({BatchCount:  RoundUp(RowCount / BatchSize, 0)},
		ForAll(RenameColumns(Sequence(BatchCount), "Value", "i"),
			Collect(
				repodatajsoncol,
                    JSON(ShowColumns(
						LastN(
							FirstN(repodata,
								i * BatchSize
							),
							BatchSize - Max(
								((i * BatchSize) - CountRows(repodata)),
								0
							)
						),"cr17c_account_number","cr17c_cnumber","cr17c_country","cr17c_d_u_number","cr17c_enddate","cr17c_entrname","cr17c_status__c","cr17c_center_name__c","cr17c_rtu__c","cr17c_type__c","cr17c_platform__c","cr17c_portfolioc","cr17c_group_name__c","cr17c_ro2q_sld_id__c"
                    )
					)
				
			)
		)
	)
);
Set(repodatajson,JSON(First(repodatajsoncol).Value));Set(varLink,PasrseJsonPAPS.Run(repodatajson).filelink);Launch(varLink)

 

This is the JSON received in Powerautomate which is giving above error in ParseJSON

"\"[{\"cr17c_account_number\":\"XXXXXXXX\",\"cr17c_cnumber\":xxxxxxxxx,\"cr17c_country\":\"XX\",\"cr17c_d_u_number\":null,\"cr17c_enddate\":\"2021-09-30\",\"cr17c_entrname\":\"XXXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"cr17c_status__c\":\"Active\",\"cr17c_center_name__c\":\"XXXXXX\",\"cr17c_rtu__c\":null,\"cr17c_type__c\":\"XXXXX\",\"cr17c_platform__c\":null,\"cr17c_portfolioc\":\"XXXXXXXX\",\"cr17c_group_name__c\":\"XXXXXXXXXX\",\"cr17c_ro2q_sld_id__c\":XXXXXXX}]\""

 

Hi @markfleet,

 

I use your code to collect more than 5000 records in JSON it got collected but its throwing SCHEMA error in ParseJSON step in Powerautomate "Expected Array but got String". Am I missing something in Powerapps code or any step before ParseJSON.

 

This is powerapps code generating & passing json

Clear(repodatajsoncol);
With({BatchSize: 1000, RowCount: CountRows(repodata)},
	With({BatchCount:  RoundUp(RowCount / BatchSize, 0)},
		ForAll(RenameColumns(Sequence(BatchCount), "Value", "i"),
			Collect(
				repodatajsoncol,
                    JSON(ShowColumns(
						LastN(
							FirstN(repodata,
								i * BatchSize
							),
							BatchSize - Max(
								((i * BatchSize) - CountRows(repodata)),
								0
							)
						),"cr17c_account_number","cr17c_cnumber","cr17c_country","cr17c_d_u_number","cr17c_enddate","cr17c_entrname","cr17c_status__c","cr17c_center_name__c","cr17c_rtu__c","cr17c_type__c","cr17c_platform__c","cr17c_portfolioc","cr17c_group_name__c","cr17c_ro2q_sld_id__c"
                    )
					)
				
			)
		)
	)
);
Set(repodatajson,JSON(First(repodatajsoncol).Value));Set(varLink,PasrseJsonPAPS.Run(repodatajson).filelink);Launch(varLink)

 

This is the JSON received in Powerautomate which is giving above error in ParseJSON

"\"[{\"cr17c_account_number\":\"XXXXXXXX\",\"cr17c_cnumber\":xxxxxxxxx,\"cr17c_country\":\"XX\",\"cr17c_d_u_number\":null,\"cr17c_enddate\":\"2021-09-30\",\"cr17c_entrname\":\"XXXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"cr17c_status__c\":\"Active\",\"cr17c_center_name__c\":\"XXXXXX\",\"cr17c_rtu__c\":null,\"cr17c_type__c\":\"XXXXX\",\"cr17c_platform__c\":null,\"cr17c_portfolioc\":\"XXXXXXXX\",\"cr17c_group_name__c\":\"XXXXXXXXXX\",\"cr17c_ro2q_sld_id__c\":XXXXXXX}]\""

 

markfleet
Advocate II
Advocate II

Hi @addy2019 ,

 

Try this:

Clear(repodatajsoncol);
With({BatchSize: 1000, RowCount: CountRows(repodata)},
	With({BatchCount:  RoundUp(RowCount / BatchSize, 0)},
		ForAll(RenameColumns(Sequence(BatchCount), "Value", "i"),
			Collect(
				repodatajsoncol,
				{
					Index: i,
					Json: JSON(ShowColumns(
                            LastN(
                                FirstN(repodata,
                                    i * BatchSize
                                ),
                                BatchSize - Max(
                                    ((i * BatchSize) - CountRows(repodata)),
                                    0
                                )
                            )
                            ,"cr17c_account_number","cr17c_cnumber","cr17c_country","cr17c_d_u_number","cr17c_enddate","cr17c_entrname","cr17c_status__c","cr17c_center_name__c","cr17c_rtu__c","cr17c_type__c","cr17c_platform__c","cr17c_portfolioc","cr17c_group_name__c","cr17c_ro2q_sld_id__c"
                        )
					)
				}
			)
		)
	)
);
UpdateIf(repodatajsoncol, true, {Json: Left(Mid(Json, 2), Len(Json) - 2)});
Set(repodatajson, Concatenate("[", Concat(repodatajsoncol, Json, ","), "]"));
Set(varLink,PasrseJsonPAPS.Run(repodatajson).filelink);Launch(varLink);

Thanks @markfleet it worked!

addy2019
Advocate V
Advocate V

Hi @markfleet,

 

I'm trying to pass this JSON string I created above (successfully! thanks!) to an Excel file 'OfficeScript' through Flow. It's working for upto 5000 records but beyond that it is giving me following. It seems like Office Script limitations on JSON object size. Can you help me here, thanks.

We were unable to run the script. Please try again.
Office JS error: Line 12: Workbook addWorksheet: The request failed with status code of 404, error code ResourceNotFound and the following error message: Invalid version: error
clientRequestId: 1300d58b-dc42-44e2-b2b2-71803c80cd2c

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

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

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,002)