A Sample OpenAPI/Swagger file for PowerApps

Send to Kindle

Ever since I posted a video on how to use Flow to upload photos to SharePoint from PowerApps, I get a lot of requests for help with the most mysterious bit – the swagger/openAPI file…

To save you all much pain and suffering, here is a sample file that you can use to get started.

In this post I am going to assume you have watched the video and understand the intent. Here I will simply annotate the file with some notes that will help you customise and extend it for your own purposes.

Note 1: This only works for a HTTP request trigger in Flow

Flow is capable of being called like any other web service. To do so, you have to use the following trigger.


Note that the trigger states clearly “URL will be generated after save”, so the first thing to do is generate that URL…


Once you have done so, it will look like this:


If we break the URL it down, you will see:

  • A domain something like <location>.logic.azure.com.
  • A URL path of “/workflows/<instance ID>/triggers/manual/paths/invoke” which  that identifies your specific workflow ID. Take note of this as you will need it.
  • A parameter called api-version with a (at the time of writing) value of “2016-06-01” – eg api-version=2016-06-01
  • A parameter called sp with an encoded value of “/triggers/manual/run” = eg sp=%2Ftriggers%2Fmanual%2Frun
  • A parameter called sv with a value of 1.0 – eg &sv=1.0
  • A parameter called sig with a random string value. eg sig=PeZc-ljtjmJhsz00SD78YrwEohTqlUxpQuc95BQQuwU

In combination, the URL looks as follows with the important bits in bold…

https://<location>.westus.logic.azure.com:443/workflows/<workflow instance ID>/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=<signature>

The bits in bold you will need to know, because they need to be added to the OpenAPI file. Why? because this file is what PowerApps uses to construct a HTTP call to your flow.

So let’s look at the Swagger File…

Note 2: Host, basePath and Path

Open the Swagger file and look for the section called “host”… Replace the section labelled [enter Flow URL here] with the URL from the flow Trigger I mentioned above. eg:

prod-23.westus.logic.azure.com:443 or prod-01.australiasoutheast.logic.azure.com:443

From this…


To this…


Now find the section labelled [enterid here]. This is where the workflow ID goes… so from this:


To this..


Note 3: Double check the sv, api-version and sp parameter sections.

All of the parameters expected by the Flow are specified in the OpenAPI file. You will see it in the “parameters” subsection of the “post” section…eg


Now for reference, each parameter section has:

  • name: The name of the parameter as it appears on the URL
  • in: specifies whether this parameter is in the query string, header or body. All of the default flow parameters are in the query string.
  • default: This is the value to check!! If Flow is updated in future it is very likely this parameter will reflect it. Please do not come to me for support if you have not checked this!
  • required: States that this parameter MUST be passed. PowerApps will not allow you to call this Flow without specifying this parameter
  • x-ms-visibility: this basically says “use the default value and don’t show the user”. So in effect, the above “required” condition is met, but PowerApps will not ask the user to enter it.
  • type: is self-explanatory. It tells PowerApps that the parameter is a string.

Note: For more detail on these parameters go and read the OpenAPI 2.0 standard and Microsoft’s documentation.

Note 4: Update the sig parameter…

The sig parameter is like an API key or a password. You need to paste it as the default value in your file like so…

image   image

Note: It is possible to set this up in PowerApps so that it has to be entered when a user adds a datasource. However I am not covering that here.

Note 5: Add (and remove) your own parameters…

This swagger file makes the assumption that PowerApps is going to send a file name for the photo, as well as a description, latitude and longitude. Note that all fields are set to required, but none have default values and the x-ms-visibility parameter is not specified, meaning that PowerApps will prompt the user to enter them.

Using the examples as a guide, add or remove parameters as you see fit.


Note 6: Set your function call names appropriately…

Going back to the top of of the file, update the description to suit the task you are performing. Pay special attention to “Title” and “operationId”, as PowerApps uses these. For example, based on the image below, you will be calling a function called PhotoHandler.UploadPhoto() from PowerApps.


At this point you should be able to save your file and register it as a custom connection and call it from PowerApps.

