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.
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.