Adding a SharePoint site as a data source

  • 19 September 2023
  • 6 replies
  • 551 views

Userlevel 3
Badge

This article clarifies two different methods of adding a SharePoint site as a data source in the TimeXtender Portal.

Method 1: Basic Authentication

This method specifies a user account that does not have MFA two factor authentication enabled and has been granted access to the SharePoint site. The configuration in the TimeXtender Portal for the SharePoint data source is as follows:

  1. Provider: Microsoft SharePoint
  2. Auth Scheme: Basic
  3. Share Point Edition: SharePoint Online
  4. URL: <SharePoint Site URL> i.e. https://contoso.sharepoint.com/sites/PBI
  5. User: <username> of a user that has been granted access to the site.
  6. Password: <password> for the specified user.

Method 2: OAuth

This method uses an Azure App Registration and OAuth to connect to the specified SharePoint site. 

  1. In the TimeXtender Portal, create a new data source and set the provider to be “Microsoft SharePoint”.
  2. Auth Scheme: “SharePointOAuth”.
  3. Share Point Edition: SharePoint Online
  4. URL: <SharePoint Site URL> i.e. https://contoso.sharepoint.com/sites/PBI
  5. Azure Tenant: <Azure Tenant ID> for your SharePoint site.

     

  6. In the Azure Portal, create or open the App Registration. From the Overview page, copy the Application (client) ID and paste this into the “OAuth Client ID” field in the OAuth Section of the SharePoint data source in the TimeXtender Portal.
  7. From the “Certficates and Secrets” page copy the value for your secret and then paste the secret value into the “OAuth Client Secret” field in the OAuth Section of the SharePoint data source in the TimeXtender Portal.
  8. In the App Registration “Authentication” page, set the Web Redirect URL to http://localhost:33333. Paste the “Web Redirect URL” into the “Call Back URL” field in the OAuth Section of the SharePoint data source in the TimeXtender Portal.
  9. In the TimeXtender Portal, set the “Initiate OAuth” to GetAndRefresh.
  10. Set the “OAuth Grant Type” equal to Code.
  11. Enter a path and filename in the “OAuth Settings Location”
  12. Set the Schema to “REST”.

     

  13. Click “Save” to save this new SharePoint data source.
  14. In the TimeXtender Portal, open an ODX instance and add this new data source to the “Mapped Data Sources” section.
  15. Open the TimeXtender Desktop, and double click on the ODX instance that contains the data source.
  16. In the ODX pane, right-click on “Data Sources” and select “Add Data Source”.

     

  17. Complete the fields to create Synchronization and Transfer tasks for the new SharePoint data source.
  18. Right-click on the SharePoint data source and select “Edit Data Source”.

     

  19. Click on the “Authorize OAuth” button to create the OAuth settings file.
  20. Go back to the portal and edit the data source. Locate the Initiate OAuth field and change it to REFRESH. Click Save.

6 replies

Userlevel 2
Badge +1

To anyone that is going to set up a data source with method 2 (in my case SharePoint Online): if you forget step 12 (Set the Schema to “REST”.) you will run into this error when clickin on ‘Authorize OAuth’:

 

 

So make sure to set it to “REST”. For more specifics, please review my post on the community.

Userlevel 4
Badge +1

Thanks @Greg Lennox 

 

Where you say the file types to retrieve. Is it the same setup for Excel files or CSV files?

Userlevel 6
Badge +5

To anyone that is going to set up a data source with method 2 (in my case SharePoint Online): if you forget step 12 (Set the Schema to “REST”.) you will run into this error when clickin on ‘Authorize OAuth’:

 

 

So make sure to set it to “REST”. For more specifics, please review my post on the community.

Hi @KajEmergo 
The Auth Scheme field is also important for this. It does not have to be set to OAuthSharePoint, OAuth is also valid, but if you set it to none and the Schema field to REST you will get the same error.

 

Userlevel 6
Badge +5

Hi @rvgfox 

The SharePoint provider can’t give the files stored there, the only thing it gives is Meta data about what is stored in it and Lists.

So you want to use the provider to get SharePoint lists like this.

If you want to connect to the files stored in the SharePoint, you will need to use the specific provider for that file type.

See the CSV and Excel guides default and online.

Badge +1

@Thomas Lind  The tutorial describes entering the Sharepoint URL in the URL field. This which will give you the meta data for the Sharepoint site:

What’s unclear to me is what changes I need to make in the CData connector to fetch a specific list, like the PBIlist you made above. Should I enter the URL of the list instead of the Sharepoint site URL? Including or excluding “.aspx”? Including it leads to an error with me, excluding it synchs, but doesn’t show the list as a table.

Userlevel 6
Badge +5

Hi @rogier.helmus 

You can’t add anything other than this and make it work, no aspx or ids or anything like that, it newer works.

If the lists are on a specific site, you need to point to that and only the https://company.sharepoint/site/sitename. That is the only method that is shown in this guide.

So you do not add anything besides the main part, in our case https://timextender.sharepoint.com if your list isn’t on some site.

You can’t make it see the lists if it is on some site while pointing at the main page. Even if you can see the site in the SubSites table.

Reply