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

Send to Kindle

Hi

Some time back I documented an approach to updating document library metadata using Flow. To recap, the issue was that the “SharePoint : Create File” action provided no way to update library metadata when uploading a file, nor did it return the item ID of the newly created file. Since I wrote that post, Microsoft has updated things. First, the create file action now does return an ID, and some new Flow actions have been released, including one called “SharePoint – Update File Properties”.

This now makes the whole metadata situation much less sucky…

Check out the image below. In it, I have used a PowerApps trigger, and added a Create File action, and followed it up with an Update File Properties action. Notice that when I click the ID textbox in the Update File Properties action, I now have ItemID available from the Create File action. Much simpler than before!

image

Hope this helps someone…

Paul Culmsee

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

No Tags

Send to Kindle

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

Send to Kindle

Hiya

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

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

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

image  image

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

image

So what’s going on here?

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

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

Flow to the rescue…

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

image

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

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

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

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

Going back to my original PDF it becomes:

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

So let’s test in PowerApps… wohoo!

image

Conclusion and caution…

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

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

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

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

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

Hope this helps you and thanks for reading…

 

Paul Culmsee

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

No Tags

Send to Kindle

A lesser-known, but really important update to PowerApps

Send to Kindle

This week, the PowerApps team released a great update to PowerApps that should make any InfoPath person very happy. PowerApps now supports the use of rules that allow you to easily modify the behaviour of an app based on criteria that you specify. Previously this had to be done using variables and was one of those aspects to PowerApps that felt “developery”.

This is a great new feature and I will record some videos on this in the not-too-distant future…

But there is another update to PowerApps that seems to have slipped under the radar. This one is really huge for anybody who wants to leverage rich media in SharePoint…Up until now, there was a problem with authenticated connections when using controls like the Image or PDF controls. Consider this list:

image

