Unanswered

Converting Get Item Content to JSON

(0) Share

Report

Posted on by Ginko

221

I'm struggling mightily to finish a flow that is intended to end in with a Respond to a PowerApp action that included a string or array of data retrieved by the Get File Content action step. The content will always be an excel file stored on a Sharepoint library.

The issue is that Get File Content appears to spit out a binary object and no function is allowing me to convert it to JSON. Does anyone know how to turn the result of a Get File Content action into parsable JSON?

I can't use List rows in a table action to retrieve the excel sheet because I can find no way to make the required parts of that action dynamic. The stupidity of PowerAutomate uses server-side indicators to locate the file and not even HTTP requests to the sharepoint give you all the details you need.

Categories:

Building flows

I have the same question (1)

All responses (4)

Answers (0)

Sort by

grantjenkins 11,063 Moderator on at

Like (0)

Report
Copy link

Link copied!

Do you have an actual Table within the Excel file? If so, then your best bet would be to use List rows present in a table. What is the issue with using this in your instance (you mentioned you couldn't get it to work due to it being dynamic)? Are you able to share a screenshot of your flow, and go into a bit more detail around the dynamic nature?

The other option would be to use an Office script to return the JSON output from a given Excel file.

Was this reply helpful? Yes No
Ginko 221 on at

Like (0)

Report
Copy link

Link copied!

The excel files may or may not have tables, I am creating a PowerApp solution that will take in a lot of ambiguity.

The part about that action step that is prohibiting dynamic application is the server side identifiers used to select the location, library, file and table.

This is the raw input of a List rows present in a table, if I am to make the location and library dynamic based on user input of both of those required inputs as they see it in the address bar of the browser, I somehow have to find the backend server id's for source, drive, and file. For example they see 'Excel Files' as the library name, but the action needs to receive 'b!3Ybgsv7...' I can ask my users to write out the table name.

To my knowledge there isn't an Excel Online (Business) action step that can provide these backend details for a Sharepoint file without requiring at least one of the unknowns by hard coding the step. I need them to be fluent to the users input. Any ideas if I'm missing something?

This is the entire flow, it took all this just to make Get File Content completely dynamic, but I don't need it spitting out a file, but a table of data for powerapps to take in.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
Copy link

Link copied!

Are you able to share a new screenshot (or multiple screenshots) as I can't make this one large enough to see it clearly?

I'd probably be going with an Office script to convert your Excel data to JSON that you can then pass into your PowerApp. Once I can see your full flow clearly I can build a flow showing how I'd likely go about it to get what you're after.

Was this reply helpful? Yes No
Ginko 221 on at

Like (0)

Report
Copy link

Link copied!

Hi Grant,

Sorry for the delay but I made some ground breaking revelations with this and figured out how to dynamically handle List rows present in a table! Below are clearer pictures of the new flow. It receives the file details as input in PowerApps and uses HTTP calls to pull the server side details in order to reach that final table acquisition step. The conditions and what I didn't screenshot are replica steps in case the folder/file names have spaces, it changes the steps slightly to include %20 in place of the spaces.

Thanks to this, I don't think we'll need an office script but I am stumped on how to return the table output of List rows present in a table to PowerApps in a format that PowerApps can convert to either a Table control, or honestly any control that can present that data in a table format. Do you know how to take the output of that action step and send it through the Respond to PowerApps action to the app? Mind you, the column headers and column count will be unknown because its dynamic.

I'm guessing it needs to be in a string leaving the flow, and split() in the app but I don't know how to accomplish this or what PowerApps control can ingest the string.

Was this reply helpful? Yes No