Export Sharepoint List Metadata Using Power Automate

Introduction

The purpose of this article is to explain how can we export all metadata (Title, InternalName, DataType, etc) of the SharePoint lists (all lists of the site) using PowerAutomate.

This can help in documenting the details of each list of a SharePoint site. We will also be learning a few PowerAutomate basics while we are going through each step here.

Before we achieve our final target, you will be able to learn the below topics on the way.

• How to create instant flow in power automate
• How to initialize variables and append values to them
• How to get lists from SharePoint
• how to call SharePoint API
• How to use Parse JSON
• How to use Filter Array
• How to use Select and Compose data operations
• How to create HTML output in Power automate

I came across this requirement while working for a client, where I had to document each column of the SharePoint lists within a site.

At the beginning of the project, the client had created lists, and through the description of the columns of these lists, he had given the requirements. e.g. while creating the Quantity column below, I have given a description of what this column is about and what is it supposed to store.

I had to export these descriptions along with the names of columns so that this can become the base of the document and the developer/user can add further comments to each of these columns and enrich this document.

Here is the sample list I created for this article and the defined description in the columns of this list

Column Description for Quantity column above: 

So without wasting time further, let's start with power automate. 

We will create an "Instant Cloud Flow" 

Select the "Manually trigger a flow" option as selected below and click on Create button.

I'll name my flow as "ExportListsMetadata"

The first step I am adding is "Initialize Variable" and named variable as "ListsMetadata"

The next step is to get all the lists from the site using "Get Lists". Selected the site I am interested in. All the lists from this site will be iterated, and the metadata of the same will be exported.

To iterate through each value returned by "Get lists", added "Apply To each"

Next, we will add "Send an HTTP request to SharePoint"; this will get us the metadata of the list we are on.

• Site Address: the site we need metadata for
• Uri: /_api/Web/Lists(@v0)/Fields?&@v0=guid'@{items('Apply_to_each')?['Name']}'&$select=StaticName,Title,Description,ReadOnlyField,TypeDisplayName

I want to document the column names (display and internal), datatype, and Description, so I have chosen them in the URI above. Also, I am extracting the "ReadOnlyField" property of the column; this property I am going to use to eliminate the inbuilt columns of SharePoint.

In the above URI, "@{items('Apply_to_each')?['Name']}" is the guide of the list in interaction that I have taken from dynamic content by selecting the output of the Get Lists step.

The response of the above step is JSON, so I am adding the "Parse JSON" step. Input for Parse JSON will be the output body of the above HTTP response

I am attaching the Schema.txt to be used for this step. 

If you don't want to use this schema, another option is: to save this job before adding the current step (Parse JSON) and run it. From the run history, use the output of the "Send an HTTP request to SharePoint" step and use "Generate from Sample" to generate the schema.

The next step is "Select" from "Data Operation". This step selects the specified properties from all elements of the 'From' array into a new array. We will map each property from the result data of the "Parse JSON" step. To map each property, I am using expression.

Here are all the expressions I used for each property.

Now we are going to filter the output array returned by the "Select" step where "ReadOnlyField" is false. For that, I am using "Filter Array"

This is to eliminate many system default columns of the list and only select the user-defined fields/columns. You can refine the filter by eliminating "Hidden" = true fields as well.

Below screenshot shows the expression being used for the filter and the dynamic value used as input of "Filter Array"

And now, we are going to prepare our output in HTML format by selecting "Create HTML Table"

Input to this step is going to be the "Filter Array" output body. Keeping the columns as "Automatic". This will select all the properties from the Body.

If you want to select specific properties, you can do this by defining the "Custom" Columns like below. In Columns - select Custom, then define each field mapping by defining expression like "item()?['Title']"

For now, I am going to keep Columns as "Automatic" only.

Then, I am going to append this HTML in the string that we initialized at the top, which will be holding the metadata of multiple lists (each given by this Create HTML Table step)

Here is the Value being selected : <h1>@{items('Apply_to_each')?['DisplayName']}</h1>@{body('Create_HTML_table')}</br>

I have added the name of the list by selecting dynamic property "items('Apply_to_each')?['DisplayName']" and added a few HTML tags to add little formatting here.

• Finally, I am going to use "Compose" outside of "Apply to each" to copy the final value of the variable "ListMetaData".
  
• Tada... Your flow is ready. Save this flow, and run it. Open run history, and copy the output from Compose like below.
  

You can save it in an HTML, and here is how html looks in the browser. The bottom list is the one we created with the Description when we started with the article.

If you don't like the idea of copying the output manually, you could also add another step to copy this variable to a file and save that file to SharePoint.

Summary

In this article, we learned many concepts of power automate, like, getting SharePoint lists, invoking Sharepoint APIs, using variables, using parse JSON (which is very handy in power automate), filtering arrays, etc.

For this small list I created, this may look like a tedious job to create the power automate. However, this can help when you have a huge set of fields in each list and there are many lists on site. This can become very handy at that time.

Do let me know your thoughts and suggestions in the comments. I am new to power automate, so I am sure there will be many improvements possible. Please let me know if you found this article helpful and, if not, your suggestions to improve the same.