cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darshanas
Frequent Visitor

Power Automate Flow ERROR: Pagination/Limits/Offset/HTTP Get Request/Exporting to Excel

Hi, I'm trying to export users from PagerDuty to Excel. There are many users so I need to use pagination - I'm doing this by providing the limit=100 and offset=0, offset=100 and so on in the URL. I have created an array and a loop that will iterate over the offset values so it exports the first 100 users, then the next 100 and so on. I'm parsing JSON and then storing some of the user details like name, email, timezone and role in an Excel file.

 

However, when I run my flow, I'm getting the following error: ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['users']' is of type 'Null'. The result must be a valid array.

 

I'm not sure if the order of my flow is wrong or if I'm missing any steps (I'm relatively new to Power Automate). Could someone please help me resolve this issue?

 

I've attached screenshots for reference.

 

Thanks!

 

1.png2.pngScreen Shot 2022-04-15 at 1.23.38 am.pngScreen Shot 2022-04-15 at 1.23.51 am.png

3 REPLIES 3
jdoss
Resolver III
Resolver III

Can you show what parsejson is returning? Or at least the schema? You are looking for the body of the body of parse json so I think that is where your error lies, but would need to see the schema to know for sure.

Yes, I've pasted the Parse JSON schema below

 

{
"type": "object",
"properties": {
"statusCode": {
"type": "integer"
},
"headers": {
"type": "object",
"properties": {
"Connection": {
"type": "string"
},
"Access-Control-Allow-Origin": {
"type": "string"
},
"Vary": {
"type": "string"
},
"Referrer-Policy": {
"type": "string"
},
"Access-Control-Allow-Methods": {
"type": "string"
},
"Access-Control-Max-Age": {
"type": "string"
},
"Access-Control-Allow-Headers": {
"type": "string"
},
"Access-Control-Expose-Headers": {
"type": "string"
},
"X-Request-Id": {
"type": "string"
},
"Strict-Transport-Security": {
"type": "string"
},
"X-Content-Type-Options": {
"type": "string"
},
"X-XSS-Protection": {
"type": "string"
},
"Feature-Policy": {
"type": "string"
},
"Cache-Control": {
"type": "string"
},
"Date": {
"type": "string"
},
"ETag": {
"type": "string"
},
"Server": {
"type": "string"
},
"Content-Type": {
"type": "string"
},
"Content-Length": {
"type": "string"
}
}
},
"body": {
"type": "object",
"properties": {
"users": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"email": {
"type": "string"
},
"time_zone": {
"type": "string"
},
"color": {
"type": "string"
},
"avatar_url": {
"type": "string"
},
"billed": {
"type": "boolean"
},
"role": {
"type": "string"
},
"description": {},
"invitation_sent": {
"type": "boolean"
},
"job_title": {
"type": "string"
},
"teams": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"type": {
"type": "string"
},
"summary": {
"type": "string"
},
"self": {
"type": "string"
},
"html_url": {
"type": "string"
}
},
"required": [
"id",
"type",
"summary",
"self",
"html_url"
]
}
},
"contact_methods": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"type": {
"type": "string"
},
"summary": {
"type": "string"
},
"self": {
"type": "string"
},
"html_url": {}
},
"required": [
"id",
"type",
"summary",
"self",
"html_url"
]
}
},
"notification_rules": {
"type": "array",
"items": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"type": {
"type": "string"
},
"summary": {
"type": "string"
},
"self": {
"type": "string"
},
"html_url": {}
},
"required": [
"id",
"type",
"summary",
"self",
"html_url"
]
}
},
"coordinated_incidents": {
"type": "array"
},
"id": {
"type": "string"
},
"type": {
"type": "string"
},
"summary": {
"type": "string"
},
"self": {
"type": "string"
},
"html_url": {
"type": "string"
}
},
"required": [
"name",
"email",
"time_zone",
"color",
"avatar_url",
"billed",
"role",
"description",
"invitation_sent",
"job_title",
"teams",
"contact_methods",
"notification_rules",
"coordinated_incidents",
"id",
"type",
"summary",
"self",
"html_url"
]
}
},
"limit": {
"type": "integer"
},
"offset": {
"type": "integer"
},
"total": {},
"more": {
"type": "boolean"
}
}
}
}
}
jdoss
Resolver III
Resolver III

Well since it's returning Null, I would say that either your http response is not returning values (which you probably checked in the flow run) or the schema is wrong (which you should be able to copy the http response body and use as a schema). 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (4,494)