When an Excel row is created, modified, or deleted

hello everyone if you've ever wondered if you could trigger a workflow to happen on changes to an Excel table tried to get notifications of changes or just generally worked with Excel actions and power automate you may have asked why is there no flow trigger like when a row is added when a row is modified or when a row is deleted after all other data sources like SharePoint have triggers for all of these and you wouldn't be alone in that thinking many users in the power automate Community have requested these exact features for years well today I'll answer these requests and review a flow template I made to replicate an Excel trigger for created modified and deleted rows so we can all build better workflows and notification systems with any of our shared Excel workbook tables in SharePoint document libraries to get this template you'll need to go go to this Microsoft Community forums page the link will be in the description and first you'll need to scroll down to the bottom of the page or to the bottom of this first main post where you can find the download packages now if you've seen some of my earlier videos or maybe if you don't have access to import the power apps uh solution packages then you could try one of these first two downloads and do the direct flow import but for this one I'm going to download the solution package that has the solution numbers instead of the flow version number so I'll click download here click save save that to my desktop and then next I'll go over to my power apps homepage click over to Solutions and then click import solution browse and select the folder that I just uh downloaded there so here I'll click next and then I'll click next again and then here's where I need to change out the connections for the template for the flows to use uh so I'll just select one for my current account here of course if one of these didn't have a existing connection to select you could always click new connection here but I don't need to do that I've already got my two connections there so I will just go down here and click import and then this may take a couple minutes or so to import the solution all right once that solution is done importing then the flows should now be available over in the power automate menus so I'll go to the power automate homepage go to my flows and it should be the top two here so I got version 1.1 version 1.1b I'll explain a little later a little bit more about version 1.1b but for the demo right now I just need version 1 .1 so I'll click into there then turn the flow on and then head on over to the edit menu and just to give a brief overview so we're having this trigger monitor a SharePoint document library for a any changes made to a specific file in this case it'll be our Excel workbook and then from there we have this scope set up to to basically delay for several minutes uh so that it gives the user an opportunity to make more edits and if they make any edits during those next few minutes then this is actually just going to cancel this flow run because remember up here this will be triggered again in a later another flow run uh for those later edits and that way this all of this after that piece only needs to run after the user has stopped making edits to the document so after that piece that makes sure we're only running this when we need to we then have this piece which is going through and reading in the entire Excel table that we Target and after that we have this piece which is loading in a record of the previous version of the table from a Json file and then we go ahead and take what we read in from the list rows action and paste that in as the new version as a new table version as a Json array to that Json file but again that's after we've already loaded in the previous table version after that we just have this piece which is checking to see if there were any column name changes on the table and then here we see we have the created modified and deleted sections where the these are the filters that are actually comparing the last table version to the most recent table version after the edits to figure out which records were created uh which rows were modified which rows were deleted and then I have some example uh references here which people can use to set up actions and reference the column values in these apply to each Loops for each of the created modified or deleted scenarios so more on setting this up for a new scenario the first thing I'll do when setting this up is actually go to my desktop go create new text document I'll give this a name table version Json and click into that text document give it a placeholder value like a blank Json array which is just square brackets with curly bra brackets in between uh click file save as change the file type to all files and manually give it a Json file type save that and now I have my Json file so once I have that I can then go and navigate to the SharePoint site where I have the document library with the Excel workbook that I'm interested in and and there I will just upload the blank Json array file that I just created and once that's uploaded I'll then go to add column show or hide columns and make sure that the ID column is showing and once that ID column is showing I see that the example Excel workbook I'm interested in has an ID of 512 so I'll copy that then go back to the flow go to my Trigger action go to the three dots go to settings and then scroll on down to the trigger conditions here so right here is where I need to set the ID value so I'll just paste in the ID value and what this does is it basically sets the flow to only trigger when the file that has been modified has an ID of this value so it sets it so it will only trigger when there's a change to that file that we just pulled the ID for so I'll click done there and then save this so now I need to go through each piece of the flow and add in the inputs that will Target the correct Excel file and Excel table uh so first that means here I would likely need to change this out to the Set uh site address that I'm interested in and then go and choose the right document library that holds my Excel workbook and then I can go to the first scope here which delays the rest of the flow from running until a user has stopped making workbook edits for a set period of time uh this way most edits are batched into a single run uh see if another edit was made while this flow run is going then it will cancel this flow run so the next flow run can perform both the previous and the newer edits uh here what we really need to change out is just this get file properties um so I'll delete this placeholder value and that will remove all the placeholder values so now here I can go in and change the site address to my site address and the libr name to my library name and then close the scope after that I'll open this settings action and enter the column names that form a unique key or a combined unique value for each row so if it actually took a combination of two columns to uniquely identify each row then I would put the name of the second column between the double quotes here but this happens to R set with the individual column Name ID here that I need for my example workbook see here how this has this ID column here which is unique for each row so that's already set with what I need and I'll leave it as is then move on to the next scope and this is the scope where we are reading the entire Excel table so this is set up with a 100,000 pagination with the pagination turned on so grab up to 100,000 rows of a table um I need to set this so the location targets the right site address and targets the right document Library put in the example Excel workbook there and then choose the table and then I'll also need to go to the get file properties to here and this is setting this to Target again the Excel workbook and the reason we're grabbing this to get a new read on the Excel workbook is so that we can double check if it again was modified while we were reading in all the Excel table rows because this action can sometimes take uh several minutes especially if we have a large table with many rows so this will again check if it's been modified since the triggering of the flow and if yes then it we'll cancel the flow run so that the followon flow run that includes the later edits can continue on and pick up where we leave off here it also runs the Excel rows through this select action to remove any additional metadata columns that are usually unique in each flow run and thus would mess up player actions that check check for row changes so here this scope ultimately outputs a kind of sanitized version with only the uh set columns in our table without any of the extra metadata next we'll go to the load previous table copy before updating to the most recent copy scope this is where we load into the flow the last version of the Excel table saved to the Json file and then save the new version of the table that we just read in from the list rows action uh back into that Json file so it's ready for any later flow runs but again that's only after we've pulled in the previous table version from that Json file here we just want to set these three actions uh to again go and reference our site address for our Json file and once we've done that then we can close this up and open our next scope where we check for any column name changes so this first formats some of our data uh to extract a list of all the column names for both the previous table version and for the new table version and ensures that those lists exactly match and if they do not match then it will cancel the flow this is an important step because if a user changed a column name then a later piece of the flow would register every row as having changed and run any flow actions we later set for all rows in the table this would be a very annoying mistake if the later flow actions were set to create or update data in another data source or set to send an individual notification for each change this prevents that from potentially happening for every table row uh but also note because this check happens after the new version of the table is saved to the Json file if the flow ran again then it would read and compare against the newer version of the table and no longer trigger anything for every row however that also means it would miss the flow actions for any edits being processed on that canceled flow run so if this workflow is critical to anything uh then you would have to open and inspect any flow runs cancelled due to the column name changes and manually perform the actions the flow was set for so it's probably best to warn your user or somehow prevent them against making column name changes or at the very least prevent them from making both column name changes and table edits at the same time so that's all I really need to say for that section there isn't actually anything I need to change in there um and that leads on to our next section the reformatting of the data so in this formatting scope we have select actions set to create an array of key Val vales present in the last table version to create an array of key values in the newly edited table version and to create an array of key values from an intersection of the last table version and new table version or in other words an array of key values for each row that did not get any changes at all these arrays of previous version Keys new version keys and unchanged keys will be very help helpful in the following actions to filter down the rows created or modified the rows created the rows modified and the rows edited first looking at our created or modified filter so this filters the Json array of all the records in the newly edited Excel table to those with a primary key not found in the previous array of unchanged row keys so through the intersection expression in this previous scope if any value had any change then it wouldn't have fed its primary key value to this array and then this later filter action will return all the rows with keys not found in the unchanged rows array and note that if you wanted to run something on either Creations or modifications then you could set and apply to each on this filter array action similar to the later Loops following that we have our created filter so if we want to get only the newly created records from the previous created or modified records then what we can do is just check if the previous table version had any rows with keys matching the created or modified keys if any of the newly edited Excel table keys are not found in the array of all the previous table version Keys then that means it is a new row and that new row will be returned in the outputs of this action and after created we have our modified filter this is the reverse of the created filter so if any of the newly edited Excel table keys are found in an array of pre previous table version Keys then that means an existing record was modified and it will be returned in the outputs of this action and that just leaves the deleted filter so if a record in the last version of the table has keys not found in the more recently edited table array of keys then that record must have been removed from the table and that record will be returned from the previous table save to the outputs of this action lastly we have our apply to eaches these are where we can set the actions we want to occur whenever a row is created modified or deleted and also Within These Loops we want to make sure we are referencing the correct filter array so if I went into the dynamic content I'd be able to scroll down and find the correct filter array label and then I'd use the body reference in the from input of the loop these should already be set for the template and then I'd also check in the placeholder action here uh over these references which show you how to create references to the column values in each Loop iteration for each row so we see the pattern here is the items and then the apply to each Loop name and then and the column name so if I were building this out in this template I'd be able to just straight copy that and then go paste it into my expression here and place this with whatever the name of the column is like email and click okay and that would create that um but also if I was creating this for a new Loop that I maybe named myself then I'd just go and rename this and copy the name there so that I can then go down here go to the expressions and manually type in that reference pattern just making sure to replace any spaces with underscores and then I'd add the question mark square brackets single quotes and then the name of the column there so that's how to build the expression more from scratch and we see as examples here we already have references to a hypothetical ID email sample name and number column and we can use any of these types of references to any similar columns all throughout actions within this Loop we can also see that on the next modified Loop we have similar expressions for referencing the column values in those Loop runs and the same for the deleted Loop all right I'm just going to close everything up [Music] here and I'll save everything that I've done thus far and now that we've set up and reviewed everything for the template flow we want to set the flow to run once so the existing Excel table data is saved as a previous table version in the Json file so if you remember how these couple of Scopes work they will will load the new table Json from here from the list rows presentent table action into the Json file as the new table version and then they'll go and check the previous table version against the new table version and see that the non-existent um column names in the blank Json file don't match the Excel column names and fail or cancel the flow run so decid to do that I'll just go over to the Excel file over here go to our table and make a quick modification and then I can back out to the details here and wait for the flow run to start okay and once that previous table version is loaded to the Json file then you can go back to the edit menu and start adding actions in to occur on the created modified and deleted Loops but for this demo I'm just going to keep the default placeholder values and go back over to our Excel table and make a few more changes so I will go here and create one more row I will modify a row and I will delete one row then we can go back here and wait for that flow run to appear on our list go into that flow run and here we could watch what happens but just to show how this part Works in action I'm going to go and make another modification to our Excel table so that this will actually be cancelled and then we'll jump on over to the new flow run where the rest of the actions will actually continue so that was cancelled as expected because there were more edits so now we can back out and go to the most recent run and as long as we don't make any more edits to the Excel table then this run will go through all the actions in the flow so we can go down and see the updated column values for each of the created modified and deleted rows here's the row that was created there were two runs for the modified and here's the row that was deleted and note for the modified rows we have the first modification edit from the first set of changes that we made and then we also have the second modify edit from the second set of changes that we made after that first flow run started so that's a demo of the loops running but for something more visually appealing I'll show an alternate setup using more aggregate actions like a few create HTML tables to show a single email notification with all the created modif ified and deleted rows in their own HTML tables so let's go down and remove all the loops and add in our create HTML table actions and we'll make sure that we insert the body for the create filter the modified filter and the deleted filter and now now for some extra table formatting I'll also add a compose action up [Music] here and I'll go over to Ryan McLean's blog here and copy this segment here link to the blog is in the description and paste that into my compose action so this is something I'll put after each HTML table in the email action just send this to myself and then here I can put the respective tables beneath their labels and then add that styling I was talking about and then I'll go here and save my changes and I'll go make a few more edits to the Excel table so add few more modified row I'll create a few more rows oh don't want that and I'll also delete a few more rows then I can go back to my flow go back to the detail screen and wait here until I see the last flow run that I want to watch there's the final run after all the edits so I'll go in here so many minutes later when the flow run is complete I'll receive this email message with the HTML tables that we created with one table for all the created rows one table for all the modified rows and one table for all the deleted rows so this is one example of something that may not be looping through actions for every single situation for every single row but it in aggregate uh works on all the rows at once and you know shows this is one way to maybe monitor all the changes made in an Excel table and give yourself some email notifications for that uh but generally both the setups that I've shown thus far should work for most use cases that you want to build and customize for but maybe say several minute flow runs is too long for some of your use cases that you have in mind or maybe your account only has a lower level license that cannot use pagination and cannot read more than 5,000 Excel rows in a single action or maybe you have more than 100,000 rows in your Excel table in any of those cases you are probably interested more in the version B that I created and this should be included in the solution package import so it's available here I go into the edit menu we can then go over the differences for version B uh one of the two differences is the delay for the check on further edits uh is reduced so it's not going to wait three or four minutes to then follow on with the rest of the flow it's only going to wait until the user has stopped editing for about a minute before it continues on and the second change is down here for the Excel table read so I've created a way to speed up the list rows the standard list rows method of reading an Excel table without using premium connectors and I do that first with this do until Loop which helps get the um General size of the table the general number of rows in the table and then using that we can in a following apply to each use that total number of rows to estimate how many many 5,000 row batches we need to read in the loop and because it's an apply to each I'm then able to read in those 5,000 row batches in parallel so this knocks off several minutes from the Excel load times so overall version B for larger tables like 50,000 or more rows will cut the load times in half and cut the entire flow run times in half as well all right that's the template and setup to run workflows when an Excel table row is added modified or deleted if this helped you in any way please subscribe to the channel and thanks for watching

Channel: Tyler Kolota

Views: 10,745

Keywords: Power Automate, Excel, Office365, Microsoft Excel, Microsoft, Flows, SharePoint, Process Automation, Power Platform, Power Addicts, Low Code, Automation

Id: 85QAQ-tb1M8

Length: 32min 59sec (1979 seconds)

Published: Wed Nov 01 2023