Variable String Created from Excel and used as BCC for Email

[ellean]

I am trying to take an xlxs file that my teammate uploads to our group shared files, pull the emails from the "Email UPC" column, and create a string that I can use in the BCC field of the send email action of power automate.

I set up my flow to create a table and then pull the rows from that table. I then try to Apply to Each of the rows in order to get the email addresses. I can't manage to see the email column in my dynamic content. I have seen several examples of this where the dynamic content displays the column name, but all I can get are the "Item" entries you see above.

Can anyone help me?

[efialttes]

Hi!
It seems since a rollout last week Flow Parsing policy is much more strict, meaning options shown in dynamic content are only well recognised email adress sources.
Try to assign the excel column content into a new Compose action block, then see if dynamic content offer the option to add Compose output to you BCC
Not with my laptop, sorry, cannot test

[v-litu-msft · Accepted solution]

Hi @ellean,

The reason for the email column in your dynamic content is invisible is that the table name is dynamic content, and it is new created table in one Flow, the new create table's columns haven't sorted by the system, so it is invisible.

I do not quite understand your requirement, for the new created table, use List rows in present in a table action to get data, but there isn't data in it, is it?

I guess you want to store data in the new table, right?

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ellean]

@v-litu-msft 

The xlsx is downloaded from another database, it is a simple excel sheet without tables but it does have data in it. Flow doesn't work with excel data that isn't in a table, from my understanding. Since the data in the excel sheet will change every time the flow is executed, a table needs to be created so that the flow will pull the data. I do not wish for the person getting the xlsx to create a table manually, because they do not have that technical skill.

[ellean]

@efialttes could you please explain what you mean by "assign the excel column content into a new Compose action block"? I added a compose action, but what do I put into the action? My "list rows present in a table" gives me "List of Items" and "value" with a subtext of list of items. If I put in List of Items into the compose block, I receive the same dynamic content as before and if I put in value then I don't see any dynamic content but the Output from compose. Is this what you are talking about?

[efialttes · Accepted solution]

@ellean 

Please forget about my first suggestion.

@v-litu-msft is totally right. The problem is the dynamic content table name, but as you explained it is the way to proceed.

Just guessing if John Liu workarounds for excel can save your day

http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

"To use List Rows Present in a Table - we need the Table ID, since this is dynamic now - I’m fetching this with Get Tables (need the file ID)"

Please let us now your progress. IMHO your approach sounds extremely reasonable once explained, but also terra incognita. So remember Shackleton ad: "Honour and recognition in event of success"

Thanx!

[ellean]

Thanks for all your help @efialttes. I have been reviewing the link you posted to John Liu and here is where I am at:

I did the HTTP Sharpoint request as stated using _api/v2.0/drive/root:/Export.xlsx as the Uri.

I did the Compose for body('Send_an_HTTP_request_to_Sharepoint')?['id']

I am creating a table, so I did that action instead of get worksheets/get tables.

Since I did do Create Table action, in the List rows present...action I put in the compose output for the file ID and 

body('Create_table')?['name']?['id'] in the table field.

Then I did length(body('List_rows_present_in_a_table')?['value']) in the second compose.

Still all I am seeing is the Item and not the columns in the table. What am I overlooking?

Thanks for all your help. Sorry I am struggling. I think I am close though.

[efialttes]

Hi!
Once you create the table, try to 'get tables'
Use Table Id from this output on 'list rows present in a table'
Hope this helps

[ellean]

Sorry to poke you again @efialttes but I found myself stuck again.

I can see the file data in the List rows present in a table action. I still can't get the row data to show up in the dynamic content popup. I must be having a brainpoof or something! It is very frustrating to see it there but not be able to figure out how to get it into the variable. Would you be willing to provide any further insight, please?

[ellean · Accepted solution]

I return triumphant, friends! 🌠

Perhaps someone can suss out a more elegant way, but here is what I did:

After the List All Rows Present in a Table per the John Liu page posted, I did a Parse Json of type array. I did an Apply to All of the Parse Json body, and inside the Apply to All is a second Parse Json of type object. The content of the second Parse Json is Current Item of the Apply to All. Finally, there is an Append to a String Variable action that the table cell data I wanted (the email address) is created.

[efialttes]

BRAVO!

Congrats, @ellean !

Great job!