How to Work Effectively With SharePoint Documents using Power Automate

Managing documents in the context of model-driven apps (MDAs) is one of the most crucial requirements asked by clients when defining their business processes. A sizable part of this is achieved by enabling server-based SharePoint integration and then configuring the Document Management settings for the Dataverse environment. This will allow users to manage documents that are specific to a Dataverse record contextually from within the MDA without having to navigate to SharePoint. The part I wanted to discuss today is what goes beyond this basic setup, those requirements that require a certain level of automation. For this, we will leverage the power of Power Automate 🤓.

In this post, we will walk through a simple scenario that will allow us to use connectors such as Dataverse, SharePoint, Excel Online (Business) and Microsoft 365 Group to work more effectively with SharePoint Documents.

As a side note, this post came about after a specific requirement I had to dynamically work out the input values for the Run Script action for the Excel Online (Business) connector. Although this concept of working with SharePoint using Power Automate flow is not new, I thought it would be good to document the entire process in case someone else needs it. So, let’s get to it!

 

What’s Our Scenario?

For our fictitious scenario, we will be using the following Dataverse tables:

The requirements are as follows:

1. Only the Project table is selected for SharePoint document management
2. When the status of the Project Release record changes from Not Started to In Progress, an Excel document will be created containing the financial statements about the release. This will use the Excel template that is stored in a document library called Template in one of the other SharePoint sites
3. This document will:
   • follow a naming convention of Financial-Statements_<Project Code>_<Region Name>_<Release Version>.<Excel template extension>;
   • saved in a folder called Financials under the project document location;
   • a worksheet will be added to the document that contains a table of metadata regarding the project using Office Script

We will use this high-level flow diagram, annotated with reference numbers for each Cloud flow action to aid further discussions:

 

Laying the Groundwork

When working with SharePoint documents, there are many things you need to know about the context of the document, like the parent site, document library it resides in, folders, etc. To simplify our scenario, let’s assume the project record already has relevant SharePoint document locations and sites set up in Dataverse as records. To retrieve these records we can use the List rows Microsoft Dataverse action on the Document Locations table with the following Fetch XML Query:

<fetch>
  <entity name="sharepointdocumentlocation">
    <attribute name="sharepointdocumentlocationid" />
    <attribute name="relativeurl" />
    <filter>
      <condition attribute="regardingobjectid" operator="eq" value="@{body('Get_Project_Release')?['trh_ProjectId']?['trh_projectid']}" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="sharepointdocumentlocation" from="sharepointdocumentlocationid" to="parentsiteorlocation" alias="parentlocation">
      <attribute name="sharepointdocumentlocationid" />
      <attribute name="relativeurl" />
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
      <link-entity name="sharepointsite" from="sharepointsiteid" to="parentsiteorlocation" alias="parentsite">
        <attribute name="sharepointsiteid" />
        <attribute name="absoluteurl" />
        <attribute name="isdefault" />
        <filter>
          <condition attribute="statecode" operator="eq" value="0" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

The document location for the project record is two levels deep in relation to the site, therefore we can consistently retrieve both the parent document location and parent site records at the same time, and use aliases to easily identify them in the payload. The request only returns one record in the results array so we will output the first record into the Project_Document_Location compose action for easier access:

{
  ...
  "sharepointdocumentlocationid": "372a9f27-272d-ec11-b6e6-00224811a7b5",
  "relativeurl": "General",
  ...
  "parentsite.sharepointsiteid": "6049c529-262d-ec11-b6e6-00224811a7b5",
  "parentsite.absoluteurl": "https://demo.sharepoint.com/sites/ProjectHappiness",
  ...
  "parentlocation.sharepointdocumentlocationid": "02ef2654-262d-ec11-b6e6-00224811a7b5",
  "parentlocation.relativeurl": "Shared Documents"
}

Since a lot of this data, in its many shapes and forms, will be re-used in many actions in this Cloud flow, we are going to use a single object variable called DocumentMetadata to store and access this information. This will limit the amount of repetitive work that we need to do at each flow action, like retrieving and dicing of different outputs, and also reduce the number of variables that we may need to store some of this information. Here is an example of what this object variable value would look like:

{
  "url": "https://demo.sharepoint.com/sites/ProjectHappiness",
  "rootDomain": "demo.sharepoint.com",
  "rootDomainUrl": "https://demo.sharepoint.com",
  "sitePath": "/sites/ProjectHappiness",
  "folderAbsolutePath": "/Shared Documents/General/Financials",
  "folderRelativePath": "General/Financials",
  "parentFolder": "General",
  "targetFolder": "Financials",
  "fileName": "Financial-Statements_P-1000_Oceania_1.0.0.xlsx",
  "documentLibrary": "Shared Documents",
  "documentLibraryId": "81baa9ac-d1cc-434d-bec1-ae002047ab34",
  "documentLibraryName": "Documents",
  "documentId": 41
}

The following is a table of object properties with the flow expressions used to set the property values:

Property Name	Expression	Comment
url	outputs(‘Project_Document_Location’)?[‘parentsite.absoluteurl’]
rootDomain	uriHost(outputs(‘Project_Document_Location’)?[‘parentsite.absoluteurl’])
rootDomainUrl	concat(uriScheme(outputs(‘Project_Document_Location’)?[‘parentsite.absoluteurl’]), ‘://’, uriHost(outputs(‘Project_Document_Location’)?[‘parentsite.absoluteurl’]))
sitePath	uriPath(outputs(‘Project_Document_Location’)?[‘parentsite.absoluteurl’])
folderAbsolutePath	concat(‘/’,outputs(‘Project_Document_Location’)?[‘parentlocation.relativeurl’],’/’,outputs(‘Project_Document_Location’)?[‘relativeurl’],’/’,parameters(‘Financial Statement Target Folder’))
folderRelativePath	concat(outputs(‘Project_Document_Location’)?[‘relativeurl’],’/’,parameters(‘Financial Statement Target Folder’))
parentFolder	outputs(‘Project_Document_Location’)?[‘relativeurl’]
targetFolder	parameters(‘Financial Statement Target Folder’)
fileName	concat(‘Financial-Statements_’, body(‘Get_Project_Release’)?[‘trh_ProjectId’]?[‘trh_projectcode’], ‘‘, body(‘Get_Project_Release’)?[‘trh_DeploymentRegionId’]?[‘trh_name’], ‘‘, body(‘Get_Project_Release’)?[‘trh_releaseversion’], ‘.’, last(split(parameters(‘Financial Statement Template Path’),’.’)))	Naming convention – Financial-Statements_<Project Code>_<Region Name>_<Release Version>.<Excel template extension>
documentLibrary	outputs(‘Project_Document_Location’)?[‘parentlocation.relativeurl’]

For any environment-specific values, we will use the environment variables to store the information:

Display Name	Data Type	Value
Document Storage Site	Data Source (SharePoint) Site	References – https://demo.sharepoint.com/sites/DocumentStorage
Template Library	Data Source (SharePoint) List	References – https://demo.sharepoint.com/sites/DocumentStorage/Template
Financial Statement Template Path	Text	Financials/Financial-Statements-Template.xlsx
Financial Statement Target Folder	Text	Financials

Other details we will need later, and reference it many times in our actions, are the project’s SharePoint document library ID and name. For this, we will need to call the SharePoint REST API using the Send an HTTP request to SharePoint action. The easiest way I found to retrieve the document library ID and name using the relative URL is to expand and filter by the RootFolder name:

E.g.
Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
Uri: /_api/lists?$expand=RootFolder&$filter=(RootFolder/Name eq 'Shared Documents')&$select=Title,Id

With the returned result, we can then set the documentLibraryId and documentLibraryName properties of the DocumentMetadata variable for later usage.

 

1. Check if Financial Statement document exists

The first step is to check whether the financial statement document exists for the project release. There are two ways to approach this.

Option 1. Microsoft Dataverse Connector – List rows action [List Project Documents]

The SharePoint documents for the project record can be queried directly on the Documents table using the Fetch XML Query:

<fetch distinct="false" mapping="logical" returntotalrecordcount="true" page="1" count="10" no-lock="false">
    <entity name="sharepointdocument">
        <attribute name="documentid"/>
        <attribute name="fullname"/>
        <attribute name="relativelocation"/>
        <attribute name="sharepointcreatedon"/>
        <attribute name="ischeckedout"/>
        <attribute name="filetype"/>
        <attribute name="modified"/>
        <attribute name="sharepointmodifiedby"/>
        <attribute name="servicetype"/>
        <attribute name="absoluteurl"/>
        <attribute name="title"/>
        <attribute name="author"/>
        <attribute name="sharepointdocumentid"/>
        <attribute name="readurl"/>
        <attribute name="editurl"/>
        <attribute name="locationid"/>
        <attribute name="iconclassname"/>
        <attribute name="locationname"/>
        <order attribute="relativelocation" descending="false"/>
        <filter>
            <condition attribute="isrecursivefetch" operator="eq" value="1"/>
            <condition attribute="relativelocation" operator="eq" value="@{variables('DocumentMetadata')?['parentFolder']}"/>
        </filter>
        <link-entity name="trh_project" from="trh_projectid" to="regardingobjectid" alias="project">
            <filter type="and">
                <condition attribute="trh_projectid" operator="eq" value="@{body('Get_Project_Release')?['trh_ProjectId/trh_projectid']}"/>
            </filter>
        </link-entity>
    </entity>
</fetch>

This retrieves all the documents directly under the relative path where the document location for the project record is set to. Something to note, this table cannot be queried using Advanced Find, nor by using the Dataverse OData query but only by using a very specific FetchXml query. By setting the isrecursivefetch filter to 1, it will recursively find only the document nodes and ignore folders in the result. To find the document we need, we will need to use the Filter array action on the returned result. Due to its recursive nature, along with its inability to perform further filtering at the time of querying (e.g. by document name), this option may not be ideal especially when you know that there will be many other documents stored against the project record.

Option 2. SharePoint Connector – Get file metadata using path [Get Financial Statement Document]

When the exact path to the document file is known, it is possible to check if the file exists through an attempt to retrieve its metadata. If the file is not found, the HTTP status code of the request will return 404 not found.

E.g. 
Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
File Path: /Shared Documents/General/Financials/Financial-Statements_Oceania_1.0.0.xslx

Make sure to set the Configure run after setting for the condition to run after is successful or has failed.

 

2. & 2.a. Check if Financials folder exists, if not, create Financials folder

2. SharePoint Connector – Get folder metadata [Get Financials Folder]

Similarly to checking if the document exists by requesting its metadata by using the file path, we can do the same for a SharePoint folder.

E.g.
Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
File Identifier: /Shared Documents/General/Financials

2.a. SharePoint Connector – Create new folder [Create Financials Folder]

The target folder, Financials, must be created prior to creating the financial statement document. The interesting input here is the List or Library input, this can accept both the SharePoint document library ID, as well as the document library name.

E.g.
Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
List or Library: 81baa9ac-d1cc-434d-bec1-ae002047ab34 (or can use document library name: Documents)
Folder Path: General/Financials

 

2.b. Copy Financial Statement template

The financial statement document is created from an Excel template that resides in a different SharePoint site and document library. We are using the Dataverse Data Source environment variables to store the identifiers for those data sources. To copy the file, we must know the exact path to the document template, so in order to do that we must first find out the relative URL of the Templates document library.

SharePoint Connector – Send an HTTP request to SharePoint [Get Template Document Library]

The relative path for the Templates document library can be retrieved by requesting the name of its root folder:

SharePoint Connector – Copy file [Copy Financial Statement Template]

Providing input parameter values for this action is relatively easy now that we have all the information we need.

E.g.
Current Site Address: https://demo.sharepoint.com/sites/DocumentStorage
File to Copy: /Template/Financials/Financial-Statements-Template.xlsx
Destination Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
Destination Folder: /Shared Documents/General/Financials

 

2.c. Rename Financial Statement document

I was searching for the most efficient way to rename a document in a SharePoint document library without having to compose a complicated SharePoint REST API request or an even more sub-optimal option of using the Move file SharePoint action. Then I came across this cool article by Tom Riha. The article shows you how to use the ValidateUpdateListItem method exposed on the SharePoint REST API to update the list item metadata, in our case the document name:

E.g.
Site Address: https://demo.sharepoint.com/sites/ProjectHappiness
Uri: /_api/web/lists/GetById('81baa9ac-d1cc-434d-bec1-ae002047ab34')/items(2)/validateUpdateListItem
Body: {
    "formValues":[
	{
	    "FieldName": "FileLeafRef",
	    "FieldValue": "Financial-Statements_P-1000_Oceania_1.0.0.xlsx"
	}
    ]
}

 

3. Get Project Site

To demonstrate that the Excel Online (Business) connector can accept more than one form of Location source values for its actions, let’s retrieve the SharePoint site ID to demonstrate that. For this, we can either use the SharePoint REST API or Microsoft Graph API to retrieve the site information.

Before we move on, I would like to touch base on the fact that Microsoft Graph API endpoints for SharePoint resources are backed by SharePoint REST API V2. In general Microsoft Graph API acts as a mega proxy for various service endpoints it exposes, for SharePoint it is using the V2 endpoint.

Note: The Microsoft Graph API is undoubtedly more intuitive and well documented than SharePoint REST API, if you ever want to work backwards by composing the Microsoft Graph API query to workout the SharePoint REST API query, you can simply append this param $whatif to your query. This neat param will translate the Microsoft Graph API URL to SharePoint REST API URL. Try this now using the Microsoft Graph Explorer.
e.g. 
https://graph.microsoft.com/v1.0/sites/demo.sharepoint.com?$whatif =>
https://demo.sharepoint.com/_api/v2.0/sites('demo.sharepoint.com')

Option 1. Microsoft 365 Group Connector – Send an HTTP request (preview) [Get Project Site using Graph]

I didn’t know this action existed until quite recently, it allows you to request Microsoft Graph API using the user delegated permissions without having to set up an Azure App Registration for authentication and authorisation. Unlike the HTTP connector, this provides a non-premium option for makers.

Since we are using a server-relative URL for a site from the root SharePoint site, we can use the following syntax:

GET /sites/{hostname}:/{server-relative-path}

E.g.
URI: https://graph.microsoft.com/v1.0/sites/demo.sharepoint.com:/sites/ProjectHappiness?$select=id,name,webUrl

Option 2. SharePoint Connector – Send an HTTP request to SharePoint [Get Project Site]

Some of the SharePoint REST API endpoints can be executed from the context of the specific site (demonstrated below). Another option is to be more explicit like the following:

GET https://demo.sharepoint.com/_api/v2.0/sites('demo.sharepoint.com'):/sites/ProjectHappiness?$select=id,name,webUrl

E.g.
URI: https://demo.sharepoint.com/sites/ProjectHappiness/_api/v2.0/sites/root?$select=id,name,webUrl,parentReference

 

4. Get Project Site List and Drive

The Document Library input parameter for the Excel Online (Business) connector actions requires the unique drive ID of the SharePoint document library. At first, I tried to use the SharePoint list ID and its name but realised that those are invalid values. Since we already know the document library ID, the easiest way is to query for the list and then expand on the underlying drive information.

Option 1. Microsoft 365 Group Connector – Send an HTTP request (preview) [Get Project Site List and Drive using Graph]

E.g. 
URI: https://graph.microsoft.com/v1.0/sites/demo.sharepoint.com:/sites/ProjectHappiness:/lists/81baa9ac-d1cc-434d-bec1-ae002047ab34?$select=id,name,displayName,webUrl&expand=drive($select=id,name,webUrl)

Hindsight, since I don't need all the SharePoint document library information again, instead of expanding the drive from the list, I could have easily navigated directly to the drive using this URI: https://graph.microsoft.com/v1.0/sites/demo.sharepoint.com:/sites/ProjectHappiness:/lists/81baa9ac-d1cc-434d-bec1-ae002047ab34/drive

Option 2. SharePoint Connector – Send an HTTP request to SharePoint [Get Project Site List and Drive]

E.g. 
Site Address: https://demo.sharepoint.com
Uri: _api/v2.0/sites('demo.sharepoint.com'):/sites/ProjectHappiness:lists/81baa9ac-d1cc-434d-bec1-ae002047ab34?$select=id,name,displayName,webUrl&expand=drive($select=id,name,webUrl)

 

5. Get Document Drive Item

The File input parameter for the Excel Online (Business) connector actions can accept a string literal path to the document, instead, I wanted to demonstrate that the driveitem ID can be used instead (Note: The driveitem represents an item within a drive, like a document). To do that, we need to retrieve the driveitem by the SharePoint document ID.

Note: We had plenty of opportunities to retrieve the document ID in our flow process, i.e. steps 1 and 2.b. I didn't go into explicit detail but this value was set as documentId property on the DocumentMetadata variable using setProperty expression.

Option 1. Microsoft 365 Group Connector – Send an HTTP request (preview) [Get Document Drive Item using Graph]

E.g. 
URI: https://graph.microsoft.com/v1.0/sites/demo.sharepoint.com:/sites/ProjectHappiness:/lists/81baa9ac-d1cc-434d-bec1-ae002047ab34/items/41/driveItem?$select=id,name

Option 2. SharePoint Connector – Send an HTTP request to SharePoint [Get Document Drive Item]

6. Run Office Script

All the hard work up to this point was for this Office Script 😂. As you can see, the outputs from the previous 3 steps are being used to provide input values for this Office Script action. The Location and File input parameters can take alternative values as shown.

E.g.
Location: demo.sharepoint.com,75997842-71f1-42cf-9327-eec2f2a071c4,7b714cd7-35e3-4616-ae10-52129c2196ab
Location (alternative): https://demo.sharepoint.com/sites/ProjectHappiness
Document Library: b!QniZdfFxz0KTJ-7C9qB4xNdMcXvjNRZGrhBSEpwjlqusqbqBzNFNQ77BrgAgR7s0
File: 01KQDDGBSGSYOTRB7HKRR2RURWV2AB3PZK
File (alternative): /General/Financials/Financial-Statements_P-1000_Oceania_1.0.0.xlsx

What is really cool about Office Script is that it can take an object as a parameter, so if you would like to send a collection of values as one input parameter, you can compose one from Power Automate flow and pass it through. I won’t go into details of how to write an Office Script here but you can read about it in the Microsoft docs. This is the simple Office script I used for this scenario:

This script will generate the System Only worksheet:

 

Hope you enjoyed the post, let me know your thoughts in the comments below or any of my contact channels 😎

Photo by Arisa Chattasa on Unsplash