Exceleration: Power Apps Get Sharepoint Data using Power Automate

I have recently thought about caching in Powerapps using collections.  Cached data can help with performance.  I’ve been investigating the use of SharePoint REST APIs in place of Connectors to SharePoint… 1. you can avoid challenges with delegation, 2. you can leverage pagination, 3. you can cache SP JSON responses.  Caching, to me, implies that you want to check the cache for a valid entry that both exists in the cache and has not yet expired.  3 phase logic follows:

1. If the entry is valid (exists and has not expired), then just simply return the cached data from the cache/collection.

2. If the entry exists in the collection, but has expired (e.g. the data hasn’t been refreshed in x number of seconds, then get a fresh copy of the data and update (patch) the invalid entry.

3. If there is no entry in the cache, then get a fresh copy of the data and save (patch) in the cache (collection)

Coalesce helps to facilitate this.  Coalesce will essentially try an option and if that option is invalid, it will try the next option.

I have created a “Power Automate” flow called “HT – Generic SP Call” that makes a generic “GET” call to SharePoint and returns the resulting JSON (even if the JSON results contain a list… I can store this as a Single entry in my cache and set a timestamp.  This flow does not require any premium connectors or special magic.  

  • In the first step, define two inputs: in_baseurl and in_restcall.
  • In the second step, send in_baseurl to Site Address and in_restcall to URI.  Also add Header “Accept” = “application/json;odata=nometadata”.  I also rename this step = “HTTP _Request”.
  • In the third step, return “data” with expression “string(body(‘HTTP_Request’))”

Now, you can add this flow to Powerapps using the “Power Automate” configuration button.

OK, about the cache (collection) in Powerapps.  A collection called coll_httprequests with columns “httprequest”, “httpresponse”, and “timestamp” will be created by the component and available to your Powerapps.  My cached item in this case will expire after 145 seconds.  

Create an invisible component, add a property named “get” of type “Action” that returns Text.  Add 3 input parameters: in_baseurl (type text), in_request_uri (type text), and in_expiry (type number).  Place the following in the result.

If(LookUp(Sort(coll_httprequests,timestamp,SortOrder.Ascending),httprequest=in_request_uri).timestamp > (Now() - in_expiry/24/60/60), LookUp(Sort(coll_httprequests,timestamp,SortOrder.Ascending),httprequest=in_request_uri), Patch(coll_httprequests, Coalesce( LookUp(Sort(coll_httprequests,timestamp,SortOrder.Ascending),httprequest=in_request_uri), Defaults(coll_httprequests)), {httprequest:in_request_uri,httpresponse:'HT-GenericSPCall'.Run(in_baseurl,in_request_uri).data,timestamp:Now()} )).httpresponse

Now, add this component to your Powerapps.  And now you can call Component_1.get(“https://_____.sharepoint.com/sites/____”, “_api/web/lists”,30).  And then parse the results into a Table.  This is a little tricky… you need to know the structure of the response.  See https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest. Set(var_getit7,

SPConnect.get(var_baseurl,"_api/web/lists",60)); ClearCollect(coll_splists, DropColumns(AddColumns( Table(ParseJSON(var_getit7).value), "Title", Text(Value.Title), "Id", Text(Value.Id), "Description", Text(Value.Description), "DecodeURL", Text(Value.ParentWebPath.DecodedUrl), "ImageURL", Text(Value.ImageUrl) ),"Value"));

I provided some “write up” on how to do the Power Flow HTTP call to SharePoint in a “non-premium” way.  https://sharepoint.stackexchange.com/questions/282308/how-to-send-json-data-to-power-apps-from-power-automate-ms-flow

Cheers!

Leave a Comment

Your email address will not be published. Required fields are marked *