How to filter a column in excel and send an email containing the filtered data per sheet and email

[Kamacete]

Hi,

I'm trying to create a process to help me filter an excel column, get that data, and send an email to some identified email in another sheet.

to give a better view I have created "some kind" of a visual flow to try to explain the difficulty and if it's possible to create.

I do not know if I can attach the excel file, but I leave the prints below

I had tried to create a flow base on some examples that I found. however, I can´t filter the result, and can't choose the second filter.

thank you in advance,

best regards

Rui

[MarvinBangert]

Hi @Kamacete 

are there any relations between sheet1, sheet2 and sheet3? Or do you just need to go through all sheets, only get the items where the status is "go", "check" or "Validate"? Also, I don't really get the value things, why do you need to filter it, if you are no matter what value it is, need to get the email address and send an email?

Here is my first thought, hopefully I got your right:

Use the excel action "List rows present in a table" and already apply the filter within the "Filter query" section to only get items in status "go", "check" or "validate". Do this for all three tables (you can use the "get tables" excel action with an "apply to each" around the "List rows present in a table") and add the result to an array variable within your flow (append to array variable). That will give you a complete array from all three sheets. I don't understand what your "group" is, that you want to put into the email, maybe you need to use some "Filter array" actions on your whole array variable to only get the items you want. Of course you also need to select the email address list (I guess the list will be unique in production, you should look for only having unique information in there).

Also: When you have multiple actions executed every time for the same row or sheet, you could consider using a solution and child flows, that will get triggered with some information from the parent flow, does the job and sends back the information instead of building huge "apply to each" runs.

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[Kamacete]

Hi Marvin,

Thank very much for you reply.

The sheets are not related, but I have to go through all sheets to get the information and send one email by sheet (I can use the same email to send all sheets, just have to add a new column with the sheet name).

I need to filter, because I just what to send 3 of 6 stages, I leave a example below for a better understanding. this is my blocking point for now, when I run the flow, I received one email by status.

[Excel by sheet:]

[Email to be send:]

Emails address to be use or emails groups:

for each email I need to put the "Owner" on to: and others emails on CC, those (CC: emails) are not in the sheet, or don't have tasks pending, that why I thought in add a new sheet with that information. but maybe I have a easy way to do it.

in the end of the email I need just one more think. I need to upload the "Status" with a new status, in this particular case, I have to change from "new" for "info requested".

I will tried to recover the flow that I had created based on an examples that I found and post here to show you what I have (I didn't post because I changed and the flow stop to work).

I do not know if I pass the all picture, and I appreciate you help.

thank you very much

Rui

[MarvinBangert]

Hi @Kamacete 

I guess I got you in most cases, please correct me if I got something wrong:

You could start by using the "List rows present in a table" for all three sheets to get the information. Try to filter it within this action using the Filter Query to only get the information you need within the email. Afterwards you could use the "Filter array" action to filter the sheet content into different smaller arrays/tables. Using "Create HTML table" you can generate the table from the smaller arrays, you can use this html tables within your email.

To get all the email addresses from the other list, you can create a string variable at the beginning of your flow, use an "apply to each", run through the array with the information in it (in best case it's filtered by the emails you need using the "Filter Query" within the Excel action or using the "filter array" action. Then use the "apply to each" to add each email to the string variable with an ";" at the end of each email. This will generate a long string of all addresses you can use within your "Send an email" and this shouldn't generate multiple emails for the same content (only for each sheet).

I didn't test it completely; I would need to rebuild this scenario. Maybe this already helps you, otherwise let me know and I will try to build something similar.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[MarvinBangert]

Hi @Kamacete 

I guess I got you in most cases, please correct me if I got something wrong:

You could start by using the "List rows present in a table" for all three sheets to get the information. Try to filter it within this action using the Filter Query to only get the information you need within the email. Afterwards you could use the "Filter array" action to filter the sheet content into different smaller arrays/tables. Using "Create HTML table" you can generate the table from the smaller arrays, you can use this html tables within your email.

To get all the email addresses from the other list, you can create a string variable at the beginning of your flow, use an "apply to each", run through the array with the information in it (in best case it's filtered by the emails you need using the "Filter Query" within the Excel action or using the "filter array" action. Then use the "apply to each" to add each email to the string variable with an ";" at the end of each email. This will generate a long string of all addresses you can use within your "Send an email" and this shouldn't generate multiple emails for the same content (only for each sheet).

I didn't test it completely; I would need to rebuild this scenario. Maybe this already helps you, otherwise let me know and I will try to build something similar.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[Kamacete]

Hi Marvin, 

Thank you once more.

How can I send to you the flow that I have?

it's to long to put in where.

[Kamacete]

Let see if I can show what I have do in here.

🙂

thank you

Rui

[Kamacete]

Hi,

I'm

 

trying to apply more than one option on the filter, but without success.

this is what I have.

I will have to apply another filter for another column, can some one help me it this?

thank you in advance

Rui

[v-yujincui-msft]

Hi @Kamacete ,

As we know we cannot use 'AND' and 'OR' operator in the filter query(List rows present in a table Action). In filter query, it only supports single 'eq', 'ne', 'contains', 'startswith' or 'endswith'. So we cannot create multiple filter queries in Power Automate list rows present in a table.

Maybe you could consider adding a Filter array Action.

@or(contains(item()?['Status'], 'Info requested'),contains(item()?['Status'], 'LT requested'))

Here are some links that might help you:

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Query-to-show-last-month/m-p/1548939#M1732...

https://powerusers.microsoft.com/t5/Building-Flows/Multiple-Filters-on-List-Rows-Present-in-a-table-...

Best Regards,
Charlie Choi

[Kamacete]

Hi v-yujincui-msft

thank you very much for your help. but it doesn't work.

😞

Could you please help me?

Thank you

Br,

Rui

[v-yujincui-msft · Accepted solution]

Hi @Kamacete ,

It seems that works fine on my side.

@or(contains(item()?['Status'], 'Info requested'),contains(item()?['Status'], 'Need info'))

Result Screenshot:

Best Regards,

Charlie Choi

[Kamacete]

Thank you very much v-yujincui-msft,

it's working fine the filter

best regards