Azure Logic App - List Folder - "message": "The response is not in a JSON format."

[jeppoo1]

Hello,

I'm building an Azure Logic App that retrieves Excel files from a SharePoint folder. The Logic App is triggered through Azure Data Factory Webhook, and the aim is to parametrize the Logic App so that it can be used to retrieve Excel files from several subfolders. These subfolders are selected in the Azure Data Factory.

I have a problem in the List Folder part of my Logic App.

1) First, the HTTP Request works fine. It takes its parameters from ADF (e.g. the data lake and connects to SharePoint):

2) The problem is in the next step, List Folder. I passed it the following:

*Site Address: ​sharePointSite
​*File Identifier: ​sharePointFilePath

This is the error code:

{

  "error": {

    "code": 404,

.........

    "message": "The response is not in a JSON format.",

    "innerError": "The resource you are looking for has been removed, had its name changed, or is temporarily unavailable."

  }

Sometimes, after I run the Logic App, it puts in its input something like

/datasets//folders + the path to my SharePoint folder, and I have no idea where the datasets/... came from. And this will end in the following status code:

{

  "statusCode": 404,

  "message": "Resource not found"

}

Does anyone know what I am doing wrong? How can I get the logic app to work and retrieve every Excel file in a given SharePoint folder? And finally parametrize the Logic App so that I don't have hard code the paths to my SharePoint folders?

(The next part in my Logic App is a forEach loop but it has usually worked relatively fine so I'm concentrating only on the first two steps shown here.)

[jeppoo1]

To answer my own question, I already solved most of the problem with my colleague:

a) These are the parameters to use in the ADF (click the ADF Pipeline canvas to access the parameters):

*One trick was to point to the sharePointFolder in this parameter shown above, AND use %2F instead of / (slashes) and %20 instead of a space, in the url address. This "modified" url can simply be retrieved from the SharePoint site url from your browser. For that SharePoint folder where your Excel files are in, use the exact url found in your browser, and don't use slashes here!

*Remember to modify the ADF Webhook --> Settings:
--> URL: find this in your Logic App -> Properties -> Access Endpoint
--> Method: POST
--> Body includes this dynamic content below:

b) The HTTP Request must be modified to include those ADF parameters shown in a):

{

  "properties": {

    "SharePointFolder": {

      "type": "string"

    },

    "callbackUri": {

      "type": "string"

    },

    "datalakeAccount": {

      "type": "string"

    },

    "datalakePath": {

      "type": "string"

    },

    "sharePointSite": {

      "type": "string"

    }

  },

  "type": "object"

}

This way the Logic App List Folder part is able to find the correct folder. 

c) The List Folder step now includes sharePointSite (as the Site Address) and SharePointFolder (as the File Identifier), both as dynamic content.

d) Next part is the forEach loop + Upload File

e) Finally, Callback to ADF is simply:

f) Run your ADF pipeline and check if the Logic App has succeeded. If it has, add a Copy Data step in your Pipeline and convert your Excels to CSV or do whatever you need to do with them.