Writing batch SharePoint API calls in Power Automate

I ran into a situation recently where I wanted to send a repeatable call to SharePoint, but I didn’t want to send 12 separate requests. My particular call I wanted to send to SharePoint was to update a list view. I wanted to add 12 columns to the default view. To do this, however, you need to call this request (notice it only adds one column at a time).

POST http://<sitecollection>/<site>/_api/web/lists(listid)/Views(viewid)/ViewFields/add(strField)

I thought there must be a better and faster way. After some Googling, I came across the following presentation, 5 Key Design Elements to make Flows run insanely fast by John Liu, presented at the Microsoft Flow Online Conference 2019. Not only did I learn how to do batch requests, I also learnt other tricks to help my flows along.

The presentation provides amazing tips on how to speed up your flows, but also provides guidance on how to write batch API REST calls to SharePoint from Power Automate (timestamp 8:50:30).

I have provided my experience on writing a batch API call to SharePoint below.

White space matters

I had an extra line between my last action and the end of my changeset.

POST https://<tenant>/sites/<site>/_api/web/lists/GetByTitle('<title>')/Views('bb5a3b52-8704-4ad2-b803-15ff851683eb')/ViewFields/addViewField('<field>') HTTP/1.1
Content-Type: application/json;odata=verbose



--changeset_afb4792d-bae3-4842-9f56-03731a0997da--
--batch_2d57b080-4f9b-4110-9f13-db63dcabe096--

The rest of my actions completed successfully, however, my last action gave me an error of “Unable to read data. The end of input stream is encountered.”. Therefore, all my columns were added to the view, but the last one. Once I removed one line from between the action and the end of the changeset, it worked.

You will most likely need to have a changeset

This article from Microsoft explains how to make batch requests to SharePoint: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/make-batch-requests-with-the-rest-apis.

If you are only making read requests (i.e. GET actions), then you will only need to incorporate one GUID into your flow for the batch.

If you are making any other type of request (i.e. POST, DELETE, PUT and PATCH), then you will need to create another GUID for the changeset.

To create a GUID in Power Automate, simply write the expression:

guid()

Batch request template

The following formula works when making a batch request:

--batch_guid
Content-Type: multipart/mixed; boundary="changeset_guid"
Host: https://<tenant>/sites/<site>
Content-Transfer-Encoding: binary

//Remove this line. Between the comments is the repeatable actions
--changeset_guid
Content-Type: application/http
Content-Transfer-Encoding: binary
(POST, DELETE, PUT, PATCH call) HTTP/1.1
Content-Type: application/json;odata=verbose (the content-type you would use for the API call you are making in your request above)
//End of repeatable actions

--changeset_guid--
--batch_guid--

Putting it all together

To put it all together, time to put it down in Power Automate. First, I went through all my columns that I put into an array. I composed my changeset actions. Notice I have two blank lines at the end of the input field. This was to give me a line between the changeset when I join them into a string (read above, white space matters!).

This is a handy trick to join all the changesets together. Instead of initializing a variable, and updating the variable inside the apply to each loop, you can place the compose action just outside the loop to join it together. John Liu mentions this in his video above. He aptly named it the Pieter Method: https://sharepains.com/2019/07/09/compose-actions-inside-apply-to-each-steps-in-microsoft-flow/.

Since the changeset is still in an array, we need to change it to a string.

Finally, we call the batch request.