So the first thing we're going to do is create an Excel table so that we can import it and to check that you can follow along I'll do it from scratch so we'll choose to go into OneDrive and what I'm going to do in one drive after clicking on my files is create a new folder so click on add new and I'll call my new folder films and then click on that folder you can now upload a file so this file called top 10 films is one.
Of two which are attached to this video and you can get the web page giving the reference to the files from the notes for this video in YouTube so upload that but to be honest you don't really need it because any rectangular block of data would do you could pretty much make your own up your own as long as it has headings going across the top top of the columns and I guess at least a couple of rows of data so we've got there a table of data except we don't.
It's not a table if you click on the drop Arrow next to the range name box there's no tables listed and this matters because in power automate you can only ever get data from an Excel spreadsheet online if it's defined as a table so we need to create a table and probably the easiest way to do that is to click on any single seller within the table it doesn't matter which one click and insert and choose to create a table what it will then do is select the data so it says B2 to E12 and if I show you.
What B2 to E12 is you'll recognize the fact that it's pushed out to select the rectangular block of data boarded by blank rows and blank columns it does that automatically so what I can do is choose ok to confirm that and I've got my table my table is currently called table one the reason I know that is when I click on this drop list that's what it displays but you can change that by clicking on the table design tab at the top there going to your table name and calling it something more meaningful so I'm going to call mine films table I.
This so go to table design and instead of calling films table three if you call this other table quite was having the film's table two I don't know by the way so I've now got a spreadsheet containing a workbook containing two worksheets two tables we're good to go there's no need to save that indeed you can't so I could just shut it down and you can see my file has been created there top 10 films.
So what I'm going to do now is to try loading the data from Excel and displaying it in some useful format so I'll create a new flow it'll be an instant Cloud flow because I wanted to be able to run whenever I click on the button I'll call it load films from Excel choose to manually trigger a flow and click on create so I want to add a step and the step is going to be based on Excel online business there's three types of tables you can bring in you can bring in a little three types of list rather you can bring a.
List of all the worksheets you can bring a list of all the tables in an Excel workbook but the thing I'm most commonly going to want to do is to list the rows present in a table so that's what we're going to do now if you click on that we now need to answer a few questions we need to say where the location is it's OneDrive for business which the document library is it's OneDrive which the file is called so it's in the films folder and if that thing would move out of the way it's called top 10 films and finally which tables is it's.
In and it's called films table so now I can choose to save that and what I'm going to do now is to run this it's as I've said before it's a good practice to get into to see what is coming out from that so if I run this what I may need to do after clicking on run flow depending on how quickly this runs is to pause and come back to the video and indeed that's what I had to do there's been a five to ten second gap between me which I thought you'd prefer not to watch.
So if I click on click to download now you can see what's coming out from this it's Json as always and what I need to do as always is understand this as always you can forget about the header section that's irrelevant the body section begins with the word body and consists of two main properties one is called at data.context that looks to me like an internal field I have absolutely no interest in and so I'm going to disregard it the other property is called a values or.
- FULL VIDEO Design A Responsive Homepage Using HTML, CSS & JS - Live Blogger BlogSpot
- OneNote Tutorial
- Web Automation - How to import web data using VBA
Rather just value and that contains a number of sub properties giving the column names out - ID Card Make
Of those sub properties the first two I imagine I'm not going to be interested in but the last four give the rank the title the gross income for the film and the year of release and those I will be really interested in so again if you could remember that structure please because we're going to be using it in a second close that down and what I'm now going to do is come out of this and try selecting data.So I'm going to show presenting this data in a usable format using two different ways we're going to start with using the select command so I'm going to add a new step for the action I'll choose a select command which is one of the data operation commands and I'll choose to select data the place I'm going to get it from is the table of data now at this point I want to do it wrongly first what I want to do is choose value which will give me the array of all the items.
In the spreadsheet on the table but let's suppose I didn't do that and chose one of the individual columns I'll choose title what will happen is quite confusing it creates something called an apply to each Loop and what this will do is automatically Loop over all the rows in the table doing something for each film title that's definitely not what I want to do at the moment I'll cover apply to each Loops in a couple of tutorials time probably but for the moment I definitely don't want to show you this nor do I want to do it so what I'm going to do is delete that and try again and the reason.
I showed you this is because you're quite likely to do it yourself and it's frustrating when things go wrong and you have no idea why so we'll try that one again choose a select command again and this time instead of choosing an individual column I'll choose the entire set of rows there's almost no difference between value on body by the way we'll choose value so what I want to do now is pick up on two columns the First Column I want to pick up on is the title.
And I want to call that fill name except when I type in film name like so oh it does accept it I thought it wasn't going to accept the space on this occasion and what we're going to do for the second column is choose to pick up on the year and I'll call that release Here so those are my two columns I want to display and the column names I'll give them if you let your mouse linger over one of these columns you'll see it contains an expression which says from that.
Particular row pick up the field called title or the field called here so now what I can do is try testing that if I try running this when it's saved and then if I look at the output from this you can see that if you look at the raw outputs you can see I've got exactly what I wanted I've got an array with two I properties within it the fill name and the release here ready to use and to be honest that's already a pretty good result but we'll now do something which is even better and look at creating this and.
Presenting it as an HTML table so what I'm going to do now is to create a parallel Branch so I can still look at my select statement and I will use an HTML command it's one of the data operations we've looked at again and what I want to do is create an HTML table I use this a lot in power automate so I'm going to get my data from the value the list of all the items as I did before.
What I'll do is just try running that without any alteration whatsoever to see what happens so if I try running that flow and then look at the output from it you can see it presents me with an HTML Table and there it is good but it possibly is a bit too detailed so it contains columns which I'm particularly not particularly interested in I just wanted the title and the year so what we'll do is come out of that and choose show Advanced options.
Now what this allows you to do is choose specific columns so I'm going to choose a custom there and I'm going to choose my first column is the title and I'm going to call that fill name with a spacing and this time you really can't type A spacing or can you and I'll call this release Here again I can't type A Space in and I'll pick up in the year here's one of the most peculiar things about power automate you can't type A.
Spacing you can copy one in so if I type in in something like notepad the words film
Name and then if I go back into this and paste that into that box it accepts it bizarrely how strange is that so if I now try running this again I'm going to get the same thing but this time my HTML table will contain just two columns and it already is going to look quite pretty actually as you'll see in a second that's quite.Nice now what I could do is to pick up on this output and email it to myself for example what I'm going to do is choose to create a new Step underneath both these two actions to send an email and I will send it inevitably to myself and the subject will be films and the body of the text could be the output either from the HTML table or from the other select statement I'm going to choose the output from the HTML table.
And then if I save that and if I run this one more time choose to run it while that's happening I think I'll just go into Outlook see the answer so that should have run by now and should have sent me my email it's looking good now if we're going to Outlook you can see my email containing the HTML table of all the films so that's a very nice way to pass information between different programs.
Or between different applications what we'll now do is look at making this look even nicer by applying CSS styles to make this look really pretty so what we're going to do now as the PS the resistance is formatter HTML one way to do that would be to get something to appear before the output giving a definition of all the Styles and you'll considerate presenter has given you access to that so if you look at some of the files.
Attached to this video which as I said you can get out by looking at the YouTube notes you can see that there's a file called HTML star table styling.txt which contains some sample HTML styles you don't have to know much about HTML this will take any table apply a one pixel solid board around it set a background color like gray by the look of it and a few other things besides but obviously you can type in your own Styles as well so what I'm going to do is select all of that and copy it to the clipboard.
And then back in my flow I'm going to put it into a variable so I'll add an action before the final one and what this action will do is to initialize a variable and I'll call my variable CSS it's going to be a text variable and it's going to contain all this CSS so then what I can do is go to my send email task and before I put my output in I can put.
The CSS in so if you think about what this will compile it will have a load of CSS defining how table should appear and then a natural table itself so the results should be more attractive so let's try doing that if I now save that and if I try running it I'm sorry about these pings I keep meaning to turn them off if I go to Outlook there's there's my original table and here's one which has just arrived much nicer and obviously then you can format it any which way you want.
So I personally think the best way to send information is as a formatted HTML table if you have the time and energy to apply a CSS to make it look nice so I promised I think that we would create some other lists from Excel information so let's do that what I'm going to do is create another flow I'll call it show Excel lists manually trigger it and choose create choose new Step go to Excel and what I'm.
Going to do is show you what the other two tables you can get at look like so the first thing I'll do is get the worksheets I need to choose rather tediously where I'm getting them from which is OneDrive for business I need to choose one drive and I need to choose my file and choose save and then I'll add another action underneath that which will do a very similar thing in Excel but instead of getting the worksheets what it'll do is get a list.
Of all the tables I don't know if you remember that I got you to create a second worksheet and indeed a second table and the reason I did that is just so you can see something more exciting from this than a single worksheet and a single table so I can now choose my second file and what I'll do without any further Ado is just run that to illustrate how this is going to work so if I test that and have a look at the output what I'm expecting to see is two worksheets and two tables.
So you missed about five seconds or so waiting there um which I spared you so there's the output from getting the worksheets you can see there's two worksheets and for each worksheets it gives you the name of the worksheet and some other information about it it's a position whether it's visible or not and so on so you could get at that and for the second action to get the list of tables you can see this is the output it gives you so from this.
You can see it's given me a bit more information actually for each table the name of the table and all sorts of other information about it to be honest some of it I don't understand but it includes things like whether you're showing alternate columns and rows in different colors and so on so that's other information you can display about Excel so what we're now going to do is to look at how you can upload a SharePoint list and because I'm on this tutorial to be.
As self-standing as possible so you can work your way through things we'll create the list first if you go to SharePoint so I'm going to click on the waffle and choose to go to SharePoint and make sure you've selected one of your SharePoint sites what I'm going to do is create a list in fact I'm going to import it from an Excel file so I can click on the icon to create a new list I can choose to get my information from Excel and if we look quickly in Excel we can see that that's what the file is going to look like so it's a set of 1200 movies.
And it contains various details for each I'm just going to shut that down just in case there's any problems working with an open file and what I'm going to do is upload that file so I'll choose the movies file and that's provided with this tutorial you can see where it is by looking at the notes for the YouTube video and you can see I've got a list of films here there's only one table in the file it's called films and for each of the columns you've got a column header and.
You might think look at this for example that the genre field is called genre as we'll discover it's not it's probably called something like field underscore 4. so what I can do is choose next to go into the next stage I can choose crate and what it'll do is create my list I'm going to leave that chugging away in the background when I come back to it it will actually display the list but it takes up quite a while to refresh so let's go back now to power automate and create a flow which will actually import that so if I choose to create a new flow I'm going to make it an instant.
Cloud flow and let's call it a node SharePoint list and I'll choose to manually trigger the flow quick look nope still not updated I'll create a new step and what it will do is take a SharePoint file or SharePoint list click on the SharePoint actions and what I want to do is to get items from SharePoint so you can see if I scroll down there is an action called get items something quite difficult guessing which action you should choose.
So I'm going to choose my site which is the power ultimate video tutorial I'm going to choose my list which hopefully is now in the list of this is movies and what I want to do now is one other important thing I'm going to click on show Advanced options and this allows me to set some things called odata filters now I've got a separate tutorial coming up on no data but for the moment I'm just going to say I want the top 10 records this is a vital power automate can run.
Very very slowly if you access a lot of Records because each for each record it will go back to the Cloud Server and I think Microsoft starts sulking and start returning the data more slowly so we're just going to pick out the first 10 films and there they are by the way they've refreshed that's what I can now do is to try running this so if I save this and having done that I'll just collapse that and try running it.
You may notice a bit of a theme here that I always run things after doing anything and what you should see is the output from this when I run it is a set of the first 10 films so if I look at the um the output from that if I download that you can see I've got the usual header section at the top and then I've got the body section and again I'm going to ask you to remember that this is the body section and it contains a value section each I guess record within that is a film so the title of the film is given by the.
Title column because every item sorry every SharePoint list has to have a title field but all the subscript Fields have somewhat surprising names for example the director field is called field underscore five who would have guessed it I'm going to go in a slight digression here just in case anyone finds it useful and just show you how you can find the name of a field in SharePoint because it took me a very long wait for time to find this out there may be a better way but I never found it so here's my SharePoint list how can I.
Find out the true name of that field well one way to do it is to click on the settings at the top right choose to look at the list settings and then scroll down and find the field you're interested in which in this case is the director column so I can click on that and then go to the URL go to the end of this and can you see right at the end it says field fives that is the name of that field and that's the only way I can find to determine what a field is called.
And that's the end of that digression so I'll close this down and what I now need to do is to display this in a table perhaps so I'll go back into editing my flow I'll add a new step and because I really like my HTML way of displaying information we've just learned what I'll do is display this in HTML format so I'll create an HTML table the information I'm going to get is from the value so that's from the output from the previous table and I'll choose Advanced options and choose custom.
Whoops so I can specify my columns now at this point you can disregard what I told you about needing to know the SharePoint field names because I can just refer to them from this list so for example if I want to show the title I can call that title and I'll rename that as title and if I want to show the director for example I can just click on the word director so it shows the visual representation of the column rather than its true underlying field name and I'll call that director too.
So here's what's happening behind the scenes the value is taking the output from the previous action which is called get items and it's looking the body section and within that the value section for each item each row it's then picking out the field called title and is picking out the field called Fields underscore five you don't need to know this yet but it's all good background information for future tutorials believe me so what I can now do is to save that and try running it.
And it's taking a while to save so if I click on test and choose to run this and what I'm hoping i'll see at the end of it is 10 films and two columns look at the output from this you can see a neatly formatted HTML table with the two columns are requested and the first 10 rows so it's all worked so what I want to do in the closing parts of this tutorial is just give lots of examples of tables in power automate so we've done Excel we've done SharePoint let's try Outlook let's list out the subjects of our Outlook emails.
So if I go into our look you'll see that my inbox contains three emails that will do and what I'm going to do is list out the subject and maybe the send date of each two of the films by the way I've got the same subject films so to do this I need to create a new flow obviously so I'll create an instant Cloud flow I'll call it list out list Outlook emails and then we'll choose manually trigger the flow and choose create.
So I need a new step and what I'm going to do is go to my Outlook actions and what I want to do is get all my emails I'll list all my emails so if you scroll down you can see the things like getting calendars that sounds interesting you can find all your meeting items you can get your contact folders get your contacts so you can get a pretty much any aspect of Outlook I want to get to just the emails I'm going to choose that item I'm going to go to my inbox and pick out all the emails but there's one thing I want to change if I go to Outlook you.
Can see you can see I've actually read two of my three emails at the moment it's only fetching the unread messages so I just need to have a quick look down there option is set by default for filtering messages and change that to no so I get all the three messages and what I can now do is to save that and then what I'll try doing is running this to see if it gives me the three emails and to inspect the Json returned so I'll click on test choose to manually run it and click on continue and click on run.
Flow and what we should do after second or two is list out the emails inspect the output of that you can see that it's becoming familiar now you've got the header section and then you've got within the body section you've got a value subsection and within that you've got one entry for each email so looking at the first one for example you can see that this is the information returns and what I'm going to do is pick out the subject property.
