While working on automating Excel data management, I faced the challenge of deleting all rows in an Excel table without affecting the table’s structure. After researching various approaches, I discovered two effective methods using Power Automate.
In this tutorial, I will show you how to delete all rows in Excel using Power Automate. We’ll cover two approaches: the first involves iterating through rows with the List rows present in a table and Delete a row actions. In contrast, the second leverages an Office Script executed through the Run script action to clear all rows simultaneously.
Delete All Rows in Excel Using Power Automate
Suppose you manage an “Activity Log” Excel file stored in a SharePoint document library called “Corporate Projects.” The file contains a table named “ActivityLogTable” that logs user actions. At the start of each month, you want to delete all rows in this table while keeping the table and file intact.
Now follow the below steps:
1. In the Power Automate, click the Scheduled Cloud flow, enter the Flow name, and provide the following:
- Starting: Provide the date you want to run your flow, like 1st December.
- at: Provide the time you want to run the flow, like 10:00 AM.
- Repeat every: We want to run the flow every month, so here, select 1 Month.
2. Add the List rows present in a table action to retrieve all rows from the “ActivityLogTable” in the Excel file. Provide the below parameters:
- Location: Select the SharePoint site address.
- Document Library: Choose the document library.
- File: Select the Excel file.
- Table: Choose the Excel table value.
3. Add an Apply to each loop to iterate through all the rows retrieved in the previous step.
4. Inside the loop, add the Delete a row action to delete each row one by one. Provide below parameters:
- Location: Specify the SharePoint site where the Excel file is stored.
- Document Library: Select Corporate Projects.
- File: Choose the Activity Log file.
- Table: Select ActivityLogTable.
- Key Value: Use the Key Column and Key Value fields to identify the row to delete (e.g., the row’s unique identifier or primary key).
Save the flow and test it by running it manually. Once the flow runs successfully, wait a few minutes for the changes to reflect in the Excel file. You will then see that the rows in the Excel table have been deleted.
Delete All Rows in Excel Using Power Automate Run script from SharePoint Library Action
To delete all rows in an Excel table using the Run Script action in Power Automate, you must create an Office Script in Excel Online and then call it through Power Automate.
Create Office Script using Excel Online
First, the office script clears all rows in the specified table (ActivityLogTable) without deleting the table structure or headers.
To create an office script in Excel, follow the below steps:
1. Go to Excel Online and open a workbook. Ensure you use a workbook stored in OneDrive or SharePoint, as Office Scripts are supported only in online workbooks. For this example, I used SharePoint:
2. Click on the Automate tab in the ribbon at the top of the Excel interface (If you don’t see the Automate tab, it might need to be enabled by your organization admin). Then, click New Script. Replace the default code with the below script to delete all rows in the table:
function main(workbook: ExcelScript.Workbook) {
let Table = workbook.getTable("ActivityLogTable");
let RowCount = Table.getRowCount();
Table.deleteRowsAt(0, RowCount);
}- getTable(“ActivityLogTable”): This retrieves the table named “ActivityLogTable” from the workbook.
- getRowCount(): This gets the total number of rows currently in the table.
- deleteRowsAt(0, RowCount): This deletes all rows in the table, starting from the first row (index 0) and spanning the total row count (RowCount).
3. After that, rename the script and save the script.
Create a Flow in Power Automate
Now follow the below steps to delete all rows:
1. In the Power Automate, click the Instant Cloud flow, enter the Flow name, and choose the trigger flow (i.e., manually trigger a flow).
2. Add the Run script from SharePoint library action from the Excel Online (Business) connector and provide the below parameters:
- Group: Select the group or site where your workbook resides (e.g., Power Automate).
- Workbook Library: Choose the library where the file is stored (e.g., Corporate Projects).
- Workbook: Enter the file name, including the path if necessary (e.g., /Activity Log.xlsx).
- Group: Select the group or site where your script resides (e.g., Power Automate).
- Script Library: Select the library where the script is saved (e.g., Corporate Projects).
- Script: Choose the script you created earlier (e.g., DeleteAllRows).
Now, it’s time to save the flow. Click Test and select Manually. After it runs successfully, go to the SharePoint document library, open the Excel file, and you will see that all rows have been successfully deleted.
Conclusion
In this tutorial, we explored two methods to delete all rows in an Excel table using Power Automate. The first method involves using the List rows present in a table action with an Apply to each loop and the Delete a row action to remove rows one by one. The second method utilizes the Run script action to execute an Office Script, which deletes all rows in the table in a single operation.
You may also like:
- Convert CSV to Excel Using Power Automate
- Add Row to Excel Table Using Power Automate
- Send Birthday Emails from Excel Using Power Automate
- Create CSV Table From Excel File Using Power Automate
- Append to String Variable Action in Power Automate
- Create an Excel File from SharePoint list Items using Power Automate
- Create Hyperlink Dynamic Content in Power Automate
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
