Skip to main content

Notifications

Power Automate - Using Flows
Unanswered

Add New Data to Excel from Email Attachment using Power Automate

(1) ShareShare
ReportReport
Posted on by 9

Hi,

Everyday, I receive an excel report from my email which I will open, copy the whole content then add (append) it into my main excel file stored in OneDrive.

Excel email report (Report dd-mm-yy-hh-mm-ss.xlsx)

> Does not have table
> Dynamic name (Report dd-mm-yy-hh-mm-ss.xlsx)
> There are 13 columns (there could be blank cell, except columns B,C, L which has all rows data)

 

Main excel file (TEMP_DB.xlsx):

> Has table (called Table 1)


I created the following flow and it went through, except it does not add the content from my email to my main excel file.



TEMP.png
Summary:
✔ Attachment file is created and stored in the OneDrive folder "TEMP Report" with Report dd-mm-yy-hh-mm-ss.xlsx

✘ No data was appended to the main file TEMP_DB.xlsx 

 

What I am missing?

  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    EDIT 2024-07-11 14:33 (-4:00 UTC) I figured out the Power Query bit via OneDrive. I do not have time to post about it today but will return tomorrow with the details, Power Query code and screenshots.

     

    OneDrive does make this a bit trickier. There's a way to access information from OneDrive (Get Data from OneDrive or SharePoint with Power Query • My Online Training Hub) but I'm getting a strange error and can't access mine this way. 

     

    Are you able to do so?

  • jaysoulz Profile Picture
    jaysoulz 9 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    If we can use use Power Query to combine that will be great, but the file is in OneDrive, so I am not sure how you gonna make it work.

    I did multiple thing like after creating the file, create table from the temporary file...

    Here is  the OfficeScript to create table from the temporary file (another test which failed too):

     

    function main(workbook: ExcelScript.Workbook) {
     // Get the active worksheet
     let sheet = workbook.getActiveWorksheet();
     
     // Get the used range of the worksheet
     let range = sheet.getUsedRange();
     
     // Add a table to the used range
     sheet.addTable(range.getAddress(), true);
    }

     

    It works if I run from ExcelOnline, but it will create error when adding it into the PowerAutomate. The issue is still the ID or the variable from the created file (including if I need to add the loop For Each or not).

    //////////////////
    Here is the error:
    Action 'For_each_2' failed: The execution of template action 'For_each_2' failed: the result of the evaluation of 'foreach' expression '@body('List_rows_present_in_a_table')' is of type 'Object'. The result must be a valid array.

    ActionFailed

    An action failed. No dependent actions succeeded.

    The table was created. It stops there so no content was added into the main file.

    TEMP5.png
  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    Before I answer your question (later in this reply) I have a question: Instead of inserting the rows into the same workbook, could you instead put the Excel files into the same directory (and only those reports) and use Power Query to combine them? One of the things that I'm concerned about is you may inadvertently hit a quota limit in Power Automate for processing the rows if you're receiving multiple files in a day that each have thousands of rows. Power Query can dynamically combine files just by refreshing the query. 

     

    I have had to write Office Scripts which are more complicated in order to send the data to Excel in Json format and write them to a range - converting thousands of "Add row into a table" calls into a single request to Excel.

     

    Missing Rows

    The missing rows are due to pagination. Different actions return a maximum number of items per request before the service needs to make a subsequent api call. Change the settings of the "List rows" to enable pagination and set it to a number that is higher than the maximum number of rows that you need.

     

    sbitaxi_0-1720605929919.png

     

    Regarding the Expression error, can you post the error text?

  • jaysoulz Profile Picture
    jaysoulz 9 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    Will need your expertise on this Arrray thing. Already spent the whole day trying by sending a hundred emails testing...

    Last one I did it by chance, but it copied only 500 rows when it should have copied the 4200 rows. I modify something, now it keep failing and I cannot figure it out. Something does not make sense here.

     

    TEMP4.png


    I think it is the dynamic part where it has to load the column from the table we created then paste it into the Main File.

  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    I'd first inspect the output of the Create table step, it may include the table Id. 

     

    However, if not, use an List Tables Excel action and then for each table (it'll only be one but List returns an array), then for each row.

     

    I'm away from my computer for a bit, but will loop back on this and if you're still having trouble will post a solution. 

  • jaysoulz Profile Picture
    jaysoulz 9 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    I manage to add the Table name in the Report file, but how do you get that table and append it in the main file? I tried multiple time sending multiple test email since hours, but it does not work.

     

    TEMP3.png

     

    Greatly appreciated if you can help by providing the picture with the input variable, if any.

     

    Thanks!

  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    Additional note, when creating the table you should limit the number of rows (don't use A:L, instead A1:L100 or 1000), whatever is a reasonable limit to always contain the data included in the reports, otherwise it'll loop through 1 million rows if you don't have a clause to terminate the loop early)

  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    You need to map the path to the file and use the "name" property that you used when you created the file in the OneDrive action.

    For my test, the file was in /Documents/Downloads/test/ 

     

    sbitaxi_0-1720551906501.png

     

  • jaysoulz Profile Picture
    jaysoulz 9 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    Thanks for the reply, but my issue is because the file created has dynamic filename, what should I input in the File field so it will load that new created file?

     

    TEMP2.png

    Thanks

  • sbitaxi Profile Picture
    sbitaxi 29 on at
    Re: Add New Data to Excel from Email Attachment using Power Automate

    You are receiving the file, saving the file, but you are not reading from the new file to copy the data.

     

    I would try to:

     

    1. Save the new Excel file (you're doing this)
    2. add a "Create table" Excel action using the range A:J in the new file
    3. Loop through the rows in the new Excel table and add the row to TEMP_DB.xlsx

    To avoid adding blank rows, you can include a condition (pick a column that will always have data) - "IF L is null, don't add the row"

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics

Restore a deleted flow