web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Gallery / Power Automate Cookbook / When an Excel row is create...

When an Excel row is created, modified, or deleted

Views (42914)
takolota1 Profile Picture Posted by takolota1 4,974 Moderator

WhenExcelRowThumbnail.jpg

 

Template workaround for "When an Excel row is created", "When an Excel row is modified", and/or "When an Excel row is deleted" triggers.

 

Limitations

• Initially only set up for Excel tables of less than 100,000 records and less than 100MB contents. Version b allows for more than 100,000 records, but still has a 100MB contents limit.

• Each record must be unique. So each record must have a column or combination of columns that is unique & not empty for every single row. In other words, it must include a primary key.

• Every time a new row is added, it must include the primary key column(s) value(s).

• For large tables, the initial set-up may have a 10+ minute delay between the last edits & running whatever actions one adds for the rest of the flow. Version b does include a set-up that is almost twice as fast, but even that will still see a several minute delay after the last edit when the selected Excel file is 10s of thousands of rows.

 

Example Run

Initial table

InitialTable.png

 

Table edits 

EditedTable.png

 

Flow run for the 50,000 record table 

AllTemplateFlowActionsRun.png

 

Example email message with HTML tables for each filtered set of records 

EmailTablesOutput.png

HTML table styling: https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/ 

 

Template Flow Import & Set-Up 

 

Go to the bottom of the post, download the zip file, go to the page for your flows, and select the legacy import option

DownloadFlowZip.pngImportLegacy.png

 

Upload the import, change the connections, & select the import button

UploadImport.pngUploadImport2.pngSwitchConnections.pngSelectImport.png

 

Select the Open Flow link & delete the initial placeholder value compose action

FollowOpenFlowLink.pngDeletePlaceholder.png

 

Switch to your desktop, create a text file on your desktop, input something that follows a JSON structure, go to save the file, add a .json file extension while changing the Save as type to All files & preferably give the file a name that refers to the target Excel workbook name & table name

CreateTxtFile.pngInputSomethingFitStructureJSON.pngSaveAsJSONWithNaming.png

 

Go to the same SharePoint library as your Excel workbook with the Excel table you are creating the flow for & upload the new JSON file. Make sure it is set up in a place where no one else will delete or alter it.

UploadSP.pngUploadJSONFile.png

 

In the list for the SharePoint library select the Add column option, select the Show or hide columns option, then select to show the ID column & Apply the changes. Once the ID column is showing, go to the row with the Excel workbook you are creating this for & copy the file row ID.

ShowColumnID.pngShowColumnID2.pngGetFileID.png

 

Switch back to the flow. Open the 1st trigger action "When an item or file is modified" & choose the site & document library for your Excel file.

SetTriggerSite.png

 

Then go to the 3 dots on the 1st trigger action "When an item or file is modified", go to the Settings, select the +Add button under Trigger Conditions, input the ID copied for the file row into the expression & paste the expression into the Trigger Conditions & select the Done button

TriggerSettings.pngSetTriggerCondition.png

 

@equals(triggerOutputs()?['body/ID'], InsertIdNumberHere)

 

 

Open the 1st Scope of the flow "Only let the flow run if all users stopped editing for a set time", Open the "Get file properties" action, and select the Site address & SharePoint Library Name for your Excel workbook

SetGetFile1.png

 

Then open the "Settings" action & input the name(s) of the column(s) that form a unique key for every row. So the names for the column or combination of columns with values that are unique for every row in the table

SettingsCorrection.png

 

Then go to the Scope below that "Read the Excel table and only let the flow run if no edits during read" and in the Excel List rows & Get file properties 2 actions select the Excel Location, Document Library, File, Table, Site Address, & Library Name

SetExcelAndGetFile2.png 

If you are using version b, then you will need to input the Excel file & table references in two places:

VbExcelConnectChange.png

 

Then go to the next Scope "Load previous table version copy before updating to the most recent table copy" and in the Get file & Update file actions select the Site Address, File Path, Site Address, & Site Address for the JSON file you loaded into the SharePoint library. This will hold a JSON array copy of the Excel table for the flow runs.

SetPrevTableVersJsonFileInputs.png

 

Save & run the flow once so any existing data in your Excel table is recorded in the previous table version JSON file. This will prevent any later actions you add from running for every existing row the 1st time the flow is triggered. By running it once without added actions, it will correctly run your added actions for only the edited rows the 1st time it is triggered later.

 

 

That is all the set-up for the template that will run whenever anything in the Excel workbook is modified and that will output different sets (arrays) of the Created records, Modified records & Deleted records. From here you can use the example Apply to each loops & example compose action inputs to set up what actions you want the flow to run for newly created records, for modified records, and/or for deleted records. In the example Apply to each loops the From field is filled with the array Body of the appropriate filter array action for the Created, Modified, or Deleted records and within the loop you can reference any of the record column values with the provided expression by inputting your chosen Apply to each loop name & inputting your chosen column name.

