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…
- avoid challenges with delegation
- leverage pagination
- cache SP JSON responses
Caching implies that you want to use a recent version of data that in the cache and has not yet expired.
3 phase logic follows:
- If the entry is valid (exists and has not expired), then just simply return the cached data from the cache/collection.
- 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 (aka patch) the invalid entry.
- If there is no entry in the cache, then get a fresh copy of the data and save (aka patch) in the cache (collection)
Power function “COALESCE” helps to facilitate this. Coalesce will essentially try an option and if that option is invalid, it will try the next option.
Create a Power Automate flow called “Generic Sharepoint API Call” that will make a generic GET call to SharePoint and returns the resulting JSON (even if the JSON results contain a list, this list can be stored as a single JSON object in cache along with a set a timestamp. What’s cool is that this flow does not require any premium connectors or special magic (well, perhaps a little 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!
