Skip to main content

Notifications

Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : g8H1dZXqLOEXk1VbnA+7d2
Power Platform Community / Forums / Power Automate - Building Flows / Create a flow which fe...
Power Automate - Building Flows
Answered

Create a flow which fetches the list of Active/Resolved work items from Azure DevOps and daily re-populates the online sharepoint excel table

Like (0) ShareShare
ReportReport
Posted on 18 Apr 2023 10:04:51 by hjoshi4u26 93

The scenario is to keep a table updated with Active/Resolved Bugs List in an online sharepoint excel. You will have to manually keep on editing each cell value in excel after opening the DevOps work item links. But with power automate you can do it within 1-2 minutes.

To do this you will have to list all the rows in the excel table, delete all rows, fetch devops queries result, update online excel table and if required an extra step to send an email.

Shown below:

Given below is the online excel table which you have to keep updated

hjoshi4u26_0-1681808943326.png

 

1. Set the recurrence for eg manually trigger the flow

hjoshi4u26_1-1681809119524.png

 

2. List Rows present in a table

hjoshi4u26_4-1681809247717.png

 

3. Apply to each --> use the 'Value' output of 'List rows present in a table' in 'Apply to each' input --> outputs('List_rows_present_in_a_table')?['body/value']

hjoshi4u26_5-1681809484395.png

 

4. in Apply to each add a new step 'Delete a Row'

hjoshi4u26_6-1681809592382.png

{
    "inputs": {
        "host": {
            "connectionName": "shared_excelonlinebusiness",
            "operationId": "DeleteItem",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"
        },
        "parameters": {
            "source": "groups/*******************************",
            "drive": "************************",
            "file": "*************************",
            "table": "{**********************}",
            "idColumn": "ID",
            "id": "@items('Apply_to_each_2')?['ID']"
        },
        "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
        }
    },
    "metadata": {
        "************************": "/General/********************.xlsx",
        "operationMetadataId": "*******************",
        "tableId": "{*******************}"
    }
}
In above screenshot the Key column should be the one which contains non-repeating values of the online excel table rows which you want to delete. Key Value should contain the value of online excel cell which you want to delete by referencing entire row. Doing this will delete all rows in the table
 
5. Get query result
hjoshi4u26_7-1681809932938.png

you can either enter the Query ID from the Query URL or navigate to the query by clicking the folder icon

Now here I have a column in query called 'Assigned To' which has the values in the format of 'Name Surname <email>'. We will separate the Name (Name & Surname combined in this case) and email id and enter into 2 different columns in online excel.

We have to do it on all the query results so we will again use 'Apply to each'.

 

6. Apply to each --> use 'Value' output of 'Get query results' as input of 'Apply to each' --> outputs('Get_query_results')?['body/value']

hjoshi4u26_8-1681810603829.png

 

7. Inside 'Apply to each' add a new step 'Compose' to split the Name and email id.

hjoshi4u26_9-1681810716726.png

split(items('Apply_to_each')?['System.AssignedTo'], ' <')

This will split the 'Assigned To' values into 2 outputs i.e Name & Surname || email>. So outputs('Compose')[0] = Name & Surname || outputs('Compose')[1] = email>. After splitting the value we will have to enter them into online excel file, so we will need to again use multiple compose.

 

8. Inside 'Apply to each' add a new step 'Compose' for Split [0]

hjoshi4u26_10-1681811023094.png

 

9. Inside 'Apply to each' add a new step 'Compose' for Split [1]

hjoshi4u26_11-1681811101110.png
Please note output('Compose')[1] = email>. We do not require '>' at the end of the email. So we will replace it.

 

10. Inside 'Apply to each' add a new step 'Compose' for replacing '>' at then end of email.

hjoshi4u26_12-1681811262128.png

replace(outputs('Compose_3'),'>','')

 

11. Inside 'Apply to each' add a new step 'Add a row into table'

