• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Power Automate Flow to batch create SharePoint List Items

June 15, 2021 by Paulie 65 Comments

This post is part of a series of blog posts which demonstrate how to use the SharePoint Batch API in Power Automate. In this post I will demonstrate how to create SharePoint items in batches of up to 1,000. The main advantages of using the batch API are:

  • Drastically improved performance.
  • Potentially 1,000 times reduction in the use of Power Automate API actions.

This Flow is very similar to the post I wrote on batch deleting items from a SharePoint list.

Table of contents

  • Example Data
  • The Standard Power Automate Method
  • Batch Create SharePoint List Items
  • Batch API Flow Overview
  • Still not fast enough?
  • Flow Code and Implementation
    • Sample Data and Flow Exports
    • Paste the Scope into a Flow
  • Conclusion

Example Data

In this flow I am going to import an Excel file containing 3 columns and 15,000 rows of data to a SharePoint list with the same configuration:

Image of an Excel Spreadsheet which is going to be used as a data source in a Power Automate Flow.

The data source is unimportant. Any JSON array containing the data that you want to insert into SharePoint will be fine.

The Standard Power Automate Method

The most often used method to import rows of data into SharePoint with Power Automate is to use an apply to each loop to iterate through a JSON array using a “Create Item” action within the loop. It would look something like this:

Image of a Power Automate Flow which uses an apply to each loop to generate items in a SharePoint list.

The great thing about this method is that it is very simple to do, so if you are new to Power Automate it’s a great way to get data from a variety of sources into a SharePoint list.

For small datasets, this method is perfect. But it does have two problems:

  • Performance is quite slow. A large dataset will take many hours to complete.
  • A large number of rows will consume many API actions from your daily allowance.

I tested this flow using the first 1,000 items from the Excel table and it took 7 minutes and 15 seconds to complete. So to do all 15,000 rows would have taken approximately 1 hour and 50 minutes.

I’m not going to go into too much detail about the method above, because if you’re reading this, you’re probably trying to find a faster method!

Batch Create SharePoint List Items

It is possible, using the SharePoint $batch API to bundle up to 1,000 actions to SharePoint in a single HTTP request. This provides drastically increased performance and reduces the number of API actions required by a factor of 1,000.

However, it is much more complicated to implement. So if you need to increase performance, it is worth the effort.

To create exactly the same 1,000 rows of data using the batch method took only 1 minute and 46 seconds. So the total time to do all 15,000 records would be approximately 26 minutes. Around 1.5 Hours faster than the standard method.

Batch API Flow Overview

As per my method for deleting items from a SharePoint list, I have created a flow which should be simple for you to modify. Here is a screenshot of the flow:

Image of a complete Power Automate Flow that uses the SharePoint $batch API to create list items

The Flow Follows this Process:

  • Data is retrieved from the data source
  • A variable named LoopControl is defined which is used to determine when the Do Until loop should end.
  • The Scope Create SharePoint Items begins:
    • A compose action called Settings defines some settings for the flow.
    • A Select action is used to reformat the incoming data to shape it correctly for the list.
    • A template is defined for the change actions.
    • A do until loop iterates through all of the JSON data in batches until no more remains.
    • A select action is used to reformat the array items.
    • A compose action is used to join the output of the select.
    • The output of the compose is sent to SharePoint and contains the batch of actions to be completed.

Still not fast enough?

The flow above, although much more complicated than the standard method provides a great performance benefit. What I noticed, is that the performance bottleneck has shifted from Power Automate to SharePoint.

So I modified the flow, which adds a small amount of extra complexity, but doubles the effective speed:

MethodTime to Create 1k List ItemsDifference
Standard Apply/Each Loop435 seconds
Batch API106 seconds-329
Batch API Threaded54 seconds-383

This is achieved by splitting each batch of 1,000 into two simultaneous batches of 500 and running them in an apply to each loop with concurrency enabled. The apply to each loop uses an expression as its source:

if
(
  equals(variables('LoopControl'), 0),
  json('[]'),
  createArray
  (
    take(body('Select'), div(length(body('Select')), 2)),
    skip(body('Select'), div(length(body('Select')), 2))
  )
)

This expression does a couple of things:

  • Checks if there are any remaining items in the dataset, and if not returns an empty array.
  • If there are items remaining it determines the length of the items and splits the array into two halves.
  • The two halves are then run in parallel which doubles the speed of the SharePoint batch command.

Flow Code and Implementation

Because this flow is somewhat complicated I am providing a number of options for you to download and implement it.

Sample Data and Flow Exports

Download my sample Excel Data File and Exports of the flows to import into your environment here:

  • Sample Excel Data
  • Create SharePoint Items Batch API Method
  • Threaded Create SharePoint Items Batch API Method

To work with my sample data you will need to put the Excel file into your OneDrive and modify the List Rows Present in a Table action accordingly.

After Importing the flow you will need to update the settings compose action at the beginning of the scope with your SharePoint site address and list name:

Image of a compose action in Microsoft Power Automate. In this image it contains the settings for the flow.

Which version is right for you depends on how many items you have and what your performance demands are. I recommend choosing the non threaded version unless you require the added performance of the threaded version.

Paste the Scope into a Flow

If you prefer to build your own flow you can do the following:

  • Create a variable called LoopControl.
  • Copy the Scope code below and paste it into your flow.
  • Update the settings compose action.
  • Change the Select action GenerateSPData to draw from your own data source, but retain the first column.

The scope code for the non-threaded version:

{
  "id": "8869cabe-b30e-400c-b0f2-3d8a-d53fb36c",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_excelonlinebusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
      }
    },
    "shared_sharepointonline_1": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "Create_SharePoint_Items",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "settings": {
        "type": "Compose",
        "inputs": {
          "siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
          "listName": "Invoices Development Test",
          "batchSize": 1000
        },
        "runAfter": {},
        "description": "list to batch delete items from",
        "trackedProperties": {
          "meta": {
            "type": "[email protected]{outputs('settings')?['listName']}ListItem"
          },
          "batchGUID": "@{guid()}",
          "changeSetGUID": "@{guid()}"
        }
      },
      "GenerateSPData": {
        "type": "Select",
        "inputs": {
          "from": "@body('List_rows_present_in_a_table')?['value']",
          "select": {
            "__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
            "Customer": "@item()?['Customer']",
            "InvNum": "@int(item()?['Invoice Number'])",
            "Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        }
      },
      "batchTemplate": {
        "type": "Compose",
        "inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
        "runAfter": {
          "GenerateSPData": [
            "Succeeded"
          ]
        }
      },
      "Do_until": {
        "type": "Until",
        "expression": "@equals(variables('LoopControl'), 0)",
        "limit": {
          "count": 60,
          "timeout": "PT1H"
        },
        "actions": {
          "Select": {
            "type": "Select",
            "inputs": {
              "from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
              "select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
            },
            "runAfter": {}
          },
          "Set_variable": {
            "type": "SetVariable",
            "inputs": {
              "name": "LoopControl",
              "value": "@length(body('Select'))"
            },
            "runAfter": {
              "Select": [
                "Succeeded"
              ]
            }
          },
          "batchData": {
            "type": "Compose",
            "inputs": "@join(body('Select'), decodeUriComponent('%0A'))",
            "runAfter": {
              "Set_variable": [
                "Succeeded"
              ]
            }
          },
          "SendBatch": {
            "type": "ApiConnection",
            "inputs": {
              "host": {
                "connection": {
                  "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
                }
              },
              "method": "post",
              "body": {
                "method": "POST",
                "uri": "/_api/$batch",
                "headers": {
                  "X-RequestDigest": "digest",
                  "Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
                },
                "body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData'))}\nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
              },
              "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
              "authentication": {
                "type": "Raw",
                "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
              }
            },
            "runAfter": {
              "batchData": [
                "Succeeded"
              ]
            },
            "limit": {
              "timeout": "P1D"
            },
            "metadata": {
              "flowSystemMetadata": {
                "swaggerOperationId": "HttpRequest"
              }
            }
          },
          "Results": {
            "type": "Compose",
            "inputs": "@base64ToString(body('sendBatch')['$content'])",
            "runAfter": {
              "SendBatch": [
                "Succeeded"
              ]
            }
          }
        },
        "runAfter": {
          "batchTemplate": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {
      "Initialize_variable": [
        "Succeeded"
      ]
    }
  }
}

Here is the scope code for the threaded version:

{
  "id": "79273084-e0e1-450a-a878-1dda-e7a2b8a4",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_excelonlinebusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
      }
    },
    "shared_sharepointonline_1": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "Create_SharePoint_Items",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "settings": {
        "type": "Compose",
        "inputs": {
          "siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
          "listName": "Invoices",
          "batchSize": 1000
        },
        "runAfter": {},
        "description": "list to batch delete items from",
        "trackedProperties": {
          "meta": {
            "type": "[email protected]{outputs('settings')?['listName']}ListItem"
          },
          "batchGUID": "@{guid()}",
          "changeSetGUID": "@{guid()}"
        }
      },
      "GenerateSPData": {
        "type": "Select",
        "inputs": {
          "from": "@body('List_rows_present_in_a_table')?['value']",
          "select": {
            "__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
            "Customer": "@item()?['Customer']",
            "InvNum": "@int(item()?['Invoice Number'])",
            "Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        }
      },
      "batchTemplate": {
        "type": "Compose",
        "inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
        "runAfter": {
          "GenerateSPData": [
            "Succeeded"
          ]
        }
      },
      "Do_until": {
        "type": "Until",
        "expression": "@equals(variables('LoopControl'), 0)",
        "limit": {
          "count": 60,
          "timeout": "PT1H"
        },
        "actions": {
          "Select": {
            "type": "Select",
            "inputs": {
              "from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
              "select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
            },
            "runAfter": {}
          },
          "Apply_to_each": {
            "type": "Foreach",
            "foreach": "@if\r\n(\r\n  equals(variables('LoopControl'), 0),\r\n  json('[]'),\r\n  createArray\r\n  (\r\n    take(body('Select'), div(length(body('Select')), 2)),\r\n    skip(body('Select'), div(length(body('Select')), 2))\r\n  )\r\n)",
            "actions": {
              "batchData": {
                "type": "Compose",
                "inputs": "@join(item(), decodeUriComponent('%0A'))",
                "runAfter": {}
              },
              "SendBatch": {
                "type": "ApiConnection",
                "inputs": {
                  "host": {
                    "connection": {
                      "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
                    }
                  },
                  "method": "post",
                  "body": {
                    "method": "POST",
                    "uri": "/_api/$batch",
                    "headers": {
                      "X-RequestDigest": "digest",
                      "Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
                    },
                    "body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: \nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
                  },
                  "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
                  "authentication": {
                    "type": "Raw",
                    "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
                  }
                },
                "runAfter": {
                  "batchData": [
                    "Succeeded"
                  ]
                },
                "limit": {
                  "timeout": "P1D"
                },
                "metadata": {
                  "flowSystemMetadata": {
                    "swaggerOperationId": "HttpRequest"
                  }
                }
              },
              "Results": {
                "type": "Compose",
                "inputs": "@base64ToString(body('sendBatch')['$content'])",
                "runAfter": {
                  "SendBatch": [
                    "Succeeded"
                  ]
                }
              }
            },
            "runAfter": {
              "Set_variable": [
                "Succeeded"
              ]
            },
            "runtimeConfiguration": {
              "concurrency": {
                "repetitions": 10
              }
            }
          },
          "Set_variable": {
            "type": "SetVariable",
            "inputs": {
              "name": "LoopControl",
              "value": "@length(body('Select'))"
            },
            "runAfter": {
              "Select": [
                "Succeeded"
              ]
            }
          }
        },
        "runAfter": {
          "batchTemplate": [
            "Succeeded"
          ]
        }
      }
    },
    "runAfter": {
      "Initialize_variable": [
        "Succeeded"
      ]
    }
  }
}

