Extract a list of all files in One drive into Excel

Dear @Bob Menlove,

Good day, and thank you for reaching out to Q&A Forum. Based on your requirement, there are a few approaches available, but one of the simplest and most reliable methods is to use OneDrive sync together with Power Query in Excel.

First, ensure your OneDrive is synced to your computer. You can do this by selecting the OneDrive cloud icon in the system tray, opening Settings, and confirming that the folders you want are selected for sync.

Once that is set up, open Excel and go to Data > Get Data > From File > From Folder, then browse to your local OneDrive folder (typically under C:\Users\YourName\OneDrive or OneDrive – Company Name). After selecting the folder, choose Transform Data.

In Power Query, you will see columns such as Content, Name, Extension, Folder Path, Date modified, ... From here, you can remove any unnecessary columns (click the column header > Remove Columns) and rename the remaining ones to suit your needs, for example:

• Name > File Name
• Folder Path > Path
• Date modified > LastModified DateTime

If required, you may add additional columns (such as an ID), although that may involve a slightly different method.

Click Close & Load to put the list into an Excel sheet. This gives you a dynamic list, you can refresh it anytime (Data > Refresh All) to get the latest files.

I hope this information is helpful. Please try the above approach and let me know how it works on your side. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Use Power Automate with the OneDrive for Business and Excel Online (Business) connectors to list files and write them into an Excel table.

High-level approach:

1. Create an Excel file in OneDrive for Business with a formatted table that has the columns:
   • File Name
   • Path
   • Id
   • LastModified DateTime
   • Relevant
2. Build a flow that:
   • Lists files from OneDrive for Business.
   • Loops through each file.
   • Adds a row into the Excel table for each file.

Detailed steps:

1. Prepare the Excel table
   1. In Excel, create a new workbook and save it in OneDrive for Business.
   2. On the first row, enter the headers: File Name, Path, Id, LastModified DateTime, Relevant.
   3. Select the header row and some rows below, then format as a table (Home → Format as Table) and ensure “My table has headers” is checked.
   4. Save and close the workbook.
2. Create the flow
   1. Go to Power Automate and create a new Instant cloud flow or Scheduled cloud flow.
   2. Add a trigger (for example, Manually trigger a flow or a schedule).
3. List files in OneDrive
   1. Add a new step: OneDrive for Business → List files in folder.
   2. Set Folder to the OneDrive folder to inventory (for example, / for root or a specific folder path).
4. Loop through files
   1. When List files in folder is added, Power Automate will wrap subsequent actions in an Apply to each loop using the file list.
5. Add rows to Excel
   1. Inside the Apply to each, add Excel Online (Business) → Add a row into a table.
   2. Configure:
      • Location: OneDrive for Business.
      • Document Library: OneDrive.
      • File: Browse to the Excel file created earlier.
      • Table: Select the table created in the workbook.
   3. Map dynamic content from List files in folder:
      • File Name → Name.
      • Path → Path or a combination of folder path and name, depending on what the connector exposes.
      • Id → Id.
      • LastModified DateTime → Last modified (or similar field exposed by the connector).
      • Relevant → leave blank or set a default value.
6. Save and test
   1. Save the flow.
   2. Run it (Test → Manually or via the trigger).
   3. Open the Excel file to confirm that each file in the OneDrive folder appears as a row.

If the issue is specifically that the Excel table dropdown is not loading in the Add a row into a table action (table-level problem), it can be due to dropdown timeouts when there are many items. In that case:

• Manually type the table name instead of selecting it from the dropdown.
• Ensure the correct connector is used (OneDrive for Business for business accounts, OneDrive for personal accounts) and that the file is not too deeply nested or in a folder with an extremely large number of items.

For scenarios where the Excel connector doesn’t list tables or fails to retrieve values, ensure:

• The correct OneDrive connector is used (OneDrive vs OneDrive for Business).
• OneDrive IP restrictions are not blocking access.
• If the table picker works but the flow fails at runtime, recreate the flow from scratch and test again.

References:

• Run a script on all Excel files in a folder
• Convert CSV files to Excel workbooks
• Fail to list Excel Online tables retrieving values
• Dropdown list doesn't load items in a trigger or an action in Microsoft Flow
• Use the ID reader prebuilt model in Power Automate
• How to use macro-enabled files in Power Automate flows
• Get started with Power Automate in Windows 11