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?