Export Sharepoint Document Library items to Excel

Question

Here's my situation.

1. I have a SharePoint site with 30 document libraries.
2. I have created a folder-based content type to be filled with specific metadata.
3. Each library is populated with lots of these structures, which are filled with office documents.
4. I'm trying to find a way to export the metadata from those structures to an excel sheet for every one of the libraries.
5. I have access to this Flow tool, from Office 365, but it doesn't seem to help.

Any bright ideas?

Thank you and regards from Brazil!

Answer 1

Create a view that has all the metadata columns. Then use the Classic Sharepoint experience, and on the Library tab select "Export to Excel". That will generate a query.iqy file, that will be opened in Excel. It contains a query to the view, so if the files change in SharePoint, you can refresh the query to update the Excel file. Note that in large libraries this approach will run into an item limit. I believe it sits at somewhere around 20,000 or thereabouts.

Another option is to use Power Query, a free add-in from Microsoft for Excel 2010 and Excel 2013 and built into Excel 2016 as Get & Transform. Create a new query from Online Services > SharePoint Online List (or Other > SharePoint List, if your farm is on premises). Connect to the list and edit the query. On the ribbon click "Choose columns" and select the columns you want to see. Save the query and let it load to a sheet. You can refresh the query when the files in SharePoint are changed.