Get Attachment Items and Create Records in Sharepoint List

[tlevine]

Hi Community - usually I am posting over in the PowerApps side but now it's my turn to take a stab with automate. I have been searching for a few pointers online but am still struggling with my flow that I am working on. Here is what I am looking to accomplish:

- New Email is Received

- Get Items in the Attachment (Excel file)

- Create new Record Sharepoint List

My biggest question so far is how to properly capture all of the items in the excel file. Does anyone recommend that I use a variable and create an array? Then I can apply to each in the variable in order to create a new record in the Sharepoint list? Any guidance here would be a ton of help. Thanks in advance. 

[v-alzhan-msft]

Hi @tlevine ,

The same requirement here, please refer to the steps and your requirement could be achieved:

https://powerusers.microsoft.com/t5/Building-Flows/Calculate-total-of-CSV-cell-and-populate-SharePoi...

https://powerusers.microsoft.com/t5/Building-Flows/Extracting-CSV-Email-Attachment-from-Outlook-and-...

And you could format table to the excel file refer to link below:

https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac6...

Best regards,

Alice   

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

[tlevine]

Thanks for the reply. I have been working on a slightly modified version of those solutions but I am countering an error in processing the "contentBytes" from the file. I am receiving the following error message.  @v-alzhan-msft 

[tlevine]

[v-alzhan-msft]

Hi @tlevine ,

Could you please share the expression in the Compose field?

The array message told you should get an item in the array and then get the contentBytes in the item, please take a try with expression below:

base64ToString(variables('Thumb')?[0]?['contentBytes'])

Best Regards,

Alice

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

[tlevine]

Hi @v-alzhan-msft  - I have tried your suggestion but am receiving the same error. For context, the expression that I tried previously was as follows:

base64ToString(variables('Thumb')['contentBytes'])

[v-alzhan-msft]

Hi @tlevine ,

What is the data in the initial variable action? Please share it so we could made a test on it to reproduce your issue.

Best Regards,

Alice

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

[tlevine]

Hi @v-alzhan-msft - I hope this answers your question but if not, please clarify. 

The steps I am following (screenshots attached) are:

- When an email arrives

- Get Attachment

- Create File in SharePoint

- Get File Content

- Initialize Variable

- Compose

[mmorley]

if you are bringing in an excel spreadsheet and it has a table in it already and the list is already created on the sharepoint then this may help

Use on trigger 'new email with attachment'

apply to each attachment in email

check attachment name ends with '.xlsx' (or '.xls') (this will ignore any other attachments like signatures and images)

create file

FileName = Attachments Name 

File Content = Attachments Name Contents

if no table in the excel sheet you can create one

otherwise use

List Rows present in table, choose the saved file from above and enter the table name (if you are going to be updating the file then set the table name as a custom entry)

apply to each value

create item (sharepoint) then add the items you want populating

it is slow but works - and remember to change the settings for list rows in table (enable pagination and set the limit to what you need - 5000 rows is max i think)

[tlevine]

@mmorley thanks for the insight here. really good idea on the xls piece in order to avoid capturing images. this is something i have been struggling with. 

i'll attempt this and report back with any findings

[tlevine]

@v-alzhan-msft @mmorley  - with some updates, I have seen a new issue. 

[mmorley]

@tlevine if you are importing from excel spreadsheet into a list, you should not need to use compose

[tlevine]

thanks for the reply, @mmorley 

how should i proceed with the flow after get file content?

[v-alzhan-msft]

Hi @tlevine ,

No, please refer to my first reply to create the flow:

https://powerusers.microsoft.com/t5/Building-Flows/Extracting-CSV-Email-Attachment-from-Outlook-and-...

You shouldn't get the flow content and you should create the flow as below:

Best Regards,

Alice

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

[tlevine]

@v-alzhan-msft - thanks, Alice.

How do I correct the error regarding the table? See screenshot below

[v-alzhan-msft]

Hi @tlevine ,

It is all refered in my provided link so I suggest that you could learn from it then create the flow.

You should know the excel table name before create the flow and you could enter the custom table name in the Table field.

Best Regards,

Alice

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

[tlevine]

@v-alzhan-msft - after using "MyTable" as a custom input which is the name of the table in my file, I receive the following error

[v-alzhan-msft]

Hi @tlevine ,

Make sure that your connection account are access to the file.

Take a try to create new flow with "List rows present in a table" action to get the rows in the table to see if the issue still exists.

Best Regards,

Alice

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

[tlevine]

@v-alzhan-msft  - thanks for the suggestion - I did a test in a new flow and created a HTML table with the list rows function from the excel file. It was succesful.

What else could I check on my current flow to avoid this error?

[mmorley]

@tlevine I have added a delay from saving the file to then reading it in on my flow to prevent access denied errors, also get these if the table doesn't exist or is on a hidden sheet.

Also always use a custom name for the table if you are loading a new file every time as this will also stop issues