Update all items in a large list/library using Microsoft Flow (no-code solution)
This is the part 3 of the 3 part series related to handling large lists with Microsoft Flow – a no-code solution.
This post will demonstrate how you can iterate through a large list (or library) circumventing the list view threshold restrictions and update the items.
Solution is simple: Get list items in chunks (of say 512 items) and iterate over each item and call into a child Flow (nested flow concept, remember?). This child flow will take care of updating the list item. The Flow that we will create will introduce you to following concepts:
- Nested flows
- Using Microsoft Cognitive API – Language detection
- Parsing JSON
- Data operations/manipulations
Which column are we going to update?
If you remember from my blog post part 1, we created a large list from tweets from Twitter. I have screenshot below, you can see that there is a column called LangDetected which is empty. We are going to pass the Tweet text (Title column) to a Cognitive API to detect the language and then update this column with the detected language.
Add Apply to each step next and pass the output from Get items step to it as input.
Action: Search for Detect Language, Select 'Text Analytics – Detect Language'. I configured the Cognitive API Text Analytics already in Part 1. Configure it by selecting the Title column as its input.
Action: Search for "Parse JSON", Select Data operation – Parse JSON. Pass the output from Detect Language step to this step. In the schema, past the following:
Tip: To create schema, you can pick sample output from DetectLanguage step and use it in jsonschema.net website to generate the schema.
Action: Search for Compose > Select Data Operations – Compose. We will use expression first(body('Parse_JSON'))?['name'] to get the first language detected from the Parse JSON step.
We will create a separate flow that will be called within this flow. This separate flow will take the language detected and item index as input parameters and update the corresponding list item.
Create a Child Flow by starting from blank Flow template.
Trigger: Search for Request, Select 'Request/Response – Request' trigger. This will create a URL for this flow that we can call into from the parent flow. This is similar to HTTP Triggered Functions in Azure. We want to send the item index and language detected to this flow as JSON data. Configure this trigger to accept ID (item id) and Lang (Detected language) using below JSON schema.
Action: Search for Update Item > Select SharePoint - Update item action. Configure it by passing the ID and Lang as follows:
Here is the full screenshot of this child flow
Expand the trigget 'Request' action and copy the HTTP POST URL
Continuing editing parent flow from step 6. We got the Language detected in step 6. We need to now call the child flow and pass the item index and language detected as JSON parameter.
Action: Search for HTTP, Select HTTP – HTTP action. Configure as follows
- Method: POST
- Uri: Paste the URL copied from stpe 7.d
- Body: as shown in this image
That completes the parent flow. Here is the full screenshot of this flow.
After running these flows (from part 2 and 3) I was able to update the Language column for every tweet for all the 5500+ items. Here is the screenshot of the large list. Notice the LangDetected column (last column)