How to Remove Top Few Lines of an Excel File Using Power Automate?

While working on Power Automate, I was required to remove rows in an Excel file that didn’t contain any tables. We know that to remove rows in Excel, the “Delete a row” Excel connector is available in Power Automate, but it requires a table name, and in this case, the Excel file didn’t have a table.

After researching, I found a method to delete rows in an Excel file using Power Automate. We need to create a new script in Excel to delete the specified rows and then use that script within Power Automate.

In this Power Automate tutorial, we will see how to remove top few lines of an Excel sheet and how to remove pictures in Excel using Power Automate.

Remove Top Few Lines of an Excel File Using Power Automate

In this example, I am using the Excel file below. You can create a similar Excel file and then follow the steps.

This Excel workbook is presented in the SharePoint document library.

Now, you can download the Excel file and copy it to any other document library for use in the Power Automate flow; I copied it to another document library. You can see this in the screenshot below:

1. Open that Excel file, then under the Automate tab, click Record Action.

2. Then, you can see ‘Record Actions’ started on the right side. You need to select the row and delete it. After that, stop recording the action.

3. Then you can see a Script. When you click the edit button, you will see the full script.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Delete range 1:4 on selectedSheet
	selectedSheet.getRange("1:4").delete(ExcelScript.DeleteShiftDirection.up);
}

4. It automatically gives the script the name ‘Script 2.’ You can change it by double-clicking and then giving it a new name.

5. Log in to Power Automate and create an instant cloud flow with manual trigger action. Select + New step and add a Run Script action from action triggers.

• Location – Select the location where the Excel workbook exists
• Document Library – Choose the document library from the dropdown
• File – Select the Excel workbook for which you want to delete the rows
• Script – Select the created Script from the dropdown.

6. Save the flow by selecting the ‘Save’ option. Then click the ‘Test’ option, choose ‘Manual,’ and click ‘Test.’ Once the flow runs successfully, it will look like the screenshot below:

7. When you go to the SharePoint Online document library and open the Excel file, you can see that the top four rows have been removed successfully.

This way, a row can be removed from an Excel file using Power Automate when the file does not contain any table.

Remove Picture from Excel Using Power Automate

I use the same Excel file for this example, but now my Excel files contain a picture or image at the top.

When you try to remove the picture using Record Actions, you will notice that the action is not recorded.

Note:

If you are unsure how to work with Record Actions, follow the steps in the example above.

In this case, first, you remove the row using Record Actions, Then click the edit button and add the script below.

 let shapes = selectedSheet.getShapes();
  for (let shape of shapes) {
    if (shape.getType() === ExcelScript.ShapeType.image) {
      shape.delete();
    }
  }

In this script, delete any pictures present in the worksheet.

Then, you can follow the steps above (5th and 6th) to delete pictures using Power Automate.

Conclusion

I hope this tutorial gives you all the information on how to remove a row from an Excel file that does not contain any table and how to remove pictures in Excel using Power Automate.

You may also like to read:

• Send Birthday Emails from Excel using Power Automate
• Create Custom Response Options for Approval Flows in Power Automate
• Create a Scheduled Cloud Flow in Power Automate
• Delete all rows in Excel using Power Automate
• Convert CSV to Excel in Power Automate
• Power Automate Read Excel File From SharePoint

Bijay Kumar

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.