Introduction
In this article, we will go through the steps to get the Doc ID generated automatically using power automate. In this article, we will use following functions in Power Automate
- split - This function is used to split the string using a separator. And this function requires 2 parameters i.e. the string to split and the separator.
syntax: split(text, separator)
- Indexof - This function is used to find the index of a value within a given string case insensitivity. And the Function requires only two parameters: the string that contains the value and the value to search the index of
syntax: indexOf(text, searchText)
- Substring: This function in Power automate returns a subset of characters from a string. This function requires 3 parameters: the string from which the substring is taken; the index where the substring begins; and the length of the substring. The last two-parameter of the function is number.
syntax: substring (text, startIndex,length)
- first: This function in power automate returns the first value from the array object.
syntax: first(arrayobject)
- last: This function in power automate returns the last value from the array object.
syntax: last(arrayobject)
The design here is we will send an HTTP request to SharePoint using ‘Send HTTP request to SharePoint‘ which is a standard connector. We will use get method and the API method is ‘GetFileByServerRelativeUrl’.
In the previous article, I have explained how to use the URL from your browser and get the IDs. Now we will go through how we can achieve this using PowerAutomate actions.
The idea here is user will be uploading document to document library and user will be getting the Doc ID field updated.
Steps
Step 1 - Create a Document Library or use the standard Shared Document library with the following configuration. Create a column called ‘DocID’ of datatype ‘singe line of text’.
Step 2 - Go to the flow designer portal either from the Office 365 menu from https://portal.office.com or by using the flow portal https://flow.microsoft.com
Step 3 - Create ‘Instant cloud flow’
Step 4 - Search for the trigger with text ‘SharePoint file’ and then select the trigger ‘When a file is created (properties only)’.
Configure the trigger according to your requirements. Here I have chosen one of the site collection from my dev tenant. Below is the screen capture for reference.
Also, don’t forget to give some meaningful name. By default the name says ‘Untitled’.
In this case, I have renamed it ‘GettingDocumentID’.
Step 5 - Initialize a variable of type ‘String’ and name it as ‘secondpart’. You can choose naming as per your convenience. The reason I have declared this variable is
- at first it is required to get the URL property of the document that got created/uploaded
- extract the relative URL of the document and then store in this variable so that it can be used in action ‘send HTTP request to SharePoint’ to get the required file properties.
Unfortunately, the document ID is something that is not available as a default get properties and hence we need to exclusively use SharePoint document rest API end point and then query the results from the responses. Also, you can get some additional properties such as etag, RoleAssignments, GetDlpPolicyTip, etc, which is not available in the default ‘Get File Properties’ action.
Step 6 - Next get the link of the file and the length of the file, which can be done using ‘Get file properties’. Here the ID would be the dynamic property ‘ID’ which is under “(When a file is created properties only)
Step 7 - The document links are little different for word, XLSX and PPTX vs PDF.
- For the PDF files it will be in the below format
https://cts229051.sharepoint.com/teams/InspectionData/Shared%20Documents/MerlinHubDoc5.pdf
- For the Word/Excel/PPTX files it will be in the below format. If noted there is some query string embedded in the URL
https://cts229051.sharepoint.com/teams/InspectionData/Shared%20Documents/MerlinDocsAmena.docx?d=wb1525dac8c9341a59f6d6c678e89ca61
- Irrespective of the PDF file or not, the relative URL where it needs to be passed to SharePoint rest API should be in following format
/ManagedPath/SiteName/LibraryName/FilePathWithExtension
- In my current scenario, the relative URL that needs to be passed would be for PDF file
/teams/InspectionData/Shared%20Documents/MerlinHubDoc5.pdf
- For word/excel/ppt the relative URL would be.
teams/InspectionData/Shared%20Documents/MerlinDocsAmena.docx
Step 8 - At first we need to split based on ‘//’. This splits the link that we got from file properties into array object based on separator //
Syntax: split([Link to item],’[Separator]’)
The configuration of the flow action should be like below screenshot.
The output of the above configuration should be similar to below screen capture. We are interested in the last part of the URL.
Step 9 - To obtain the last part we need to use last function.
Syntax: last(Outputs from Compose Split Url action)
Step 10 - Now we need to check if this is PDF or not. If it is PDF there is no extra query string to the link and we can directly save the output of the second part of URL after trimming ‘https’. In this case, I am using ‘Identifier’ property from the ‘Get file properties’ and checking if it ends with pdf
Below is the screen capture of Yes block configuration
If it is not PDF there will be extra query string to the link and we need to trim query string at the end of the link after trimming https. For this I am using split function again based on ‘?’ separator
After this, I need to extract the first object from the Array output, as the output of split function will be an array.
Now storing the outputs of above compose into the variable ‘second part’
Below is the overall configuration of the no block (Non PDF)
Step 11 - Now next step is to extract domain. This can be done by using ‘substring’ function.
syntax: substring(“string”, StartingIndex, IndexOf(string,’separator’)
Let's try breaking this down. Here we need to extract the domain information from the ‘secondpart’ variable value. The output would be something similar to below
The starting index is 0 as we the index value starts from 0th postion and it is from left to right.
IndexOf(secondpart, ‘/’) – gives the starting index of string before the separator. In this case index upto first / that occurs in the string.
The output for the above substring function would be similar to below.
Step 12 - Now get the length of the domain that is extracted from above compose step.
Step 13 - Now get the length of the variable ‘secondpart’ before trimming the domain information.
Step 14 - Now subtract the ‘secondpart variable length’ from the ‘length of extracted domain’. This is get the range of values that needs to be extracted from second part.
Step 15
syntax: substring(‘string variable’, starting index, range of index to be extracted)
Let's understand with an example here on substituting the example values.
substring(cts229051.sharepoint.com/teams/InspectionData/Shared%20Documents/MerlinDocsAmena.docx,24,61)
The 61 value that we got from the compose range, which is subtracting domain length from ‘Total length of secondpart variable’.
Total Length of second part variable: 85 (length of “cts229051.sharepoint.com/teams/InspectionData/Shared%20Documents/MerlinDocsAmena.docx”)
Total length of domain: 24 (length of “cts229051.sharepoint.com”)
Step 16 - Configure the HTTP request action. Since we are using API GetFileByServerRelativeURL’ substitute the relative URL with outputs of 'Compose_Final_st’ing' action from above steps. For example it woulld be in simiar format to ‘/teams/InspectionData/Shared%20Documents/MerlinDocsAmena.docx’
Step 17 - Get the unique ID by querying the JSON output from Send an HTTP request action. You can refer to references link on how to extract values from JSON output.
Step 18 - Finally update the file properties with the value of GUID property which is document ID property extracted from http action json output.
Conclusion
Thus in this article, you have seen how we can use standard ‘Send an HTTP request to SharePoint’ action to get the document ID. You have also seen how to use 'split', 'substring', 'IndexOf' , 'first' and 'last' functions using PowerAutomate expressions. You can refer to the attached flow package and export it to your environment to use it and modify it accoridingly to suit the needs.
References
- https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-split
- https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/
- https://blog.enterprisedna.co/power-automate-string-functions-substring-and-indexof/
- https://www.enjoysharepoint.com/power-automate-string-functions/
- https://www.c-sharpcorner.com/article/how-to-get-document-id-in-sharepoint-online/