(ESTIMATION TIME: 5 MINUTES)
Last week I’ve explained how to create SharePoint list or library using Microsoft Flow. This time I’ll show you how you can add fields to your SharePoint List using Microsoft Flow, and add items to your list. This will complete the scenario so you’ll get a full solution for dynamic creation SharePoint List along with fields and values.
Automation is the key
Just before I’ll deep dive into the details let’s first cover the question of: why you may even need such dynamic list creation with fields and values?
So imagine you have multiple PowerApps apps for different purposes each ie:
- time tracking
- resources booking
- goals review
- team members praise
- retrospective meetings notes
Each app stores data in SharePoint and is team specific – this means some team may want to have it, others not. Also the app data should be team related.
Other often scenario is when you have an app that you want easily deploy in your client’s environment. This was my case when I’ve created my Delegation Playground app that uses SharePoint list – I wanted to automate the process of list creation so app user don’t need to create it manually. I wanted it to be as simple as possible.
In PowerApps there is a Solution concept but it applies only to PowerApps with data stored in Common Data Service (which I highly recommend – check this post of mine) so if your app works with SharePoint Online lists or libraries you may need the below Flow.
The Plan
Ok, so last time we end up with created SharePoint list. Now we need to:
- Add Number and Boolean fields
- Add 3000 rows to the list
Add new fields to SharePoint list using Microsoft Flow
To create new SP field we’ll going to use SP REST API (by using Send an HTTP request to SharePoint action). However this time we need to use new Uri – the one dedicated to list modification. As you can see in this POST request example we need to know list guid to make proper operation. The Uri need to be like:
http://<site url>/_api/web/lists(guid'<your_new_list_guid')
But how to get a newly created list GUID? Oh that’s simple – make a sample call of the Flow we’ve made last time and use data it returns.
Disclaimer: Some of you may already know that there is a simpler Uri API endpoint _api/web/lists/GetByTitle(‘<list name>’) as described here but the way I present brings an additional learning value.
Just follow guideline below 🙂
Now once we have the Uri of our newly create SharePoint list we can use example from this documentation page and use a field type value related to expected field type (all possible sharepoint field types values are here).
Disclaimer: As you can see we’re now working a lot with Microsoft official documentation. I recommend this as a really well written and complete reference source for any O365 developer.
Your action should be configured this way now:
Warning: please note that for number field I used Number field type type value, not the Integer field type value. The latter won’t work
Create similar action to create Boolean field:
Protip: Use copying action to clipboard – it’s new and fancy and I love it. And everyone was waiting for it 😀
Add SharePoint items to SharePoint list using Microsoft Flow
To add 9001 items we’re going to use Microsoft Flow “Do Until” loop. It will do defined operations and at the end of the iteration it will increase the iterator. Once the iterator exceed 9001 flow will know that it’s the right time to go out of the loop.
So add new variable called “Iterator”
Now add Do Until loop and select Iterator variable as value property. Set the check to be “is greater than 3000”. Remember also to unfold Change limits section and increase Count to 3000 items and Timeout to PT5H (just in case 1 hour will be to short)
Warning: Do Until has hard limit on 5000 iterations at maximum.
At this point we’ll normally use “Create item” action for SharePoint in Microsoft Flow. However we’ve just created the list and the mentioned action won’t work with dynamically created list. So…for the forth time we’ll use Send HTTP request to SharePoint 🙂
Protip: “Send HTTP request to SharePoint” is sooo universal and powerful. You can do almost ANYTHING using this action. One of my favorite.
You can find here documentation page where the example Create List Item POST HTTP request is made (see? I told you it’s well written and helpful). Let me show you configured action and code first and then I’ll describe it:
{
__metadata:
{
type: "SP.Data.Delegation_x0020_PlaygroundListItem"
},
Title: 'Item @{variables('Iterator')}',
Integer_x0020_Field:@{variables('Iterator')},
Bool_x0020_Field:@{if(equals(mod(variables('Iterator'),2),0),'true','false')}}
So what I’ve done is (follow my steps):
- Add “Send HTTP Request to SharePoint” action inside Do Until loop
- Fill Site Address, Method, Uri and Headers as in the screenshot
- Paste above code but please make sure to:
- swap Delegation_x0020_Playground with your list name
- have ListItem suffix. So for your list named “MyList” the type string will be SP.Data.MyListListItem
- swap all special characters (like space) in the list name. Good article about it was written here by Stefan Bauer
- When providing field names remember about special characters rule described above
- In my case I used expression with modulo function so every even item has Bool field = true
Last thing we need to do is to increase the iterator by 1 inside Do Until loo to not end up with infinite loop.
AND THAT’S IT!
WOOHOO!
You’ve just created flow that create list with fields and list items. And everything done with one push of a button to trigger your flow 🙂
Automation is beautiful.
And just in case you want to download ready made solution – you can download it from here 🙂
Hope this post was helpful. Write me a comment if you have any question or used my flow.