Hiya

Pretty much every time I run a project kick-off meeting involving tools like SharePoint or PowerApps, I always ask the group the following question:

“If you had <insert goal here>, how would things be different to now”?

(Replace <insert goal here> with any common IT platitude like “improved collaboration”, “knowledge management”, “big data analytics” or “digital transformation”. )

Now inevitably, someone will say something like “I would have information at my fingertips”. While that may sound like a cliche, these days, it is pretty easy to meet this goal. So easy in fact, that I can do it in around 10 minutes using a SharePoint Document Library, a quick PowerApp and a Flow of only 5 steps. I then get to be a smartass, hand them my phone and say “there you go – at your fingertips… That’ll be $250 thanks”.

Now if you want to score $250, I have bad news for you. No client has ever actually paid me when I used that line. But nonetheless, if you have been looking for a demo to show just how powerful these tools have become, then one is pretty good… If you check the image below, you can see I have a PowerApp that is browsing a SharePoint document library and irrespective of whether the document is Word, Excel or PowerPoint, is loading it as a PDF into PowerApps for preview. Better still, this solution is very much in the citizen developer realm…

A common use-case for this sort of solution is a field worker who needs to access schematic data like drawings or equipment documentation. I have also deployed something similar for quality management and safety apps. In all cases, users had a need to be able to retrieve documents quickly and easily…

How to build this app

This app consists of a simple PowerApp, a SharePoint doclib and a 5 step Flow.

Step 1: Create a document library in SharePoint – in my case I called my library “FingertipsBaby”…

 

Step 2: Start PowerApps studio and make a blank tablet app.

Step 3: Connect the document library as a data source using the SharePoint connector. Note, although document libraries are not listed by default, you can definitely connect them. Just scroll to the end of the lists and in the last box, type in the name of your document library…

Step 4: On the left side of the screen, add a blank vertical gallery and linked it to the data source.

Step 5: Add a label to the gallery and set its Text property to ThisItem.’{FilenameWithExtension}’. If you check the image below, you can see my three documents listed from my library. Not pretty I know, but this is a 10 minute demo remember?

Step 6: Go to the Action menu and click the Flow icon. in the Data panel that opens on the right, click Create a new flow. A new tab will open in your browser and log you into flow. A new flow will be created using a PowerApps trigger as shown above. Take the opportunity to give your flow a name, such as “PDF Viewer”

Explanation interlude:

Now at this point I feel some explanation is needed. We are about to use a few recent enhancements to Flow and SharePoint. Kudos needs to go to Brian Edwards who made me aware of a powerful new SharePoint API that among other things, generates thumbnails and PDF’s of documents. Brian already has a blog that explains how to do thumbnails – so consider this article a companion one that does PDF’s.

The key to the whole thing is an API called RenderListDataAsStream. This is a very powerful API that has a truckload of goodness, but for now I will keep it simple. I will pass a SharePoint List Item ID of a document to the API and it will give me back a PDF. The API call looks like this:

_api/web/lists/GetbyTitle(‘FingerTipsBaby’)/RenderListDataAsStream?FilterField1=ID&FilterValue1=<List Item ID>

In the body of the request, I need to specify the data I’d like returned. The parameter is called RenderOptions and looks like this:

{ 

    “parameters”: {

       “RenderOptions” : 4103

    }

)

What is the deal with the number? Well, the documentation includes a table of different interesting things you can return, which you can do by adding the Values together.

 

Label	Description	Value
ContextInfo	Return list context information	1
ListData	Return list data	2
ListSchema	Return list schema	4
EnableMediaTAUrls	Enables URLs pointing to Media TA service, such as .thumbnailUrl, .videoManifestUrl, .pdfConversionUrls.	4096

So we are asking this API not just to bring back the data associated with a list item, but also some additional useful stuff. The last entry is particularly interesting as it mentions a mysterious beast known as the Media TA service which I assume means “translation”. Basically what happens is if we total the numbers listed in the above table (4103), we will end up all the data we need to do PDF conversion.

Now at this stage, I don’t want to do an exhaustive examination of the JSON data returned by this API call, but I will call out a couple more things before we get to flow…

First up, you will find this entry…

“.pdfConversionUrl”: “{.mediaBaseUrl}/transform/pdf?provider=spo&inputFormat={.fileType}&cs={.callerStack}&docid={.spItemUrl}&{.driveAccessToken}

This parameter refers to a URL that will convert the list item to a PDF. All you need to do is access this URL. How easy is that eh? Now all of the stuff in curly braces are tokens but they are also returned as part of the API call, so they can be grabbed from elsewhere in the output. For example, {.mediabaseURL} is a few lines up and in my case is:

“.mediaBaseUrl”: “https://australiasoutheast1-mediap.svc.ms”

So if we find each token in the .pdfConversionUrl and replace them, we basically have converted our document to PDF without needing to store a PDF. The document can stay in its native format!

Back to Work…

Okay so let’s deal with this flow.

Step 7: Add an SharePoint action called Send an HTTP Request to SharePoint to the flow. Set the Site Address to the site that contains your document library and set the Method to POST. Set the URI to _api/web/lists/GetbyTitle(<docLib>)/RenderListDataAsStream?FilterField1=ID&FilterValue1=, where <Doclib> is the name you specified for the document library (for example, mine is _api/web/lists/GetbyTitle(‘FingerTipsBaby’)/RenderListDataAsStream?FilterField1=ID&FilterValue1=). On the end of the URI, click Dynamic Content and choose Ask in PowerApps as shown below:

Step 8: In the Body section, paste the following configuration (watch the quotes when pasting from this article):

{ 

    “parameters”: {

       “RenderOptions” : 4103

    }

}

\

Step 9: Click the Save and then Test icon in the top right. Choose the option I’ll perform the trigger action and click the Save and Test button. Click the continue button and on the next screen, type in the ID number of one of the documents in your library and click the Run Flow button. Your flow will start and you can click Done. Assuming it worked, you will see a green tick of happiness.

Step 10: Click on the Send an HTTP Request to SharePoint action to expand it. We need to grab the output from the API call for the next action. Find the OUTPUTS section and copy the entire contents to the clipboard….

Step 11: Go back to edit mode and add a Data Operations action called Parse JSON to your flow. This action will allow us to make use of the output of the API call in the subsequent flow step.

Click the Use sample payload to generate schema link and paste your clipboard contents into the window and click the Done button. In the Content field, go to Dynamic content popout and choose Body from the Send an HTTP Request to SharePoint action.

  

Step 12: Add an Initialize Variable action to your flow. Name the variable PDFURL (or something similar) and set it to a string. Now we come to the most complex bit of the flow where we have to substitute the token we examined in the interlude earlier. Be careful here… this is the most likely place to make an error…

In the Value textbox, click the dynamic content flyout and find .mediaBaseUrl from the Parse JSON action…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step. <edit>Please note the image has a quote that is not meant to be there, so ignore it!</edit>

/transform/pdf?provider=spo&inputFormat=

Now we come to a slightly tricky bit. The next bit of content we need is the file type of the document we are dealing with. Now this is formatted as a single value array, so despite there being only a single value, we need to create an expression to handle it. Click the Expression tab and type in the following:

first(body(‘Parse_JSON’)?[‘ListData’]?[‘Row’])?[‘File_x0020_Type’]

For the uninitiated, this is known as Workflow Definition Language and is well worth learning…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step.

&cs=

Also be super careful here because at the time of writing, the cursor in this textbox can randomly move and wipe out your edits…

Now in the Value textbox, click the dynamic content flyout and find .callerStack from the Parse JSON action…

Next, add the following text to the Value textbox, taking care not to delete what you just added in the previous step.

&docid=

Now we come to another array that needs to be handled. This is the URL of the document we are dealing with. Click the Expression tab and type in the following:

first(body(‘Parse_JSON’)?[‘ListData’]?[‘Row’])?[‘.spItemUrl’]

Okay we are almost done… Add an ampersand ( & ) to the Value textbox, and then click the dynamic content flyout and find .driveAccessToken from the Parse JSON action…

Step 13: Add a the PowerApps – Respond to PowerApps action to the flow. Click the Add an output icon and choose Text from the list of output types. Name the output PDFURL and set the value to the variable you created in step 12 (PDFURL).

Ok we completed the flow. Save it and give it a quick review. It should look something like this…

Step 14: Back in your PowerApp, now, select the label gallery you created in step 5. From the Action menu, choose Flow and you should see your newly created flow listed. Click on it to add it to your PowerApp. Once added, the label will partially fill in the OnSelect property to run your flow.

  

Step 15: Complete the flow invocation by adding a reference to the ID of the document from the gallery by using the following function:

Set(PDF,PDFViewer.Run(ThisItem.ID))

This function will set a variable called PDF to the output from our newly minted flow. When you click on a file in the gallery, the flow will get called. In fact you can try this out before we move on. Press the play icon to test your app and click one of the files. Then go back into edit mode and click the Variables icon from the View menu. You should see a global variable called PDF that has a data type of record.  Clicking the variable will bring up further details and if you click on the record icon, you will see the output returned from flow.

  

If you get this far, that’s great because you are almost home….

Step 16: From the Insert menu, Add a PDF Viewer from the Controls menu. Place it to the right of the gallery and set the Document property to PDF.pdfurl. (if you look closely at the above image you can see that PowerApps has decided that the output of the API is a record with a column name of pdfurl).

Now click on some of the documents… if you did it correctly, you will be seeing PDF’s!  Wohoo!

Conclusion

Not bad eh? A 5 step flow has enabled a very powerful use-case and once again showcases how well PowerApps and Flow work together. I should note that there are three recent innovations that have enabled this scenario, namely the recent Send an HTTP request to SharePoint action, the RenderListDataAsStream API and the Respond to PowerApps action.

The ability to generate PDF’s as part of a business process is a common one – mainly driven by compliance. A common way to do this is to create a HTML file and then convert that to PDF.

<TLDR>Go to the conclusion for a summary of the approaches</TLDR>

In Microsoft Flow, there has been options for doing this for a while now. For a start, there are two 3rd party flow actions available that are part of a broad suite of tools for managing and manipulating documents. They are Muhimbi PDF and Plumsail Documents.

Both offerings are simple to set up and use. They also allow some configuration and tuning. At the time I wrote this article, both allow you to specify page size and orientation, and Muhimbi has a couple of extras like letting you make password protected PDF’s. As they are both commercial tools, these will come at a cost which depends on how many PDF’s you produce. But in saying that, the cost is not particularly excessive.

Below I show you both actions in use. Each one is followed by an action to save the PDF into my OneDrive – easy peasy…

But I am cheap Paul!

For those of you who do not have a budget, or are simply cheap-assed, there is also the OneDrive Convert File action. This one works by saving a HTML file to OneDrive (or OneDrive for Business). You then pass that file into the Convert File action and save the resulting PDF. So instead of two steps like the ones above, you have three.

Head over to John Liu’s page for a great example of this technique…

Now there is only one teeny problem with this. Not so long ago Microsoft broke it and as I type these lines, it remains broken but with a commitment to get it fixed…

Now by the time you read this it may well be fixed, but you need to be aware of another limitation with this approach. Unlike Plumsail and Muhimbi, this converter does not honour css page breaks. Therefore your PDF can end up looking pretty ugly as content wraps over pages in ugly ways…

Is there another option? Why bother?

So you might be thinking, okay so just use one of the commercial offerings while Microsoft sorts out a fix? After all, even if it costs you a few backs, you can always go back to the cheap version later.

I indeed attempted this but I had an issue with my Flow that precluded it. I had no problem signing up for both Muhimbi and Plumsail, but when I added the actions to my flow, I was met with this type of error. My flow simply did not like using 3rd party connections it seemed.

Unable to process template language expressions in action ‘Convert_HTML_to_PDF’ inputs at line ‘1’ and column ‘2336’: ‘The template language expression ‘json(decodeBase64(triggerOutputs().headers[‘X-MS-APIM-Tokens’]))[‘$connections’][‘shared_muhimbi’][‘connectionId’]’ cannot be evaluated because property ‘shared_muhimbi’ doesn’t exist, available properties are ‘shared_sharepointonline, shared_onedriveforbusiness’. Please see https://aka.ms/logicexpressions for usage details.’.

Now this error is the subject of an open case with Microsoft so I will update this post when I get an answer. <update> It turns out that for flows with a PowerApps trigger, you need to disconnect and reconnect it to PowerApps to start working</update>. But in the meantime I had a deadline and had to demo PDF creation to a client. So I decided to make an Azure function and call it from flow – after all it sounded like a perfect scenario for that technology right?

Now I won’t cover the Azure function stuff in depth here, except to say I tried a heap of HTML to PDF approaches and not a single one worked properly. Eventually I worked out that Azure functions restrict the use of GDI+ libraries. Quoting from the linked article…

For the sake of radical attack surface area reduction, the sandbox prevents almost all of the Win32k.sys APIs from being called, which practically means that most of User32/GDI32 system calls are blocked. For most applications this is not an issue since most Azure Web Apps do not require access to Windows UI functionality (they are web applications after all).

However one common pattern that is affected is PDF file generation

Eventually though I was able to ascertain that if you provision your azure functions using an app service plan instead of a consumption plan, it will work. The reason for this is the latter runs on dedicated virtual machines.

Of course now you are up for hosting costs for your app plan. Unless you already have an Azure function app provisioned for other purposes, this is no longer free.

Once I got past the Azure function issues with GDI support, I was easily able to find and use a pre-existing HTML to PDF function found here. This uses a tool called wkhtmltopdf which is a pretty powerful PDF generation library. I simply added the necessary files and configuration and was able to test it successfully in minutes.

Finally all I needed to do to call this function was to create a HTTP action in Flow like so…

Yay! I had my PDF’s!! Even better, this approach does not have the page break issues that the built-in one does!

Conclusion (and comparison)

So here is a little table that summarises the approaches…

 

Method	Cost	Page Breaks	Features	Complexity
OneDrive Convert File Action	Free	No	Basic	Low
Plumsail HTML to PDF action	Not Free	Yes	Medium	Low
Muhimbi HTML to PDF action	Not Free	Yes	Medium+	Low
Azure function	Not Free1	Yes	Advanced	Medium2

 

¹ You will have to pay for the azure function app subscription, but many orgs will have one already so might be very low.

² I marked this as medium if you are doing basic stuff, but if you want to do stuff like set page size and orientation, you are having to edit code directly so could be classified as High.

 

Now for my real use-case, I would likely use one of the commercial offerings, but if the organisation was going to do a lot of PDF generation, then the Azure function approach could be quite cost effective. Additionally, expanding the code to deal with additional options might also be justified.

I think the key point is that I was able to quickly work around this issue and deliver good outcomes for my client. So they are not adversely impacted while I wait for the various issues to be resolved.

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

Background

Hi and welcome to the third article that describes how to make a PowerApps-based activity feed based on SharePoint search results. If Google’s search algorithm has landed you here for the first time, then I suggest going back to parts 1 and 2 for background and context. What we are building can be seen in the picture below highlighted red..

Now there are 5 parts to this solution and in this third post, we are in the home stretch because we have done the first three steps and we are halfway through part 4.

1. Setting up SharePoint search
2. Querying the search index via a PnP PowerShell script
3. Creating the Azure function
4. Creating the custom connector
5. Testing in PowerApps

I covered parts 1 and 2 in the first post where we created a Result Source in SharePoint and and tested a PowerShell script that uses it to bring back the latest 20 search results. In the second post, we created and tested an azure function, and just deployed our custom connector as shown below:

Before we rush off to PowerApps to bask in our awesomeness, let’s test our custom connector first. Click the pencil button to edit your freshly minted connector. Using the breadcrumb, navigate to the Test menu.

You will be presented with a Test operation screen and (very likely) a prompt to create a connection. While newbies might find it weird to ask for a connection just after we just made one, it is worth paying close attention to the terminology used. We actually created a custom connector, not a connection. The PowerApps team describe it like this:

Data is stored in a data source, and you bring that data into your app by creating a connection. The connection uses a specific connector to talk to the data source

Therefore, it stands to reason that to test a connector, one has to create a connection. So let’s do that right now by clicking the + New connection button. On the resulting confirmation dialog box, click Create.

 

Now you will be redirected to the connections screen. This is somewhat counter-intuitive given at first, since we actually wont do anything with the connection from this screen, but if it makes you feel good, feel free to look for a new connection with the same name as your connector as shown below. This means all went to plan…

Using the left side navigation, head back to the custom connections screen. Edit your connector once again, and navigate back to the Test screen. This time the Test Operation screen will show your new connection listed…

Scroll down and you will see an Operations section. The code query string parameter that we worked with in part 2 is listed.

Note: It should be greyed out and not editable. If it is editable, you have likely forgotten to change the visibility parameter when we created the custom connector in part 2.

Click the Test operation button to give it a whirl. After a few seconds, you should see a successful response. Looking in the body of the response, you can see the Json search results produced by your Azure function. Yay!!

Part 5 – Testing in PowerApps

Now that we have created a connection and used it to test our custom connector, it is time to move to PowerApps. To keep things simple, lets build an app from scratch to show this working. In PowerApps studio, create a blank phone app and then follow the steps:

1. From the View menu, choose Data sources and click the Add data source from the Data panel. From the list of connections, find the connection we created moments ago to test the connector. Click it and it will be added to the app.

  

Note the name of your connector matches the Postman collection we created in part 2 of this series. Make a note of this because we will need it momentarily…

2. From the Insert menu, choose Icons and choose the Reload icon.

Select the refresh icon you just created and choose the OnSelect property. Type in “ClearCollect(Feed,” and then enter the name of the data source you just added (mine is called “CATalogue”). The intellisense in PowerApps will also present the name of your function that you specified back in part 2 when creating the postman collection. In my example it was called “GetMewsFeed”.

 

Note: If you are presented to provide a code as a parameter to your data connection as shown below, you missed an important step in the custom connector setup. Recall in part 2 I mentioned that the code parameter in the custom connector had to be changed from none to internal. For now, you can add the same code that was generated as part of the Azure function URL, otherwise update the custom connector.

3. Click the new icon to trigger a data refresh and then navigate to Collections from the File menu. You should see a collection called Feed with some of the data returned. If you see a table of data then congratulations! You have successfully queried SharePoint search and returned the data to PowerApps via an Azure function.

The columns that are of interest to us for the purposes of this post are:

• Title
• ListID
• ModifiedBy
• LastModifiedTime
• OriginalPath

So now that we have gotten some data, let’s now show how we can display it in the form of a data feed.

4. From the Insert menu, choose Gallery and pick a Blank flexible height gallery. In the data panel, choose the Feed collection from the Data source dropdown.

 

5. Ensure your Gallery is still selected via the left side navigation. Click the pencil icon inside the gallery and then from the Insert menu, choose Label. This label will bind to a column in your data source and you should see something like the second screen below, where data is repeated down the page. If the label control is still selected, the Text property will be set to something like ThisItem.Author.

Modify the Text property of the label to “Updated by ” & ThisItem.ModifiedBy & ” on ” & ThisItem.LastModifiedTime. The result is a line that shows who modified the item in the feed and when they modified it. While you are here, rename this label control and call it something more meaningful. In PowerApps I use Hungarian notation as it makes it easier to understand complex formulas later. Thus I called this one lbWhoWhen. Finally, resize the gallery to fit the screen and drop the font size of the label so everything fits on one line…

6. Add another label to the gallery (remember to use the pencil icon to ensure the new label control is added to the gallery and not the screen). Place it below the label you created in step 5 and set it’s text property to ThisItem.Title. Adjust the font size and position it appropriately. Rename this label to something meaningful (I used lbTitle ).

7. Add an icon to the screen and choose Rectangle from the Shapes section. Resize it so that it looks like a horizontal line (Set the Height property to 1) and change its colour to grey either via the toolbar or via setting the Fill property to “LightGray”. Also rename it to something like Separator.

  

Preview the app and the feed should start to take shape…

Hopefully you can see by now that we are more or less done, as we are now moving into aesthetics in terms of how our feed is to look. So I will round out this post by showing you a couple of tricks that I think make this feed more interesting…

6a. Pimping your feed (easy)

If you look closely at the sample data I used to build this feed, it includes images as well as list item content. In fact, to remind you of where we started, I mentioned that my daughter wrote an app called CAT-alogue which used three SharePoint lists/libs: There was a list called Cat Directory, a list called CatImageRegister and a library called CatImages.

So let’s make the following enhancements:

1. If the search results contain a Description field, use that instead of the Title field (the Cat Directory list utilises the built-in SharePoint site column called Description, which happens to be a managed property in SharePoint search)
2. If the search result is the image library, display the image rather than the file name.

Handling description requires a couple of changes to our Title label. First, we need to set Auto height to On because the description field is multi-line. This is easiest done by toggling it to On via the right-side properties panel when selecting the label as shown below:

Next, change the Text property in the label to If(IsBlank(ThisItem.Description),ThisItem.Title,ThisItem.Description). This tells PowerApps to display the Title field if no description has been crawled. The effect should be immediate…

But this is a flexible height gallery, so let’s take advantage of this. We will now set the position of our separator rectangle to be based on the height of the Title/Description label. Remember this label is now set to auto-height… To do this you will need to take note of the name of your Title/Description label. Mine is called lbTitle.

Set the Y property of your rectangle to: lbTitle.Y + lbTitle.Height + 20. Previewing this change should result in a change to your screen for the better…

6b. Pimping your feed (less easy)

Right! So what we can do about displaying images in the feed? Turns out this is one of those examples that might seem easy, but is actually quite tricky and also not particularly bandwidth efficient either. It also has a lot to do with how you set up your data in SharePoint, so some refresher is needed before we start.

First up, Ashlee’s CAT-alogue app is structured with 3 lists. There is a list called Cat Directory, a list called CatImageRegister and a library called CatImages. The second list is the one that matters here, as each time a photo is taken, the metadata is stored in this list rather than the CatImages library. It then links to the actual photo in CatImages as shown below. Note that the title field for CatImageRegister is the name given to the photo in CatImages.

  

Why set it up this way you ask? Well for a start, PowerApps cannot connect to libraries yet, and these days, PowerApps is smart enough to be able to retrieve images from a list like this. So let’s take advantage of this…

Also some more useful nuggets of context: another thing we can take advantage of is the built-in SharePoint search property called ListID. Way back in Part 2 when we did the PowerShell script, I included a line that specified some search managed properties to be returned that are not by default. The line was:

$returnproperties = @(“ContentType”,”ListID”,”ModifiedBy”, “ListItemId”)

Specifically, the property of ListID is what matters here. This property returns the unique ID of each list. Can you see where I am going with this yet?

From the Insert menu, choose Media from the toolbar and insert an Image control into the gallery. Rename it to CatPhoto and resize it and place it under the first label.

 

Set the Visible property to only display if the list ID of the feed item is the CatImageRegister list. It will be something like: If(ThisItem.ListID=”11d474d0-5725-4ec5-b273-8bb09a3e097e”,true,false). Of course the ID you need to specify depends on our setup. This should now hide the image control when the ListID does not match my CatImageRegister list.

Next, let’s get the image control to display the photo itself. To do this, we will connect to SharePoint using the native PowerApps connector. From the View menu, choose Data Sources and add a SharePoint data source. Choose the SharePoint list that holds a link to your photos (in my case, CatImageRegister).  

Now set the Image property of the Image control to something like this: LookUp(CatImageRegister,Title=ThisItem.Title).CatImage. Although things look ugly, you should see the photos…

So what we did here was utilise the PowerApps Lookup function which returns first record in a data source that satisfies a formula. In my case I wanted to bring back the record where the Title field in CatImageRegister matched the Title field for each row in the feed gallery. From here, I specified that I specifically wanted the CatImage property and voila!

Next, we make the feed look less sucky by making the position of the Title/Description label more dynamic. If an image is displayed, we will reposition the Title/Description label to the right of the image. To do this, set the X property of the Title/Description label to: If(CatPhoto.Visible,CatPhoto.X + CatPhoto.Width + 10,0). This tells PowerApps to shift the label to the right of the image control when it is visible and sets the X position to 0 when its not.

Now let’s set the Y position. In this case, if the image control is visible, let’s vertically align the label to the middle of the image control. But if the image control is hidden, let’s set the label to positioned relative to the top label. To do this, set the Y property of the Title/Description label to: If(CatPhoto.Visible,(CatPhoto.Y + CatPhoto.Height) / 2,lbWhoWhen.Y + lbWhoWhen.Height + 10).

Nice… the feed is looking a lot tidier…

Now let’s fix the separator rectangle we created earlier. Here, if the image control is visible, the Y position of the separator should be offset from it, however if the image control is hidden, then it should be based on the Title/Description label. To do this, set the Y property of the separator to: If(CatPhoto.Visible,CatPhoto.Y + CatPhoto.Height + 20, lbTitle.Y + lbTitle.Height + 20).

Preview the app and you should have yourself a nice feed!

Conclusion

At this point, I think we have covered enough to give you a really good feel for how PowerApps an play nicely with SharePoint search and Azure functions. While I fully accept this is an advanced scenario for the typical citizen developer, it is still a very low-code scenario and working knowledge of Azure functions and PowerApps/Flow custom connectors is very handy indeed.

I think it also shows that these platforms are highly flexible. This activity-feed scenario was one of those tricky scenarios that in the past, might have been added to the “too-hard” basket or resulted in custom development work.

Finally, I have recorded a series of videos to accompany these posts. I split the videos up into easy to consume chunks. The first one can be found below:

 

Thanks for reading

 

Paul Culmsee

Hi all

Welcome to part 3 of my posts that are intended to teach people more about the intricacies of Flow, SharePoint Web Services and oAuth. The cover story for this journey was the issue that presently, managed metadata fields are not supported natively in PowerApps nor Flow.

As a result, we are taking an approach where we update managed metadata values by talking to the SharePoint REST web service.

To recapitulate our journey, we have managed to achieve the following thus far. We have a basic powerapp, and a flow that now has an access token that will allow us to update the managed metadata column via API. If you are new to this series, then I strongly suggest going back and reading parts 1 and 2, before continuing here…

A SharePoint Web Service Interlude…

Those of you who got past part 2, and think the hard stuff is over, think again. You are about to take a dive into the weird world of talking to SharePoint via API, so go get yourself a coffee and I will do my best to provide an accessible explanation to what is going on.

To start with, let’s do a couple of general queries to SharePoint via API and get a feel for things. To do this, use the Chrome browser and sign into your SharePoint site collection. Once signed in, try the following URL. Note, if you are using a different list name to the one I have been using in this series, adjust accordingly:

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')

If this worked, you will be rewarded with a ton of data related to the Coffee Shop Ratings list. It won’t be overly meaningful in the Chrome output, but rest assured this is a good thing…

Now let’s make things a little less scary by being more specific about the information we want to retrieve…

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items?$select=Title

This API call is basically saying “get all items from this specified list, and only show me the Title column for each item” (that is what the $select=Title bit is doing). While the formatting in Chrome sucks, you can nevertheless see the output being returned in XML format. Note the title columns below showing the café names.

Now let’s get specific and select a particular item from the list by its ID. I can achieve this in the following way…

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(16)?$Select=Title

This call is still only asking for the Title column from the list ($Select=Title), but now it only wants the item 16 in the list. I happen to know in my list this is Café Gelato and the output confirms this…

Finally for now, let’s bring back the 3 columns we are using, including the one that’s been causing us all the trouble… I do this by specifying multiple columns in the $select bit at the end of the URL.

https://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(16)?$Select=Title,Rating,Beans

This time I will format the XML output a bit nicer for you and strip out some uninteresting stuff. In the <m:properties> section, you can see the information returned about our 3 columns…

A cursory glance shows the Title and Ratings columns are pretty simple. The ratings column differs from title in being numeric (m:type=”Edm.Double”) but still is a single line and fairly easy to understand. But take a look at the Beans column. It is a lot more complex than the others in that instead of being a single value, this column actually consists of three properties, Label, TermGuid and WssId.

At this point I should say that the SharePoint List API has a lot more to it than the simple examples above. If you are interested in learning more then start here. The key thing I want you to take away from this section is this: We are going to use Flow to perform queries just like we have done. We will query the lists web service to get some data and more importantly, we will also use calls to the web service to update the managed metadata column.

So let’s have a look at how we can do that…

Updating a List Item via REST API

Note: when I wrote this I was not aware you could reduce the amount of JSON manipulation you have to do via the odata=nometadata directive that Marc wrote about. I strongly suggest you use that article to sense-check what I write about here.

First up we need to understand the general pattern to updating a list item using the API. We need to issue a POST call to the list web service and in the body of our post, send our instructions about what to update. The URL is unchanged from what we have been working with…

http://<site collection url>/_api/web/lists/GetByTitle('Coffee Shop Ratings')/items(item id)

In terms of the format of the update instruction, the example below shows updating the Title field. While some bits of it might not make sense right now, we can see that the Title column is being updated to the value “TestUpdated”…

{ '__metadata': { 'type': 'SP.Data.TestListItem' },
  'Title': 'TestUpdated'
}

So what is the SP.Data.TestListItem? A hint comes from Microsoft’s own documentation that states: “The ListItemEntityTypeFullName property is especially important if you want to create and update list items. This value must be passed as the type property in the metadata that you pass in the body of the HTTP request whenever you create and update list items”

We will return to this mysterious ListItemEntityTypeFullName when we start building the flow, but for now lets park it and look at what needs to be sent in the HTTP Headers when posting to the API..

Authorization: "Bearer " + accessToken
IF-MATCH: etag or "*"
X-HTTP-Method:"MERGE"
accept: "application/json;odata=verbose"
content-type: "application/json;odata=verbose"

Wow – that’s quite a bit to do, and we have not even gotten to the Managed Metadata column yet! So let’s take a look at that now since we have already dug ourselves a bit of a theory-hole. Soon enough we will put all of this stuff into practice…

Updating a Managed Metadata Column

Now that we have seen the anatomy of a POST to update a list item, let’s now look at example of updating a managed metadata column. In a nutshell, the body of the POST looks like this: Note that in this example I have specified the ListItemEntityTypeFullName property for my Coffee Shop Ratings list. Don’t worry, I will show you how to find this out in the next section.

{
   "__metadata": { "type": "SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem" },
   "Bean":
     { "__metadata": {"type":"SP.Taxonomy.TaxonomyFieldValue"},
     "TermGuid":"0937fbc2-0dfe-439e-a24f-ba6d13897abd",
    "WssId":"-1"
   }
}

Unlike the title column above,  here we need to set two parameters – a TermGuid and a WssID. The former is the GUID of a term in managed metadata and can easily be found when looking at a term: for example:

The WssID parameter you can ignore. We need to specify it, but we will always be putting “-1” in the value. If you really want to know what it does, check this post. Also for those who have read other articles on this topic and are wondering why I left out the Label parameter… the answer is that with my testing, it was not necessary, so I omitted it.

So let’s summarise our journey so far… we need to do the following:

So let’s get back to the land of Flow and finish this!

Finishing off Flow…

1. Picking up from where we last left off, add a HTTP action. Rename it to “Get ListItemEntity”, set it to a GET, and set the URL to:

https://<your site collection>/_api/lists/GetByTitle(%27Coffee%20Shop%20Ratings%27)?$select=ListItemEntityTypeFullName')

in my example the site collection is:

https://culmsee.sharepoint.com/_api/lists/GetByTitle(%27Coffee%20Shop%20Ratings%27)?$select=ListItemEntityTypeFullName')

Check the URL carefully. You can see that I am using the $select to only bring back the property we are interested in… ListItemEntityTypeFullName. Also note that I have encoded the spaces in the list, I.e. ‘Coffee Shop Ratings’ has now become ‘Coffee%20Shop%20Ratings’.

2. In the headers section of the request, add:

• a key called Accept, with a value of application/json; odata=verbose
• a key called Authorization, with a value of “Bearer “ and then the AccessToken variable from the Dynamic Content panel…

Now before we get to the final bit, we have another minor theory interlude. The output of the web service you just created follows a particular structure. An example output is below and I have highlighted the bit we need.
{"d": 
   {"__metadata":
     { "id": <url>,
     "uri": <url>,
     "etag":"id",
     "type":"SP.List"
   },
   "ListItemEntityTypeFullName":"SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem"
   }
}

This means we will need to parse this output and just grab the ListItemEntityTypeFull name parameter. The expression in Flow to do this will be:

actionBody(<Name of the workflow action you just created>)['d']['ListItemEntityTypeFullName']

So using my tenant, the expression is:

actionBody('Get_ListItemEntity')['d']['ListItemEntityTypeFullName']

Note: if you want to know more about these expressions, this is the place to go…

So with that bit of context, lets continue…

3. Add an Initialize Variable action and rename it to “Set ListitemEntity”. Name the variable ListitemEntity and make it a string. In the value, use the expression builder and use the expression I outlined above:

The final task…

Now we get to the final and most mind-bending piece of the puzzle. It is time to build the JSON payload that we will POST to SharePoint to update the managed metadata column. Let’s begin this bit with the end-in-mind and work back.

First up, we need to make a variable to store the Term ID that we will write to managed metadata. This eventually come from PowerApps, but right now we need to test so we will hard code a term.

1. Add an Initialise Variable action and name it “Set Test Term ID”. Call the variable TermGuid and make it a string. Set the value to any term in your managed metadata term set that the Beans column is using. In my case, the GUID 96a085d2-ed7d-4a75-8d63-c6ca56d0b358 refers to the term “Fiori”.

2. Add an Initialise Variable action and name it “Build Update Managed Metadata JSON payload”. Call the variable JsonPayload and make it a string. In the value field, type in

{ "__metadata": { "type": "'

…and then add the variable ListItemEntity from the Dynamic Content tab.

3. Next append the following text to the value…

" },"Beans":{"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},"TermGuid":"

4. From the Dynamic Content tab, insert the TermGUID variable that you created in step 1

5. Next append the following text to the value…

", "WssId":"-1"}}

This completes the JSON payload that will update the managed metadata column. To recap, it has built the following string which is entirely consistent with the theory at the start of this article.

{ "__metadata": { "type": "SP.Data.Coffee_x0020_Shop_x0020_RatingsListItem" },
   "Beans":
     {"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},
     "TermGuid":"96a085d2-ed7d-4a75-8d63-c6ca56d0b358",
     "WssId":"-1"
   }
}

Now bear in mind this payload is a string and not in JSON format yet. We will take care of that in the final HTTP call.

6. Add a HTTP action, name it “Update Managed Metadata” and set the method to POST. In the URL field, enter the following:

https://culmsee.sharepoint.com/_api/lists/GetByTitle('Coffee%20Shop%20Ratings')/items(

7. From the Dynamic content menu, find the CoffeePlace content, find and insert the ID field and then add a close bracket to the end…

In case you are wondering, we already know the ID of the newly created coffee shop entry because of the SharePoint – Create Item action that we created way back in part 1. Fortunately for us, that action returns the ID of the newly created item, making it easy for us to specify it in our API call. Neat eh?

8. In the headers section, add an entry for each of the headers I described earlier in this article, namely:

• IF-MATCH: etag or “*”
• X-HTTP-Method:”MERGE”
• accept: “application/json;odata=verbose”
• content-type: “application/json;odata=verbose”

9. Now let’s add the access token that took up the bulk of part 2. In the headers, add a key called Authorize and in the value, type in:

“Bearer “ (showing quotes to illustrate a space on the end)

Then insert the variable AccessToken from the dynamic content pane…

10. Finally, let’s convert our payload to JSON and send it off. In the Body field, use the expression builder and enter the following:

json(variables('Jsonpayload'))

Are you ready???

Okay, so we have wired everything up. Save the workflow and either submit a new entry via PowerApps or use the Run Now function in Flow. If all things go to plan, you should see a bunch of green. In the screenshot below, I ran from Flow and added a title and rating (no metadata of course because we are not asking PowerApps for it.

After running the workflow I see green goodness

Now let’s check SharePoint for the evidence… Yeah baby!!!

Conclusion

Okay so that was quite an effort. If you have stuck with me through this, well done! Messing with JSON payloads and web services is bloody fiddly, but I have to say that understanding app principals and the SharePoint API is very useful knowledge.

You will be pleased to know that at this point the worst is over. In the next post we will return to PowerApps and set it up to send the term set item to flow, so it is not hard-coded like it is now. We will also discuss some weaknesses and limitations with this approach and look at ways we can make this whole thing a bit more resilient.

Until then, thanks for reading…

Paul Culmsee

 

 

 

Hi and welcome to part 2 of my series on showcasing the utility of Flow. Specifically, we are updating managed metadata fields – which is something that neither PowerApps or Flow can do natively as I type this. Having said that, if you are reading this in 2018 and beyond, it is likely this post is out of date the the gap has been addressed. Nevertheless, there are lots of good nuggets of info in this series that actually go beyond this particular use-case and will arm you with the knowledge to use Flow in all sorts of interesting ways.

At the end of our last exciting instalment, we had created an app principal, a flow and a powerapp. In this post we are going to focus on the first of the three – the app principal. Recall my analogy from part 1 where I described how the type of oAuth we are using is roughly analogous to getting into a nightclub. Basically our flow is like someone waiting in line to get into the club. Once they get past the bouncer at the door and pay the entrance fee, they are stamped so that they can enter and exit the club as they please, without having to wait in line and pay the entrance fee all over again. The stamp is temporary though, so if the patron returns the next night they will have to show their ID all over again and pay up.

The following diagram illustrates the idea…

In the last post, we registered an application principal. It looked like this:

This is what our virtual doorman is going to use to verify who we are. Our “dooman” is actually a Microsoft API sitting in Azure named https://accounts.accesscontrol.windows.net and not only will we need the Client ID and Client Secret from the app principal, but he also wants to know which Office365 tenant that we belong to. For that, we need the Office365 tenant ID, so let’s go get it.

Getting the Tenant ID (and other bits)…

As Microsoft states, “Your Office 365 tenant ID is a globally unique identifier (GUID) that is different than your tenant name or domain. On rare occasion, you might need this identifier, such as when configuring Windows group policy for OneDrive for Business”.

Guess what – this is one of those rare occasions.

To find the ID, either use one of the methods specified in this article, or go back to your site collection and append /_layouts/15/appprincipals.aspx. This URL will display your registered apps and also display your tenant ID (highlighted yellow below).

Now that we have a Client ID, Client Secret and Tenant ID, we can look at what a request to the doorman known as https://accounts.accesscontrol.windows.net actually looks like…

First up we need to add the tenant ID to the URL. E.g. https://accounts.accesscontrol.windows.net/[Tenant ID]/tokens/OAuth/2 where [TenantID] is replaced with your actual tenant ID we just discussed.

The second requirement is to send a payload of data to that URL via a HTTP POST. In the header of the post, we set a parameter called Content-Type to a value of application/x-www-form-urlencoded. This tells the web service to expect the rest of the data we send to be in a single string where name/value pairs are separated by the ampersand (&), and names are separated from values by the equals symbol (=). Trust me this will make sense in a moment…

Inside the payload, our doorman is expecting the following parameters:

• grant_type – this is where we specify the type of oAuth request we are making. As discussed in part 1, we are using the Client Credentials approach, so the value will be: “client_credentials”.
• client_id – this is actually a combination of the client id and tenant id. eg: “417ab7b8-c1bb-4475-8117-38ecec3cc63c@777e0ae8-66e6-4e0e-9ba4-0a8d9f02e915”
• client_secret – exactly as specified in the app principal
• resource – this specifies the actual data the access token is intended for. It is a combination of “00000003-0000-0ff1-ce00-000000000000” which is the ID for SharePoint within Office365, then it has the domain name of your tenant, followed by the tenant ID. Eg: “00000003-0000-0ff1-ce00-000000000000/culmsee.sharepoint.com@777e0ae8-66e6-4e0e-9ba4-0a8d9f02e915”

If we supply these parameters correctly, the service will return a data structure that contains various parameters. The only one we need is called access_token, which is highlighted below.

{

   "token_type":"Bearer",

   "expires_in":"3599",

   "not_before":"1507958657",

   "expires_on":"1507962557",

   "resource": “…”,

       "access_token":"…"

}

Our first few flow steps will create variables to store the parameters we need, and then we will try to get past our virtual doorman with a correctly formed HTTP request.

Back to Flow…

Let’s reopen the flow we created in part 1. It should have a trigger and one action – the create item action used to write the new coffee stop to SharePoint.

1. Add a new action and choose Variables – Initialize Variable. Call the Variable ClientID, make it a string and set its value to the client ID from the application principal. Also rename it to Set Client ID.

2. Add a second initialize variable action. Call this variable ClientSecret, make it a string and set its value to the Client Secret from the application principal. Rename it to Set Client Secret

Note: If the client secret has symbols in it like a +, you will see the following error:

Error validating credentials. AADSTS50012: Invalid client secret is provided. error_codes”:[70002,50012]

This error occurs because we need to encode the key to account for these characters. Use an expression and call the function encodeUriComponent. E.g.

encodeUriComponent(‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx+xxxxxxxxxxxxx’)

3. Add a third initialize variable action. Call this variable TenantID, make it a string and set its value to the tenant ID you obtained in the last section. Rename it to Set Tenant ID

As described above, we need to take the client ID and tenant ID and join them together to get our access token. So we will make a new variable that is a combination of the existing ones…

4. Add a fourth initialize variable. Rename it to Combine Tenant and Client ID. Call this variable Realm and make it a string. This time though, instead of typing in a value, click the expression tab to the right.

Now follow this section carefully as the UI is not obvious here. First type in concat() and place your cursor inside the two brackets. Then click on the “Dynamic content” tab and click the ClientID variable you created in a step 1. If you did it right, the concat function will now have a reference to that variable as shown in the 3rd image below.

 

Now type in ,‘@’, exactly as shown below (include the single quote)…

Finally, click on the Dynamic content tab and click the TenantID variable you created in a step 3. If you did it right, the concat function will now have a reference to that variable as shown below.

For reference, the expression should look like this:

• concat(variables(‘ClientID’),’@’,variables(‘TenantID’))

Click ok and the value in the step will look like the image below:

5. Add a fifth initialize variable. Rename it to Set Resource. Call this variable Resource and make it a string. using a similar approach to step 4, add the expression as follows:

• concat(‘00000003-0000-0ff1-ce00-000000000000/<tenant name>.sharepoint.com@’,variables(‘TenantID’))

Make sure you replace <tenant name> with the name of your tenant. I.e. mine is culmsee.sharepoint.com, resulting in:

• concat(‘00000003-0000-0ff1-ce00-000000000000/culmsee.sharepoint.com@’,variables(‘TenantID’))

At this point your workflow should look like this:

Let’s do a quick test and see if things are working as we expect. Go back to PowerApps and add a new café… eg:

In flow, check the history to confirm things ran successfully… lots of green ticks will be there if you got things right…

In particular, make sure the last two actions worked. Click on them and check the output to ensure your concat expressions worked as expected.

  

Getting the token…

Now it’s time to craft the HTTP action that will get our access token. As a reminder, the URL is actually: https://accounts.accesscontrol.windows.net/[Tenant ID]/tokens/OAuth/2. Our POST to this URL will supply grant_type, client_id, client_secret and resource. Let’s get this done!

1. Add a HTTP action and set the Method to POST. In the URL field, type https://accounts.accesscontrol.windows.net/

2. Click Add dynamic content and add the TenantID variable

3. Finish the URL by typing in /tokens/OAuth/2. You now have the URL ready to go.

4. In the header section, add a key called Content-Type with a value of application/x-www-form-urlencoded

5. In the body section, add the text grant_type=client_credentials&client_id=. From the dynamic content menu, add the Realm variable.

6. Add the text &client_secret=. From the dynamic content menu, add the ClientSecret variable.

7. Add the text &resource=. From the dynamic content menu, add the Resource variable.

8. Finally, rename the action to Call Token Service. Your action should look like the following:

9. Add a new initialize variable action. Call this variable AccessToken and make it a string. Rename the action to “Get Access Token”. Click the expression tab to the right and enter the following expression…

actionBody(<Name of your HTTP Action>’)[‘access_token’] – eg: actionBody(‘Call_Token_Service’)[‘access_token’]

This basically says to flow “Take the body text that came back from the HTTP step and pull out just the parameter called “access_token”

10. Finally, save the flow and submit another coffee place review, either via PowerApps, or by clicking the Run Now button in flow. Flow will ask for the same parameters that it expects from PowerApps.

In the debug of the flow, check the HTTP task and ensure it has a green tick.

Note: Setting all this up us tricky, so take your time, check everything and above all, DO NOT PROGRESS UNTIL YOU HAVE A GREEN TICK! If you do, examine the output of the Get Access Token. If everything has worked, you should see a string that looks like the second image below…

Conclusion:

If you have made it this far, congratulations! You have just done something quite tricky and usually the realm of developers. In terms of the breakdown of work we need to do, we have now progressed quite far…

In the next post, we are going to use the access token to call the SharePoint API that allows us to update Managed metadata.

In the meantime, thanks for reading and I hope these articles are starting to make this whole process a little less mysterious…

Paul Culmsee

Hi budding PowerApps and Flow users…

One (current) issue affecting both PowerApps and Flow is that neither support Managed Metadata Columns properly. I have a method to get around this issue, but it will take more than one post to explain. Lately I have refrained from writing epic multi-part posts because things change quickly. In fact this issue will likely go away by the end of 2017. <update> Native support for Managed metadata is now available. Be careful when you use this content</update>.

Due to this pace of change, this is very likely the last multi-part blog series I will do. So consider this a last hurrah from a bygone era of Cleverworkarounds

Now let’s get to work. To illustrate the managed metadata issue we currently have to contend with, consider the list below that tracks the most business critical task ever – where the best coffee is near the office…

The beans column is actually a managed metadata column, linked to the following term set which has a list of locations and coffee beans from that location. As you can see above, each café sells a certain type of coffee bean…

Now that we all understand the vital importance of the data contained in this list, it is obvious that we need an app for it so that our workforce can have locations of the best coffee at their fingertips. So let’s quickly examine the degree to which PowerApps is able to handle this list. I created a blank app and connected it to the above list. I then tested with a gallery, data table, display form and edit form. Here are the results below…

Gallery: Fail

As you can see in the image below, the gallery is bound to the Coffee Shop Ratings list, but in the dropdown highlighted below, the managed metadata “Beans” column is not listed.

Data table: Pass

The data table does work. You can see below that the Beans column is visible….

Display Form: Pass

Like the data table, a display form also will display the managed metadata value…

Edit Form: Fail

As you can see below, the edit form sees the Beans column, but will not render it as editable in PowerApps… bummer. Not a great solution if you want your users to add new coffee places to the list via the app.

Flow to the rescue?

If you have read some of my recent posts, I have found Flow to be very useful to work around various obstacles. For example, I use flow to handle photo uploads from PowerApps to SharePoint. So how does Flow fare with Managed Metadata? Unfortunately this is also a bust at present. The built-in “SharePoint – Create Item” action will not show the managed metadata columns. Notice the Beans column is missing from the image below…

Now all is not lost. Just because the built-in Flow actions do not work, does not mean we cannot achieve our goal. One can also talk to SharePoint via it’s API’s. Flow makes this possible because one of its built-in actions called the HTTP action. This is a super powerful action because you can use it to pretty much make any form of web request you want.

So let’s do an experiment and find out how easy it is to support managed metadata via Flow, by talking to SharePoint via API.

But there is one issue we have to contend with first. The SharePoint actions in flow take care of authentication for us, whereas the HTTP action is generic by definition. So how are we going to make an authenticated connection to SharePoint using the HTTP action?

The answer is we are going to use oAuth to request an access token. For the non-developers reading this who’s eyes start to glaze over when terms like oAuth start getting bandied about, let me use an analogy…

An oAuth primer using beer…

Let’s say that you are going to a nightclub or a bar, and you have to prove that a) you are over the legal age and b) you are wearing suitable attire for the venue. You present your drivers license or other form of ID. The doorman checks your ID, decides if you are good looking enough and permits you to enter. You then walk into the club, pay your entrance fee, and an ink stamp is used to mark your arm. This tells staff that are are legally allowed to be in the club and can enjoy a frothy beer or two. Think of that stamp as your access token. If you walk out of the club for a break, you can skip the line to get back in, because you have already been stamped. You just show them your hand…

So the basic pattern is to get an access token is:

Once you have access, no one has to check your ID, instead they just make sure the stamp on your arm is the same as the one they issued you earlier. Eg:

Now access tokens don’t last forever. If you turn up the next day, try and skip the line and present your stamp, the doorman will turn you away because today they used the blue stamp and last night they used the green stamp. So you will have no choice but to go to the other line, get your ID checked, and pay up before getting a new blue stamp issued…

So let’s now turn our attention to SharePoint and how this works in real life. What I just described above is roughly equivalent to something in oAuth called the Client credentials grant. In short, before we do anything related to managed metadata, we need to get into the club. So we will need to get some identification so we can get our hand stamped. So who issues the ID in the first place?

Enter the app principal

In our analogy above, there was an implicit trust going on. You showed your identification to the doorman, and they trusted that the ID you flashed before him was genuine. In the context of SharePoint, we need to have SharePoint itself issue your ID for it to be deemed trustworthy. After all, even bouncers have problems with fake IDs.

So our flow is going to need to use an app principal (a genuine ID) before it can be issued a “stamp” (or access token). Fortunately for us, the plumbing to do this in SharePoint has been in place since the oft-derided app/app-in model was brought in. I am not going to get too caught up in the details here, because all-round guru Wictor has detailed the process in a few posts. In particular, have a read of this one, and in particular the “Create the App Principal” page…

Here are the basic steps to create an app principal to use in flow:

1. Navigate to your site collection and append /_layouts/15/AppRegNew.aspx

2. Generate an App ID, App secret and enter some other identifying info

 

3. Copy the client id and secret to clipboard.

4. Append /_layouts/15/AppInv.aspx to your site collection and grant the permissions needed by this app principal.

In our example, we are updating a managed metadata field in a single list, so this app principal is going to be given the ability to write to SharePoint lists. The XML is below, and the schema for other scenarios is here:

<AppPermissionRequests AllowAppOnlyPolicy=”true”>

    <AppPermissionRequest Scope=”http://sharepoint/content/sitecollection/web/list” Right=”Write”>

    </AppPermissionRequest>

</AppPermissionRequests>

6. Choose the list that the app principal will be working with. In my case it is the coffee shop rating list with the managed metadata column.

Although I said I would not get into too much detail, here are some handy notes around this process:

• The permission XML is very fussy. It will not tolerate spaces between elements
• This app principal is registered/stored in azure active directory. You need privileged access in Azure AD to do this (site collection admin is not enough)
• You can also do all of this in PowerShell
• The app principal is valid for 1 year. So just like your drivers license expires periodically, the same happens here. If you want something longer, do this in PowerShell. (in our case native support for Managed Metadata in PowerApps/Flow is a few months off, so a year is fine
• The oAuth method we are using doesn’t actually use the App Domain nor the request URL. But other methods do, hence why we have to enter something anyway
• Microsoft are now steering people to the Graph API, which is a unified API that allows access to all of Office365 services. This will become the approach to take, but Wictor Wilen told me on Twitter that currently you cannot scope an access token down to a particular site collection.

Back to Flow…

Now that we have an app principal with permission to our site collection, it is time to start work on our Flow.

The basic operation for the flow is follows… First, the flow will be triggered by PowerApps, which will send Coffee shop name, rating and Bean. We will create a new entry in the list using the standard SharePoint flow action, before using the HTTP action to get an access token, and then update the newly created item with the coffee bean used by the shop.

Now the actual flow is going to look different to what I have outlined above. There are multiple actions involved for each of the steps, and some architectural and security considerations too. So we will adjust our flow as we go…

So let’s end this post by dealing with the trigger and step 1 above, as they are easiest. Part 2 will deal with the access token and part 3 will deal with the updating of the managed metadata column.

1. Create a new flow and choose the PowerApps trigger…

2. Add a SharePoint – Create Item action.

3. Specify the site collection where your list is stored and then the Coffee ratings list. Flow will read the list and display the fields it has found (excusing Beans)

4. Rename the flow to CoffeePlace and for the Title and Rating columns, set it to ask PowerApps for the data

5. Save the flow and give it an appropriate name…

Build a PowerApp…

Now let’s go to PowerApps and build a basic app to add a new coffee place.

1. In PowerApps create a new blank app using the phone layout

2. Insert 2 text boxes and 2 labels as shown below… note that I have named my controls to be more meaningful. I suggest you do the same…

3. Add a button to the bottom the app and label it “Submit”

4. Select the button, and from the actions menu, choose “Flows”

5. A panel will open to the right of your app screen, and you should see the flow we made earlier. Basically PowerApps will show any flows that you have access to, that use a PowerApps trigger

6. Choose your Flow, and PowerApps will add a submit action to the button that calls the flow. Any “Ask in PowerApps” parameters will be displayed in the function as shown below. Now hopefully you can see why I made you rename your Create Item action when building the flow! The image on the right is the flow action where we told it to ask PowerApps. Note the names in both images…

 

7. Fill in the parameters by choosing the text boxes we added in step 2. Now you will see why I recommend naming them…

8. Save your flow and then preview it… Enter a shop name, rating and click the button. Check your SharePoint list and you should see the new entry…

 

Conclusion…

Awesome! If you have gotten this far, we have all of our raw ingredients set up. We have an app principal, a PowerApp and a flow. In the next post, we will focus on getting our access token, so we can update the managed metadata field.

Thanks for reading…

Paul Culmsee

Hiya

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…

 

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 . 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

Hiya

Some of you might have seen my post where I explained how how my daughter, Ashlee (18), won a competition from Microsoft to develop a fidget spinner app. Well if you think that was impressive, check this out…

Inspired by the positive feedback from the community, Ashlee decided to up her game and investigated some of the various online services that PowerApps and Flow can connect to. The net result is an app which has capabilities that are quite extroadinary. By connecting to Microsoft Cognitive Services in Azure, Ashlee has created an app that will determine your IQ from your facial structure. In other words, you download this app, take a selfie, and via cloud-based machine learning that has processed millions of photos, you can find out your IQ without doing a traditional IQ test!

Even better, Ashlee has kindly donated it to the community as a learning tool, so you can not only learn about these ground-breaking techniques, but you can download/share it with your colleagues too.

So far this app has been used in various global organisations including Microsoft. It has also been demonstrated to rapturous applause at the recent Digital Workplace Conference in Sydney.

 

So DO NOT MISS OUT on this, especially when using this app is a simple set of easy-to-follow steps!

 

Ready… Steady…

 

Wait… I forgot to mention, this app is optimised for phones, and utilises Microsoft speech recognition, so be sure to turn up the volume so you can hear the instructions…

 

Right are you ready?

 

Oh… don’t forget… you need an Office365 subscription for this so make sure PowerApps is enabled on your tenant…

 

Okay, so do the following right now!

 

1. Download and install PowerApps studio onto your PC.

2. Download Ashlee’s Guess your IQ MSAPP (rename the file extension from .zip to .msapp) and open it in PowerApps Studio

3. In PowerApps studio, save a copy of the App to your tenant (“the cloud”) via the File menu

4. Now install PowerApps on your IOS or Android Phone (you will find it in the app store and its free)

5. Run PowerApps on your phone and sign in with the same user account you used in step 3

6. Select the app called Guess Your IQ. It might take a little while to load, but don’t worry – there is a lot of awesome functionality.

7. Enter your name when prompted…

8. Now use your phone camera to take a selfie…

9. Click the “Find my IQ” button and watch the magic of machine learning…

 

10. Seriously, you WILL NOT BELEIVE what happens next!… but you have to try for yourself!

 

Make sure you let me know what your IQ is!

 

Paul Culmsee

www.hereticsguidebooks.com

Introduction

Okay citizen developers, listen up! This post and its forthcoming second part will teach you how to get PowerApps to connect to an online web service to add an extra dimension of awesomeness to your app. It also covers what I think is the most off-putting aspect of PowerApps work in general – dealing with the horribleness of Swagger/OpenAPI. Hopefully by the end you will find my approach useful to make make it a bit less horrible. If you have not come across OpenAPI. you did not read a previous post of mine when I whined about it. That’s ok because that post is not a prerequisite for this article, BUT another post is required pre-reading.

In a previous post, I created a sample app that demonstrated an inspection scenario where a user would be taking photos, which were then sent to SharePoint via Flow, with nice file names. In this small series of posts we are going to build on this example, so I suggest starting there first and getting to the point where you can save photos to SharePoint via Flow as shown below:

 

In this post, I will focus on getting PowerApps talking to an external web service…

Now a common inspection scenario would be to capture current weather information each time a photo was taken. A good example would be an park ranger, inspecting sensitive environmental sites, or a plant operator who needs to conduct and document a safety inspection of equipment before it is used. In both scenarios, weather such as rain or wind, might have a material impact on the result of the inspection, so it makes sense to capture location and weather data along with the photo.

Now location data is easy, as PowerApps has the in-built ability to capture GPS data. All we need to do is pass that GPS data to a weather web service to get the local conditions like temperature, humidity and wind speed/direction. At first I thought I could do it all out of the box because Microsoft provides a built-in connection to MSN Weather API as shown below, but unfortunately you can only pass it location data in the form of a city name, not a latitude/longitude.

It did not take long for me to find an alternative. The nice folks at OpenWeatherMap offer an API that does accept geographic co-ordinates as input. Even better, they have a free option, provided you stay within certain limits. To use the service, you need to sign up and generate an API key so they can identify you, which is added to the API call. To access weather data for a location, the URL looks like the following:

http://api.openweathermap.org/data/2.5/weather?lat=[latitude]&lon=[longitude]&appid=[API Key]&units=metric

Of course, the “units” parameter at the end of the URL can be changed in case you live in one of the 3 remaining countries holding out against metric (looking at you USA! )

The data that is returned by this API is in JSON format. For example here is what comes back for my home town of Perth (lat -31.9529 & lon 115.8573)

I have pasted the data in a clearer format below. Note, it pays to get used to the JSON format, as it underpins a lot of modern web applications.

 

{"coord":{

   "lon":115.86,

   "lat":-31.95},

 "weather":[{

   "id":804,

   "main":"Clouds",

   "description":"overcast clouds",

   "icon":"04n"}],

 "base":"stations",

 "main":{

   "temp":5.37,

   "pressure":1023,

   "humidity":100,

   "temp_min":5,

   "temp_max":6},

 "visibility":7000,

 "wind":{

   "speed":1.5,

   "deg":50},

 "clouds":{

   "all":90},

 "dt":1499297700,

 "sys":{

   "type":1,

   "id":8189,

   "message":0.0089,

   "country":"AU",

   "sunrise":1499296640,

   "sunset":1499333125},

"id":2066756,

"name":"Maylands",

"cod":200}

As you can see above this is a bunch of name/value pairs. But now the challenge is to get this API/data into PowerApps so we can use it. Below is an image showing what it looks like when things are wired up into PowerApps. You might be thinking, where did this “OpenWeather.getWeather” function come from? How does it know to ask for latitude, longitude and units of measure?

The answer my friends is OpenAPI/Swagger. Strap yourselves in fellow citizen developers, as we need to go on a fun-filled ride.

Generating an initial OpenAPI file

Basically the OpenAPI specification (formerly known as Swagger) is a way to describe web services. Just as we create columns in document libraries so we have metadata to describe our documents, OpenAPI is basically “metadata for web services”. Unfortunately the comparison ends here because unlike documents in a SharePoint library, describing web services is an entirely different beast. I found the OpenAPI format so ugly and hard to get my head around, it took me an entire day just to use it previously. In short the learning curve is high, and although with persistence you will eventually get there, I found the whole thing to be poorly documented with not enough good examples.

Now it is not my intent to describe the format of OpenAPI. Luckily for us, there are websites that make the process of creating the metadata we need much easier. To this end, we are going to use a service from Apigee called openApiGen that takes 90% of the pain away.

So go ahead and sign up to OpenWeather and get yourself an API key. Confirm that it works in the browser as I showed earlier and then head on over to http://specgen.apistudio.io and follow these simple steps:

Step 1: Run your API

Paste your working API call from earlier into the text box and click the Send button. Check for a successful response and the JSON is in the expected format as shown below: 

Step 2: Enter API Program Information

This is where you describe your API in general. Note that PowerApps uses this info, so keep the title short and sweet and rename it from the default. I called mine OpenWeather.

Step 3: Update API Call Information

In this step, you have the option to perform more fine-grained tuning of your API, such as the function name that will be called from PowerApps. Like step 2, PowerApps uses this info, so keep your title short and sweet. The main thing is to set the Operation Id, as this corresponds directly to PowerApps as I show below (I called mine getWeather)…

Step 4: Update header info

In this step, you provide information about any API parameters passed in the HTTP headers. In this case the OpenWeather API does not use headers, so there is nothing to verify/edit here…

Step 5: Update Query Parameters

In this step, need to provide additional metadata information the parameters passed to the query in step 1. As you can see below, the 4 parameters we used have been found. Go ahead and add descriptions for the lat, lon and unit parameters, but ignore appid. We actually will not be using appid in the same way as the other three, so no edits need to be made (we will take care of appid later).

Step 6: API Path Info

This step is designed to handle API’s where the URL needs to adjust. (eg /api/{somedynamicvalue}/weather. In our case the URL is static, so there is nothing to do here.

Step 7: Save your goodness!

At this point, you have generated an OpenAPI file! Now that wasn’t too bad was it? Click the bright orange Download button to grab a copy of it.

Tweaking your initial OpenAPI file

At this point you might think that you are done, but not so..  While that site does a great job of generating your file, it is not a 100% guarantee to work, and in fact if we try this in PowerApps now, it will have an issue. For the sake of learning let’s go through the process anyway so you know how to deal with quirks…

So let’s turn our attention to PowerApps. Choose Connections from the left navigation and then click Manage custom connectors”. On the following screen, click Create Custom Connector.

 

One the next screen, upload your newly minted OpenAPI file. Note the connector name, as it will be based on the API Program Title you specified in step 2 of the previous section.

Scroll down and optionally, add a custom icon. I grabbed the OpenWeather icon from their site and added it here. Click the Continue button at the bottom of the screen.

The next screen is really important to get right. PowerApps needs to know what authentication method should be used when talking to the OpenWeather API. As we learnt earlier, it is an API Key, which is one of the options available. On choosing this option, you will be asked to provide some additional detail. As it states, “users will be required to provide the API Key when creating a connection”. This is a good thing because it allows you to distribute your PowerApp to others without requiring you to hardcode your own API key.

The important thing to get right is Parameter name and Parameter location. You will note that I entered “appid” for Parameter name, which corresponds to identically named parameter in the URL for the web service, I.e.

http://api.openweathermap.org/data/2.5/weather?lat=[latitude]&lon=[longitude]&appid=[API Key]&units=metric

The Parameter location simply tells PowerApps that the API key is in the URL query, and not in the HTTP headers.

The next screen is a busy one, principally because it is where you get to tweak your API definition as from what is specified in the OpenAPI file. First up, on the top half of the screen, in the General section, verify that the Operation ID is correct as per step 3 of the previous section. Note that PowerApps will warn you if the function name does not start with an Upper case letter, so make that change if you are really concerned about it.

 

The more important bit is further down the screen. Scroll down to the Request section and delete the appid parameter altogether. Since we have already told PowerApps to ask for it when you add a connection to OpenWeather, we should not be asking for it inside the PowerApps function call…

  \

Finally, complete the operation by clicking Create connector.

PowerApps will chug away and eventually you will see an error. Unfortunately for us, whatever team designed this part of PowerApps didn’t think things through, because they leave limited space for the error message and the actual meaningful part of the error is truncated. To alleviate this piece of poor design, so move your cursor over the error message to see the full text. Unfortunately the poor design does not stop as this error hover over is fleeting and disappears, requiring you to hover off and back on again to see it.

For the record, it will say:

Your custom connector has been successfully created, but there was an issue converting it to WADL for PowerApps: An error occurred while converting OpenAPI file to WADL file. Error: ‘Required property “type” is not present or not a string at JSON path paths[‘/data/2.5/weather’].get.responses.200.schema.properties.weather.items’

So we are going to have to tweak our OpenAPI file to fix this. If you do not have a text editor that supports JSON, I suggest you download and install Notepad++ to make this easier…

Fixing the OpenAPI file…

Now delving into the guts of the OpenAPI specification is not my idea of fun, nor is teaching you to learn JSON. So let’s just assume you are not familiar with JSON and  review the message and see if it helps us:

Error: ‘Required property “type” is not present or not a string at JSON path paths[‘/data/2.5/weather’].get.responses.200.schema.properties.weather.items’

Looking at the OpenAPI file, I see the a pattern. First I can see how it corresponds with the OpenWeather web service API output. For example, the response from calling the API in the browser produces output that starts with a “coord” parameter and then a “weather” parameter.

When I look in the OpenAPI file, I can see those same parameters under the “properties” declaration. Check out the image below… given the property section is below the “Responses” and “200” sections, it is easy to see now that this section of the OpenAPI file is describing the metadata for a successful response from OpenWeather (i.e. HTTP code 200). Hmm… suddenly this ugly file is looking slightly less ugly…

While the image above shows me the section I need to focus on according to the error message. The image below more detailed view of that section with some annotations (and can you tell I just bought a laptop with a pen? ). I noticed that every property has a type declaration for it (highlighted), except for the property called “items” (marked with red).  It seems to be anomalous in that no “type” is designated for “item”.

So the next question is if a type is missing, what type is it? Looking elsewhere in the OpenAPI file, it appeared the missing type was “object” based on the fact that the items property itself was made up of sub properties. So I inserted the “type” : “object” under the declaration of the items property. made the following change:

I saved the file and returned to PowerApps. I deleted the problematic connector and then repeated the steps in PowerApps to create a custom connection. Aha! We have lift-off!!!

Okay, so at this point we have things wired up. I hope this gives you the confidence to wire up other web services to PowerApps.

In the next post, we will make use of this newly connected API in PowerApps!

 

 

Until then, thanks for reading

 

Paul Culmsee

www.hereticsguidebooks.com

Hiya

A client recently asked to make a PowerApps proof of concept audit app for safety inspections. The gist was that a user would enter an audit number into the App, take a bunch of photos and make some notes. The photos needed to be named using the convention: <Audit Number>-<Date>-<Sequence Number>. Eg

• 114A3-13:04:17-3.jpg is Audit number 114A3, photo taken on 13th of April and it was the 3rd photo taken.
• 114A6-14:04:17-7.jpg is Audit number 114A3, photo taken on 14th of April and it was the 7th photo taken.

As I type these lines, it is still not possible to save pictures directly into SharePoint from PowerApps, so we are going to build on the method that Mikael Svenson documented. But first let’s look at this file name requirement above. While it seems straightforward enough, if you are new to PowerApps this might take a while to figure out. So let’s quickly build a proof of concept app and show how this all can be achieved.

In this post I will build the app and then we will use Microsoft Flow to post the images to a SharePoint document library. I’ll spend a bit of time on the flow side of things because there are a few quirks to be aware of.

First up create a blank app and then add a text box, camera, gallery and two buttons to the form… Rename the text box control to “Auditnumber” and arrange them similar to what I have done below…

First up, let’s disable the submit button until an audit number is entered… Without an audit number we cannot generate a filename. To do this, set the Disabled property of the button labelled “Submit” to AuditNumber.Text=””. This means that while the audit number text box is blank, this formula will return “true” and disable the button.

 

Now let’s set things up so that when a photo is taken, we save the photo into a Collection. A collection is essentially an in-memory data table and I find that once you get your head around collections, then it opens up various approaches to creating effective apps.

On the camera control (assuming it is named Camera1), set the OnSelect property to “Collect(PictureList,{ Photo: Camera1.Photo, ID: CountRows(PictureList)+1 } )”

Now if you are a non developer, you might think that is a bit ugly, so let’s break it down.

• The collect function creates/populates a collection – and the first parameter is simply the name of the collection. You could call this anything you like, but I chose to call mine PictureList.
• The second parameter is a record to insert into the collection. This consists of a comma delimited set of fields and values inside curly braces. eg: { Photo: Camera1.Photo, ID: CountRows(PictureList)+1 }

Now that whole curly brace thing is a bit ugly so let’s break it down further. First up here is an image straight from Microsoft’s own documentation. You can see a record is a row of fields – pretty standard stuff if you are familiar with databases or SharePoint list items.

In PowerApps formulas, a record can be specified using curly braces. So { Album: “Thriller”, Price: 7.99 } is a record with 2 fields, Album and Price.

Now take a look at the record I used: { Photo: Camera1.Photo, ID: CountRows(PictureList)+1 } . This shows two fields, Photo and ID. The values for the Photo field is Camera1.Photo, which holds the latest photo taken by the camera. The ID field is a unique identifier for the photo. I generated this by calling the CountRows function, passing it the PhotoList collection and then adding 1 to the result.

So here’s what happens when this app starts:

• The PhotoList collection is empty
• A user clicks the camera control. A photo is taken and stored in the Camera1.Photo property.
• I then count the number of records in the PhotoList collection. As it is currently empty, it returns 0. We add 1 to it
• The photo and an ID value of 1 is added to the PhotoList collection
• A user clicks on the camera control again. A photo is taken and stored in the Camera1.Photo property.
• I then count the number of records in the PhotoList collection. As it is currently has 1 record from steps 1-4, it returns 1. We add 1 to it
• The photo and an ID value of 2 is added to the PhotoList collection

… and so on. So let’s test this now…

Press play to test your app and take a couple of photos. It may not look like anything is happening, but don’t worry. Just exit and go to the File Menu and choose “Collections”. If everything has gone to plan, you will now see a collection called PictureList with the Photo and ID columns. Yay!

So next let’s bind this collection to the Gallery so we can see photos taken. This part is easy. On the Gallery you added to the page, set the Items property to PictureList (or whatever you named your collection). Depending on the type of gallery you added, you should see data from the PictureList collection. In my case, I changed the gallery layout to “Image and Title”. The ID field was bound to the title field and now you can see each photo and its corresponding ID.

   

Now let’s provide the ability to clear the photo gallery. On the button labelled “Clear”, set the OnSelect property to “Clear(PictureList)”. The clear command does exactly what it suggests: clears out all items from a collection. Now that we have the gallery bound, try it out. You can take photos and then clear them to your hearts content.

Now the point of this exercise is to generate a nice filename. One way is by modifying the record definition we were using: eg

From this:

• Collect(PictureList,{ Photo: Camera1.Photo, ID: CountRows(PictureList)+1 })

To this crazy-ass looking formula:

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

To explain it, first check out the result in the gallery. Note my unique identifier has been replaced with the file-name I needed to generate.

So this formula basically uses the Concatenate function to build the filename in the format we need. Concatenate takes a list of strings as parameters and munges them together. In this case, it takes:

• The audit number from the textbox – Auditnumber.Text
• A hyphen – “-“
• Todays date in dd:mm:yy format and converts it to text – Text(Today(),”[$-en-US]dd:mm:yy”))
• Another hyphen – “-“
• The row count for PictureList collection, adds 1 to it and converts the result to text – Text(CountRows(PictureList)+1)
• The file type – “.jpg”

The net result is our unique filename for each photo.

Now we have one final step. We are going to send the entire collection of photos and their respective file names to flow to put into a SharePoint Library. The method we will use will take the PictureList collection, and save it as one giant string. We will send the string to Flow, and have Flow then pull out each photo/filename pair. Mikael describes this in detail in his post, so I will just show the code here, which we will add to the onSelect property of the submit button.

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

So what is this formula doing? Well working from inside to out, the first thing is the use of the Concat function. This function does something very useful. It works across all the records of a PowerApps collection and returns a single string. So Concat(PictureList, ID & “|” & Photo & “#”) takes the file name we generated (ID), joins it to a pipe symbol “|” and joins that to the photo, and then adding a hash “#”. The UpdateContext function enables us to save the result of the Concat into a variable called FinalData.

If we test the app by taking some photos and clicking the submit button, we can see what the FinalData variable looks like by the Variable menu as shown below. Our table is now one giant text string that looks like:

“filename 1 | encoded image data 1 # filename 2 | encoded image data 2 # filename x | encoded image data x “

Now a quick caveat, avoid using PowerApps desktop client to do this. You should use PowerApps web creator due to an image encoding bug.

Anyway, let’s now move to Flow to finish this off. To do this, click on the Submit button on your app and choose Flows from the Action menu and on the resulting side panel choose to Create a new flow.

 

A new browser tab will open and sign you into Microsoft flow, and be nice enough to create a new flow that is designed to be triggered from PowerApps. Rename the flow to something more meaningful like “Upload Photos to Audit Lib” and then click the New Step button, and add a new Action. In the search bar, type in “Data Operations” and in the list of actions, choose the “Compose” action…

 

Okay so at this point I should a) explain what the hell we are going to do and b) credit Mikael Svenson for first coming up with this method. In relation to the first point, PowerApps is going to send flow a giant string of photos and filenames (remember the FinalData variable – that’s what flow will receive). If you recall with the FinalData, each photo/filename pair is delimited by a hash “#” and the file name is delimited by a pipe “|”. So we need to take what PowerApps sends, and turn it back into rows. Then for each row, we grab the file name, and the file content and upload it to our friendly neighbourhood SharePoint library.

Sounds easy right?

Our first step is to use the compose action we just added to split the data from PowerApps back into photos. This particular workflow action does something really neat. It executes workflow definition language formulas. What are these? Well workflow definition language is actually a part of another Microsoft product called Azure Logic Apps. Flow leverages Azure Logic Apps, which means this language is available to us. Sounds complex? Well yeah, it does at first but when you think about it this is not new. For example, MS Teams, Groups and Planner use SharePoint behind the scenes.

Anyway, the point is that there are several workflow definition language functions we can use, namely:

• Split() –  Takes a string, and splits it up into smaller strings based on a delimiter
• Item() – This is used to to return an element of an array. Eg if we use the split command above, item() will refer to each smaller string
• dataUriToBinary() – This takes an image encoded and turns it back into binary form.

Okay enough talk! Let’s fill in this compose action. First up (and this is important), rename the action to something more meaningful, such as “ProcessPhotos”. After renaming, click on the text box for the Compose action and a side panel will open, showing a PowerApps label and a box matching the PowerApps logo called Body. Click the Body button and the compose textbox should have a value called ProcessPhotos_Inputs as shown in the 3 images below…

So what have we just done? Essentially we told the Compose method to ask PowerApps to supply data into a variable called ProcessPhotos_Inputs. In fact, let’s test this before going any further by saving the flow.

Switch back to PowerApps, click the Submit button and select the onSelect method we used earlier. You should still see the function where we created the FinalData variable. Copy the existing function to the clipboard, as you’re about to lose everything you typed in. Now click the Flow once and it should say that it’s adding to PowerApps.

At this point, the function you so painstakingly put together has been replaced by a reference to the Flow. Delete what’s been added and paste the original function back. Add a semicolon to the end of the function (which tells PowerShell that another command is coming) and then press SHIFT+ENTER to insert a newline. Now type in the name of your Flow and it should be found via auto-complete. Click the matching flow with .Run on the end. Add a left bracket to the end of the flow and it will ask for an input parameter. If you have done it right, that parameter will be called ProcessPhotos_Inputs. Note that it matches the parameter from above image. The parameter we are passing is the FinalData variable we constructed earlier.

  

Okay so basically we have wired up PowerApps to flow and confirmed that Flow is asking PowerApps for the correct parameter. So let’s now get back to Flow and finish the first action. If you closed the Flow tab in your browser, you can start a new flow editing session from within PowerApps. Just click the ellipsis next to your flow and click Edit.

Right! So after that interlude, lets use the first function of interest – split(). In the text box for your compose function, modify it to look like the string below. Be sure to put the whole thing in quotes because Flow is going to try and be smart and in doing so, make things really counter intuitive and hard to use. Don’t be surprised if your ProcessPhotos_Inputs box disappears. Just add it back in again via the “dynamic content” button.

In fact, save and close the flow at this point and then edit it again. You will now see what the real function looks like. Note how the ProcessPhotos_Input has magically changed to {@triggerbody()[‘ProcessPhotos_Inputs’]}.

Unfortunately this sort of magic will not actually work… there are a few too many curly braces and excessive use of “@” symbols. So replace the function with the following (include quotes):

• “@split(triggerBody()[‘ProcessPhotos_Inputs’], ‘#’)”

Like I said… flow is trying to be smart, only its not . If you have done things right the action looks like the screen below. Double check this because if you do not have the quotes right, it will get messy. In fact if you save the flow and re-open it the quotes will disappear. This is actually a good sign…

After saving the flow, closing an re-opening… look Ma, no quotes!

Now for the record, according to the documentation,  the triggerbody() function is a reference function you use to “reference outputs from other actions in the logic app or values passed in when the logic app was created. For example, you can reference the data from one step to use it in another”. This makes sense – as ProcessPhotos_Inputs is being passed from the PowerApps trigger step to this compose function we are building.

In any event, let’s add the next workflow step. The output of the ProcessPhotos step should be an array of photos, so now we need to process each photo and upload it to SharePoint. To do this, click on “New step”, and from the more button, choose “Add an apply to each” action. In the “Select an output from previous steps” action, click “Add dynamic content” and choose the output from the ProcessPhotos step as shown in the sequence of images below.

  

Next click “Add a condition” and choose the option “Edit in advanced mode”. Replace any existing function with :

• @not(empty(item()))

The item() function is specifically designed for a “repeating action” such as our “apply to each” event. It returns the item that is in the array for this iteration of the action. We are then using the empty() function to check if this item has any data in it and then via the not() function so we can only take action of the item has data. In case you are wondering why I need to test this, the data that comes from PowerApps has an extra blank row and this effectively filters it out.

The resulting screen should look like this:

At this point we should have a file name and file body pair, delimited by a pipe symbol. So let’s add an action to get the file name first. On the “If Yes” condition, click “Add an action” and choose another Compose Action. Rename the action to “Get File Name” and enter the function:

• “@split(item(),’|’)[0]”

 

The square brackets are designed to return a value from an array with a specific index. Since our split() function will return an array of two values, [0] tells Flow to grab the first value.

Now let’s add an action to get the file body. Click “Add an action” and choose another Compose Action. Rename the action to “Get File Body” and enter the function:

• “@dataUriToBinary(split(item(),’|’)[1])”

Looking at the above function, the split() side of things should be easy to understand. We are now grabbing the second item in the array. Since that item is the image in an encoded format, we are then passing it to the dataUriToBinary() to turn it back into an image again. Neat eh?

Now that we have our file name and file body, let’s add the final action. This time, we will choose the “Create File” action from the SharePoint connector. Rename the action to “Upload to Library” and enter the URL of the destination SharePoint site. If you have permission to that site, you can then specify which library to upload the photo to by browsing the Folder Path.

 

Now comes the final steps. For the File Name, click the “Add dynamic content” button and add the Output from the Get File Name step (now you know why we renamed it earlier). Do the same for the File Content textbox by choosing the output from the Get File Body step.

Right! We are ready to test. Sorry about screenshot hell in the Flow section, but I am writing this on the assumption you are new to it, so I hope the extra detail helped. To test, use PowerApps on your phone or via the web browser, as the PowerApps desktop tool has a bug at the time of writing that will prevent the photos being sent to Flow in the right format.

Now it is time to test.

So check your document library and see if the photos are there! In the example below I took a photo of my two books for blatant advertising purposes . The 3rd image is the document library showing the submitted photos!

  

Finally, here are some tips for troubleshooting along the way…

First up if you are having trouble with your flows, one lame but effective way to debug is to add a “Send an email” action from the Ofifce365 Outlook Connector to your flow. This is particularly handy for encoded images as they can be large and often in the flow management tools, you will see a “value too big to display” error. This method is crude, and there are probably better approaches, but it is effective. In the image below you can see how I have added to action below my ProcessPhotos action prior to splitting it into an array.

  

Another thing that has happened to me (especially when I rename flow actions) is a disconnect between the trigger connection between Flow and PowerApps. Consider this error…

 

If your flow fails and you see an error like

“Unable to process template language expressions in action [one of your actions] inputs at line ‘1’ and column ‘1603’: ‘The template language expression ‘json(decodeBase64(triggerOutputs().headers[‘X-MS-APIM-Tokens’]))[‘$connections’][‘shared_office365’][‘connectionId’]’ cannot be evaluated because property ‘shared_office365′ doesn’t exist, available properties are ”. Please see https://aka.ms/logicexpressions for usage details.’

.. the connection between PowerApps and Flow has gotten screwed up.

To resolve this, disconnect your flow from PowerApps and reconnect it. Your flow will be shown as a data source which can easily be removed as shown below.

Once removed I suggest you go back to your submit button and copy your function to the clipboard. This is because adding the flow back will wipe the contents of the button. With your button selected, click the Flows button from the Action menu and then click on the Flow to re-associate it to PowerApps. The third image shows the onSelect property being cleared and replaced with the flow instantiation. This is where you can re-paste your original code (4th image)…

Finally, another issue you may come across is where no pictures come through, and instead you are left with an oddly named file, which (if you look closely) looks like one of your compose actions in Flow. You can see an example of this in the image below.

The root cause of this is similar to what I described earlier in the article where I reminded you to put all of your compose actions in quotes. I don’t have a definitive explanation for why this makes a difference and to be honest, I don’t care. The fix is easy: just make sure all of your compose actions are within quotes, and you should be cooking with gas.

Phew! Another supposedly quick blog post that got big because I decided to explain things for the newbie. Hopefully this was of use to you and I look forward to your feedback.

Paul Culmsee

www.hereticsguidebooks.com