I am trying to connect to an excel file in sharepoint in office 365. However, when I enter the address in Sharepoint connector in Alteryx Designer, I am getting an error "Root not found". How can I connect to an excel file on Sharepoint?
Hi @balajilolla1,
Yes, you can connect to Excel files in SharePoint. To find the filepath, open an Internet Explorer browser - it must be Microsoft Internet Explorer, this does not work in Chrome or any other browser. Navigate to the Sharepoint folder that contains your file. Under All Documents, select "View in File Explorer" (this will not be available in any browser other than Internet Explorer). This will open Windows Explorer. In the filepath it will show something like https://companyname.sharepoint.com > site > subsite > folder > subfolder. Your path may be different depending on your company's configuration, but the syntax will be similar. The path for the Input Data tool will be:
//companyname.sharepoint.com/site/subsite/folder/subfolder.../filename.xlsx (dropping "https:" and adding a "/" in place of the " > "
It may be easier to copy the path from Windows Explorer and clean up the path in the Input Data tool.
To get the workflow to connect, you may need to be actively logged into your company's SharePoint site any time you run the workflow, especially if your company uses Single Sign On (very common). Once it connects you should be able to select the sheet and input your data.
Thank you T_Willins - saving the day once again!
Thank you @T_Wilins. That worked
Thanks for that detailed description @T_Willins.
Following the instructions I get the following error message:
"Cannot access the folder xxx"
Any ideas what creates that issue?
Thanks,
Sonja
API Download Solution:
Syncing Sharepoint excel files to your one drive can be a good option but for that your one drive has to be synched before workflow runs and for that it has to be ON for syncing. I was able to develop a workflow to download sharepoint excel files using API download process..
1. You need to first go to sharepoint site and generate client ID, client secret and tenant ID details to generate Authorization details Access token, Bearer). Follow this link for more details: (80) How To Test SharePoint Online REST API Using Postman - YouTube.
2. Then Add these details along with sharepoint site, folder, file details and command to download as shown in below screenshot.
Workflow steps:
3. Convert column and value columns into different columns (header: column fields, 1st row: value fields) with cross tab
4. First filter:
5. and 2nd filer:
6. First API Download, generate Access token and Bearer (Using json parsing)
7. Once authenticated, you can download list of files with second api call with below command
GET https://{site_url}/_api/web/GetFolderByServerRelativeUrl('/Folder Name')/Files (using xml parsing)
8. then third api call will be to download actual file to your local and then opening with Dynamic input for further processing.
file download command:
GET https://{site_url}/_api/web/GetFolderByServerRelativeUrl('/Folder Name')/Files('{file_name}')/$value
reference for sharepoint file commands: Working with folders and files with REST | Microsoft Docs
Can you share this workflow?
I was able to get it to work in Alteryx. I still don't understand why it doesn't work in Postman but it works like a dream in Alteryx. I just had to update the resource information. CharudattaChousalkar thank you for all your help and for designing the process in the first place !
