When an Excel row is created, modified, or deleted
Вставка
- Опубліковано 5 вер 2024
- Power Automate template workaround for "When an Excel row is created", "When an Excel row is modified", and/or "When an Excel row is deleted" triggers.
Community Thread & Template Download Page: powerusers.mic...
Microsoft started renovating their community site. They didn't transfer the download files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team.
In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.c...
HTML Table Styling: ryanmaclean365...
Venmo
venmo.com/u/Ty...
PayPal
www.paypal.com...
Buy Me a Coffee
www.buymeacoff...
Thanks for any feedback,
And reach out on LinkedIn ( / kolota ) if you want to hire me to consult or build more custom Microsoft solutions for you.
Hello! The downloadable packages are missing on the referenced page. Is there another way to download the flow templates?
Microsoft is renovating their community forums. I'm not sure if the page they moved my stuff to even has file uploads and I'm unable to edit the posts.
I'll need to figure something else out, but in the meantime, email me at takolota@gmail.com with what templates you want the solution packages for.
Here is a google drive link to a zip folder with the solution package import for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
@@tylerkolota Thank you! Your work is really inspiring!
Hello Tyler,
Thank you for the tutorial; it is exactly what I have been looking for for a long time. I did exactly what you proposed, but my Power Automate is limited, so I used version B. However, when I run the flow, which runs without error, it does not identify any changes, additions, or deleted rows.
I have checked the unique ID key, and it is correct, and I configured the columns the same as in my spreadsheet, setting ID, Category, and Status to change, add, or delete to identify. However, when I make changes, they are not found in the input or output.
What could be happening?
Thank you in advance.
Hi Tyler, thanks for the great tutorial! I'm currently following this tutorial and there is one part I would like to clarify. In the timestamp of 11:07 of this video, where you described the "Condition If Users edited during table read stop and let later flow run" action, you have this fields: "Modified" "is greater than" "Modified". I would like to know the expression for the "Modified" for the left operand before the "is greater than" and the "Modified" at the right operand after the "is greater than" because these fields were not mentioned in your tutorial. Thanks!
left hand ‘modified’ is dynamic content from the most recent get file properties. Right hand ‘modified’ is dynamic content from the when an item or file is modified trigger.
Hi Tyler, thanks for sharing this video, very helpful! The whole process met the requirements of my business project. However, there is one thing I need your helps based on this valuable video. Our current resource file is JSON format instead of the Excel file. So, is there any step I need to change for getting the email when the JSON file has been re-remodified or updates.??
If you are saying your main file you are checking for updates is itself a JSON file with a JSON array of records in it, then you will need to switch out all the Excel List rows actions with actions to get the file content of the JSON file & convert it into typed JSON like what I did for the previous table version JSON file in the "Get file content using path Last table version" & "Last table version" actions. Then use the output of those actions wherever the Values of the Excel List rows had been referenced.
Great set of instructions. I'm trying to adopt this now and have 2 questions: 1) While the flow executes is skips the step "Load previous table version copy before updating to the most recent table copy" shows 'skipped' when testing. Why? and 2) the email recipient in your example is static. How can I get a dynamic email looked up based on the value in a column of the spreadsheet?
1. If users made more edits to the workbook before it could load the previous edits then it will cancel that flow run so only the last flow run will proceed after users have stopped making any edits to the workbook for a few minutes. Check only the most recent flow runs that were not 'Cancelled'.
2. If you want to do something like send an email each time a row is updated & send it to the email listed on that row then you need to use the flow version with the Apply to each loops and set up an Outlook Send email action inside the look with the To input set to the reference for the Excel email column.
@@tylerkolota Ok, I managed to create a send email action and send an email but I don't get the delays as most of the time execution cancels: when the flow is not running I make a few modifications to the Excel file and don't touch the file at all yet it cancels. So the second action waits, the gets the file properties to determine whether changes were made and if within the delay duration cancels? How can I determine whether I was within the delay window? I'm using v1.1b and have extended the delay to 2 mins but no change. I have made about 5 edits but received only 1 email
If I was trying to change how the date was pulled from the excel file how and where would I put in that step? I know it can be changed to ISO 8601 but is there a step I can put in for a specific format without breaking the flow?
You could put a Select after the final Filter array actions & change the date format in that Select before passing the output body of the Select to the loop or to the HTML table.
This is helpful and valuable thank you! I am just starting this path and I am not a technical person my background is mostly in business.
I am getting this error: Selected column name 'ID' not found in Excel table.
clientRequestId: 3486357b-6173-4973-90b4-47e90dd4521d
serviceRequestId: f483af1d-1f97-4f24-8403-2faae7c4641f;e9d5c58c-e4b0-41fc-9502-cb2d827bb46d;3269ab32-1e6e-4e8c-86be-e0c8d2da9571
I am supposing this is a table format error, Do you have a different video of how to set up this kind of tables or excel documents to work properly?
Thanks a lot
PD: Sorry if its a really basic/dumb question!
Do you have a column in Excel named “ID”?
If not, what is your primary key column? A column or combination of columns that is unique for every row in your Excel table?
@@tylerkolota Yes I added a column named ID. It is now succesfully running but it doesn't track any changes :(.
Is there a way to pay you for a couple minutes session to help me fix it? Thanks a lot!
@@danielsepulveda2548 did you just add like a blank ID column? Does it actually act as a primary key column or no?
www.techtarget.com/searchdatamanagement/definition/primary-key
@@danielsepulveda2548 You can email me at takolota@gmail.com or post to the community thread to share screenshots
powerusers.microsoft.com/t5/Power-Automate-Cookbook/When-an-Excel-row-is-created-modified-or-deleted/td-p/2264670
Hi! Great tutorial, but I am stuck with a point if you could help me out. I want to pass all the values dynamically in all the actions, so In action List rows present in a table how can I get Table name dynamically like which file is triggered it should get table name of that file dynamically, rest of I had configured values to set dynamically. Thanks
You’ll want to check the Get tables action
ua-cam.com/video/XQ4XjO3Ho7s/v-deo.htmlsi=pYu_6oDzED-kWIyE
How would I pull out a specific column of the modified row? My table rows are assigned to specific people. Therefore, when this row is modified that person needs to be notified. The column contains the assigned persons email.
Thanks!
In the apply to each you can set a Send email action to send To the
item()?[‘InsertAssignedEmailColumnName’]
to reference the assigned column email address
(Don’t copy directly from above, the single quotes ‘ ‘ will be different)
That should send an email for each item to the assigned email address.
Thank you!
@@tylerkolota
Building on Dustyn2429's question, the flow currently batches the notifications when it runs, sending the same group of change notifications to each person. Is it possible to have each row that is assigned to a specific person only go to that person (rather than all who were in the same batch of changes when the flow triggered)? This is a very useful tutorial but I'm stuck on this tweak. Thanks!
You’ll want to do a Select on the items coming out of the Filter array, switch the Select to a single box input using the right-side button, use an expression in that single input box of item()?[‘InsertEmailAddressColumnName’] so it will output an array of just the email addresses, then do an Apply to Each on the Union( ) of that Select so it loops once for each unique email address, then in the loop use a Filter array on all the items with changes where the email address column equals the Current Item of the loop so it gets all the changed items each given email address it loops over, then you can input the outputs of that Filter array into an HTML table action & Send email to show only the items changed for that given email address.
@@tylerkolota Thanks a bunch!
Hi Tyler,
i downloaded the same file as you, but when i go to solution > Import Solution > Browse > Select the file > Next at this point i'm getting an error "The solution file is invalid. The compressed file must contain the following files at its root: solution.xml, customizations.xml, and [Content_Types].xml. Customization files exported from previous versions of Microsoft Dynamics 365 are not supported."
@@KhurramGohar You need to download the solution file that ends in like 1_0_0_6.zip
Great Video Tyler! It was extremely helpful! Do you mind sharing your expertise with me for a bit?
1. In the HTML email for modified rows, would it be possible to include two tables: one for the previous values and the other for the modified values?
2. I have about 10 tables in one workbook and would like to build 10 different workflows. Would it be possible to built the condition in the early of the flow to check if a specific table is updated, then run the flow? currently I can only find "When an item or a file is modified". Thanks!!!
Hello,
Here is a forum post on the download page where I show & share how to set that up: powerusers.microsoft.com/t5/Power-Automate-Cookbook/When-an-Excel-row-is-created-modified-or-deleted/m-p/2845078/highlight/true#M2914
@@tylerkolota Thanks soooooooooooooooooo much! It worked really well :) If you don't mind, can I ask you another question?
Would it be possible to trigger this workflow not when a file is modified, but rather when a cell for specific columns is modified? and in the html table, only show these specific columns?
For example, in the table from the video, it only triggers when data about "SampleDescription" and "SampleName" changes and shows only "SampleDescription" and "SampleName" in the html table.
If you could share your expertise, that would be really appreciated! Thanks!
@@amilia_8273 You can set the flow actions to only run with certain columns as described here: powerusers.microsoft.com/t5/Power-Automate-Cookbook/When-an-Excel-row-is-created-modified-or-deleted/m-p/2813457/highlight/true#M2897
And you can further specify html columns by going to “Show advanced options” on the HTML table action & switching from automatic to custom mapping. Then you would have to input the column names you want on the left side of the mapping & use the item()?[‘InsertColumnName’] references on the right side of the mapping to reference the column values. Although this part may not be necessary if your Excel Select query is already narrowing down to just the columns you want.
@tylerkolota Thanks so much!! It worked like wonders. I really appreciate your help 😊😊
Hi, I’m really stuck on the whole check for column changes section.
Please could you share the expressions that are being used in the create CSV, The Linebreak and the condition
Also, what is type of action is ‘Linebreak’ and what is this doing?
Thanks!
The CSV action expressions are just taking the 1st record from the new & previous data, the Linebreak action is just a linebreak character (so a single enter key), the expressions in the condition split the CSVs into lines & just takes the 1st one of each which are the header rows so they can get comma-separated lists of all the column names for both the previous data & new data. If the list of column header names do not exactly match across previous & new data then fail the flow.
@@tylerkolota thanks for the reply. I understand the purpose of the whole scope, but I’m struggling to replicate the expressions (I’m unable to download the example files).
Are you able to share them here in the comments section?
@@preservedjams4109 What error are you running into trying to download the sample files?
I am getting an error indicating "The compressed file must contain the following files at its root:solution.xml,customizations.xml, and [content_types].xml. Customization files exported from previous versions of Microsoft dynamics 365 are not supported.
Did you download the solution package with the 1.0.0.x.zip at the end, or did you download one of the V1 import packages for the legacy Power Automate import method?
@@tylerkolota I believe I used the V1 options.
@@tylerkolota I attempted the 1.0.0.X.Zip file but it's not appearing in my flows-power automate
@@elijahd.spragueph.d8905 That's strange it should show up once you import the solution.
Well you could go back to one of the V1 packages, go to make.powerautomate.com/flows & select Import > Import Package (Legacy) then upload that V1 file to go through the legacy flow import process.
Can you show us simply trigger a email notification when new Row added with ID Number>
Do you want a different email notification for each created row, or do you want a single email notification with an HTML table of all the newly created rows?
@@tylerkolota different email
@@pri938 If you want a different email for each row then you can put the Send Email V2 action inside the Apply to each loop for created or for modified & use the references pattern to add fields to the email.
thanks for the video also how can I send the modified rows before and after to see the change as an email?
@@melisisk7871 Hello,
Here is a forum post on the download page where I show & share how to set that up: powerusers.microsoft.com/t5/Power-Automate-Cookbook/When-an-Excel-row-is-created-modified-or-deleted/m-p/2845078/highlight/true#M2914
Some work questions I you don't mind:
* My table has about 30 columns and all 30 get put into the email. Where is the most logical spot to alter this so that I only add the 5 relevant columns into the email?
* Secondly while the code works and sends emails for the rows that have changed (not using created or deleted) the HTML styling is not applied. I have double checked the CSS. Do I need to enable HTML anywhere else?
* Do I have access to the old data? Say the ETA changes from 'date old' to 'date new'. Could I have both dates in the email notification?
@@meierthomas There is an option on the HTML table action to change from automatic to custom columns. If you change it to custom then you can add column names to the left side mapping & their corresponding expressions item()[‘InsertColumnName’] to the right side of the mapping.
@@meierthomas You can add a Filter array action on the previous version JSON where an array of modified primary keys contains the item primary key to get the previous values for modified records.
@@meierthomas I’m not sure what may be off on your styling after the HTML table
@@tylerkolota I have tried custom columns at the create HTML table step using the body from the previous step (Modified records) in the from field. The table content renders as item()['Ordered Date'] so it doesn't pick up the actual value. Looking at the execution the 'raw input' to Create HTML Table shows the data is there; i.e.
{
"from": [
{
"Column1": "May Containers",
"Ordered Date": "45337",
"Warehouse": ".....",
"Email": "......",
"Payment Required ": "45398",
"ETA": "45395",
"Model": ".....O",
"Ordered by": ".....",
"PO No_x002e_2": "575",
"Customer name": "......",
"Sales Order No_x002e_": "SO009071",
"Supplied": "0",
"CIN": "2405-991",
"ID": "mY885K"
},
but the output doesn't pick up the data. Also I'm not able to enter when I entered the header / value pairs; wouldn't accept item()['Ordered Date'], only item()['OrderedDate'] so I created the expression in a notepad and copied it into header / value
@@tylerkolotaDid some more research and found that GMail seems to be the issue. Works fine on Outlook
Great video! Will this also work for a xlsm format?
Looks like you may have to add some more actions if the file/table don’t appear in the Excel List rows action: powerusers.microsoft.com/t5/General-Power-Automate/List-all-rows-from-a-MACRO-ENABLED-excel-file/td-p/764594
@@tylerkolota thanks for the quick response, will try that
hi, i dont see the zip packege. How could I download it?
Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team.
In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing