You should try the SharePointOnlineConnector transformer.

Hi @caracadrian​ 

The SharePointOnlineConnector allows users to Upload, List, Delete and Download data from SharePoint, but doesn't let you search.

For some additional background info, the SharePointOnlineConnector uses the MS Graph API to interact with SharePoint which as far as I can tell doesn't support searching SharePoint. In order to search, this needs to be done with the SharePoint API, which requires a different web service (to the SharePointOnlineConnector) that can be used in the HTTPCaller.

@Anh Phuong Tran​ 

I believe what you're trying to do is possible, and I made a rough video of the steps as it got a little wordy. You should be able to watch it here: https://youtu.be/cvb5ktv9YDQ

1. Use a different web connection

There's a few different ways to interact with SharePoint in FME, and under the hood they are different . The SharePoint list reader uses the SharePoint API, and the SharePointOnlineConnector (SPOC) uses the MS Graph API. What you've tried so far is to use the SPOC web connection in the HTTPCaller. During the set up of this web connection/service, you'll be giving the app permissions to some SharePoint functionality through the Graph API. What you need to do is use the SharePoint (Template) web connection in FME, the one that is designed for the SharePoint List reader.

2. Azure Portal / SharePoint API Permissions

In Azure Portal, you will need to make sure this app has the Sites.Search.All permission.

When you test the web service in FME, and create the new web connection, you should verify that you see it asking for permission to search.

3. Change the HTTP call

I was looking at this page in the SharePoint API doc, and their example call was this:

GET http://{server}/_api/search/query?querytext='sharepoint'

I changed the HTTPCaller to be a GET request and enclosed my search value in single quotes ' '. I also used the new web connection for authentication. That gave me an XML response so I think that might be what you're after.

Hi @jlutherthomas​ ,

The client sent me the ClientID and I was following your video to configure the Web Connection. However I encountered this problem:

I did a bit of research, and found this post:

https://stackoverflow.com/questions/52046967/application-is-not-supported-over-the-common-or-consumers-endpoints-please-us

This error is usually caused by an incompatibility between your app registration and the authentication library you are using.

The code in that sample is using the Microsoft Authentication Library (MSAL), which uses the Azure V2 OAuth endpoints, which supports converged auth (both Azure AD accounts and Microsoft accounts). In order for the v2 auth endpoints to work, your app registration MUST come from https://apps.dev.microsoft.com.

If you register your app on the Azure portal (https://portal.azure.com), you'll see this error. That's because the Azure portal registers the app using the Azure v1 OAuth schema.

There is also a case where the https://apps.dev.microsoft.com portal can create a v1 registration. If you login to that portal and you see more than one grouping of apps, with multiple "Add an app" buttons, you need to choose the "Add an app" button for Converged Apps.

So, I'm wondering if you registered the app with Azure or apps.dev.microsoft.com?

Or it could be something else?

Thanks

@Anh Phuong Tran​ 

I made the app in Azure Portal which is the right way to do it. 

Logging into apps.dev.microsoft.com I get this warning:

Application registrations portal is no longer available to register and manage converged applications. We recommend that ​you manage your existing applications and register new applications by using the App registrations (now Generally Available) experience in the Azure portal. ​

I think this is an easy fix at least. In Azure Portal, App Registrations, Overview page, you should see a Directory (tenant) ID under the Application (client) ID. You'll need to edit the web service in FME and take this ID and replace all of the 'common' parts of the url and replace it with the Directory/tenant ID. For example, https://login.microsoftonline.com/common/oauth2/v2.0/token becomes https://login.microsoftonline.com/TENANTID/oauth2/v2.0/token

This article should guide through the steps.

Application registrations portal is no longer available to register and manage converged applications. We recommend that ​you manage your existing applications and register new applications by using the App registrations (now Generally Available) experience in the Azure portal. ​

Thank you @jlutherthomas​ , I put the TENANTID and followed the article and I was able to advance, but then I had a problem with the Redirect URI not configured. 

I then asked the client to configure the Redirect URI in Mobile and Desktop sharepoint's application configuration: 

https://login.microsoftonline.com/TENANTID/oauth2/nativeclient

Now it works.

Thank you very much

One last question @jlutherthomas​, my client is using FME Server 2019 with FME Desktop 2019. It seems the Microsoft Sharepoint Online (Template) only exist in FME 2020.2. I also tried to export the Web service and import that in FME Desktop 2019 but there is always an error saying it is related to password.

Do I have to ask my client to upgrade to FME 2020.2 to use this Web Service, or is there a workaround to import it into FME 2019? Thanks

@Anh Phuong Tran​ 

That will be due to the SharePoint authentication changing. In 2019 the web connection had to send a client secret (there's no optional button) which causes the sharepoint authentication to fail (because microsoft will reject it if it sends a client secret). The SharePoint List Reader (which we're adapting the web connection for in 2020+) doesn't use web connection authentication in 2019.

If they can't upgrade to 2020+, then they may have success creating a new web service from the MS OneDrive (Graph) connection:They will then need to:

• Set the Authorization URL to 

https://safesoftware.sharepoint.com/.default" target="_blank">https://login.microsoftonline.com/common/oauth2/v2.0/authorize?scope=https://safesoftware.sharepoint.com/.default offline_access&response_type=code&prompt=consent

• Replace all values of 'common' with their TENANT ID
• Make sure the app in Azure Portal is registered as a Web application 
  • In AP > Authentication > Add a platform > Web
  • redirect uri http://localhost/ (or change it to whatever, as long as it matches in AP and FME)
• Create a client secret and paste it into FME
  • The client secret is only visible when first created, be sure to copy the secret value (I've seen lots of people copy the ID instead) 

In summary, they need to create a new oauth connection/service in FME 2019 that can send a client secret (that's why it has to be web platform in Azure Portal). Copying the OneDrive (Graph) web service is an easy starting point, but it could be done from scratch too.