Add New Data to Excel from Email Attachment using Power Automate

[jaysoulz]

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.

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]

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"

[jaysoulz]

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?

Thanks

[sbitaxi]

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]

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)

[jaysoulz]

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.

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

Thanks!

[sbitaxi]

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]

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.

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]

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.

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

[jaysoulz]

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.

[sbitaxi]

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?