cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeppoo1
New Member

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

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):

1 http.PNG

 

 

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

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

2 list folder.PNG

 

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.)

 

1 REPLY 1
jeppoo1
New Member

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):

3 ADF parameters.PNG

 

*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:

3 adf webhook.PNG

 

 

 

 

 

 

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.
4 list folder.PNG

 

 

 

d) Next part is the forEach loop + Upload File
 
5 foreach and upload file.PNG

 

 

e) Finally, Callback to ADF is simply:
6 callback to ADF.PNG

 

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,302)