hjoshi4u26_13-1681811386898.png
{
    "inputs": {
        "host": {
            "connectionName": "shared_excelonlinebusiness",
            "operationId": "AddRowV2",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"
        },
        "parameters": {
            "source": "groups/****************",
            "drive": "*****************",
            "file": "***************",
            "table": "{*****************}",
            "item/ID": "@items('Apply_to_each')?['System.Id']",
            "item/Work Item Type": "@items('Apply_to_each')?['System.WorkItemType']",
            "item/Title": "@items('Apply_to_each')?['System.Title']",
            "item/Name": "@outputs('Compose_2')",
            "item/Email": "@outputs('Compose_4')",
            "item/State": "@items('Apply_to_each')?['System.State']",
            "item/Due Date": "@if(empty(items('Apply_to_each')?['Microsoft.VSTS.Scheduling.DueDate']),'',formatDateTime(items('Apply_to_each')?['Microsoft.VSTS.Scheduling.DueDate'],'dd-MMM-yyyy'))"
        },
        "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
        }
    },
    "metadata": {
        "*****************************": "/General/********************.xlsx",
        "operationMetadataId": "************************",
        "tableId": "{*************************}"
    }
}
 
This flow will delete all the rows in online table, fetch query results, split Name and Email, enter the data into online excel. Now you can use 'List rows in a table', 'Filter' condition, 'Create HTML Table', and send an email as optional steps if required which I am listing below. All this steps will be done outside of 'Apply to each'.
 
12. List rows present in a table
hjoshi4u26_14-1681811829707.png

 

Now my table has a column which lists the colour value Red/Yellow/Green. Supposedly we want to send the data of only Red colour.

 

13. Filter Array

hjoshi4u26_15-1681811921633.png
{
    "inputs": {
        "from": "@outputs('List_rows_present_in_a_table_2')?['body/value']",
        "where": "@equals(item()?['Colour'], 'Red')"
    },
    "metadata": {
        "operationMetadataId": "*********************"
    }
}
 
14. Select to map the Table
hjoshi4u26_16-1681811989607.png
{
    "inputs": {
        "from": "@body('Filter_array')",
        "select": {
            "ID": "@item()?['ID']",
            "Work Item Type": "@item()?['Work Item Type']",
            "Title": "@item()?['Title']",
            "Assigned To": "@item()?['Name']",
            "Email": "@item()?['Email']",
            "State": "@item()?['State']",
            "Due Date": "@if(empty(item()?['Due Date']),'',formatDateTime(item()?['Due Date'],'dd-MMM-yyyy'))",
            "Days Remaining": "@item()?['Days Remaining']"
        }
    },
    "metadata": {
        "operationMetadataId": "*****************"
    }
}
 
15. Create HTML Table
hjoshi4u26_17-1681812055534.png
{
    "inputs": {
        "from": "@body('Select')",
        "format": "HTML"
    },
    "metadata": {
        "operationMetadataId": "*******************"
    }
}
 
16. Compose
hjoshi4u26_18-1681812107841.png
{
    "inputs": "@replace(replace(replace(replace(replace(body('Create_HTML_table'), '&lt;', '<'), '&gt;', '>'), '&amp;', '&'), '&#39;', '\"'),'&quot;','\"')",
    "metadata": {
        "operationMetadataId": "*******************"
    }
}
 
17. Compose to format HTML Table
hjoshi4u26_19-1681812193405.png

<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 100%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>
@{outputs('Compose_5')}

 

18. Send an email V2

hjoshi4u26_20-1681812275755.png

 

  • Verified answer
    hjoshi4u26 Profile Picture
    hjoshi4u26 93 on 18 Apr 2023 at 10:06:11
    Re: Create a flow which fetches the list of Active/Resolved work items from Azure DevOps and daily re-populates the online sharepoint excel table

    I do not have any problem in this flow. Just posting an idea. @krishsolver 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans