Size Limit on JSON?

[wsalling]

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?

[addy2019]

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}]\""

[addy2019]

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]

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);

[addy2019]

Thanks @markfleet it worked!

[addy2019]

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