Power Automate  

Delete Rows from an Excel Template Using Office Scripts and Power Automate

Introduction 

Excel templates are commonly used in Power Automate to generate reports, export data, and refresh files with the latest information. In many scenarios, you need to clear the existing data from the Excel table before adding new records.

Although Power Automate provides actions to manage Excel data, deleting rows one at a time can be slow, especially when the table contains a large number of records.

A simple and efficient solution is to use Office Scripts in Excel Online. By running an Office Script from Power Automate, you can quickly remove all rows from an Excel table while keeping the table headers, formatting, and structure unchanged.

In this article, we will learn how to:

  • Check whether the Excel table exists.

  • Clear any active filters on the table.

  • Delete all existing rows.

  • Prepare the Excel template for inserting fresh data.

Prerequisites 

Before starting, ensure the following: 

  • The Excel file is stored in SharePoint or OneDrive for Business. 

  • The data is stored inside an Excel Table. 

  • You have access to Excel Online (Business). 

  • You have access to Power Automate. 

Sample Scenario 

Suppose you have an Excel template named: 

Excle_PowerShellBulkData.xlsx

The workbook contains a table named: 

Table1 

Every time your Power Automate flow runs, you want to: 

  1. Remove all existing rows. 

  1. Keep the table headers and formatting. 

  1. Insert fresh data from SharePoint or another data source. 

Why Use Office Scripts? 

Using Office Scripts provides several benefits: 

  • Faster than deleting rows one by one. 

  • Keeps the table structure intact. 

  • Easy to integrate with Power Automate. 

  • Suitable for large datasets. 

  • Prevents issues caused by filtered rows. 

 Why Clear Filters Before Deleting Rows? 

Sometimes users apply filters manually in Excel or the file may already contain filtered data from previous executions. 

If filters remain active, the script may: 

  • Fail to delete all rows. 

  • Delete only visible rows. 

  • Return an error during execution. 

To avoid these issues, it is recommended to clear all filters before deleting rows. 

Create the Office Script 

Open the Excel file in Excel Online and follow these steps: 

  1. Open the workbook. 

  1. Select the Automate tab. 

  1. Click New Script. 

2_639186591860654268

  1. Replace the default code with the following script.

function main(workbook: ExcelScript.Workbook) { 
   const tables = workbook.getTables(); 
 
   if (tables.length === 0) { 
       console.log("No tables found."); 
       return; 
   } 
 
   const table = tables[0]; 
 
   // Clear filters applied to the table 
   table.getAutoFilter().clearCriteria(); 
 
   // Get the total number of rows 
   const rowCount = table.getRowCount(); 
 
   // Delete all rows if data exists 
   if (rowCount > 0) { 
       table.deleteRowsAt(0, rowCount); 
   } 
 
   console.log(`Cleared table: ${table.getName()}`); 
 

Understanding the Script 

Step 1: Get All Tables 

const tables = workbook.getTables(); 

This retrieves all tables available in the workbook. 

Step 2: Check Whether a Table Exists 

if (tables.length === 0) 

This prevents the script from failing if no tables exist in the workbook. 

If no table is found, the script writes the following message: 

console.log("No tables found.");

Step 3: Select the Table 

const table = tables[0]; 

This selects the first table in the workbook. 

If your workbook contains multiple tables, you can specify the table name instead: 

const table = workbook.getTable("Table1");

Step 4: Clear Existing Filters 

table.getAutoFilter().clearCriteria(); 

This removes all filters currently applied to the table. 

Why is this important? 

  • Makes all rows visible. 

  • Prevents script failures. 

  • Ensures all records are deleted. 

  • Improves reliability when users manually filter the workbook. 

Step 5: Get the Number of Rows 

const rowCount = table.getRowCount(); 

This returns the total number of rows in the table.

Step 6: Delete All Rows 

table.deleteRowsAt(0, rowCount); 

Parameters: 

  • 0 – Starting row index. 

  • rowCount – Number of rows to delete. 

This removes all data rows while preserving: 

  • Table headers 

  • Table formatting 

  • Table name 

  • Column structure

Step 7: Write a Log Message 

console.logCleared table: ${table.getName()}); 

This writes a message to the script logs indicating which table was cleared successfully.

Using the Script in Power Automate 

After saving the script, you can execute it directly from Power Automate.

2_639186586198239179

Step 1 – Create an Instant Cloud Flow

Open Power Automate and create a new flow.

For this example, we are using the Manually trigger a flow trigger to test the Office Script and verify that the Excel rows are deleted successfully.

Step 2 – Add the "Run Script" Action

Add the following action: 

Excel Online (Business) → Run Script 

Configure: 

  • Location: SharePoint Site 

  • Document Library: Documents 

  • File: Excle_PowerShellBulkData.xlsx 

  • Script: DeleteRowScript

Conclusion 

Office Scripts provide a simple and efficient way to clear data from an Excel template in Power Automate. By using the clearCriteria() method before deleteRowsAt(), you can avoid issues caused by filtered rows and ensure that all data is removed successfully. 

This approach is faster, easier to maintain, and ideal for report generation, SharePoint exports, dashboard refreshes, and data synchronization scenarios. 

By integrating this script into your Power Automate flow, your Excel template will always be ready to receive fresh data while preserving the table structure and formatting. 

Membership not found