To copy the Scopes above simply copy the code into your clipboard, like this:

Conclusion

This is a great way to improve the performance of creating items in a SharePoint list. I have made my best attempt to make it as simple as possible for you to duplicate and I hope you find it useful. Please do let me know in the comments if you have managed to speed up your flows using this method. I’d be interested to know how long it took before and how much time you saved.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Mezza says

    June 15, 2021 at 9:21 pm

    A very thorough and detailed post! Thanks!

  2. Pratibha says

    June 17, 2021 at 12:48 am

    for me this flow is running fine without any error but it isn’t writing anything in the list means it isnt creating items in the list, I can see values coming up from database. what could be the possible reason for it?

  3. Paulie says

    June 17, 2021 at 9:24 am

    This means that the HTTP request was a valid format, but the contents of the batch request were not. So you need to look at the results compose action and see what the error returned from SharePoint was. My guess is that you have a string in a number column or vice versa. Have a look at the results and see what the error is.

  4. Lluis says

    June 17, 2021 at 11:53 am

    Hi ,thanks for all ,really goes to much faster, but just a question, i have trayed with a excel and only imports 1024 records from 65000… Flow takes 6 minutes to end and with error, but not all records have been imported, any idea?
    Thanks again!

  5. tachytelic says

    June 23, 2021 at 10:42 am

    Lluis, you need to check the settings on “List rows present in a Table”. The threshold value is probably set to 1,000

  6. Steven says

    June 29, 2021 at 10:07 am

    Hi Paulie, I got this error,
    Request to Azure Resource Manager failed with error: ‘{“error”:{“code”:”ActionRepetitionNotFound”,”message”:”The specified repetition ‘000002-000000’ of workflow action ‘Results’ is not found.”}}’.

  7. Steven says

    June 29, 2021 at 12:08 pm

    Hi Paulie, thank you so much, please post Batch Update multi items?

  8. Donavan Marais says

    August 6, 2021 at 12:33 am

    Great post! I followed it to the T, however, I couldn’t work out the tracked properties for the scope settings. When I ran my flow, I encountered this error:
    Unable to process template language expressions in action ‘batchTemplate’ inputs at line ‘1’ and column ‘23313’: ‘The template language expression ‘actions(‘settings’)?[‘trackedProperties’][‘changeSetGUID’]’ cannot be evaluated because property ‘changeSetGUID’ cannot be selected.
    What needs to be stored in the tracked properties?
    Thanks

  9. Paulie says

    August 6, 2021 at 12:51 am

    If you copy the scope from the post and paste it into your own flow you will be able to examine what is in the tracked properties of the settings action, did you recreate the flow manually?

  10. Donavan Marais says

    August 6, 2021 at 12:03 pm

    I copied it over as per your instructions.

  11. Donavan Marais says

    August 6, 2021 at 12:36 pm

    Did you perhaps set the changeSetGUID in a variable?

  12. Donavan Marais says

    August 6, 2021 at 1:14 pm

    I managed to get past that issue by adding a compose for both batchGUID and changeSetGUID. Next hurdle was the sendBatch action. Got this error:

    {
    “status”: 400,
    “message”: “The ‘Content-Type’ header value ‘application/http\\nContent-Transfer-Encoding: binary’ is invalid. When this is the start of the change set, the value must be ‘multipart/mixed’; otherwise it must be ‘application/http’.\r\nclientRequestId: 11a6b56d-e054-43a0-befe-c09bdd5a4a14\r\nserviceRequestId: bad8e29f-904e-c000-579d-aed473ac947b”,
    “source”: “https://test.sharepoint.com/sites/test/_api/$batch”,
    “errors”: [
    “-1”,
    “Microsoft.Data.OData.ODataException”
    ]
    }

    My Headers are configured as per yours:

    Content-Type: multipart/mixed;[email protected]{actions(‘settings’)?[‘trackedProperties’][‘batchGUID’]}

  13. Donavan Marais says

    August 6, 2021 at 1:17 pm

    Apologies, I found the issue. Corrected and it’s working great now. Thank you so much.

  14. Paulie says

    August 6, 2021 at 1:21 pm

    Well done! Glad you got it working. Lot’s of people getting on quite well with it now.

  15. Donavan Marais says

    August 6, 2021 at 1:25 pm

    It was definitely user error on my part! Again, thanks for an excellent article. You saved me on my project. Super happy with the results. Took 6 minutes to create 3,200 list items.

  16. Paulie says

    August 6, 2021 at 1:26 pm

    It does provide a massive speed boost! How long did it take the normal way to create 3,200 items?

  17. Donavan Marais says

    August 6, 2021 at 1:37 pm

    Unfortunately it timed out each time I tried to do it the normal way

  18. Shatha says

    August 17, 2021 at 2:21 pm

    Thank you so much
    But does this method works if I need to update records in share point list?

  19. Paulie says

    August 17, 2021 at 2:22 pm

    This will not update existing records. I have not yet written up how to perform an update using this method.

  20. Derek Crawford says

    August 17, 2021 at 4:27 pm

    Hey Paul, thank you for your videos, they are helping me grow tremendously. Is there a way to circumvent the returned 5K record limit during the ‘List rows present in a table’? Perhaps a nested Do Until, like you implemented in your “Solutions to the SharePoint 5000 item limit using Power Automate” April 2020 post?

    https://www.tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/

  21. BK says

    August 18, 2021 at 1:53 pm

    Hi Paulie, this was very helpful and I have incorporated your way to assign tasks to users (The task lists and number of users are dynamic based on user selection in Power Apps). I made slight modifications to your approach and incorporated it into my flow. The way you have organized and developed the flow was also very helpful. I learnt some new things. Thanks for great help.
    On the other note, have you ever done uploading of document (PDF and Office documents, it works for ascii texts, but never got it work for PDF and office documents) via SharePoint HTTP action? If you have, any help would be appreciated. The reason for me to use HTTP action is because the document library (there are 50 document libraries and it is not feasible to write a switch statement in Power Automate) changes based on user selection (currently I am using non-SharePoint HTTP action which is a premium connector. This works fine. I am looking to see if I can eliminate the premium connector). Any help or suggestions would be greatly appreciated.

  22. Paulie says

    August 18, 2021 at 1:58 pm

    Hi BK,

    Pleased to hear that you found it helpful. Yes, I have uploaded documents via SharePoint HTTP Action, and it is possible to do any file type.

    You cannot create a switch statement, but you can easily create an array of document libraries and then filter that list based on the user selection and then dynamically select the correct library. You could do this within a couple of actions and avoid the need for a switch or a condition entirely.

    Paul

  23. BK says

    August 18, 2021 at 2:12 pm

    Paulie, Thanks for reply, For me the problem lies with how the File content is being passed to SP HTTP action. Every time I do this, the file gets created but the file (PDF and Office documents specifically) is corrupted.

  24. Tyler says

    August 25, 2021 at 10:48 pm

    Hello Paulie,

    Thanks for the demos and great tool with both the batch delete and batch create. I’m using batch delete to reduce the actions in a lot of flows that delete old data on a schedule, but I have a flow where I actually want to use both batch create and batch delete because I need to archive data before deleting it.

    Unfortunately I keep getting the following error:
    HTTP/1.1 400 Bad Request
    CONTENT-TYPE: application/json;odata=verbose;charset=utf-8

    {“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”A type named ‘SP.Data.OlderRecords_Archive_and_MMListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”}}}
    –batchresponse_8a3589a4-81ec-46a9-a2d8-060b2a09a147–

    I suspect one of two things, either there’s something wrong with the list name or there’s something wrong with the mapping of values in the select action.

    I checked the name of the site and list and they are both good. Is it possible for SharePoint to have a different name for a list if I recently changed the name?

    I checked the value mapping in the select action. I made sure all the values are the correct type and this shouldn’t be difficult because I’m creating in an archive list that is a copy of the original list where the Get items action is pulling from. I also tried a couple variations on the choice column names, like making sure the Status Value value is labeled with the Status/Value label it’s given in the original Get items action.

    But I’m still getting the same error. Do you have a better idea what may be wrong?

    Thank You,

  25. Paulie says

    August 25, 2021 at 11:18 pm

    I have no idea what the error means really. If you exclude the status column completely does it then work?

  26. Tyler says

    August 28, 2021 at 7:06 pm

    I figured out what the specific issue is.
    I was able to get this to work on a test list on a different SharePoint site in the collection with a siteAddress set to “https://metropolitanministries.sharepoint.com/sites/ProcessImprovementTeam/”. The flow just won’t work when I try to use the site that is the homepage for the SharePoint collection where the siteAddress is “https://metropolitanministries.sharepoint.com/”.

    I don’t know yet if there is a way to alter the flow to get it to work with the different siteAddress structure of the homepage.

  27. GG says

    September 9, 2021 at 4:07 pm

    Works great, but underscores in SP list names should be replaced as well else you get the type resolution error from comments above. This worked for me:

    json(concat(‘{“type”:”SP.Data.’, replace(replace(outputs(‘settings_2’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘_’, ‘_x005f_’), ‘ListItem”}’))

  28. AK says

    September 12, 2021 at 10:49 pm

    Hi Paul,

    This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”

    Or maybe is there a similar way to bulk update the list item permissions.

    Thank you so much as always.

    Regards,
    AK

  29. AK says

    September 12, 2021 at 11:05 pm

    Hi Paul, This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”

    Or maybe is there a similar way to bulk update the list item permissions.

    Thank you so much as always.

    Regards,
    AK

  30. AK says

    September 12, 2021 at 11:16 pm

    Hi Paul,

    This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”

    Or maybe is there a similar way to bulk update the list item permissions.

    Thank you so much as always.

    Regards, AK

  31. Paul Graham says

    September 13, 2021 at 5:19 pm

    You have changed my life and the way I use Power Automate. I am very very grateful x

  32. Paulie says

    September 13, 2021 at 11:20 pm

    Paul – Glad to be providing a life changing service!

  33. john good says

    October 18, 2021 at 10:56 pm

    Thanks for these post! I was able to use both delete list and create list with sql server connections. went from 3 hours to 8 minutes.

  34. Paulie says

    October 18, 2021 at 11:00 pm

    Wow – 3 hours to 8 minutes is a seriously good result!

  35. Shahar says

    November 23, 2021 at 7:08 am

    Hey Paulie,
    Thanks for this great recourse, I got a question:
    What if I want to let the user to upload a file via power app (or form) and from there to dynamically “translate” the excel file to SharePoint list, would it be possible with this method?

  36. Amit Lohogaonkar says

    November 24, 2021 at 12:03 pm

    great! can this be used for updating files in different libraries in same site?

  37. X.S says

    December 9, 2021 at 4:05 pm

    I am trying o import 9725 items from SQL to SharePoint but I am having to issues, one the final count of items is 8632 and the flow apparently runs but on the SendBatch step I am getting this error:
    {
    “status”: 400,
    “message”: “The maximum number of bytes allowed to be read from the stream has been exceeded. After the last read operation, a total of 1055925 bytes has been read from the stream; however a maximum of 1048576 bytes is allowed.\r\nclientRequestId: ee1b1838-7a91-47fe-bbf5-3c683d9038f9\r\nserviceRequestId: 32200ba0-4073-1000-5c68-3c147bfbadd6”,
    “source”: “https://xxsite.sharepoint.com/sites/xxxx/_api/$batch”,
    “errors”: [
    “-1”,
    “Microsoft.Data.OData.ODataException”
    ]
    }

    Any suggestions?

  38. Ha Le says

    December 9, 2021 at 11:52 pm

    got it to work. went from ~1h 50min to around 16 minutes…. biggest time saver ever!

  39. Jesslyn says

    January 12, 2022 at 6:44 pm

    Thank you so much for both the Delete list and Update list flows. We are migrating from SharePoint server where our large lists worked, but not so much in SharePoint Online. These flows are lifesavers.

  40. zo says

    January 12, 2022 at 11:18 pm

    Running into the same issue X.S. is, anyone have a solution?

  41. tahsin says

    January 13, 2022 at 8:28 am

    Hello,

    I’m trying to import example List with 3 Rows.
    ListName is = testBatchImport
    __metadata= json(concat(‘{“type”:”SP.Data.’, replace(outputs(‘settings’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘ListItem”}’))

    I got following Bad Request:

    –batchresponse_46e39e77-1f85-42de-8096-5c0fbfd87563
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    HTTP/1.1 400 Bad Request
    CONTENT-TYPE: application/json;odata=verbose;charset=utf-8

    {“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”de-DE”,”value”:”Ein Typ mit dem Namen \”SP.Data.testBatchImportListItem\” konnte vom Modell nicht aufgel\u00f6st werden. Wenn ein Modell verf\u00fcgbar ist, muss jeder Typname in einen g\u00fcltigen Typ aufgel\u00f6st werden k\u00f6nnen.”}}}
    –batchresponse_46e39e77-1f85-42de-8096-5c0fbfd87563
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    Any suggestions?

  42. zo says

    January 13, 2022 at 7:57 pm

    @tahsin, capitalize the ‘t’ in your ListEntityType path to read SP.Data.TestBatchImportListItem

  43. tahsin says

    January 14, 2022 at 7:19 am

    @zo, I capitalze the ‘t’ in my ListEntityType path but still having bad request.

    –batch_e10aee85-3362-44e0-9fb1-7ecd3d917e16
    Content-Type: multipart/mixed; boundary=”changeset_29189f5f-ccfc-41ce-9161-f4d0d3ae7798″
    Content-Length:
    Content-Transfer-Encoding: binary

    –changeset_29189f5f-ccfc-41ce-9161-f4d0d3ae7798
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    POST https://arfmann.sharepoint.com/sites/IFARotorionDigitalerSCMKollege/_api/web/lists/getByTitle('TestBatchImport‘)/items HTTP/1.1
    Content-Type: application/json;odata=verbose

    {“__metadata”:{“type”:”SP.Data.TestBatchImportListItem”},”A”:”1″,”B”:”1″,”C”:”1000″}

    #.2 row
    #.3 row

    –batchresponse_22ed63a5-7aa4-4e37-8deb-87fba8e17e0b
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    HTTP/1.1 400 Bad Request
    CONTENT-TYPE: application/json;odata=verbose;charset=utf-8

    {“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”de-DE”,”value”:”Die Eigenschaft \”A\” ist f\u00fcr den Typ \”SP.Data.TestBatchImportListItem\” nicht vorhanden. Stellen Sie sicher, dass nur Eigenschaftennamen verwendet werden, die im Typ definiert sind.”}}}
    –batchresponse_22ed63a5-7aa4-4e37-8deb-87fba8e17e0b
    Content-Type: application/http
    Content-Transfer-Encoding: binary

  44. Max1981 says

    January 14, 2022 at 10:26 am

    Hello together, hello Paul,

    first: Paul, thanks a lot for this post. I saw it last year but I didn’t had the time to rebuild my process. So today I finally used your “template” and the time saving is create.
    I’m using the PowerPlatform/PowerAppsForAdmin-Management connectors to save information in SharePoint. Unfortunately, the batch runs into in error if I’m using batches in size 1000:
    Content-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 500 Internal Server Error\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-2146233083, Microsoft.SharePoint.Client.ClientServiceTimeoutException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”The operation has timed out.\”}}}\r\n–batchresponse_d404c9a9-b2c5-48d8-ae29-41ba2a92c4b9–\r\n”

    So I reduced it to the size 200 and it works fine now. So I will play around with it and also try to get information if the response has an error in it.

    Maybe this information is helpful for someone.

  45. Jesslyn says

    January 14, 2022 at 6:03 pm

    Thank you again for this flow. I am having success on one list, but get this error:
    A type named ‘SP.Data.IPT_x0020_PE/VC_x0020_ListListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.\

    on another list. I can’t for the life of me figure out what is going on.

  46. ??????? ???????? says

    January 17, 2022 at 6:24 am

    Hello Paul, I am working in a project as a junior and I wanted to try your method, but it didn’t work out. Flow ran successfully, but there’s no change in my list, the records never show up, I’ve already checked everything and I can’t setting (( please help me, I’d appreciate it. In my excel file I have 15000 record, but ‘Do until’ have 3 steps (variable LoopControl – 1000 first step, 24 – second step and 0 ) I changed Setting and GenerateSPData (besides __metadata / json(concat(‘{“type”:”SP.Data.’, replace(outputs(‘settings’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘ListItem”}’))

  47. Jesslyn says

    January 19, 2022 at 3:57 pm

    I thought I would follow up on the errors that I got in case it would be of some help to others. It seems that the code does not like certain symbols in the column names. I had to replace both ‘/’ and ‘:’. I was having inconsistent results, either those columns were returning null values or the code threw errors. Since the SharePoint list displayed those symbols, it really didn’t matter what the Excel file shows (for us) so we’re good

  48. Vitali Varabyeu says

    January 19, 2022 at 5:19 pm

    I don’t have any spaces and so on in column naming

  49. Josh Butcher says

    January 26, 2022 at 9:55 am

    That’s a brilliant solution! If I have a source Excel file with uo to 10k rows, does this solution handle it as-is or should I loop the List Rows action + this batch create (e.g. 1k items)? How did you get to this answer; really?

  50. Paulie says

    January 26, 2022 at 10:07 am

    The example flow in the post should do exactly what you want. Bring in all 10k rows from Excel in one action and then use a do until loop to create the items in batches of 1,000. It does depend how many columns there are in your source Excel file though. If it is very wide you might need to reduce the batch size a little bit to prevent timeouts.

  51. Alex L. says

    February 10, 2022 at 8:29 am

    At the SendBatch action, I got this error message”: “The maximum number of bytes allowed to be read from the stream has been exceeded. After the last read operation, a total of 1055877 bytes has been read from the stream; however a maximum of 1048576 bytes is allowed.(…)”
    Any idea of how to fix this? My excel file only has about 7K rows.

  52. Paulie says

    February 10, 2022 at 8:30 am

    It must be the number of columns pushing you over the size limit then. If you look in the settings action there is a batch size setting. Try reducing it to 750 and see if it works then.

  53. Alex L. says

    February 14, 2022 at 11:18 am

    @Paulie, thank you so much for writing this article and for your advice about reducing the batch size. I don’t get the over-the-limit error message anymore, and my flow takes 8 mn and 20 s for ~ 7.6k rows.
    I also want to thank @Jesslyn for recommending to remove symbols from the column titles.
    Once again, thank you very much!

  54. Valdeir Barbosa says

    February 23, 2022 at 8:25 pm

    Regards Paul, thank you for deliver us this content really appreciated that!!

    When I try to run my flow I got the message “The ‘from’ property value in the ‘select’ action inputs is of type ‘Object’. The value must be an array.” I’m start in to learn a batch ways a few days, please can you help me to figure out where I missed, please ?

  55. Avik says

    March 26, 2022 at 7:24 pm

    Hi Paul,

    Thank you as always.

    I wanted to ask how can we batch create items in a sharepont list where one column is a “people” column and in the excel I have a column with email ids.

    Can you guide to bulk update this “people” column.

    Regards,
    Avik

  56. Gianluca M. says

    March 30, 2022 at 2:29 pm

    Very cool. Should also reduce the number of API calls, right? Is it 1 API call for each 1’000 items batch? That would be an ENORMOUS advantage, apart from being fast!

  57. Richard Davis says

    April 15, 2022 at 3:25 pm

    Great post. I am trying to apply this method to another upload process but am getting this error message:

    {“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”A type named ‘SP.Data.UserApplicationAccess_TestListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”}}}

    I am a newb so not exactly sure where I went wrong, Any help or direction would be greatly appreciated. Thanks

  58. Richard Davis says

    April 15, 2022 at 3:50 pm

    Followup.. I found the issue, replacing the underscore.. something so simple!

  59. David says

    April 22, 2022 at 10:57 pm

    Hi Paul,

    This is really a great time-saver, thank you so much. I attempted to use the “My Clipboard” option to paste your code, but for some reason, it doesn’t provide an action/operation choice to paste to. Anyway, I was able to get it to work by reviewing the “peek code” with your code. The Flow only added two records to my Sharepoint list. I looked at the “SendBatch” operation output and it shows the following errors for all the other records to be loaded (998): Invalid JSON. More than one value was found at the root of the JSON content. JSON content can only have one value at the root level, which is an array, an object or a primitive value.” Any ideas on what is causing this? Thank you,

  60. David says

    April 24, 2022 at 8:22 pm

    Hi Paul,

    I realized my error…it was adding an “O” instead of “0” in the %0A batchData input. What was taking 1 to 2 hours is now down to 10 minutes…amazing!

  61. Paulie says

    April 24, 2022 at 8:23 pm

    Well done David – yes, it’s a great way to boost performance.

  62. Will says

    May 10, 2022 at 6:00 pm

    Thank You So Much! This was very helpful and what I needed for a list I need to delete and reupload daily but the calls were too high.
    I’m not an IT person and just learning these flows for work. It took me hours trying to get a batch create to work so I gave up. Then I saw the batch update and the flow had some extra details that allowed me to figure out the issue of how this works, and got the flow working and I think it could help make it easier for others to use.

    Issue: Sharepoint List Display Name vs Web Address Name
    The list name from Settings is used in multiple places. However, the formatting has to be adjusted based on the location it is used. In the batchTemplate the SPList name needs to be regular display name. In the GenerateSPData _metadata the needs to be the web address name. OP tried to help the _metadatafield by adding some replace values for underscores, but that only works if the name never changed.
    If a Sharepoint list is created with the initial name “InitialSharePointName” and then the display name is changed to “New Display Name”, then it will never call the correct webaddress since the webaddress doesn’t change from initial name just like column names.

    Solution to first issue: Add a second row in the Settings and differentiate the sharepoint display name and the sharepoint webaddress name.

    Original Settings Format:
    {
    “siteAddress”: “https://OrgName.sharepoint.com/sites/SiteName/”,
    “listName”: “ExampleListName”,
    “batchSize”: 1000
    }

    New Settings Format:
    {
    “siteAddress”: “https://OrgName.sharepoint.com/sites/SiteName/”,
    “SPListDisplayTitleName”: “(Copy Sharepoint Name from List Settings)”,
    “SPListWebAddressName”: “(Copy list name section from the Web Address section in List Settings”,
    “batchSize”: 1000
    }

    After Changing the settings you have to go into where they are used and update the expressions.
    1. Batch Template – change the output of getByTitle in the PATCH statement to: PATCH @{outputs(‘settings’)[‘siteAddress’]}_api/web/lists/getByTitle(‘@{outputs(‘settings’)[‘SPListDisplayTitleName’]}’)/items(|ID|) HTTP/1.1

    2. Generate SP Data – change the _metadata value field to contain json(concat(‘{“type”:”SP.Data.’, replace(replace(replace(outputs(‘settings’)[‘SPListWebAddressName’], ‘_’, ‘_x005f_’), ‘ ‘, ‘_x0020_’),’-‘,’_x002d_’), ‘ListItem”}’))

    I hope this helps some people because this flow really helped me get what I needed and saved a massive amount of calls.

  63. Paulie says

    May 10, 2022 at 6:43 pm

    Nice one William – thank you for the useful comments too.

  64. Mark says

    May 14, 2022 at 9:44 am

    Hi Paul,

    I love your blog; you have helped me (without knowing) so many times before and now I’m back, to try my hand at your batch create flow.

    I have chosen the threaded version of the flow for performance reasons.

    When I replace the excel fields using the dynamic pick list at the Select Action called ‘GenerateSPData’, and despite having a unique key/value pair in the dataset.

    Power Automate is ‘helpfully’ creating an Apply to Each loop. I could create a ‘first’ expression to get around this but wonder if there is something that I am missing?

  65. Spoorthy says

    May 16, 2022 at 3:12 pm

    Hey Paul! Thanks for the blog! Its great! I ran the Power automate flow and it worked perfectly fine. But i have done the same in the Logic apps and i get {“code”: “-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
    “message”: {
    “lang”: “en-US”,
    “value”: “Invalid request.”
    } error.
    Output of Batch:
    –batchresponse_14847879-408c-4477-a6f7-279c15af55ae
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    HTTP/1.1 400 Bad Request
    CONTENT-TYPE: application/json;odata=verbose;charset=utf-8

    {“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”Invalid request.”}}}
    –batchresponse_14847879-408c-4477-a6f7-279c15af55ae
    Content-Type: application/http
    Content-Transfer-Encoding: binary

    Any Idea why i get this issue?

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 246 other subscribers.

Go to mobile version