AddActionsAndUseExpressionsForReferences.png

 

items('Insert_Loop_Name_Here')?['InsertColumnNameHere']

 

 

Also if you do not plan to ever use one of the Created, Modified, or Deleted outputs, then you can delete both the filter array action for those records & the associated Apply to each loop

IfNotNeededCanDelete.png

 

And if you only want the flow actions to run when changes are made to specific columns, you can set the Excel List rows table read to only Select the primary key column & those specific columns to check for changes as described here.

 

 

Version B:

Version B replaces the single 100,000 pagination Excel List rows connector with a bit more complicated set-up that determines a rough approximation of the total rows in the table, then loads several 5000 row batches from the table with concurrency/in parallel and then combines all those 5000 row batches into a single JSON array output similar to the output of the standard 100,000 pagination Excel list rows connector.

Reasons to use Version B:

If you need a faster flow with less time between the last Excel table edits & the triggering of flow actions for those edits.
If you want to use this on an Excel table with more than 100,000 rows that is still below 100MB total content.
If you have a lower level Microsoft / Office365 license and only have a maximum pagination / Excel connector load of 5000.

 

 

Thanks for any feedback,

Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).

And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.

​​​​​​​Also if you find my free builds/templates helpful, note they are often a by-product of work I do with a USAID contractor.



If you have trouble importing through the legacy import method, go to https://make.powerapps.com/ and import the Solution package.

​​​​​​​

Solution Zip Download Link: https://drive.google.com/file/d/16m1Ut07QPnzW6s9gdIqnjaCyDYTySYLF/view?usp=sharing

V1.1 Legacy Power Automate Import: https://drive.google.com/file/d/1SE2jKhv48myUPlPtFTRYysL841qUR5gV/view?usp=sharing

If you get a “pagination policy is not valid. Maximum 5000” error then your license is not high enough for v1.1, instead import v1.1b.

V1.1b Legacy Power Automate Import: https://drive.google.com/file/d/1_YDFzkf4QSAeloTv9_zxVuVQBAWlt8kf/view?usp=sharing

WhenAnExcelRowIsC...
WhenAnExcelRowIsC...
WhenanExcelrowisc...

Categories:

 Business Process Flows

