Call Sharepoint rest api from Azure Data Factory web activity

Question

Hi,
I am just getting started with Azure Data factory so go easy on me :-)

Hi I am trying to access sharepoint lists data in Azure Data factory. One of my lists has a multi-valued lookup columns so the out of the box connector wont return it,

I figured I would try to use the web activity to make a call to the rest api to get the data.

So in a pipeline i configured a web activity to get a token by calling out to https://accounts.accesscontrol.windows.net/ee780cdf-8eab-4e6f-a740-c31a2aede2c0/tokens/OAuth/2

That call works and i got a valid token).

In the next web activity i am trying to call out to https://russellwgove.sharepoint.com/sites/tr/us/_api/web/lists/getbytitle('End Uses')/items passing an Authorization header containing the token I got from the previous step (@{concat('Authorization: Bearer ', activity('GetAToken').output.access_token)})

This activity fails with error code 2108 and a Details message saying :
Error calling the endpoint 'https://russellwgove.sharepoint.com'. Response status code: 'NA - Unknown'. More details: Exception message: 'NA - Unknown [ClientSideException] The format of value 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSIsImtpZCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSJ9.eyJhdWQiOiIwMDAwMDA{
{...}
OCn2pBpSHi4h1-5yWPzkVfSRroGrGzUUM8pJqEE1kKGhj7q-TBVWN1VEhRtk_r-06ELOSJ0JQgPHgbYCN48ROP4wEI5KN7uW1jD74XZa9tblNe77dVuxw' is invalid.'.
Request didn't reach the server from the client. This could happen because of an underlying issue such as network connectivity, a DNS failure, a server certificate validation or a timeout.

The inputs to this activity are :
{
"url": "https://russellwgove.sharepoint.com/sites/tr/us/_api/web/lists/getbytitle('End Uses')/items",
"method": "GET",
"headers": {
"Authorization": "Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSIsImtpZCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSJ9.eyJhdWQiOiIwMDAwMDA.....
`OCn2pBpSHi4h1-5yWPzkVfSRroGrGzUUM8pJqEE1kKGhj7q-TBVWN1VEhRtk_r-06ELOSJ0JQgPHgbYCN48ROP4wEI5KN7uW1jD74XZa9tblNe77dVuxw",
"Accept": "application/json; odata=verbose"
},
"disableCertValidation": false,
"turnOffAsync": false
}

Anyone know what the issue is?

Answer 1

Hi @russellgove ,

According to my research and testing, please do some troubleshooting, perhaps it is caused by an expired or timeout t of Authorization, try to re-generate access_token. And then use the following Rest API to get SharePoint list items:

https://xxx.sharepoint.com/sites/sitename/_api/web/lists/getbytitle('a test')/items  

If you still get the error, try to generate the access_token by following the steps in this document, and then connect to SharePoint:
https://global-sharepoint.com/sharepoint-online/in-4-steps-access-sharepoint-online-data-using-postman-tool/

    {  
                // _spPageContextInfo.webAbsoluteUrl - will give absolute URL of the site where you are running the code.  
                // You can replace this with other site URL where you want to apply the function  
                // Optionally you can append "$top=10" to restrict the results to 'n' number of rows only.  
                // e.g. - url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('List Name')/items$top=10",  
                // "$select" can be used, if only a defined colmns need to be returned in result set  
                url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('List Name')/items?$select=Title,LinkFilename",  
                type: "GET",  
                headers:  
            {  
                // Accept header: Specifies the format for response data from the server.  
                "Accept": "application/json;odata=verbose"  
            },  
                success: function (data, status, xhr) {  
                    var dataresults = data.d.results;  
                    for (var i = 0; i < dataresults.length; i++) {  
                        alert(dataresults[i]["LinkFilename"]);  
                    }  
                },  
                error: function (xhr, status, error) {  
                    console.log("Failed");  
                }  
            }  

More information for reference: Get All Items in SharePoint using REST API

Hope it can help you. Thanks for your understanding.

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

try again with this syntax

"Authorization": "Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSIsImtpZCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSJ9.eyJhdWQiOiIwMDAwMDA..... `OCn2pBpSHi4h1-5yWPzkVfSRroGrGzUUM8pJqEE1kKGhj7q-TBVWN1VEhRtk_r-06ELOSJ0JQgPHgbYCN48ROP4wEI5KN7uW1jD74XZa9tblNe77dVuxw",

Answer 3

Hi @russellgove ,

I hope that you have already figured out this problem but if you haven't and wanted to know how to get this to work:
firstly ensure your endpoint is correct
secondly the syntax I used in synapse (which is pretty much exactly the same as ADF) is as follows:

@concat('Bearer ',activity('GetAToken')

As you can see it does successfully bring back web activity (in my case I want a siteid)