Forum Discussion
Automate - Merging Excel Files from Multiple Folders
To query the single file located in SharePoint connector is
Data->Get Data->From Other Sources->From Web
Enter file URL taken from File->Info->Copy path if to open the file, the only to remove ?web=1 at the end.
Alternatively file URL could be taken in SharePoint, at the bottom of Details pane for the file
To query the folder use Data->Get Data->From SharePoint Folder connector, enter root URL on the site with library, on next step filter on desired folder.
Not sure what exactly do you mean under merging of files. If all of them have the same structure, you may access them from parent folder using File->From SharePoint Folder connector. Filter the list as needed, remove unnecessary columns and combine files from Content column.
- Thank you for your reply-- I'm using Power Query to append sheets from multiple SharePoint folders to one file. The process has been a mess, and I don't think it's necessarily a fault in Power Query, but in SharePoint. Excel seems to be unable to access files in a SharePoint folder directly, but it can access them via OneDrive when they are synced.
Basically, Excel doesn't recognize the direct file path to the SharePoint folders.
My solution, if it helps anyone, has been to sync the SharePoint folder to One Drive, set up the Power Query to get data from the One Drive folders, and then grant access to the One Drive folders for any user who will need the final results. It's not pretty, and complicates manageability.To query the single file located in SharePoint connector is
Data->Get Data->From Other Sources->From Web
Enter file URL taken from File->Info->Copy path if to open the file, the only to remove ?web=1 at the end.
Alternatively file URL could be taken in SharePoint, at the bottom of Details pane for the file
To query the folder use Data->Get Data->From SharePoint Folder connector, enter root URL on the site with library, on next step filter on desired folder.
- SergeiBaklan
Thanks again for your advice. That eliminated the need to sync files and query OneDrive. I've set up the sheets to query directly from SmartSheet-- one per sub-channel-- and I will find out if the appending sheet can successfully update when the next batch comes in tomorrow morning.