Note 7: Do not use the word “SharePoint” in your custom  connector name

Believe it or not, if you name your custom connector with the word “SharePoint” it will confuse PowerApps completely. For example, consider this custom connector:

Now look what happens when you try to use it… you get the message “The data did not load correctly. please try again”, with a sub message of “Resource not found”…

The solution? Name your connector anything, so long as the word SharePoint is not there 🙂

Parting notes…

If you intend to send data back to Flow, you will also have the define the schema for what is returned to Flow in the responses section. I have not added any custom schema in the sample swagger file and discussing it is outside the scope of this article. But in case you are interested, to get you started, below is an example of calling Microsoft’s QNAmaker chatbot service REST API and sending the results back to PowerApps.


"responses": {
  "200": {
    "description": "OK",
    "schema": {
       "type": "object",
       "properties": {
          "answers": {
          "type": "array",
          "uniqueItems": true,
          "minItems": 1,
          "items": {
             "required": [
             "type": "object",
             "properties": {
                "answer": {
                   "type": "string",
                   "minLength": 1
                "questions": {
                   "type": "array",
                   "items": {
                      "type": "string"
                 "score": {
                   "type": "number"
       "required": [


Thanks for reading

Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

From Rick Astley to Fidget Spinners: A slew of PowerApps and Flow video tutorials

Send to Kindle


I have been recording various videos over time of some advanced PowerApps and Flow concepts/solutions. All of these are either workarounds for current limitations in PowerApps or Flow or work I have done with my daughter, Ashlee.  I have listed each here with explanations…

How to Save Photos from PowerApps to SharePoint via Flow

This video outlines a robust and flexible method for uploading photos from PowerApps to SharePoint. At the time of writing, it is the best option despite having to create OpenAPI files.


Calling Cognitive Services Vision API from PowerApps via Flow

This video demonstrates a simple receipt tracker that uses the OCR capability of Microsoft cognitive services to find price information from a scanned receipt.


How to set SharePoint list permissions using Flow

This video shows the high level view on how Flow can be used to set SharePoint permissions, much like an app step that is used in SharePoint Designer. It also demonstrates the idea of breaking up flows into reusable chunks – called service flows.


It’s not a Flow, nor a Proxy… It’s a Floxy!!

This is an example of utilising flow to display document library content in PowerApps. I also wrote a detailed post about this one…


How To Rickroll Your Friends Using PowerApps

A funny app with some very clever design considerations. This was actually done by my daughter, Ashlee. She explains how she did it below…


Paul and Ashlee on PowerApps

More nerdy fun with my daughter, who is already an accomplished PowerApps coder as you will soon see. In this video, she build me a sophisticated audit/checklist app using Microsoft PowerApps and Flow. This app demonstrates offline support, calling external API’s and photo handling.



and finally….the famous fidget spinner…

Build a FidgetSpinner using PowerApps

Demonstrating the power of the PowerApps platform for citizen developers, Ashlee won a contest from Microsoft to create a fidget spinner using PowerApps. In this video, Ashlee explains to me how she built the app and shames me for my dodgy high school maths…

p.s don’t miss out the Solar System PowerApps by MVP Daniel Christian, who was inspired by Ashlee’s fidget spinner. Amazing stuff…

I think these videos highlight the flexibility and power of this platform. Let me know if you would like me or Ashlee to record others or expand on them!

Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

It’s not a proxy, it’s a Floxy! A way to display SharePoint PDFs in PowerApps

Send to Kindle


I will say up-front that I used to be a security guy, so what I am about to share with you makes part of me feel dirty. With that said, I think this method is worth sharing because I get to call it a Floxy, and we all know that coming up with cool names trumps all other considerations!.

So what am I talking about? Let’s use an example…

In PowerApps, one can display a gallery of PDFs quite easily. Just drop it onto a screen and feed it a URL by setting the Document property. Here is an example PDF from a journal article I wrote with Kailash Awati…

image  image

Okay so that was easy… but what happens if you put these images inside SharePoint? In the next screen, I have saved the same PDFs as shown above to a SharePoint document library and entered its link into the PDF control in PowerApps. Now look at what happens…


So what’s going on here?

In a nutshell, when PowerApps tries to access the SharePoint URL, it does so anonymously. Now that’s all well and dandy on a public URL like the first example, but isn’t going to do much good with SharePoint, which requires authentication. It’s a pity really, since there are many use-cases where it would be wonderful to browse SharePoint libraries for PDFs. Think of remote workers calling up drawings or schematics as an example.

So is there a (clever) workaround? Sure is!

Flow to the rescue…

Microsoft flow happens to have a SharePoint action called “Get file content using path”.


Basically you feed this action two things: 1) a site collection and 2) the path to a file residing in a document library. In return for your efforts flow will bring back the content of that file. Since, all SharePoint flow actions are authenticated, if we can find a way to pass a URL from PowerApps to flow, and have flow return the file content back to PowerApps we could in effect, get around this authentication issue.

Turns out this is pretty easy. Here are the steps…

1. Create a new blank flow using the Request trigger. We are going to make this flow into a webservice and PowerApps is going to call it, passing the reference to the required file.


Now the important but is to click “Show Advanced Options” and to change the method to a GET.


2. Add a compose action using the expression ‘trigger()[‘outputs’][‘queries’][‘itemurl’]’ (no quotes). What this does is tell flow to look for a parameter called itemURL from the workflow trigger (step 1). Specifically, we are telling it to look for itemurl in the query string from the HTTP request made by PowerApps.


While you are there, rename the action to “Get PDF Path”.


3. Add a SharePoint – Get File Content Using Path action. Specify the site collection you want to work with and then set the “File Path” parameter to be the output of step 2 (Get PDF path).


4. Add a request – response action. In the “Body” parameter, add the File Content from step 3.


Now create this flow and re-open it. The Request trigger will have a URL generated. Click the icon to copy this URL to clipboard. We are going to need it in PowerApps.


Right you are done with Flow. Now go back to PowerApps and change the Document property for the PDF viewer to the URL you just copied, but make one change, namely adding an itemurl parameter. eg:

  • from: http://bigflowurl
  • to http://bigflowurl&itemurl=/Shared Documents/<name of your PDF file>

Going back to my original PDF it becomes:

“https://[flow host].[location].logic.azure.com:443/workflows/[workflow ID]/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=[sig]&itemurl=/shared documents/towards-a-holding-environment-final.pdf

So let’s test in PowerApps… wohoo!


Conclusion and caution…

Okay so we were able to solve a problem fairly easily by using Flow to perform the authenticated connection to PowerApps. In fact, with a little imagination you can extend this idea out further like my video below where I make a document library/pdf browser.

As I mention in the video, Flow has in effect become a proxy – or a floxy Smile. But the issue is now you have a URL that has the ability to access items in the site collection anonymously and potentially expose things you do not want to.  After all, you could give that URL to anybody and if they change the file specified, then you are exposed.

So my strong suggestion is to think carefully about this and take steps to mitigate any risk. For example, add some checks into your flow if you only have one document library that you want to be able to pull files from. Perhaps leverage the differences in the HTTP request between a browser and PowerApps and check the trigger headers (as an example the “User-Agent” for PowerApps is different to what a browser will report). After all, you have to be authenticated to use PowerApps so the real risk is hitting the flow URL from non-PowerApps sources.

Now one other consideration with this… it will run a lot of Flows, so keep an eye on your monthly flow run limits. Also really importantly, things move fast in this space, so the gap addressed by this method may go away really soon. For example: you do not need to use a floxy with images anymore.

Like I said at the start, there is a part of me that feels a little dirty about this method because it allows an unauthenticated URL access to authenticated content, but then again with appropriate controls it should be ok.

Hope this helps you and thanks for reading…


Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

How My Daughter Won a PowerApps Contest With a Fidget Spinner

Send to Kindle

Hi all

<TD: DR>My daughter, with no coding experience, won a contest put by the PowerApps team and we recorded a video on how she did it which you can find below</TD: DR>

This little story starts with a tweet, put out by Audrie Gordon, who on August 7, asked the PowerApps community to make a fidget spinner.


As it happened, I had been teaching PowerApps to my 18 year-old daughter, Ashlee, for a couple of weeks prior to this. Now, Ash has always been good at mathematics and it was her favourite subject at school, but aside from that she has never coded anything in her life, never showed any interest in it, and is currently in her first year of a Psychology degree. Being a biased dad, I always suspected that she had a mind for coding. In saying this however, even I underestimated her progress. To put it bluntly, her speed at picking up PowerApps, Flow and WebServices has been nothing short of phenomenal. I previously uploaded a video about one of her apps that we built together, but with this new challenge, I had absolutely no idea how to do it myself.

It was around 10pm my time (GMT+8) when I showed Audrie’s tweet to Ashlee, who took a look at the video and promptly announced “I can do it better…”

So the challenge was on, and Ashlee spent the next 90 minutes writing her basic solution. As a parent and IT professional it was fun to watch as she went through the typical trials and tribulations of writing code, except I was hearing it through someone who a) was only 18 and b) had never coded before and did not know the terminology. I wish I had recorded the audio to be honest Smile

Then came the breakthrough moment, where she danced a little “I am awesome” jig like real developers are prone to do when their code works. She got the base concept working, which I tweeted out so she could claims dibs on the prize. But Ash was not done, and spent an additional couple of hours making the fidget spinner look authentic in pretty pink as shown in the animated GIF below…


By this time it was close to 1am, so she tweeted her solution and went to bed. Next morning we were pleasantly surprised. She won the contest!


But that was not all, she impressed not only the community but various Microsoft people including Darshan Desai – Group Program Manager for PowerApps who commented that not many in the PowerApps team could have come up with this solution….


Now Ashlee and I recorded a video yesterday where she took me through the solution step-by-step. If you want to learn some neat PowerApps tricks, brush up on your trigonometry and watch my awful math get exposed, then here it is in all its glory Smile

Here are some of the other tweets she received. So you still think I am a biased dad? Smile

imageimage  image  image   image

I feel the PowerApps team should also be proud that a teenager was able to build an app on their platform so quickly. In fact, many kids could benefit from learning PowerApps and perhaps Microsoft should look at its utility in schools.

Thanks for reading

Paul Culmsee


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

The (currently) best way to update SharePoint document metadata using Flow

Send to Kindle

Hi all

<update>This method looks to be superseded. Please read this post and only use this method if you have trouble</update>

Anyone new to Flow, who has come from a SharePoint Designer Workflow background, will automatically assume that Flow has out of the box support for not only uploading documents to a SharePoint library, but to set metadata on it. Unfortunately this is not the case. Currently Flow has limitations with SharePoint integration that makes many common real-world scenarios difficult to achieve, including:

  • The SharePoint : Create File action provides no way to update library metadata when uploading a file, nor does it return the item ID of the newly created file
  • The SharePoint: Create Item action provides access to metadata but only works on lists
  • The SharePoint: Update Item action works on document libraries, but requires the item ID of the document to do so. Since Create File does not provide it, we have no reference to the newly created file

Basically this means it’s hard to upload a file to SharePoint via flow and then set metadata on it.

Given this is such a common scenario, I initially used a dodgy workaround. Basically the method was to:

  1. Use Create File action to upload a file
  2. Use the Get Items action to bring back the metadata for the most recently created file in the library
  3. Grab the ID from step 2
  4. Use the Update Item action to set the metadata on the recently uploaded file.

The big problem with this issue is there is no guarantee that step 2 will actually get the file from step 1. For example, if a lot of files were uploaded to a library at once, step 2 would easily grab the wrong file. The implication is this approach has data integrity concerns and does not scale.

This morning I finally found a method that is much better than my dodgy method. The basic approach is the same except step for 2. What we do now is to use the Get Items action to return the item matching a supplied file name, like so…


As you can see above, we are uploading a file to SharePoint and then passing the file name to a filter query of “FileLeafRef eq [Filename]” in the Get Items action.

Now behind the scenes, what blindsided me to this rather obvious approach was the web service that Flow uses to do this. You see, traditionally this would work fine with a REST call in SharePoint:

https://culmsee.sharepoint.com/_vti_bin/ListData.svc/Documents?filter=Name eq ’somefile.jpg’

Note that “Name” can be used to filter results based on file name. But when you look at the debug output of the Get Items action in Flow, you see it hitting a very different endpoint.


(and decoded to make it a bit more readable…)

https://culmsee.sharepoint.com/_api/SP.APIHubConnector.GetListItems(listName=’Documents’,queryOptions=@q)?@q=’$filter=Name eq ‘somefile.jpg’

Now if you try this in Flow, you will get an error that column ‘Name’ does not exist. So this web service behaves differently to the SharePoint list web service that I know. Luckily there is a workaround… changing Name to FileLeafRef works Smile

This back in the Get Items action, the filter “FileLeafRef eq ‘[File name]’ will work and always return the correct ID as shown in the sequence below that adds in step 4.


Now note the plural in the action: “Get Items”. That means that by design, it assumes more than 1 result will be returned. Even though we are matching on a unique ID, the implication is that the data will comes back as a JSON array. Flow will pick up on this and automagically add an “Apply to Each” section around your “Update Item” action.

By the way if you are thinking that file name is not unique if there are folders in the document library, fear not because this method also works… In this case we use FileRef (which includes folder path) and compare it to Path, which is returned by the Create Item action…

Hope this helps sometime…

Paul Culmsee


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

How’s the weather? Using a public API with PowerApps (part 2)

This entry is part 3 of 3 in the series OpenAPI
Send to Kindle


Hi again

This is the second half to a post that will use the OpenWeatherMap API in PowerApps. The business scenario is around performing inspections. In the first post I gave the example of a park ranger or plant operator, both conducting inspections where weather conditions can impact the level of danger or the result of the inspection. In such a scenario it makes sense to capture weather conditions when a photo is taken.

PowerApps has the ability to capture location information such as latitude and longitude, and public weather API’s generally allow you to get weather conditions for a given location. So the purpose of these posts is to show you how you can not only capture this data in PowerApps, but then send it to SharePoint in the form of metadata via Flow.

In Part 1, we got the painful stuff out of the way – that is, getting PowerApps to talk to the OpenWeather web service via a custom connector. Hopefully if you got through that part, you now have a much better understanding of the purpose of the OpenAPI specification and can see how it could be used to get PowerApps to consume many other web services. Now we are going to actually build an app that takes photos and captures weather data.

App prerequisites…

Now to get through this post, we are going to do this is to leverage a proof of concept app I built in a separate post. This app was also an inspection scenario, allowing a user to take a bunch of photos, which were then sent to SharePoint via Flow, with correctly named files. If you have not read that post, I suggest you do so now, because I am assuming you have that particular app set up and ready to go.

Go on… its cool, I will wait for you… Smile

Seriously now, don’t come back until you can do what I show below. On the left is PowerApps, taking a couple of photos, and on the right is the photos now in a SharePoint document library.

image  image

Now if you have performed the tasks in the aforementioned article, not only do you have a PowerApp that can take photos, you’ll have a connection to Flow and ready to go (yeah the pun was intended).

First up, lets recap two key parts of the original app.

1. Photo and file name…

When the camera was clicked, a photo was taken and a file name was generated for it. The code is below:


        Photo: Camera1.Photo,

        ID: Concatenate(AuditNumber.Text,”-“,Text(Today(),”[$-en-US]dd:mm:yy”),”-“,Text(CountRows(PictureList)+1),”.jpg”)

} )

This code created an in-memory table (a collection named PictiureList) that, when a few photos are taken, looks like this:


2. Saving to Flow

The other part of the original app was saving the contents of the above collection to Flow. The Submit button has the following code…

UpdateContext( { FinalData : Concat(PictureList, ID & “|” & Photo & “#”) } );

The first line takes the PictureList collection above and munges it into a giant string called FinalData. Each row is delimited by a hash “#” and each column delimited by a pipe “|”. The second line then calls the Flow and passes this data to it.

Both of these functions are about to change…

Getting the weather…

The first task is to get the weather. In part 1 we already created the custom connector to the service. Now it is time to use it in our app by adding it as a data source. From the View menu, choose Data Sources. You should see your existing data source that connects to Flow.

image  image

Click Add data source and then New connection. If you got everything right in part 1, you should see a data source called OpenWeather. Click on it, and you will be asked to enter an API key. You should have this key from part 1 (and you should understand exactly why you were asked for it at this point), so go ahead, add it here and click the Create button. If all things to go plan, you will now see OpenWeather added as a data source.

image  image  image  image

Now we are connected to the API, let’s test it by modifying the code that takes a photo. Instead of just capturing the photo and generating a file name, let’s also grab the latitude, longitude from PowerApps, call the API and collect the current temperature.

First here is the code and then I will break it down…


UpdateContext( { Weather: OpenAPI.GetWeather(Location.Latitude,Location.Longitude,”metric”) } );

     Photo: Camera1.Photo,

     ID: Concatenate(AuditNumber.Text,”-“,Text(Today(),”[$-en-US]dd:mm:yy”),”-“,Text(CountRows(PictureList)+1),”.jpg”),



     Temp:Weather.main.temp } )


The first line is where the weather API is called: OpenAPI.GetWeather(Location.Latitude,Location.Longitude,”metric”) . The parameters Location.Latitude and Location.Longitude come straight from PowerApps. I want my temperature in Celsius so I pass in the string “metric” as the 3rd parameter.

My API call is then wrapped into an UpdateContext() function, which enables us to save the result of the API call into a variable I have called Weather.

Now if you test the app by taking photos, you will notice a couple of things. First up, under variables, you will now see Weather listed. Drill down into it and you will see that a complex data structure is returned by the API. In the example below I drilled down to Weather->Main to find a table that includes temperature.


image  image

The second line of code (actually I broke it across multiple lines for readability) is the Collect function which, as its title suggests, creates collections. A collection is essentially an in-memory data table and the first parameter of Collect() is simply the name of the collection. In our example it is called PictureList.  The second second parameter is a record to insert into the collection. A record is a comma delimited set of fields and values inside curly braces. eg: { Title: “Hi”, Description: “Greetings” }. In our example, we are building a table consisting of:

  • Photo
  • File name for Photo
  • Latitude
  • Longitude
  • Temperature

The last parameter is the most interesting, because we are getting the temperature from the Weather variable. As this variable is a complex data type, we have to be quite specific about the value we want. I.e. Weather.main.temp.

Here is what the PictureList collection looks like now. If you have understood the above code, you should be able to extend it to grab other interesting weather details like wind speed and direction.


Getting ready for Flow…

Okay, so now let’s look at the code behind the Submit button. The change made here is to now include the additional columns from PictureList into my variable called FinalData. If this it not clear then I suggest you read this post or even Mikael Svenson’s work where I got the idea…


UpdateContext( { FinalData : Concat(PictureList, ID & “|” & Photo & “|” & Latitude & “|” & Longitude & “|” & Temp & “#”) } );

So in case it is not clear, the first line munges each row and column from PictureList into a giant string called FinalData. Each row is delimited by a hash “#” and each column delimited by a pipe “|”. The second line then calls the Flow and passes it FinalData.

At this point, save your changes to PowerApps and publish as you are done here. Let’s now make some changes to the SharePoint document library where the photos are currently being uploaded to. We will add columns for Temperature, Latitude and Longitude and I am going to assume you know enough of SharePoint to do this and will paste a screenshot of the end in mind…

image  image

Right! Now it is time to turn our attention to Flow. The intent here is to not only upload the photos to the document library, but update metadata with the location and temperature data. Sounds easy enough right? Well remember how I said that we got rid of most of the painful stuff in part 1?

I lied…

Going with the Flow…

Now with Flow, it is easy to die from screenshot hell, so I am going to use some brevity in this section. If you played along with my pre-requisite post, you already had a flow that more or less looks like this:

  1. A PowerApps Trigger
  2. A Compose action that splits the photo via hash: @split(triggerbody()[‘ProcessPhotos_Inputs’],”#”)
  3. An Apply to each statement with a condition inside @not(empty(item()))
  4. A Compose action that grabs the file name: @split(item(),’|’)[0]
  5. A Compose action that grabs the file contents and converts it to binary: @dataUriToBinary(@split(item(),’|’)[1])
  6. A SharePoint Create File action that uploads the file to a document library

The image below illustrates the basic layout.


Our task is to now modify this workflow to:

  1. Handle the additional data sent from PowerApps (temperature, latitude and longitude)
  2. Update SharePoint metadata columns on the uploaded photos with this new data.

As I write these lines, Flow has very poor support for doing this. It has been acknowledged on UserVoice and I know the team are working on improvements. So the method I am going to use here is essentially a hack and I actually feel a bit dirty even suggesting it. But I do so for a couple of reasons. Firstly, it helps you understand some of the deeper capabilities of Flow and secondly, I hope this real-world scenario is reviewed by the Flow team to help them understand the implications of their design decisions and priorities.

So what is the issue? Basically the flow actions in SharePoint have some severe limitations, namely:

  • The Create File action provides no way to update library metadata when uploading a file
  • The Create Item action provides access to metadata but only works on lists
  • The Update Item action works on document libraries, but requires the item ID of the document to do so. Since Create File does not provide it, we have no reference to the newly created file
  • The Get Items function allows you to search a list/library for content, but cannot match on File Name (actually it can! I have documented a much better method here!)

So my temporary “clever” method is to:

  1. Use Create File action to upload a file
  2. Use the Get Items action to bring me back the metadata for the most recently created file in the library
  3. Grab the ID from step 2
  4. Use the Update Item action to set the metadata on the recently uploaded image.

Ugh! This method is crude and I fear what happens if a lot of flows or file activity was happening in this library and I really hope that soon the next section is soon redundant…

Okay so let’s get started. First up let’s make use of some new Flow functionality and use Scopes to make this easier. Expand the condition block and find the Compose action that extracts the file name. If you dutifully followed my pre-req article it will be called “Get File Name”. Above this, add a Scope and rename it to “Get File and Metadata”. Drag the “Get File Name” and “Get File Body” actions to it as shown below.

image  image  image

Now let’s sort out the location and temperature data. Under “Get File Body”, add a new Compose action and rename it to “Get Latitude”. In the compose function add the following:

Under “Get Latitude”, add a new Compose action and rename it to “Get Longitude”. In the compose function add the following:

Under “Get Longitude”, add a new Compose action and rename it to “Get Temperature”. In the compose function add the following:

  • @split(item(),’|’)[4]

This will result in the following:

image  image

Now click on the Get File and Metadata scope and collapse it to hide the detail of metadata extraction (now you know what a scope is for Smile)


So now we have our metadata, we need to apply it to SharePoint. Under the “Create File” action, add a new scope and call it “Get Item ID”. This scope is where the crazy starts…

Inside the scope, add a SharePoint – Get Items action. Enter the URL of your site collection and in the name (not URL) of your document library. In the Order By field, type in Created desc and set the Maximum Get Count to 1. Basically this action is calling a SharePoint list web service and “Created desc” says “order the results by Created Date in descending order (newest first).

Actually what you do is set Filter Query to FileLeaf eq ‘[FileName]’ as described in this later post!

Now note the plural in the action: “Get Items”. That means that by design, it assumes more than 1 result will be returned. The implication is that the data will comes back as an array. in JSON land this looks like the following:

[ { “Name”: “Value” }, { “Name”: “Value2” }, { “Name”: “Value3” } ]

and so on…

Also note that there is no option in this action to choose which fields we want to bring back, so this action will bring back a big, ugly JSON array back from SharePoint containing lots of information.

Both of these caveats mean we now have to do some data manipulation. For a start, we have to get rid of the array as many Flow actions cannot handle them as data input. Also, we are only interested in the item ID for the newly uploaded photo. All of the other stuff is just noise. So we will add 3 more flow actions that:

  1. clear out all data apart from the ID
  2. turn it from an array back to a regular JSON element
  3. extract the ID from the JSON.

For step 1, under the “Get items” action just added, add a new Data Operations – Select action. We are going to use this to select just the ID field and delete the rest. In the From textbox, choose the Value variable returned by the Get Items action. In the Map field, enter a key called “ID” and set the value to be the ID variable from the “Get Items” action.


For step 2, under the “Select” action, add a Data Operations – Join action. This action allows you to munge an array into a string using a delimiter – much like what we did in PowerApps to send data to Flow. Set the From text box to be the output of the Select action. The “Join with” delimiter can actually be anything, as the array will always have 1 item. Why? In the Get Items action above, we set the Maximum Get Count to 1. We will always get back a single item array.


The net effect of this step will be the removal of the array. I.e., from:

[ { “ID”: 48 } ]


{ “ID”:48 }

For step 3, under the “Join” action, add a Data Operations – Parse JSON action. This action will process the JSON file and each element found will be available to subsequent actions. The easiest way to understand this action is to just do it and see the effect. First, set the Content textbox to the output from the Join action.


Now we need to tell this action which elements that we are interested in. We already know that we only have 1 variable called ID because of the Select action we set up earlier that has stripped everything else out. So to tell this action we are expecting an ID, click the “use sample payload…” link and paste some sample JSON in our expected format…




If all goes to plan, a Schema has been generated based on that sample data that will now allow us to grab the actual ID value.

image  image

Okay, so we are done with the Get Item ID scope, so collapse it as follows…


Finally, under the “Get Item ID” scope, add a SharePoint – Update Item action. Add the URL of your site collection and then specify the document library where the photos were uploaded to. If this has worked, the additional metadata columns should now be visible as shown in the red box below. Now set the specific item we want to update by setting the ID parameter to the ID variable from the Parse JSON step.


Now assign the metadata to the library columns. Set Latitude to the output variable from the Get Latitude step, Longitude to the output variable from the Get Longitude step and Temperature to the output variable from the Get Temperature step as shown below.


Now save your flow and cross all fingers and toes…

Testing and conclusion!

Return to PowerApps (in the browser or on your mobile device – not the PowerApps studio app). Enter an audit number and take some photos… Wohoo! Here they are in the library along with metadata. Looks like I need to put on a jacket when I step outside Smile

image   image

So taking a step back, we have managed to achieve quite a lot here.

  1. We have wired up a public web service to PowerApps
  2. We have used PowerApps built-in location data to load weather data each time a photo has been taken
  3. We have used Flow to push this data into SharePoint and included the location and weather data as parameters.

Now on reflection there are a couple of massive issues that really prevent this from living up to its Citizen Developer potential.

  1. I had to use a 3rd party service to generate my OpenAPI file and it was not 100%. Why can’t Microsoft provide this service?
  2. Flow’s poor support for common SharePoint scenarios meant I had to use (non) clever workarounds to achieve my objectives.

Both of these issue were resolvable, which is a good thing, but I think the solutions take this out of the realm of most citizen developers. I had to learn too much JSON, too much Swagger/OpenAPI and delve deep into Flow.

In saying all that, I think Flow is the most immature piece of the puzzle at this stage. The lack of decent SharePoint support is definitely one where if I were a program manager, I would have pushed up the release schedule to address. It currently feels like the developer of the SharePoint actions has never used SharePoint on a day to day basis, and dutifully implemented the web services without lived experience of the typical usage scenarios.

For other citizen developers, I’d certainly be interested in how you went with your own version and variations of this example, so please let me know how you go.

And for Microsoft PowerApps/Flow product team members who (I hope) are reading this, I think you are building something great here. I hope this material is useful to you as well.


Thanks for reading


Paul Culmsee


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle