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

Background

Hi and welcome to the second article that describes how to make a PowerApps-based activity feed based on SharePoint search results. Although I did this as part of a large real-world client project, because of confidentiality considerations I am utilising one of my daughters PowerApps to illustrate the idea. Her CATalogue app which is shown below, has a feature she called the “Mewsfeed” which displays a list of cat-related activity on a SharePoint site collection, in order of recency…

As a reminder, the solution approach is to have PowerApps call an Azure function via a custom connector. The Azure functions runs PnP PowerShell to execute a search against a SharePoint Result Source. You can read the first post if you want to know more about why I utilised this method.

There are 5 parts to this solution.

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. Now it is time to do steps 3 and 4. That is, create an Azure function and custom connector so PowerApps can run the script…

3. Creating the Azure function

For the uninitiated, Azure Functions allow you to take your dodgy scripts and turn them into web services. In other words, I can set up PowerApps up to run the search script from part 1 and retrieve the results for display. For this to work, we need a user account to connect to SharePoint. Thus, if you do not have a service account to use, go ahead and create one now, making sure you grant it access to your SharePoint site.

Also, I expect that making an Azure function might be new to some readers. For the sake of brevity, I am not going to exhaustively cover this end-to-end. I will assume you have an Azure subscription, and have created an Azure Function App. The type of function you need is a HTTP trigger running PowerShell as shown below. So give your function an appropriate name and click Create.

In a short time, your function will be provisioned with some skeleton PowerShell code. Take a quick look at this default code because it is useful to understand for the future. Line 2 shows that all of the data posted to this webservice is stored in a variable called $req which is assumed to be Json format. The Get-Content cmdlet reads the content of $req and converts it from Json into a PowerShell object called $requestbody. This is handy knowledge, (despite the fact that for our needs PowerApps will not be sending anything to this function) because it means you can create functions that behave in different ways based on what data you to it send when calling it from PowerApps.

Now that we have seen the default code in a freshly minted function, we have an additional task to complete before we start modifying it. We have to upload the PnP PowerShell cmdlets that among other things, contain the Submit-PnPSearchQuery command that we learnt about in part 1. The easiest way to do this, is to install the PnP PowerShell module to your PC, and then copy the entire installation folder up to your Azure function app. To install PnP PowerShell to your PC or update to the latest build, follow the documented instructions (which usually amounts to typing in “Install-Module SharePointPnPPowerShellOnline” in a PowerShell admin session).

Once PnP PowerShell module are installed to your PC, we need to upload it to the Azure Function App. To do this, I use the Kudu tool that comes built-in to Azure functions. You can find it by clicking on your Azure function app and choosing the Platform Features menu. From here you will find Kudu hiding under the Development Tools section.

 

When the Kudu tab loads, click the Debug console menu and create a CMD or PowerShell console (it doesn’t matter which). We are going to use this console to copy up the PnP PowerShell components we just installed locally. Focusing on the top half of the screen, click on site and then wwwroot folders. This is the folder where all of your azure functions are stored (you will see a folder matching the name of the function we just made). What we will do is install the PnP modules here, so it can be used for other PowerShell-based functions that you are sure to develop .

 

Click the + icon to create a folder here and call it “Modules”. From here, drag and drop the PnP PowerShell install location from your PC to this folder. In my case PnP was installed into C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\2.19.1710.1 on my PC. Thus, I copied the 2.19.1710.1 folder and all of its content here.

Once the copy is done, click the folder to confirm the PnP modules are there…

Now let’s turn our attention to the script itself which actually looks like this…

1. Import-Module "D:\home\site\wwwroot\modules\2.19.1710.1\SharePointPnPPowerShellOnline.psd1" -Global
2. $username = mewsfeed@culmsee.onmicrosoft.com
3. $password = $env:PW;
4. $siteUrl = https://culmsee.sharepoint.com
5. $secpasswd = ConvertTo-SecureString $password -AsPlainText –Force
6. $creds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd)
7. Connect-PnPOnline -url $siteUrl -Credentials $creds
8. $returnproperties = @("ContentType","ListID","ModifiedBy", "ListItemId")
9. $result = Submit-PnPSearchQuery -Query "*" -MaxResults 20 -SourceId "81359f0f-8e1d-4b51-8251-1c4f2006e909" -RelevantResults -SortList @{"LastModifiedTime" = "Descending"} -SelectProperties $returnproperties
10. $output = $result | ConvertTo-Json
11. Out-File -Encoding Ascii -FilePath $res -inputObject $output

Notes to the script:

• Line 1, via the Import-Module cmdlet, enables us to use the PnP PowerShell cmdlets we just uploaded.
• Lines 2-7 are about setting up the credential to log into SharePoint online. This uses the service account I mentioned earlier
• Lines 8 and 9 set up and execute the query against the SharePoint Search Result Source created in part 1. Line 8 specifies the fields we want to return in an array which is then specified in line 9 via the “–SelectProperties $returnproperties” parameter. After all, no point wasting bandwidth sending back data to PowerApps that we are not going to use…
• Line 10 and 11 format the results into Json, ready to send back to PowerApps.

Handling passwords

An important consideration here is addressing the issue of passwords. This is where the $env:PW comes in on line 3 of my code. You see, when you set up Azure Functions application, you can create your own settings that can drive the behaviour of your functions. In this case, we have made an environment variable called PW which we will store the password to access this site collection. This hides clear text passwords from code, but unfortunately it is a security by obscurity scenario, since anyone with access to the Azure function can review the environment variable and retrieve it. Therefore a better (but not foolproof) solution is to use Azure Key Vault via this method described by Darren Robinson, but this will take us too far afield from an already long article.

In any event, you will find the ability to specify an environment variable under the Applications Settings link in the Platform Features tab. Scroll down until you find the “App Settings” section and add your password in as shown in the second image below. Calling the new setting PW is optional – just make sure you update line 3 of the PowerShell code to whatever name you choose.

Right so it’s now time to test our function. Simply click the Save and run button, and if all goes to plan, you will see a “function completed” message in the logs and a status of 200 OK in the Outputs section of the screen, along with a heap of Json. Don’t proceed further until you are seeing this output. If it does not work here, it certainly will not work in PowerApps!

4. Creating the custom connector

Now we come to the area that is most scary-looking and error-prone, which is to create a file that describes the Azure function in a way that PowerApps can use. In a nutshell, PowerApps needs to be told the type and format of data to expect from our new Azure function.

There are two supported ways to do this. Either generate an OpenAPI file or a Postman Collection. In previous blog posts I have taken the OpenAPI route and used the Open API Spec Generator (specgen) to create the custom connector. This time, for the sake of illustrating the alternative, I will use the Postman method to do it instead. For the uninitiated reading this, Postman is a powerful and versatile tool that helps developers make and debug HTTP requests. Like Fiddler, I recommend anybody that has to work with or debug webservices to keep it handy.

So lets get down to business.. here are the steps to make the collection we need.

1. In your Azure function, find the Get function URL link, click it and copy the URL to clipboard. This is your web service endpoint that PowerApps will talk to.

Note: The structure of an Azure functions URL is important to understand. Take close note of the code parameter as you will need it later when troubleshooting…

https://[appurl].azurewebsites.net/api/[function name]?code=[ a string of characters ]

2. Start Postman, and on the Builder tab, select POST for the HTTP method, enter the request copied URL for the function and set Authorization to No Auth. Click the Send button and wait for the reply…

The response field contains the full response from the API. If all goes to plan, you will see the same Json from your successful test of the PowerShell script earlier. If you get an error, go back to the Azure function and check the log screen to see what happened.

3. Save the request to a collection by clicking Save. On the next screen, give the request a name and description. Take care with this name and description, as the custom connector uses these values for the API operation summary and description which will be used in PowerApps (more on that later). In my example I saved the request as “GetMewsFeed”.

4. Further down the dialog box, click + Create Collection and provide a collection name. Note, also be careful here because the custom connector uses whatever you type here when you call the API. In the example below, I called it “CATalogue”. Click the tickbox and then click the “Save to CATalogue” button (the name of the button obviously depends on your collection name).

 

5. Unfortunately we have only saved the HTTP request made to our Azure function so far. This is insufficient, because we need the response with the Json search results so that we can create the custom connector that PowerApps needs. To add the response to the collection, find the Save Response button to the right. Click it, give your response a name and save it (I called mine “GetMewsFeedResponse”).

6. Next we clear all headers from the collection. Microsoft states that:

“before you export the collection, remove [any] content type and security headers – these were required to make API requests, but they are handled differently in the custom connector.”

Who am I to argue eh? So to to this, find your way back to the API call, rather than the example we just saved. If you are still in the example screen, you will find your API in the navigation above the example name as shown below. Click it to get back to your API call.

In the Headers tab, hover over each header, and choose the X next to the header to remove it. Choose Save to save the collection again.

7. Now we are ready to export the collection for PowerApps. Choose the ellipsis (. . .) next to the collection, then choose Export. Choose the Collection v1 export format and save the file.

8. Now it is time to import our newly minted Json file into PowerApps to create a custom connector. Sign into PowerApps, navigate to Custom Connectors and click + Create Custom Connector and then choose Import a Postman collection. Choose the file we saved in the previous step, give it an appropriate name and click Continue.

 

Here you will see the first implication of naming decisions you made in previous steps. You will be presented with a General Information screen, which as can be seen below, shows a description based on the name of the collection that you specified in step 4…

 

Click the Continue button and you will be taken to the Security tab. We have nothing to modify here, so click Continue to move to the Definition tab, which is where the important stuff is. What you should notice about this screen is the Summary, Description and Operation ID settings matche what you typed in when exporting from Postman in step 3. You have an opportunity here to modify this if you wish…

There is a super-important step here! Note how the above image shows a code parameter in the Query section. This has been inferred from the Azure function URL which we examined in step 1. We need to make a change to this configuration, as well as double check things. In my testing, Postman collections do not always capture the code correctly, so click the ellipsis (…) and choose Edit so we can make sure it is right.

Comparing the Default value for code in the custom connector to the URL from the Azure function portal below, we can see a discrepancy. The former seems to have stripped off a couple of characters, so make sure that the code parameter in the custom connector matches the azure function URL exactly.

 

Additionally, change the Visibility section from none to internal. If you do not do this, PowerApps will ask users to add the code when they use this data source. In some enterprise scenarios this is a desirable behaviour, but not in this case…

Now that you have made your edits to code, click the Back button. Let’s now verify that we are sending useful data to PowerApps. Find the Response section as shown below and click on the box that containing the number 200.

You will now see what has been done with the Postman collection. All of the Json from the search result has been used to create a payload. Each and every piece of search data that is returned by the PowerShell script is shown. Clicking on the ellipsis for any of them allows you to review the settings and change data type if necessary (eg from a integer to a string).

   

In my case, we are not going to modify the schema that PowerApps has detected. However, there is a possibility that in your case, you might have to if the data types for the parameters have been incorrectly inferred. So let’s go ahead and create our connector by clicking the Create Connector link.

Assuming all goes to plan, you have a Shiny new connector!

Right! If you have gotten through all that then well done! While all of this might seem a bit foreign and intimidating, rest assured that after doing 1 or 2 of these, the whole process is quite straightforward in terms of building a connector. Most of the hard work is figuring out how to call webservices properly, rather than creating the connector itself.

Phew! I think that is enough for now. In the next and final post, we will test this connector to make sure it works and then add it to a sample PowerApp. Until then, thanks for reading…

 

Paul Culmsee

Background

In a recent project, I developed a portal for the Project Management function of a global multinational. One of the key design principles we took into the engagement was not to simply make an electronic version of a Project Management manual. Instead, the solution had to be a source of new and timely information that would keep people coming back, as well as actively contributing.

One of the many things done to achieve this goal was to create a PowerApp to compliment the portal. This app, among other things, allows users to submit project tips, lessons learnt, participate in pulse surveys, and receive notifications when particular topics (for example risk management or project controls) have new or updated information. A key feature of the app is an activity feed showing the latest information across the portal. The basic idea is shown in the wireframe below, where under the main navigation represented by the round icons, users can view and click on any new activity that interests them. Activity reports include new or updated content, newly submitted tips, photos, and lessons learnt.

The data for this application is stored in SharePoint, so activity occurs across multiple lists/libraries. Therefore to produce an activity feed, PowerApps needs to talk to SharePoint search. Search, via the concept of a Result Source, allows us to specify exactly what lists and libraries are used in search results. This is important because we also want to exclude some SharePoint content from the feed. After all, if you maintain a list that is used to store and manage configuration data, it’s unlikely that you want blast out changes to that list to all users via an activity feed.

Result Sources also allow us to specify the ranking model on the results, which for an activity feed is usually based on date modified (i.e. recency rather than relevance).

Now I can’t show you the actual solution for confidentiality reasons, but luckily for all of us, my cat-obsessed daughter Ashlee (of fidget spinner fame), created a PowerApp called the CAT-alogue which is shown below. So in this post, I will explain the approach to the activity feed using the CAT-alogue via a feature called the (ahem) “Mewsfeed”…

The Solution

Below is a diagram of the approach I ended up taking.

As you can see, the main components are:

• A custom SharePoint Search Result Source
• An Azure function (leveraging PnP PowerShell)
• A PowerApps custom connector

Now I should say this was not my original approach. I started off assuming that I’d query SharePoint search via Flow, using HTTP actions to talk to the SharePoint Search REST webservice. This approach has worked well for me when I needed to leverage unsupported SharePoint functions in PowerApps or Flow. But there was one major issue that was search specific. That is, when you talk to SharePoint REST API using Flow, you need to register and specify an App Principal for authentication. Now this is supported in SharePoint online search, but has an implication. There is no security trimming of search results. Quoting from the referenced article…

When you are using SharePoint Online search APIs with app-only permissions, you will need to request full permissions to the tenant when you are registering the add-in/app for the tenant. This will grant needed permissions to query information from the Office 365 tenant without security trimming applied.

For some scenarios this might be fine, but in my case, the idea of an app being able to query any/all SharePoint content in the entire tenant was never going to fly. My alternate approach uses the more traditional technique of a dedicated user account to access SharePoint. Thus, if the account only has access to this site, we have the search security trimming we need.

Right! Lets get down to business then. I have broken this into 5 parts across a few articles:

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

By the way, don’t let the length of this series put you off. I have tried to explain things as best I can with liberal use of screenshots.

1. Setting up SharePoint Search

The first step is to make a SharePoint result source which specifies the content that will be viewed in an activity feed. In my developer tenant, my daughter’s CAT-alogue app uses a list called Cat Directory, a list called CatImageRegister and a library called CatImages. If any activity happens in any of these lists, we want to see it on her app.

I decided to create a Search result source, using the List IDs as the filter. Note that this is not the only approach to take for this result source, but will suffice for my needs. To get the IDs of the lists in question, I used PnP PowerShell. After connecting to my tenant, I used the Get-PnPList command to find all lists with the word “Cat” in their Title like so…

PS C:\Users\paulc> get-pnplist | Where Title -Like "*Cat*"

Title                               Id                                   Url
 -----                               --                                   ---
 Cat Directory                       2b4ee9b8-714e-464d-a8ae-ab379776c826 /Lists/Cat Directory
 CatImageRegister                    11d474d0-5725-4ec5-b273-8bb09a3e097e /Lists/CatImageRegister
 CatImages                           c5b56dac-b7f1-4519-b7e6-ac688ac158dc /CatImages
 Notification List                   3a649e3a-6473-4f73-b856-31740368369c /Notification Pages
 Suggested Content Browser Locations e6e70c65-096b-4733-ad03-c86e25708f05 /PublishedLinks

I strongly suggest you use this method to get to know the awesomeness that is PnP PowerShell if you are new to it. But for the holdouts who think PowerShell is for developers, if you want to get the list ID’s via the SharePoint UI, you just need to go to List Settings and check the URL as shown below.

Once you have assembled your list ID’s, you can create a result source. Go to Site Settings > Site Collection Administration > Search Result Sources and choose to create a New Result Source. Give your result source a name. Ashlee made me call it “Mewsfeed” – honest!

 

Scroll down and find the Launch Query Builder button. From the Property Filter dropdown, choose –Show all managed properties– and find the ListID property. In the property filter, choose Contains and choose Manual Value from the Select Value dropdown. Specify the ID of one of the lists as shown below:

Repeat these steps for each list ID and click the Test query button to validate you are getting results.

At this point, if you look closely at the search results, you will likely see items that should not be included in an activity feed. In the picture below I’ve demonstrated this by highlighting some results I’d rather not see. To address this, let’s refine the result source to only bring back list items, rather than the list views themselves. From the Property Filter dropdown, choose contentclass property. In the property filter, choose Equals and choose Manual Value from the Select Value dropdown. Set the value to “STS_ListItem_GenericList” as shown below:

Now re-test your results. You should see that the unwanted pages are now excluded from results. Go ahead and save your result source with an appropriate name.

2. Querying the search index via a PnP PowerShell script

Now that we have our result source, we need to write a PowerShell script to query it. First up, we need to get the ID of the result source we just created because it is needed by the PowerShell cmdlet we are going to use. To do so, select the result source and grab the ID from the URL as highlighted below…

The ID has been encoded, so the best step is to use an online decoder to get the ID in the right format. Bing has one built in so just paste the ID into it and click Decode.

Next step is the script which, thanks to the PowerShell PnP project, is really simple since PnP includes a dedicated command that we can use, namely Submit-PnPSearchQuery. If you are using PowerShell interactively, a search can be performed in a couple of lines: E.g.

1. Connect-PnPonline https://culmsee.sharepoint.com
 2. $result = Submit-PnPSearchQuery -Query "*" -MaxResults 20 -SourceId "0bfa50f5-041d-40af-a6e1-8b01124eca69" -SortList @{"LastModifiedTime" = "Descending"} -RelevantResults
 3. $output = $result | ConvertTo-Json

The first command connects to the tenant and will prompt for credentials.

The second command performs a wilcard search (-Query “*”), returns 20 results (-MaxResults 20) and specifies the result source set up earlier (-SourceId “0bfa50f5-041d-40af-a6e1-8b01124eca69” ). We sort the results via recency (-SortList @{“LastModifiedTime” = “Descending”}  and instruct the command to strip out extra detail and bring back just the results (–RelevantResults).

The third command converts the output to Json format and stores it in the $output variable, which is important because this is what we will send back to PowerApps.

To test that this is working as expected, I compared the results of a more simplified search. In the example below I have searched for the term “Jessica” – the name of one of Ashlee’s cats. First, I tested via the Result Source in SharePoint by returning to the query builder, navigating to the Test tab and clicking Show more…

I entered “Jessica” into the search term and found 11 relevant results…

Now in PowerShell we see the same search for “Jessica” and take a peek at the Json. Sure enough, we have the same 11 results as the Result Source test above.

So now that we have confirmed we are executing the correct search, we need to go and make ourselves an Azure function to run the script. We will do this in part 2…

 

Thanks for reading…

 

Paul Culmsee

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

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

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

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

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

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

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

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

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

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

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

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

So let’s look at the Swagger File…

Note 2: Host, basePath and Path

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

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

Note: The image below shows the port number shown (443), this no longer works so omit it altogether as shown in my 2 examples above.

From this…

To this…

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

To this..

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

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

Now for reference, each parameter section has:

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

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

Note 4: Update the sig parameter…

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

  

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

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

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

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

Note 6: Set your function call names appropriately…

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

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

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

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

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

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

Parting notes…

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

 

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

 

Thanks for reading

Paul Culmsee

Hiya

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

How to Save Photos from PowerApps to SharePoint via Flow

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

 

Calling Cognitive Services Vision API from PowerApps via Flow

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

 

How to set SharePoint list permissions using Flow

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

 

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

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

 

How To Rickroll Your Friends Using PowerApps

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

 

Paul and Ashlee on PowerApps

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

  

 

and finally….the famous fidget spinner…

Build a FidgetSpinner using PowerApps

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

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

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

Paul Culmsee

Hi and welcome to the final post in this series of articles that examined how we can work around an unsupported function in PowerApps and Flow. Along the journey we have learnt about app principals, the SharePoint REST API and written some crazy JSON.

In this final post, we will tie up a few loose ends. Currently the flow hard-codes the term ID from managed metadata when in fact, we want it to be sent from PowerApps. I will also also give you some insight into handling more complex situations, like multiple managed metadata fields as opposed to a single one.

But first, the (giant) weak spot…

As I write this I if any of you, as you read the first 3 posts, realised the weakness to this method. A strong hint was given in the last post. Recall that the JSON to update a managed metadata field required a term GUID…

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

Now right now in PowerApps, there is no managed metadata control. This means you cannot browse a term store like you can in SharePoint. Among other things, one likely reason for this is that there is no REST API support for managed metadata. This has been a constant source of frustration for many as you can see in the 761 votes (as I type this) in uservoice.

What’s the implication of this limitation? For a start, there is no way we can build a REST call that will return us all of the terms in a term set. We have to either hard-code the term GUID’s in our application (ugh) or find another way to get the list of terms (ugh again). One thing that should be noted is that sometimes, hard coding terms and GUID’s isn’t as bad as it sounds. For example, you might have a term set for your corporate offices in certain countries. Since those terms are unlikely to change quickly, one can embed their GUID’s into PowerApps without too much concern.

For the first part of this article, we will use this approach and hard-code our term set of coffee beans into our Powerapp. Then we will modify our flow to expect this parameter from PowerApps.

Back to PowerApps…

If you go back to the PowerApp we built way back in part 1 and also the term set of coffee beans, they looked like this:

 

To hardcode our terms into PowerApps, I am going to use the ClearCollect function to create a collection called BeanDetails. I have grabbed the term label and GUID from managed metadata and constructed this function:

ClearCollect(BeanDetails,
{
   Name:  "Fiori",
   Id:  "96a085d2-ed7d-4a75-8d63-c6ca56d0b358"
},
{
   Name:  "Jungle Coffee",
   Id:  "eb04988d-acc4-4bde-9861-506e90de94c5"
},
{
   Name:  "Alfredo",
   Id:  "38db966f-3b0c-4872-a7d3-982ecd8528dd"
},
{
   Name:  "Death Wish Coffee",
   Id:  "e33c65f9-4202-40d7-89f2-aa1a1016ad9f"
})

Next, I use the OnStart property of the screen and paste the ClearCollect function as shown below.

Now you will need to save, close and reopen the app for this to take effect. Alternatively add a temporary button and use the above code on the OnSelect property. Either way, you should be able to see your terms and GUID’s when going to Collections from the File menu…

Now that we have our term set stored in PowerApps, let’s add a dropdown list to the screen that lists our coffee beans. Choose Insert > Drop Down and set its Items property to BeanDetails.

Now you should be able to see the choice of beans in the dropdown as shown below:

Next, we have to go back to Flow and modify it to accept a term GUID as a parameter (if you recall in part 3 the term is currently hard-coded).

Tweaking Flow…

If you have followed our Flow adventure so far, you should have a flow action called Set Test Term GUID that looks something like this:

First, rename this flow step to Get Term GUID and then delete the GUID that we previously had in the Value field. From the Dynamic Content pane, choose Ask in PowerApps.

Update your flow and before returning to PowerApps, click the Run Now button. Ensure you are now prompted for three parameters, rather than the original two.

Back to PowerApps…

Now in PowerApps, we have to delete and flow and re-add it so it finds the new parameter we just added.

Select the Submit button and review the OnSelect property. It should show the function below…

Now go to View > Data Sources and remove the NewCoffeePlace data source. You should see an error appear on the Submit button. It will say “Invocation of unknown or unsupported function”. Makes sense right? After all, we just took away its connection to flow.

  

Once again make sure your Submit button is selected, and select Action > Flows. Choose the flow called New Coffee Place. In a few moments, it will ask you to complete the parameters in the formula bar. If you did this correctly, you should see a prompt for a third parameter – the term GUID.  

 

The third parameter will be the GUID that matches the term in the dropdown list. The formula for this is:

Now to explain Dropdown1.Selected.Id, remember that we set the Items property of the dropdown to BeanDetails collection, which has two columns, ID and Name. Essentially, by specifying Dropdown1.Selected.Id, whichever bean name is the currently selected one in the dropdown, the GUID will be sent to Flow…

Test this by putting the Powerapp into preview and adding a new café. If all things go to plan, you will see your new entry along with the managed metadata term. Yay!!

 

Hard-coding sucks – surely there a better way?

At this point you might still be hating the fact we had to hard-code the term ID’s into our app. I can sympathise, as it irritates me too. So is there a way to dynamically load the term set into PowerApps? Turns out there is, provided you use an Azure function along with PnP PowerShell.

I won’t cover this in detail here, unless there is sufficient demand, but I will explain the high level overview.

1. Set up an Azure function using PowerShell
2. Load PnP PowerShell modules
3. Write a PowerShell script to connect to your tenant and pull the terms from the term set.
4. Convert the terms to JSON output
5. Create a custom connector for PowerApps to consume it.
6. Add the custom connector to your app, and pull down the latest term set on startup.

Now I wrote another blog post where I went through another example of using Azure functions with PowerApps, and that covers a lot of detail on steps 1, 2, 5 and 6.

In relation to step 3 and 4, here is some PowerShell code sample to do this that you can use as a starting point. Be warned though… you will need to have some understanding of PnP PowerShell to make sense of it!


Import-Module "D:\home\site\wwwroot\modules\2.19.1710.1\SharePointPnPPowerShellOnline.psd1" -Global
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
$name = $requestBody.name
$username = "paul@culmsee.onmicrosoft.com"
$password = $env:PW;
$siteUrl = "https://culmsee.sharepoint.com"
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd)
Connect-PnPOnline -url $siteUrl -Credentials $creds
$tg = Get-PnPTermGroup -Identity 2830778b-dbfa-4441-bb45-0cbb97b582e7
$ts = Get-PnPTermSet -TermGroup $tg -Identity "Coffee"
$tl = Get-PnPTerm -TermSet $ts -TermGroup $tg -Includes Terms
foreach ($term in $tl) {
   $terms+=$term.terms
}
$output = $terms | Select Name, Id | ConvertTo-Json
Out-File -Encoding Ascii -FilePath $res -inputObject $output


Like I said, if you are interested in knowing more about this, let me know and if there is sufficient demand, I will make a dedicated blog post about it…

Handling multiple managed metadata columns…

I will end this series by showing you how to handle updating multiple managed metadata columns. This is an advanced topic, but is a good test for developing JSON manipulation skills in Flow. Like the previous section I will cover it in a high level only and leave it to you to figure it out for your environment…

So far we have updated a single column, but my real-world project that led to this series actually had 3 columns that were managed metadata. What made this interesting and tricky was that these columns were entirely optional. The complexity is easy to illustrate by looking at what a JSON payload looks like when updating multiple columns. In the example below, I am updating 3 columns called ProjectType, ProjectPhase and ProjectElement respectively…

{
   "__metadata": {
     "type": "SP.Data.ProjectListItem"
     },
   "ProjectType": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "d4e4494f-34a4-4278-b0ee-7eba372a3f80",
     "WssId": "-1"
   },
   "ProjectPhase": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "78d647c7-8b92-4ee0-a29d-22346b1af560",
     "WssId": "-1"
   },
   "ProjectElement": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
   },
     "TermGuid": "6f9f653f-09ec-4771-9063-676d16734b37",
     "WssId": "-1"
   }
}

Contrast the above example with the one below where only one is updated…

{
   "__metadata": {
     "type": "SP.Data.ProjectListItem"
   },
   "ProjectElement": {
     "__metadata": {
     "type": "SP.Taxonomy.TaxonomyFieldValue"
     },
     "TermGuid": "6f9f653f-09ec-4771-9063-676d16734b37",
     "WssId": "-1"
   }
}

