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

Introduction

Hi again

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

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

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

App prerequisites…

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

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

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

 

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

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

1. Photo and file name…

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

Collect(PictureList,{

        Photo: Camera1.Photo,

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

} )

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

2. Saving to Flow

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

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

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

Both of these functions are about to change…

Getting the weather…

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

 

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

     

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

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

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

     Photo: Camera1.Photo,

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

     Latitude:Location.Latitude,

     Longitude:Location.Longitude,

     Temp:Weather.main.temp } )

 

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

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

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

 

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

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

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

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

Getting ready for Flow…

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

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

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

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

 

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

I lied…

Going with the Flow…

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

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

The image below illustrates the basic layout.

Our task is to now modify this workflow to:

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

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

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

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

So my temporary “clever” method is to:

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

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

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

   

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

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

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

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

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

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

This will result in the following:

 

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

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

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

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

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

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

and so on…

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

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

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

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

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

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

[ { “ID”: 48 } ]

to

{ “ID”:48 }

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

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

{

    “ID”:48

}

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

 

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

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

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

Now save your flow and cross all fingers and toes…

Testing and conclusion!

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

  

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

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

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

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

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

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

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

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

 

Thanks for reading

 

Paul Culmsee

www.hereticsguidebooks.com

Introduction

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

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

 

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

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

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

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

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

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

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

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

 

{"coord":{

   "lon":115.86,

   "lat":-31.95},

 "weather":[{

   "id":804,

   "main":"Clouds",

   "description":"overcast clouds",

   "icon":"04n"}],

 "base":"stations",

 "main":{

   "temp":5.37,

   "pressure":1023,

   "humidity":100,

   "temp_min":5,

   "temp_max":6},

 "visibility":7000,

 "wind":{

   "speed":1.5,

   "deg":50},

 "clouds":{

   "all":90},

 "dt":1499297700,

 "sys":{

   "type":1,

   "id":8189,

   "message":0.0089,

   "country":"AU",

   "sunrise":1499296640,

   "sunset":1499333125},

"id":2066756,

"name":"Maylands",

"cod":200}

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

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

Generating an initial OpenAPI file

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

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

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

Step 1: Run your API

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

Step 2: Enter API Program Information

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

Step 3: Update API Call Information

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

Step 4: Update header info

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

Step 5: Update Query Parameters

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

Step 6: API Path Info

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

Step 7: Save your goodness!

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

Tweaking your initial OpenAPI file

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

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

 

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

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

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

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

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

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

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

 

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

  \

Finally, complete the operation by clicking Create connector.

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

For the record, it will say:

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

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

Fixing the OpenAPI file…

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

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

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

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

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

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

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

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

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

 

 

Until then, thanks for reading

 

Paul Culmsee

www.hereticsguidebooks.com