Power Automate - Pull in all forms into a MS List
Something that I have been playing around with more and more is the ability to make API calls from Power Automate. Going to start off by saying that this solution is not scalable in any way shape or form but it is something that I found very cool as I was working on my day to day job.
The fact is, that I make a lot of random forms that do a lot of random things while I am building forms for customers. Do this a couple 100 times and now you've got the messiest team imaginable.
Yes, I know this is an “Ian” problem and not a real use case but in a world of MANY formspaces and MANY forms (because we are using ProntoForms for everything ;) ) In comes our API to the rescue to make finding exactly what you need a little easier. We can use the API to build out a list of all the forms and formspaces that are in your team. I also have it running on a schedule daily.
Here is our amazing API documentation that will answer all your ProntoForms API questions: https://live.prontoforms.com/api-docs
API Key and Authorization Basic token
There are many different ways to authenticate your API requests. In all my examples I have created myself a Basic Authentication token to include in the headers of my requests.
You do this by using the parameter name “Authorization” taking your API Key and Secret and encoding them in Base64. There are quite a few tutorials on how to do this online and there are websites that can provide the Basic Auth token.
Create and update a List with all forms in a Team
Create a List
| Form Name |
Form ID |
Formspace Name |
Formspace ID |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
On the Form ID column, we will want to enforce Unique Values.
- Click on the carat next to Form ID, move to Column settings and select Edit
- Select More Options
- Change “Enforce unique values” to Yes
Create a Flow
- Navigate to Power Automate and Create a new flow.
- This one will be best set on a schedule since we will be updating the list of forms at a regular cadence, it really depends on how often you have new forms. In my example, I have it set to Daily.
- Once in the Flow, the first action to take is to make the API call. There is an action called HTTP, we will use that. This API call will return all the formspaces on a single page in your team. If there are more than 100 formspaces, this will only pull the first 100.
Method: GET
URI: https://api.prontoforms.com/api/1.1/formspaces
Headers: Authorization → Basic (this is where you would put your Basic Auth Token that was created on step 1)
- Next will be to Parse the response that comes back from the GET request. Add an action and use the “Parse JSON” action type.
For the Content, we will use the Body of the HTTP GET action.
Schema will be the following (this was automatically generated based on expected response):
{
"type": "object",
"properties": {
"totalNumberOfResults": {
"type": "integer"
},
"totalNumberOfPages": {
"type": "integer"
},
"zone": {},
"pageData": {
"type": "array",
"items": {
"type": "object",
"properties": {
"identifier": {
"type": "string"
},
"name": {
"type": "string"
},
"problemContactEmail": {},
"pushUpdatesToDevices": {
"type": "boolean"
}
},
"required": [
"identifier",
"name",
"problemContactEmail",
"pushUpdatesToDevices"
]
}
}
}
}
2. Next action will be another HTTP Action. Now we will need to pull all the forms in all of the formspaces.
Method: GET
URI: https://api.prontoforms.com/api/1.1/formspaces/{identifier}/forms (identifier relates to the identifier from the Parse JSON action)
Headers: Same Authorization and Basic token as previously.
- Once you indicate the {identifier}, the HTTP action will be automatically placed in a “Apply to each” condition.
- The next action in the “Apply to each” will be another “Parse JSON” action.
Content: Use the Body object from the second HTTP action.
Schema: Use the schema below (Auto-generated by Microsoft based on the expected response)
{
"type": "object",
"properties": {
"totalNumberOfResults": {
"type": "integer"
},
"totalNumberOfPages": {
"type": "integer"
},
"zone": {},
"pageData": {
"type": "array",
"items": {
"type": "object",
"properties": {
"identifier": {
"type": "string"
},
"asyncStatus": {},
"name": {
"type": "string"
},
"description": {
"type": [
"string",
"null"
]
},
"state": {
"type": "string"
},
"locked": {
"type": "boolean"
}
},
"required": [
"identifier",
"asyncStatus",
"name",
"state",
"locked"
]
}
}
}
}
2. Next will be a Sharepoint “Create Item” action. Point this to the List that we created in the first step.
Using the items from the “Parse JSON” actions in the previous steps.
Once you start to use the items from the previous “Parse JSON” actions, the Create Item will be put in it’s own “Apply to each” condition.
3. Since we indicated that our Form ID needs to be unique, the next time that this runs, it will actually fail but this is expected so that we can pick up any new forms that were created since the last update. We can solve this problem and return a “Success” instead of “Failure” by using a Terminate action.
4. Outside the Apply to Each loop, add in a new Terminate action and change the status to Succeeded.
5. Click on the 3 dots on the Terminate action and select Configure run after. Check off the “is successful” and “has failed’ and click Done.
6. You are now ready to let this run, and forget about it. Will run at whatever cadence you set and will keep your list of forms up to date. Considerations
There is a daily API call limit so be aware of that. This does only account for the first 100 entries on a page, if you have more than 100, there will be some extra steps needed to paginate through. I am NO EXPERT, I just like sharing some of the cool things I am doing with ProntoForms and our data.
Are you using the ProntoForms API in conjunction with Power Automate in any way? Do you see any value in this as a citizen developer? Let us know your thoughts and more to come!
#TechTalkImplementation
------------------------------
Ian Chamberlain
Implementation Specialist
ProntoForms
------------------------------