Skip to main content

Notifications

Power Automate - General Discussion
Answered

Help with Iterating Excel Rows and Sending HTML Table

(0) ShareShare
ReportReport
Posted on by 13

Hello Power Automate Community,

 

I am seeking assistance with a manually triggered flow I am working on. My goal is to read data from an Excel spreadsheet, match each row with a list in SharePoint named 'ListGerel' using OData to compare the 'Prefix' column, then create an HTML table with those matched rows to send a personalized message to each manager via Microsoft Teams.

 

Here’s what my current flow does:

  1. Manual trigger is initiated.
  2. Lists rows from a table in an Excel file named 'Report.xlsx'.
  3. Retrieves items from the 'ListGerel' list in SharePoint by matching the 'Prefix' column from Excel with the 'Prefix' (Title) column in SharePoint using an OData filter.
  4. Creates an HTML table with the fetched rows that correspond to each prefix.

The part I am struggling with is how to efficiently iterate through each row of the Excel spreadsheet, generate a separate HTML table for each set of rows sharing the same prefix, and send that table to the corresponding manager in Teams using the email from the 'email' column in the SharePoint list.

 

I am looking for guidance on the best way to:

  • Efficiently iterate through each row of the Excel spreadsheet.
  • Generate a separate HTML table for each group of rows with the same prefix.
  • Send this table to the appropriate manager on Teams, grabbing the manager's email from the 'ListGerel' list in SharePoint.

Any insights, suggestions on approaches, or examples of how to set up these steps in Power Automate would be incredibly helpful.

I appreciate any assistance you can provide!

Categories:
  • Verified answer
    fladsonlima2022 Profile Picture
    fladsonlima2022 13 on at
    Re: Help with Iterating Excel Rows and Sending HTML Table

    Hi, @v-yetonggu-msft.

    The information from the Excel table. The Sharepoint list would just be the source for searching for recipients.

    After many tests and together with the tips in this video: https://www.youtube.com/watch?v=bS4eV_9hK9o, I managed to achieve the expected result. Once again, thanks for the support.


    Here's an overview:

    fladsonlima2022_0-1711621734516.png

    fladsonlima2022_2-1711622335172.png

    fladsonlima2022_4-1711623201587.png

    fladsonlima2022_5-1711623203511.png

    fladsonlima2022_6-1711623691283.png

    fladsonlima2022_8-1711624058760.png

     

    Result: 2 different emails with each case separated by manager

    fladsonlima2022_9-1711624125069.png

    fladsonlima2022_10-1711624143893.png


    Best regards,
    Fládson Lima

  • Re: Help with Iterating Excel Rows and Sending HTML Table

    Hi @fladsonlima2022 ,

    I'm confused whether you want to send the item information in the SharePoint list or the information in the Excel Table to the manager?

    Best Regards,

    Sunshine Gu

  • fladsonlima2022 Profile Picture
    fladsonlima2022 13 on at
    Re: Help with Iterating Excel Rows and Sending HTML Table

    Hi, @v-yetonggu-msft 

    Thank you for your support.

    I have managed to understand the logic of the flow, but it seems there is still something that needs to be adjusted, as a single table is being sent for each prefix. In the report, there are 4 occurrences, with 2 for each prefix. The recipient is only receiving 1 email with a single occurrence.

    I tried to replicate the same logic that was suggested, but I believe I made a mistake at some point. Here are more details about the results of the applied test:


    Image of the list, which is actually called “Lista Rede”:

    fladsonlima2022_1-1711456487992.png


    Example of the report:

    fladsonlima2022_2-1711456551804.png


    Overview:

    fladsonlima2022_3-1711456602368.png

    fladsonlima2022_4-1711456623226.png

    1 – Ler relatório:

    fladsonlima2022_15-1711456675899.png

     

    2- Selecionar:

    fladsonlima2022_16-1711456814235.png

     

    De: outputs('Ler_Relatório')?['body/value']

    Mapa: item()?['Prefix']

     

    3- Compor:

    fladsonlima2022_17-1711456905005.png

     

    4- Obter itens:

    fladsonlima2022_18-1711456927788.png

     

     

    5- Aplicar a cada:

    fladsonlima2022_19-1711456949592.png

     

     

    5.1 – Matriz do filtro:

    fladsonlima2022_21-1711457038050.png

    De: outputs('Obter_itens')?['body/value']

    Prefixo: item()?['Title']

     

    5.2 – Selecionar email:

    fladsonlima2022_20-1711456970525.png

    De: body('Matriz_do_filtro')?['Body']

    Mapa: item()?['email']

     

    5.3 – Compor email:

    fladsonlima2022_22-1711457166062.png

    Entradas: join(union(body('Selecionar_email'),body('Selecionar_email')),';')

     

     

    5.4 – Criar tabela HTML:

    fladsonlima2022_23-1711457190858.png

    De: body('Matriz_do_filtro')?['Body']

    Prefixo: item()?['Title']

    Dependência: item()?['field_1']

    Comercial: item()?['field_2']

     

    5.5 – Enviar email:

     

    fladsonlima2022_24-1711457222066.png

    Para: outputs('Compor_email')

    Corpo: body('Criar_tabela_HTML')

     

    Email response:

    fladsonlima2022_25-1711457244847.png

     

     

     

     

  • Re: Help with Iterating Excel Rows and Sending HTML Table

    Hi @fladsonlima2022 ,

    I did a test for your reference.

    In my scenario:

    My SharePoint List:

    vyetonggumsft_2-1711420746676.png

    My Inbox:

    vyetonggumsft_3-1711420832122.png

    My Flow:

    vyetonggumsft_4-1711420858281.png

    vyetonggumsft_5-1711421098131.png

     

    item()?['Prefix']
    union(body('Select'),body('Select'))

     

    vyetonggumsft_0-1711422655519.png

     

    item()?['email']
    join(union(body('Select_email'),body('Select_email')),';')
    item()?['Title']
    item()?['Prefix']
    item()?['email']
    item()?[Due']

     

    Best Regards,

    Sunshine Gu

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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard