Power Automate Export to Excel | Dynamically create Table, Columns & Add Rows to Excel | Send Email

hello everyone reza here in this video i will show you how to export data from power automate to excel we will build the table inside the excel file dynamically add columns to the table add rows to that table and export the excel file so let's check it out in action i would like to export data from this sharepoint list to an excel file i'll pick the instant cloud flow i'll pick the manual flow trigger i'll give my flow a name and i will click create now to get the data from sharepoint i'll go to new step and use the get items action from sharepoint connect to my sharepoint site connect to my sharepoint list and under show advanced options i can go ahead and add a filter criteria to get specific data from that sharepoint list so let's say i need an export of all the data that has the ticket status as closed so i can add a filter query here that says status equal to closed now if i was to run this flow if you look at the outputs of the get item action and if we look at it in visual studio code the value property returns an array of data in json format and in this i have all of those tickets that have the status closed for each of those tickets i can see all of the fields right here along with their values now when i'm exporting to excel i do not want all of that column information to show up in excel i just want specific column data to be exported and there are different ways in which we can achieve that and one option we have here is to limit the columns by view what i can also do is go to new step and use the data operation action called select and what select requires first is an array of data which the get items action value property returns and here i can create my key value pairs so for example i need the id of my issue so i've entered issue id and the value is coming from the id column in my sharepoint list so i can search for id and pick the id column next i'll say status and here i'll pick the status column now status is a choice column in my data source the value for that choice column is contained in a property called status value so i will select this on similar lines i have a choice column for priority who this task was assigned to which is a person type column so i just need the display name of that user so you can go ahead and generate your own select action here and pick the specific column values that you need and if we look at the output of the select action this gives us the json array of those columns that we created along with the values coming directly from sharepoint to create the excel file what i need is a template file first so in my onedrive i will go ahead and create an excel file and i'll just rename this to excel template so in my onedrive now i have this empty excel file that does not have any data nor any tables created within it now for my flow i can go to new step search for the create file action and in the onedrive for business connector there is the action create file so i'll select this it's asking me for the folder path i will pick the root and here it's asking me for the file name now every time this flow runs i want to create a file in one drive for that i need to give it a name and the way i can make the name unique is you can go to expression and use the function good this will generate a random good each time the flow runs so i'll click ok and because i'm creating an excel file i'll give it the extension for excel file content this is where i need to put the content of the file i need an empty file to start with and that's why i created that template file so i can put that empty templates file content right here and to get that right before the create file action i'll search for get file content and from the onedrive for business connector i'll pick that action it's asking for the unique identifier of the file so i'll select this show picker icon i'll go to show contents and here i'll pick that excel file which is that excel template file that i created in onedrive and i will provide that right here to file content now in order for power automate to work with an excel file we need a table created inside that excel file we'll go to new step pick the excel online business connector and in here if i search for table there is a create table action so i'll select this the location will be my onedrive for business library that would be my one drive now to give the file context i just created the file so i'll click here and go ahead and pick the id property from the create file action table range and this uses a1 notation now currently i have five columns selected and i will show you how to make this dynamic as well but for now i will put the following notation dollar a one colon dollar e one i'm trying to create a table here that goes from cell a1 to cell e1 in my excel file needs a name for my table i'm just going to give it a name data and then it says enter the column names separated by semicolon or comma load the data into this table i'll go to new step pick the excel online business connector and use the add a row into a table action location onedrive library is onedrive for the file i'll pick the id once again from the create file action for the table because we are creating the table dynamically in the previous step we need to go to enter custom value and here we need to pick the name dynamic content property now for the row property i need the data and the select action has that array of data so i need to loop through the rows of that array and add them one by one to this row property i will add the apply to each loop the data for the loop will come from the output property of my select action and my add a row into a table action now needs to be placed within this loop so i'm just going to drag it and place it right here for the row property now i can just search for current item which will give you the context of the current item that's running in this loop let's go ahead and test this flow out so the flow has run successfully if i head back to my onedrive i have this excel file that got created it has a table that has all the columns that i created in that select action with all the data now that i have this file populated i can just go ahead and send this as an email attachment in order to send the email out i first need to get the content of the file that i just created so once again i'll use get file content from onedrive for business for the id of the file i will pick the dynamic content property id from the create file action i will rename this action to my excel file i'll go and add a new step send an email from the office 365 outlook connector i'll send this email to myself give it a subject body go to show advanced options here i can add my attachments which will be my excel file i need the name of my attachment so here if i just go and search for name the create file action has the name and for the content i grab the content by using this action which i renamed to excel file so i'll just pick the file content property from that action right before grabbing the file content just to give some more time to the flow we'll introduce a delay here and you can introduce a delay let's say of a minute and i can go ahead and test this out i get this excel file attached in my email if i open this i get all my data right here now in order to make the create table action dynamic i need two things one i need a list of all my column names and this has to match the names that i've provided here in my select action plus i need the length of the number of columns in here so i can go and calculate the a1 notation as well big shout out here to mvp john liu he actually showcased a wonderful hack in which if you use the create csv table action you can go and grab the first line from that action which actually has all the heading information which are the column names and then from that we can go ahead and get an array of all those column names so following john's technique right after my select action i will add the create csv table data operation now i can give it the array of my select action but that array could have a lot of data and all i literally need is just the names of my columns which i will even get if i provided a single row i can create an array and in this i will go ahead and write a very simple expression called first which just gives me the first item from an array and which is the array i'll switch over to dynamic content and pick the output of select now if you look at the output of the create csv table action every record is separated by the next one with a line break so now to get the first row of data which has all that column information i'll add another action after create csv table i'll use the compose data operation i will add an expression called split and split my output of the create csv table action with the new line character and to get that just going to go ahead and paste this function here called decode uri component and provided this value which basically converts it into a new line character and this function will give me an array and i just need the first row of this array that's because it contains the headers so i'll use the first function so this gives me a string of all my column names all of my column names will be separated by commas and if i need all the individual column names in the form of an array once again i'll use split and i will split this entire function with comma i'm going to rename this action array of column names one key thing to note is your column names cannot contain comma because i'm splitting based on the comma character in the create table action i need my column names i want them to be comma separated have it right here by using this expression without that comma split so i'll just go ahead go to expression paste that code and say okay so i've got all my column names here dynamically added now comes the table range if i use the length expression on this array it will give me the number of columns however i need to put the a1 notation here i need a mapping here that tells me which column in my excel file this relates to so what i can do here is add an action once again use compose i'll call this a1 notation mapping use an expression once again split and split the following text which is i'm just going to write all the alphabets here so i've gone from a to z this covers 26 columns if you have scenarios when you feel your number of columns can go above 26 after 26's goes to a a so you can start with a a a b a c and so and so forth so i'm going to split this with comma because that's the delineating factor that i'm using for my string in the create table action where i have the hard coded column e i can use outputs a1 notation mapping this is case sensitive so a1 space should be an underscore notation underscore mapping that's my array and if i need an index value from the array i'll use the length function and in here i will pick the outputs from my array of column names and this length has to be subtracted by one so i'll use subtract and remove one click ok all of these expressions i'll make it available in the description of this video so you can grab it from there and let's go ahead and test this flow if i head over to my email now and open up my excel file i will just have the data for those four columns from this point onwards that is after the select action all of the steps are going to be the same no matter which data source or data service you connect to as long as you give me an array of information i have a table in microsoft dataverse that has devices information so let's say i want to export all my data to an excel file where the device type is laptop so back to my flow i will first delete my get items action and this time i'll use the listrows action from microsoft dataverse i'll choose my table of devices put the filter query in and now for the select action the from property would be the array value coming from list rows and i can now define the key value pairs once i'm done with my changes i will go ahead and test my flow here is the excel data that i receive in my email let's take planner planner has an action called list my tasks so i'm going to select this here for value i need the array and that isn't the value property so i'll pick that and here i can pick the properties so let's say i need the title of my task the start date due date let me go ahead and run this flow here is the email of that excel file that has all the data coming from planner if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 7,348

Rating: 4.965517 out of 5

Keywords: power automate excel tutorial, power automate mail tutorial, power automate excel mail, power automate excel guide, excel power automate, microsoft power automate excel, microsoft power automate excel mail, microsoft power automate excel tutorial, power automate, power automate how to, excel, how to add rows to excel, add rows to excel, create table in excel, create table in excel power automate, excel and power automate, flow add columns in excel, flow create table table range

Id: Kupz71dWYyY

Length: 16min 26sec (986 seconds)

Published: Tue Aug 24 2021

Reddit Comments

[u/north7]

This is great stuff, keep it coming!