In this example, we have a Title column and a hyperlink column called CatPhoto. Up until recently, if you were to try and display this content in PowerApps, only anonymous URL’s would work. In the example above, the first 3 cat photos are in a SharePoint document library called Pics, and the resulting URL is an internal one (eg: https://culmsee.sharepoint.com/Pics/cat1.jpg). The 4th image in the list above is Ralph, who apart from being a dog, is an external image – eg: https://i.ytimg.com/vi/SfLV8hD7zX4/maxresdefault.jpg.

Previously, if you pulled this list into PowerApps and tried to display this in a gallery, you would only see Ralph. PowerApps would try and access the SharePoint URL’s without credentials and fail.

<Edit: If your original SharePoint column storing the URL is set to display URL and not picture, the issue remains!! If you are affected by this you can try my Floxy method>

But now if we pull the above list into PowerApps we now have awesomeness!!!

1. Add a SharePoint data source and connect to the list of cats

image

2. Add a horizontal gallery and set the data source to SharePoint.

image  image

3. Sit back and enjoy the awesomeness!

image

Now how did the PowerApps team pull this off? The easiest way to tell is to add a label to the gallery and have a look at the CatPhoto column. Previously it was the URL of the image as it is stored in SharePoint, but now it is very different…

Looking at the above image, Ralph’s URL still looks normal, but the SharePoint URL’s have been rewritten. Passing through a decoder and we can start to ascertain what’s going on..

appres://datasources/Cats/rows/1/reference/https://australia-001.azure-apim.net/apim/sharepointonline/[snip a GUID]/datasets/https://culmsee.sharepoint.com/GetFileContentByPath?path=/Pics/cat1.jpg

The closest thing to this URL format I have been before in PowerApps is when you upload media. It also uses the appres:// URL prefix too.

My guess of the above is:

Application Resources (appres://) –> Data Source (datasources/Cats/Rows/1) –> Datasource webservice endpoint (reference/https://australia-001.azure-apim.net/apim/sharepointonline/[snip a GUID) –> data source parameters (https://culmsee.sharepoint.com/GetFileContentByPath?path=/Pics/cat1.jpg).

Now I experimented by manually changing the item in the path parameter for the GetFileContentByPath call. This worked fine… I was able to specify a PDF in the Shared Documents library and set that as the content source for the PDF control as shown below…

image

Conclusion

This is a brilliant enhancement to PowerApps and will open up many use-cases, given that SharePoint is the de-facto content store for most organisations. Furthermore, if you utilise my technique of using Flow to facilitate photo uploads into SharePoint, now we have closed the loop. We can take and upload photos, and subsequently browse, tag or mark them up.

Watch this space for some demo apps that take advantage of this new functionality!

Paul

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

No Tags

Send to Kindle

Incredible! Download a PowerApp that determines your IQ from a selfie…

Send to Kindle

Hiya

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

image

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

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

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

 

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

 

Ready… Steady…

 

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

 

Right are you ready?

 

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

 

Okay, so do the following right now!

 

1. Download and install PowerApps studio onto your PC.

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

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

image

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

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

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

7. Enter your name when prompted…

image

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

image

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

image  image

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

 

Make sure you let me know what your IQ is! Smile

 

Paul Culmsee

www.hereticsguidebooks.com

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

No Tags

Send to Kindle

How My Daughter Won a PowerApps Contest With a Fidget Spinner

Send to Kindle

Hi all

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

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

image

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

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

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

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

Spinner

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

image

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

image

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

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

imageimage  image  image   image

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

Thanks for reading

Paul Culmsee

 

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

No Tags

Send to Kindle

Advanced PowerApps Awesomeness in Sydney

Send to Kindle

Logo The Digital Workplace Conference Australia

Hiya

First up, if you are not aware, the Digital Workplace Conference is about to land in Sydney. I strongly recommend you attend this conference. In my opinion there has never been a more fascinating time to be involved in the world of Office365/SharePoint and Azure and some top people are going to be speaking.

I have spent the better part of the last two years working with cloud first organisations, and frankly I’m amazed at what is available in the base edition of Office365. I suspect many organisations are not particularly aware of the capabilities available to be exploited – which brings me onto my topic.

In case you are not aware, I am a bit of a fan of PowerApps. I see it having huge potential for citizen developers and more technically-minded folk alike. Most recently I have been teaching it to my daughter, who at 18 years of age, is busy studying her first year of psychology at university. For someone with no formal programming experience, she has come a long way, as this video demonstrates. In this video, we cover a real-world scenario and make use of a lot of tricks to get a nice, solid solution.

So why am I telling you this? Well I am presenting at the Digital Workplace Conference in Sydney this month, and my session is called “A Certificate in Advanced PowerApps Awesomeness” and I will be covering some of the tricks behind what we did in this video, as well as a couple of other apps that Ashlee wrote. I will be sharing various pearls of wisdom from Ashlee, who presents a fun perspective on Microsoft products. Ever seen those “kids react to?” videos on YouTube?  Well, watching Ashlee work on PowerApps is kind of like that Smile

Therefore I will share some Ashlee-penned pearls of 18 year old wisdom such as…

“Do not put spaces in the name of your SharePoint libraries, because PowerApps can’t handle it and will have a huge hissy fit”

So I hope to see you in Sydney, where I will delve into this beast known as PowerApps and show you what an 18 year old with no formal experience can do with the platform.

I’ll cover PowerApps deeper than the typical demos, but at the same time business-oriented folks shouldn’t be too off-put by it.

Hope to see you there!

 

Paul Culmsee

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

No Tags

Send to Kindle

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

Send to Kindle

Hi all

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

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

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

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

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

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

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

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

image

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

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

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

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

https://culmsee.sharepoint.com/_api/SP.APIHubConnector.GetListItems(listName=’Documents’,queryOptions=@q)?@q=%27%2524filter%3dFileLeafRef%2520eq%2520%252somefile.jpg%2527%26%25%27

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

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

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

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

image

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

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

Hope this helps sometime…

Paul Culmsee

www.hereticsguidebooks.com

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

No Tags

Send to Kindle