Indexing SharePoint columns with Power Automate: What could go wrong?

Today’s blog post is about troubleshooting an issue when trying to send an HTTP Request using Power Automate.  

More specifically, trying to create indexed columns in SharePoint to increase the performance of our list/library. Let’s imagine you need to automate a process which will create a new site, and amongst creating all the artefacts (site itself, lists or libraries, content types, etc…), you’re faced with errors and not sure why.  

That’s what we’re going to do today! 😊

Why indexing columns in SharePoint?

If you’re using SharePoint for a while, you’ll know that you can have millions of documents or list items. Therefore, you’ll want to use filters to get relevant results. That’s when indexing columns is important; Indexes improve performance on filters.

While you can index up to 20 columns (of different types), it’s not because you can that you should! 😉 Only index the most commonly used whenever possible.

Power Automate flow

In my demo site, I have a list called “Trainings”, and I’m going to choose 3 columns to index:

• Duration (type: single line of text)
• Training Start (type: choice)
• Available Seats (type: number)

For simplicity, I’m going to add a Compose action, and insert the following array containing those columns with the SPField as type.

Next, we’ll add an Apply to each action with the following headers, and try to run the flow.

Results: It failed! 🤔

The error is a little bit misleading. But if we look at the official documentation, we’ll see that we need to add ;odata=verbose for the headers. So let’s do it.

Well.. Looks like this was not enough because we had another fail with the same error! 🤔

The reason is because we’re using a POST request. Again, the documentation mentions:

You can create and update SharePoint entities by constructing RESTful HTTP requests to the appropriate endpoints, just as you do when you’re reading data. One key difference, however, is that you use a POST request. When you’re updating entities, you also pass a PUT or MERGE HTTP request method by adding one of those terms to the headers of your request as the value of the X-HTTP-Method key. The MERGE method updates only the properties of the entity that you specify

So let’s amend our headers and try one more time.

This time, the flow ran successfully and our columns are now indexed.

Sometimes the error message is not intuitive or too broad. When troubleshooting, think about a typo in your inputs, a forgotten quote the Uri or maybe an incorrect endpoint.

Thanks for reading! 🙂