Unable to copy data to excel

[manish_kumar94]

Hi all,

Every morning I get an email with excel file attachment and I need to paste the values in template which is already present in share point and send that file in email. I am able to create table but when it comes to add rows in table it is taking 10 hours and the flow is still running. And also any suggestion where I can use the same template everyday?

Thanks.

[manish_kumar94]

@eliotcole , @Pstork1 @Expiscornovus @abm  @ScottShearer 

[abm]

Hi @manish_kumar94 

How many records you having? Are your trying to copy the data into same excel sheet everytime in SharePoint? Explain bit more about the template?

Thanks

[takolota]

If you need to update and/or create many rows in Excel you can try Excel batch actions:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

Demo Video:

https://youtu.be/HiEU34Ix5gA

[eliotcole]

I would recommend using the Excel Microsoft Graph API within a Send HTTPS action from the Office 365 Groups connector.

The add row api call allows you to add multiple row with one call, and should take seconds. You just need to get it formatted correctly. 👍

[manish_kumar94]

Thank you for replying. I have 25 columns in my template. I am cancelling the flow and then I see this error. All the column name in table disappears.
I am looking for a way where I can use the same template everyday and paste the value from attachment and then send that file to others through email. 

[manish_kumar94]

@eliotcole , thank you for your reply. Could you please share more information on this as I am new to power automate.
Thanks.

[manish_kumar94]

@takolota Thank you for your reply. I am trying to copy the whole data from attachment which is in excel file to an excel template which is present in share folder.

[takolota]

@manish_kumar94 

Okay. Then you can try copying the attachment contents to a OneDrive folder with the Create file action.

Then you can use the List rows present in table action with dynamic content from the Create file action so you can get all the rows from the file you created.

That may satisfy your use-case if you’re just trying to access the data.

If you need to add it to previous data already in a template, then you can use the batch actions I mentioned & see how those work in the demo video I shared.

[manish_kumar94]

Hi everyone,
I was able to copy the data from attachment file to excel template but the issue is my template table size is 200 rows so the values are getting pasted after that and in the beginning all blank value are displayed. I had to do it  manually and remove blank values by using filter. Is there any way where I can avoid the blank value?
My second query is I should send the template attachment to everyone through email so I created a flow and also I should use the same template for next day therefore, I first created a copy of that template to other folder and then  I used delete rows and see if it works or not but I am getting error in this step. Any other suggestions on how I can create a flow where I can use the same template everyday.
Thank you!
 @takolota  @eliotcole @abm @MarconettiMarco  @Expiscornovus @David- @Hardesh15 @Paulie78 @ryleybauer @tom_riha @Shujaath_Khan @SachinG31 @ScottShearer @ekarim2020 @MarvinBangert @edgonzales @fchopo @KHammer @Mira_Ghaly 

[ryleybauer]

Hi @manish_kumar94 , 

Forgive me for stating this, but it sounds like your template file has a blank 200 row table? If another column is populated, you can use a different column as the "key" in your delete row step, but I don't think you need to delete the rows if they aren't there in the template in the first place. 

IF they're getting added by the "Create table" step, you can try A1 Notation in the "Table Range" value to specify the size of the table. If you already know how many rows you are creating, you can create a concatenate function too: 

concat('Range("A1:B' , ROWCOUNT, '")')

---------------------------------------------
I'm glad to offer assistance. If I've answered your question, please mark my reply as the solution.
If you like my response, please give it a thumbs up!

[Mira_Ghaly · Accepted solution]

@manish_kumar94 

As per our teams conversation, the problem is now solved please share the updated version of the flow that is currently working.

[manish_kumar94]

Thank you so much for your help.