cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Rows - Bulk Process

I have some data in SQL Azure and would like to provide some of that data in an Excel file in SharePoint online.

 

Querying the database is easy and seems to take less than a second for around 3000 rows.

 

Converting the content to CSV and replacing the content of a CSV file in SharePoint took seconds and worked well - but of course is missing the nice formatting, filtering etc that can be achieved with an Excel table.

 

Working with an Excel file is more of a challenge but I see that there is already a plan to allow Flow to select an Excel file from SharePoint so will ignore that for the moment.

 

I want my data to be new every time so the first step is to delete the existing rows from an Excel table.

 

Get Rows -> Each Row -> Delete Row

 

  Works fine but takes 2-3 seconds per row?

 

Execute SQL Query -> Each Row -> Insert Row


  Works fine but takes another 2-3 seconds per row.

 

Get File Content (OneDrive) -> Update File (SharePoint)

 

  Quick and easy - though hopefully this step will not be needed eventually.

 

2-3 seconds per row does not sounds too bad on its own but for a few thousand rows this seems to take way longer than it should.

 

So, is it possible to either significantly speed up the row processing in Excel or to allow bulk changes.  For example, Delete All Rows, Insert All Rows?  Or is there a better way I should be doing this?

Status: New
Comments
LordsBH
Frequent Visitor

Did you ever solve this? I'm facing the exact same issue along with some problems witht he metadata if I try with a file created via the same flow.

Paylord
New Member

@LordsBH sadly not but hope you have more luck than I did!

michaelHall05
New Member

I'm having the same issue, soo slow to load. Would be good to see some work arounds here.

AAlcala
Frequent Visitor

Same problem over here. Any workarounds? 

Anonymous
Not applicable

Same problem, please give us the possibility to BULK INSERT / DELETE records from excel without using the loops connectors!! they are too slow!!!

GeoffRen
Microsoft

Has anyone tried using Office Scripts (specifically Office Scripts with Power Automate)? This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. An example script that can (hopefully) bulk delete rows is the following. You just need to provide the sheet name and the range you want to delete as parameters. It can also be modified to delete rows from a table given a table name and whatever else as parameters.

 

function main(workbook: ExcelScript.Workbook, range: string, sheetName: string) {
  const sheet = workbook.getWorksheet(sheetName);
  sheet.getRange(range).delete(ExcelScript.DeleteShiftDirection.up);
}

 

 

And an example script that can bulk insert rows is the following. You also need to provide the sheet name and range you want to insert into as parameters. In addition, you should provide the values as an 2d array (the size of the 2d array should match the size of the range you want to insert values into). Obviously this can also be modified to support table specific inserting as well.

 

function main(workbook: ExcelScript.Workbook, range: string, values: string[][], sheetName: string) {
  const sheet = workbook.getWorksheet(sheetName);
  sheet.getRange(range).setValues(values);
}

 

 

If these are performant enough for your scenarios I can help further with things like modifying the scripts to meet more specialized needs (like tables and relative/dynamic deletion/insertions).

 

Anonymous
Not applicable

Hi @GeoffRen ,

 

Thank you! yes of course, but you can use it only with excel online and, if you have a file already more than 50MB, you cannot use the excel online interface to add the script.... What about power automate desktop? Call a vba macro is a worst solution and sincerely doesn't work well...

 

It seems in theory super simple to add this feature, I don't understand why we have to execute a script and there isn't a connector or an api call to do it... especially the bulk insert and delete from power automate desktop.

 

With an online file, we can use an api call (microsoft graph api) to bulk insert the rows in a table (mid-fantastic! - only with a formatted table) but an api call to bulk delete (👎👎 why it doesn't exists?). 

 

With a power automate desktop? completely forgotten.... we can only bulk insert in a worksheet... nothing with table... nothing with deletion....

 

It is a basic feature we definitely need... a for each, loop and so on are really slow (like 1-2 second per rows.. with a 300k rows 83h ???? seriously?).

 

Thanks!!