Record Date in Excel with File Upload

[MGS2023]

I currently have an automation to create a file in One Drive for Business. How can I add to this flow so that when a file is created in One Drive PowerAutomate will match the name of the file in a specified Excel spreadsheet row and then add the date in the next blank cell in that row so I will see all the dates that a file was created?

[rzaneti]

Hi @MGS2023 ,

If your business rule allow, I would recommend you to store your Excel data in a table format, as Power Automate integrates with tables in an easier way than with unformatted spreadsheets - you can read/update data easily and with a smaller chance to get errors. If you cannot format your data as table, it is still possible, but you may need to use Office Scripts for that (a kind of VBA for Excel online, that can be connected to Power Automate).

Let me know which of the approaches above fits better to your case and I will help you to build this flow. 

[MGS2023]

Yes, I can do this in a table. I would appreciate the assistance with building this flow. Thank you!

[rzaneti]

Hi @MGS2023 ,

Perfect! So the flow will be pretty simple 🙂

This is my table to store the files. You can add other properties if you want:

As you can see, we have a list of file names with extension (if you prefer, we can do some changes to ignore the extension) and, based on the file column, the 'Created at' will be updated once we create a file with the same naem and extension.

All files created in this folder are being tracked:

Step 1 - Selecting the trigger:

Create an 'automated flow' and select the 'When a file is created' trigger. In the input field, set the folder that you want to track. 

Step 2 - Get the file metadata:

The trigger returns some information about the new file, but some very basic data, like the created/modified date, is not displayed. So you will need to add a new action just for that (technically you can do this with different approaches, but this one is a good practice, especially if you want to track other properties or the accurate created time in the future). 

So add a 'Get file metadata using path' action and, as input, insert the dynamic content from the 'File path' from your trigger.

Step 3 - Update Excel file:

Finally, add an 'Update a row' action, from Excel connector and fill the details about your Excel file and the table name. This action will ask you about a 'Key column' and a 'Key value' (highlighted in blue below).  The 'Key' here is the 'identifier' in your Excel table to isolate the row to be updated, which in this case is the 'file' column (we are "looking up" by file name and updating the field at right from it).

So set the column that is storing your file name as 'key column' and, as 'key value', add the 'File name' dynamic content from the trigger (highlighted in yellow below).

You may also notice two non required fields that match to your table column names (highlighted in pink). In these fields, you may insert the values to be updated. As we want only to update the 'Created at', include the following expression in the field: formatDateTime(outputs('Get_file_metadata_using_path')?['body/LastModified'], 'yyyy-MM-dd'), replacing the red text by the dynamic content from the 'Last modified' dynamic content from your 'Get file metadata using path' action.

To add expressions, you just need to click in the input field (highlighted in pink below), select the 'Expressions' tab in the pop up (highlighted in blue below), type the expression in the 'Fx' bar (highlighted in yellow below) and click 'Ok':

Note: If you have more than one identical 'Key value' in your 'Key column', only the first will be updated. It is possible to update all matching records by using other strategies. You can check more details about how to write/update records in Excel Tables via Power Automate in this article: http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/

Output:

To test the flow, I created a new file with a name matching the list in the table:

The flow ran fine:

And this is the result: 

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/