So the trick is to build a flexible enough flow that could generate the JSON needed to update 1, 2 or 3 columns. Without knowing whether the column would have a supplied value, meant we have to find a way to handle any combination. The pattern I ended up using was to build the JSON for each of the columns individually before joining them together. Here’s how…

First, I used an Initialize Variable flow action to create the base JSON for each column. The key was to make this conditional, so if the column value was empty, I added an empty string. E.g.

The expression used for the value of this variable was this crazy looking Workflow Definition Language Expression:

if(not(empty(triggerBody()['GetProjectTypeTermID_Value'])), concat('"ProjectType":{"__metadata":{"type":"SP.Taxonomy.TaxonomyFieldValue"},"TermGuid":"',triggerBody()['GetProjectTypeTermID_Value'],'","WssId":"-1"}'),'')

Basically this grabs the term ID for a column (in the above example the columns is called ProjectType). I test if the column value is supplied – which is what the “if(not(empty” bit does. I then then construct the necessary JSON to update that managed metadata column. (Note if you need a refresher on the JSON in this function, review part 3)

I repeat this action for the other columns, and then perform the following steps in Flow to construct the complete JSON…

First I initialize an array variable that I call JsonPayloadArray.

Next I add the outer JSON for a managed metadata update to the array using the Variables – Append to array variable workflow task…

Here I am using the concat() function because I need to use the ListItemEntity variable. Note, if you have forgotten about ListItemEntryFullName property,  re-read part 3.

concat('{ "__metadata": { "type": "',variables('ListItemEntity'),'" }')

Next, I add the JSON for each column to the array, provided it is not null. I do this by adding a condition for each column.

The first image below shows the three conditions and the second image shows an expanded view of one of them. You can see in the example below, if a ProjectType was supplied, I add the variable initialized in the above steps to the JsonPayloadArray variable….

Finally, I use the Join() function to take this array and turn it back into a single string, delimited by commas. This elegantly handles the situation in JSON where the last name/value pair does not have a comma separator.  I do this by initializing a new variable called JsonPayload as a string as follows…

concat(join(variables('JsonPayLoadArray'),','),' } ')

Conclusion

This is one of those series that I know only a few people will get some use out of. Hopefully this whole issue of managed metadata will be gone by year-end and these approaches are not necessary. In any event the techniques outlined in these four posts apply to various other scenarios, so learning about JSON manipulation using Flow, or talking to SharePoint via REST/App principals might come in handy for you in other situations.

As for me, this is probably the geekiest series I have written in a long time! I think I better get back to management consulting now!!

 

Thanks for reading

Paul Culmsee

www.hereticsguidebooks.com

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