How to use Power Automate Get Files (Properties only)

foreign [Music] let's get back to Power automate one of the typical controls that lots of people use it yet struggle with different features that they are available in it is called get file properties only so this is the action inside power automate that is supposed to go inside SharePoint libraries and look for a file try to find it and give us the properties of the file based on our specific search criteria it has the ability to search inside folders or search the entire library in this video we want to see how it works and how we can effectively use it in our foreplus let's get into it without wasting any time as usual we start with our setup for this video I created a library inside SharePoint and I called it file search I have a few folders and files inside it all the files are text files and they are playing simple folders I also added another field called category in case we want to filter it based on the existing property this is the summary of what we have so at root level I have file 1 inside root I have folder one and folder two inside folder one I have file two and file three inside folder two I have file 4 and under folder two I also have another folder called folder tree with two more files these files are purely text files I put a dummy content inside them like File 2 content for file 2.txt in this video I'm not going to get into the content I put it here in case you want to see how to handle the content of the files I may discuss it in a different video at the moment don't worry about what is inside these files now let's see get files basic functionality to demo that I created a flow with manually trigger flow trigger and under that I just add SharePoint and I want to use get files to see how this guy works properties only I need the URL of the site and I come back here and I pick on custom and I just paste the URL here Bingo got it I'm happy the library name I just need to pick it up and I pick file search which is the library that we have here that's it I can simply save it at this stage and right after that I can put a compose and see what it returns so whatever that comes from get files as output or value I click on it and I put it inside compose and I save it this step is important so I click on test and I run it run done and bingo I have some weird stuff inside this outputs doesn't look very pleasant but I know how to handle it I can copy all of it and I can paste it inside Json beautifier you can go to code beautify.org Json viewer and I paste this guy here you see how weird it looks it doesn't really matter at the moment we have lots of additional fields that we don't need them so I'd rather get rid of them so that I can see the results better and we keep the focus on the topic rather than lots of unnecessary data that we don't need them for this video regardless let's see what we need to get rid of inside power automate if I click on edit I have an action called select select can convert one format of the array inside power automate to another one I've covered all these things in my course on udemy called master power automate expressions in two hours if you have taken that course this is piece of cake for you if not that's one of those courses that everybody must take and understand how to work with these Expressions regardless this topic is completely covered there from the basics I really don't want to get sidetracked with that so I quickly just added select select says whatever the array that is coming from the other side if you look here the Json that starts with square bracket means it's an array so whatever that comes as the output from the sky which is the value I can put it inside select and I can convert it to something very simple how we do that I need the file name see where we can find the file name inside this Json if you scroll down you will see we have something called name and we also have file name with extension so let me just copy this filing with extension because I need this field and I can bring it here inside power automate and I say instead of file name in my new array put the value coming from the expression for the current item and give me the column that the title is file name with extension don't forget this curly brackets and this extra space is going to give us some hard time regardless now we are good I can click on OK so we have the file name with extension so let's run it and see what we get out of it and test save and test run flow done so now if I click on select the output contains folder 1 folder 2 folder three hold on a second it was supposed to give us files but it also included folders but let's be fair this poor guy also returned the file names so file 3 4 5 6 fantastic so it gave us all the files and all the folders regardless how can we differentiate files and folders if I go back to my Json again you will see there is another property called is folder I can also get this one and I can bring it here in my summary so let me click on edit and I can say is folder and here I do exactly the same thing that I did before so I click on expression I pick the current item square bracket in front of it single code and just paste this guy here now if I click on OK and I run this test again save and test front flow done and now if I click on select you will see I have a list of file names which can be folder and also it tells you if it is a folder so is folder true and is folder false for the files fantastic and if you only want to see the files I can click on here I can add a filter to filter out the folders so I can use filter array the array that select returns can come here so I can pick the output of the select click edit the value that I want to verify is going to be the expression item dot is folder and I click on OK and if you're thinking where this is folder is coming from it's coming from here we want to filter it for everything that this folder is equal to expression false click OK and we are good I click on Save and now we can test it manually test and I click on run flow done we are good so if I click on filter array now whatever that it returns it only includes the files and this is the basic functionality of the get files that you can get inside power automate now let's take it one step further so far we learned that when we are using get files it returns all the files and folders inside a SharePoint Library while almost not all of them in reality it returns only 100 items nothing more if you just use the default cases what that comes from is a default SharePoint page size which is 100 so if there are thousand files and folders in a SharePoint Library it only returns 100. how can you increase this if there are more files and folders that you want to bring them inside your power automate and work with them this setting is here so when we are using get files properties only if you click on here and you click on settings there is something called pagination by default pagination is off but you can set it on and you can set any number above 100 so let's say 1000 all right put the ten thousand who cares and I can simply say done we are good so in this case it picks up 2 000 files and folders inside one SharePoint library and brings them inside power domain but what is the max you can have millions of files inside the SharePoint library inside different folders and different layers but is there a Max here yes there is you cannot use more than 100 000 items to come inside powerapps and process that I can click on done hundred thousand works but let's go beyond that and I say one and here you see the threshold that you can use so you are bound to hundred thousand Max items if you want to bring all those items inside power automate now the reality is that there might be some cases that you have a lot more than 100 000 but in one shot you really don't need all of them you just need to have a certain number of files to bring them inside power automate and work with them now we don't really care how many files are there inside SharePoint I would say okay for us even if we have 1000 files inside our power automate it's more than enough because that's the maximum number of files that we may want to use in one shot inside powerapps so there should be a way that we can filter the files that are coming from SharePoint before we retrieve them and bring them inside power automate and this is our next topic now among all the properties of the get files action filter query does exactly what we need to do it filters the items inside SharePoint library before the actually come inside power automate so this is our Silver Bullet but before we get into it there are two topics that I need to cover first we need to figure out what the internal name or static name of every column is that we want to filter it's not as easy as you may think because SharePoint is hiding it to make it more and more user friendly yeah you heard it right I said user friendly not developer friendly let's start with the first thing that we need which is called SharePoint client browser SharePoint client browser is a tool that you can download it from this link and if you feel too lazy to type it in here is your QR code you can simply go to this link and download this guy unzip it and run it and when you run it you see a screen like this now you need to connect it to your SharePoint site so let me know just right click and I click on ADD site I go back inside SharePoint the site collection that I want to connect to I just right click on the site title copy link address and just paste it here and I click OK it may ask you for the username and password if this is the first time that you connect just provide the right username and password and you are inside it and you're connected to the site that you want to examine inside this site if I just expand it you will see lists I'm just looking for the library that I want to filter the library that I'm looking for is file search I just expand it inside this Library there are items I just need to pick one item as sample to figure out who's who so I pick file one for example and if I expand file one you will see there is something called field value this is exactly what you're looking for let me just expand it and see what we have here now inside SharePoint we have two fields that we can possibly search there is a lot more but at least these are the two things that we see right at our face so one is category if I come back here I can scroll down and search for category bingo I found it so you will see the key is category and the value is training and this is for file one so everything matches we are good that means we know that not only the title but also the internal name for this category field is category the obvious but when it comes to the file name it's slightly different let me scroll down and you may need to go through them one by one and see which one is the right field that you want to filter but a property that actually contains the file name is called filed leave reference this one contains the file name and if you want to write your filter query you have to use this name for it file leave ref remember that so now that we know the names that we can use to filter we need to learn how to write the query to do that we have all data filters here's a list of valid all data filters but you can get a complete list from this URL even if you just Google odata filter cheat sheet you will find this but it tells you the operation and how to write the filter with these two we have enough ammo to fight the battle I go back here and I expand get files I don't want to look into any specific folder so I just pick file search which basically means nothing or you can simply remove it and keep it blank so it searches the entire Library I want to include the nested items because I want to search everywhere I can Simply Save it and it's time to write our filter quick let's say we want to search for file one to do that I want to use the equal filter for file leave reference so I can say where the fire leaf reference is equal to single code for the string and I want to look for file 2.txt and we already know that file 2 exists but it's not in the root folder so when we start from the root folder it should find it and bring it to us great so let's test it save and test run flow and done I click on filter array and you will see it found file 2.txt and that's it nothing more than that and you can do exactly the same thing for category so instead of file Leaf ref I can use category and I can say the category is equal to for example Trading save and test the sky test manually run flow done and if this time I expand the filter array you will see only file one features the training and if you want to look for something else a little bit deeper for example here let's say management I think we have more than one file here so let's go back here and instead of training let me change it to management and I can test it this time save and test gruntlow done and this time I will have two files file 4 and file 6 that are in the management category so we learned the filter query great filter query was good but maybe it was not as important unless you are working with large number of files because you can always bring all of them you select and use filter after that and filter out whatever that you don't want but this one is a lot more important because power automate does not support sort if you try to locate some sort actions you'll find a ton of third-party options that you have to pay for every single one of them extra premium subscription fee but hey SharePoint can sort SharePoint can sort why do we even bother we ask the SharePoint to sort the items before SharePoint delivers it to us and that comes for free great let's do it keep that in mind that this is O data sort so you cannot simply use the regular title that you see for the field you have to use the internal or static names again coming from here from SharePoint online client browser and let's get the files sorted by file name which is fire leaf ref I just get a copy of it I bring it back here and I say order by file name I really don't want to use the query here let's just bring as many items as we can here and I just say test save and test run flow done see what we got here if I see the results of the filter array you will see file one two three four five six it seems like it's the same order that we put them in so does it really do anything let's give it another shot instead of sorting it in regular order let's sort it in reverse order which is going to be descending again test save and test run flow done and let's see the result now it is sorted from 6 5 4 3 and it's reverse sorted you can sort it by category or whatever the field that you want keep that in mind that your only option if you don't want to go for third parties to sort items that you're coming from SharePoint list or library in this case library because we are dealing with files let me go to the last part which is limit The Columns by view they typically say we keep the best for last and I did exactly the opposite so basically this is the most useless part of this video so if you don't watch the rest you're not missing anything just like subscribe and get out of here now if you're still here limit columns by view I was expecting that we can pick up a view and it only shows me the clean list of those columns as a result of get files inside SharePoint we have views at the moment we are looking at the all documents view which is the default view I also created another view called all files all files is the same thing but I only have the name so we have the folder names and the file names I can still go inside a folder but because it doesn't have the icon probably it doesn't look really good the main thing is that I took out the category now inside power automate if I go to the Limit columns by view I can click on this guy and I pick a different or specific view called all files I was hoping what I get out of this get files is going to be an array that only contains the columns that you are defined in this view which is the name so category is out of question what name should be there and when I run this test test and run done if I expand this one still I see this weird click to download and instead of nice and clean Json I see here of course category does not exist in this one anymore so it's excluded but the way I am a little bit disappointed regardless the main purpose of adding this limit columns by view is that when you have a SharePoint list with lots of columns so many that get files cannot handle you can create a view with some limited columns that that power automate can handle it that's it it doesn't do much I've never used it anytime I really wanted to pick a small set of columns I always use select and as you saw it does the trick and we're good with that and just like that we are done with this all right I hope you enjoyed this video if you did you know where the like button is and if you haven't subscribed yet I sincerely hope this is the day that you push that subscribe button thank you so much for watching and I'll see you soon in the next video foreign

Channel: Alireza Aliabadi

Views: 13,706

Keywords: power automate get files, Get Files (Properties only), Power automate tutorials, Power automate tips, Get files OData, Get files Filter, Get Files sort, Power automate get files sort, Power automate get files order by, Get files limit entries to folder, Get files power automate limit columns by view, Get files include nested items, Power automate get files advanced options, Power automate get files pagination, Power automate get files limitations, power automate get file

Id: IenTcgZFW2g

Length: 22min 56sec (1376 seconds)

Published: Tue May 09 2023