Power Automate: Get dynamic information from SharePoint

Today let’s add a fantastic new tool to your toolbelt. First, we’ll check how to get dynamic information from SharePoint, like the lists, the fields for each list, and so on. Of course, you may think that it’s not helpful or that you would never use and that may be true. Still, one of my most checked articles on this site, “Power Automate: Access an Excel with a dynamic path” and “Power Automate: Access Excel with a dynamic path II,” use this strategy.

It all boils down to using the “Power Automate: Send an HTTP request to SharePoint action.” Behind the scenes, Power Automate uses the same strategy to “Get items,” for example, so let’s look at things that work in the background and how you can take advantage of them.

I’ll be building a cookbook with some of the most common endpoints. I’ll put it on GitHub, but it’s a work in progress. If you want to contribute, get in touch or submit a pull request, and I’ll be more than happy to feature you and share the work with the community.

Finally, a warning regarding the terminology: you need to understand JSON. It’s not hard, and I’ll publish a reference soon explaining in a bit more detail, but in the meantime, here’s the Wikipedia article on how it works.

Getting all lists on a site

One thing that can be useful is to know all the lists on a site. To do that, we can use the following endpoint:

_api/web/lists/

Here’s how it looks like:

Before we look at the return of the action, it’s essential to look at the lists on the test site.

As you can see, there are a few (sorry about the names, I’m not the most creative person), so now we know what to expect.

Since the output is enormous, Power Automate won’t display it. There’s a lot of information returned in this request, but let’s look at the most useful to us:

The information returned is “about” the list and not “of” the list. What I mean by this is that you won’t see the list columns and other details, but you’ll see (as shown in the image above) a link to the list items, a link to the views, etc.

Think of this as the “index” where you’ll see the information of a list and then, depending on what you want, you’ll follow the path to that information. Here’s a bit more information, still regarding the list:

Microsoft was quite generous on the amount of information returned so you can fetch information like:

Lists that were updated after a date
Private lists
The details of the list (name and description)
When was the last item inserted?
When was the list created?

If someone asks you, “how many lists do we have?” you know how to do it quickly and even provide a bit more information about each one.

Find a list

Another valuable thing to do is find a list, especially if you have a lot of them. Here’s how to get dynamic information from SharePoint by referring to the list’s name.

_api/web/lists/getbytitle('employee')

Here’s what it looks like:

Then, after running, we can see it finds it.

The search is case insensitive, so that you can search it like this:

Finally, let’s look at what happens when we find a list that doesn’t exist.

We get the following exception:

List 'EmPl' does not exist at site with URL 'https://manueltgomescom.sharepoint.com/sites/Test'.
clientRequestId: 9c24f112-9413-442c-b696-26004d042615
serviceRequestId: e98e7bec-7606-479b-b4cc-73cfa4629ef6

Notice that we’re looking for a list by name, not searching for lists with a name. We expect one or error, and you know this because of the return configuration. We get an “object” (that is represented in JSON by curly braces) and not an array (that is expressed in JSON by square braces)

Please keep this in mind.

Get sub information

Now that we know how to find a list let’s look at its fields. To do that, we have two choices. One is to get the information of the list by doing either one of the steps above and using the “Fields” URL.

Or by slightly changing the URL above like this:

_api/web/lists/getbytitle('employee')/Fields

We’ll get another massive list of items, now referring to each column in the view.

It will work for all properties that are returned, like “RoleAssignments” or “DefaultView.” So you can easily fetch them using the strategy in the previous sections and copy the value:

I prefer this option since it’s much more readable, as long as we’re careful to guarantee that the list exists.

Final thoughts

There are many more endpoints to explore, so if you want more, please go to GitHub, where I’ll keep adding more examples.

The importance of this article is the strategy and how to get dynamic information from SharePoint. As you can see, you can get a lot of information, so get some endpoints and explore. You’ll be surprised by how useful the information is.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other SharePoint-related articles here.

Photo by Eric Prouzet on Unsplash