Help with Iterating Excel Rows and Sending HTML Table

[fladsonlima2022]

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!

[v-yetonggu-msft]

Hi @fladsonlima2022 ,

I did a test for your reference.

In my scenario:

My SharePoint List:

My Inbox:

My Flow:

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

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

Best Regards,

Sunshine Gu

[fladsonlima2022]

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”:

Example of the report:

Overview:

1 – Ler relatório:

2- Selecionar:

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

Mapa: item()?['Prefix']

3- Compor:

4- Obter itens:

5- Aplicar a cada:

5.1 – Matriz do filtro:

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

Prefixo: item()?['Title']

5.2 – Selecionar email:

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

Mapa: item()?['email']

5.3 – Compor email:

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

5.4 – Criar tabela HTML:

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

Prefixo: item()?['Title']

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

Comercial: item()?['field_2']

5.5 – Enviar email:

Para: outputs('Compor_email')

Corpo: body('Criar_tabela_HTML')

Email response:

[v-yetonggu-msft]

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 · Accepted solution]

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:

Result: 2 different emails with each case separated by manager

Best regards,
Fládson Lima