Comments

  • CU23092223-0 Profile Picture CU23092223-0
    Posted at
    When an Excel row is created, modified, or deleted
    I'm sorry, I can't even manage to download the solution properly. My  download options here are not like the one in your video and downloading the google drive files is not giving me the correct files. Please help. 
     
  • CU23070701-5 Profile Picture CU23070701-5
    Posted at
    When an Excel row is created, modified, or deleted
    I keep get this message when at Update file Last table version, eventhough I have turn off require check out in Sharepoint library setting already.


    Action 'Update_file_Last_table_version' failed: The file https://rmhedge.sharepoint.com/sites/x/x/x/x.xlsx is locked for shared use by b [membership]. clientRequestId: da26767e-9e6d-4018-9807-3dd885741610 serviceRequestId: 2784b4a1-9016-4000-847e-8bed791a549b;2784b4a1-601e-4000-847e-8bb256d12392
  • CG-03070152-0 Profile Picture CG-03070152-0
    Posted at
    When an Excel row is created, modified, or deleted
    Thank you. I have been sorting out for week for this exact flow I needed to do.
  • CU03061300-0 Profile Picture CU03061300-0
    Posted at
    When an Excel row is created, modified, or deleted
    Hi,
     
    when i try to import I get this message:
     
    "Flow save failed with code 'DynamicOperationRequestClientFailure' and message 'The dynamic operation request to API 'sharepointonline' operation 'GetTable' failed with status code 'Unauthorized'. This may indicate invalid input parameters. Error response: { "error_description": "Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException' was thrown." }'."
     
    what should I do differently?
     
    Thanks, 
    Faustas
  • takolota1 Profile Picture takolota1 4,974 Moderator
    Posted at
    When an Excel row is created, modified, or deleted

    Had someone ask on YouTube how one might get a HTML table of the newly modified rows and a HTML table of those rows' previous values. Here is what I would set up...

    Screenshot 2024-07-12 155314.png

     

     

    Scope code to copy & paste into the My clipboard tab of the new action menu...

    {"id":"1c91bf25-4233-4f64-ba91-a27bb741a076","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-67c9f2b6-f805-4d6b-9c4c-337ddd32d2ec"}},"shared_sharepointonline_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-3439d40a-dd34-4a99-b552-cc2e960f1f4b"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope","operationDefinition":{"type":"Scope","actions":{"Select_ModifiedKeys":{"type":"Select","inputs":{"from":"@body('Modified_records')","select":"@concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']]))"},"runAfter":{},"description":"body('Modified_records') | concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']]))"},"Filter_array_ModifiedPreviousVersion":{"type":"Query","inputs":{"from":"@outputs('Last_table_version')","where":"@contains(body('Select_ModifiedKeys'), concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']])))"},"runAfter":{"Select_ModifiedKeys":["Succeeded"]},"description":"outputs('Last_table_version') | body('Select_ModifiedKeys') | concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']]))"},"Create_HTML_table":{"type":"Table","inputs":{"from":"@body('Filter_array_ModifiedPreviousVersion')","format":"HTML"},"runAfter":{"Filter_array_ModifiedPreviousVersion":["Succeeded"]},"description":"body('Filter_array_ModifiedPreviousVersion')"}},"runAfter":{"Modified_records":["Succeeded"]}}}
  • StevenGuard1 Profile Picture StevenGuard1
    Posted at
    When an Excel row is created, modified, or deleted

    Hi @DebarthaMitra, if you have access to the source excel sheet, you could create a couple of hidden columns that replicate the data you require, but with column names you can then use in your select filter query.

  • DebarthaMitra Profile Picture DebarthaMitra 10
    Posted at
    When an Excel row is created, modified, or deleted

    Hi @takolota thanks for your guidance, your logic worked for me. In my case, the flow needs to monitor two columns, and need to do one type of steps (for each column changes the steps will be different) for each column changes, any way I used your logic and worked for me. However, I cant use the select query option as some of the column names have space in between (such as, Assigned To etc) and (unfortunately, the stakeholders will not change the names) as you know we cant use these names (with space in between) in select query. Anyway thanks a lot for your suggestion.

  • takolota1 Profile Picture takolota1 4,974 Moderator
    Posted at
    When an Excel row is created, modified, or deleted

    @DebarthaMitra 
    You may also be able to narrow things down to only trigger something when a select column is modified by having the initial List rows action only Select the primary key column(s) and that chosen column you want to monitor

    ExampleSelect.png

  • takolota1 Profile Picture takolota1 4,974 Moderator
    Posted at
    When an Excel row is created, modified, or deleted

    If you use "Is not equal to" in the condition like in the picture, then you can put any actions you want to trigger on that column change in the green Yes side

  • takolota1 Profile Picture takolota1 4,974 Moderator
    Posted at
    When an Excel row is created, modified, or deleted

    @DebarthaMitra 

    To narrow it down to only perform some actions if a specific column has changed you will likely need to use a Filter array on the Last table version to find the record with the matching primary key, then use a condition action to check if the column from the last table version is not equal to the column from the newest table version.

     

    Here is something you can copy to clipboard, then go to a new action inside the Apply to each Modified loop & cntrl+V paste into the My clipboard tab so you can select it to insert it into the flow. It uses "YourColumnName" as the example column you are checking, so you will need to change the references in the Condition expressions from ?['YourColumnName'] to use the column you want to check, like for a column with the name Email, you would use ?['Email']

    {"id":"0edc46d5-ea93-4a32-af26-07ee2509fa28","brandColor":"#8C3900","connectionReferences":{"shared_excelonlinebusiness":{"connection":{"id":"/new_sharedexcelonlinebusiness_84336"}},"shared_sharepointonline_1":{"connection":{"id":"/new_sharedsharepointonline_a8977"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Copy_Scope","operationDefinition":{"type":"Scope","actions":{"Filter_array":{"type":"Query","inputs":{"from":"@outputs('Last_table_version')","where":"@equals(concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']])), concat(items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName']], if(empty(items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName2']]), '', items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName2']])))"},"runAfter":{}},"Condition":{"type":"If","expression":{"not":{"equals":["@first(body('Filter_array'))?['YourColumnName']","@items('Apply_to_each_Modified')?['YourColumnName']"]}},"actions":{},"runAfter":{"Filter_array":["Succeeded"]}}},"runAfter":{"Modified_referencing":["Succeeded"]}}}

     

    ExampleOnlyModColumn.png

    Filter array
From
outputs('Last_table_version')

concat(item()?[outputs('Settings')?['KeyColumnName']], if(empty(item()?[outputs('Settings')?['KeyColumnName2']]), '', item()?[outputs('Settings')?['KeyColumnName2']]))
Is equal to
concat(items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName']], if(empty(items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName2']]), '', items('Apply_to_each_Modified')?[outputs('Settings')?['KeyColumnName2']]))


Condition
first(body('Filter_array'))?['YourColumnName']
Is equal to
items('Apply_to_each_Modified')?['YourColumnName']