cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KeerthiRaj96
Helper I
Helper I

RE: extract Names from e-mail id's given in excel and display it in textbox columns in List

Hi  Folks,

 

Need your help once again. I have few textboxes in my SharePoint list. currently, I am creating a flow - bulk excel upload to Primary SP list.

 

Query: 

  • when User gives email id's  in the excel file, it should fetch and show as "display name" in textbox of SP primary list.
  • I have tried out the same logic  given as solution in my another post as reference (only changed the particular value in it):

https://powerusers.microsoft.com/t5/Building-Flows/RE-Import-excel-to-SharePoint-list-create-item-Is...

  • I have additionally included,  get User Profile Action - It works fine by displaying name for one Email Id, when i give two more e-mail ID's in excel file, it fails.

My current flow image for your reference:

 

KeerthiRaj96_4-1634542726942.png

 

 

 

select operation:

KeerthiRaj96_0-1634543592910.png

 

Compose Action:

KeerthiRaj96_1-1634542324551.png

Get User profile Action:

 

KeerthiRaj96_2-1634542381753.png

Create item Action - Mapping Display Name to textbox column:

 

KeerthiRaj96_3-1634542641001.png

 

Please help out.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Community Champion
Community Champion

If I have understood your problem correctly:

  1. You are reading email addresses from an Excel file. There could be one or more email addresses in the Excel column.
  2. You want to lookup the Display Name for each email address
  3. You want to save the Display Name in a SharePoint Text Column. The SharePoint Column will contain one or more Display names, depending on how many email addresses are read in step (1).

For example: Source Excel file

2021-10-18_08-51-49.png

The result you are looking to get:

2021-10-18_09-05-09.png

We can use a variable to store the Display Names of each person, by appending the Display Name to the variable (eg. varTeamDisplayName):

2021-10-18_08-56-38.png

I use Trim in the "Get User Profile (V2)" action - to remove spaces from the start and end of the email address - or the Get User Profile action will fail to find a match for the email address:

2021-10-18_08-55-07.png

Finally, the variable is added to the Create Item action:

2021-10-18_08-57-20.png

Hope this helps.

 

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

6 REPLIES 6
ekarim2020
Community Champion
Community Champion

If I have understood your problem correctly:

  1. You are reading email addresses from an Excel file. There could be one or more email addresses in the Excel column.
  2. You want to lookup the Display Name for each email address
  3. You want to save the Display Name in a SharePoint Text Column. The SharePoint Column will contain one or more Display names, depending on how many email addresses are read in step (1).

For example: Source Excel file

2021-10-18_08-51-49.png

The result you are looking to get:

2021-10-18_09-05-09.png

We can use a variable to store the Display Names of each person, by appending the Display Name to the variable (eg. varTeamDisplayName):

2021-10-18_08-56-38.png

I use Trim in the "Get User Profile (V2)" action - to remove spaces from the start and end of the email address - or the Get User Profile action will fail to find a match for the email address:

2021-10-18_08-55-07.png

Finally, the variable is added to the Create Item action:

2021-10-18_08-57-20.png

Hope this helps.

 

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

KeerthiRaj96
Helper I
Helper I

Hi @ekarim2020 ,

 

Thanks for your reply. Yes, you have understood the requirement correctly. I need two things to be cleared here:

 

  1. Could you please tell me how you map the column -> Team lead under Apply to Each_3 -> Split expression 
  2. Is there any expressions needs to be set for Null value in set variable?

Please share your full flow if possible for reference. 

ekarim2020
Community Champion
Community Champion

>>>Could you please tell me how you map the column -> Team lead under Apply to Each_3 -> Split expression?

No Select action is required. The split function uses the Excel column "Team Lead":

2021-10-18_10-28-05.png

>>>Is there any expressions needs to be set for Null value in set variable?

Yes, the variable needs to be reset to empty (null) before the Apply to each loop 3 - as we are now reading the next row of the Excel file, so there is now new data for the Team column:

2021-10-18_10-36-56.png

 


Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

KeerthiRaj96
Helper I
Helper I

Hi @ekarim2020 ,

 

Thank you so much for your prompt response!

 

I have tested the data with one line item with excel and it worked perfectly.

 

One more query - Is there any method in flow to refresh the excel > i.e., clear excel file once the data is created/updated into SP list.

ekarim2020
Community Champion
Community Champion

There is a Delete a row action in Power Automate. You will need to decide the criteria or condition that is required in order to delete a row. In the following example, the row with RowId equal to 2 would be deleted:

ekarim2020_0-1634589834373.png

Excel file:

2021-10-09_17-49-40.jpg

SharePoint list.

2021-10-09_17-14-25.jpg

You should ensure that the row data has been successfully saved to SharePoint before deleting the row from Excel. You could create a new column that is updated to status Delete, and then later run a new flow to delete all rows with status equal to Delete. You will need to decide what works best for you:

2021-10-18_21-50-09.png

 


Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

KeerthiRaj96
Helper I
Helper I

Hi @ekarim2020 ,

 

Thank you so much for detailed reply.

 

 I guess the above solution will work fine for one row deletion at a time. What is need is:

 

  1. User may upload 500 line items or 200 line items at a time into excel template. what they need is once the date given by them , the excel file should be refreshed(cleared) automatically and saved into SharePoint list . (so that next user may use the file).
  2. Also, multiple users will be accessing the same excel template at a time, in that case how will this method work?

Kindly provide some suggestions.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,448)