cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
william_wallace
New Member

Delete "X" of rows if table is over # (1500) of rows

Hi Pros,

I'm stuck with the following issue and would like to know if anyone has attempted this on their own.

 

I have an Excel Online table that is updated by a Power App, I want to delete rows 2-151 each Sunday at 10pm OR IF() the number of total rows in the table exceeds 1500, delete the rows 2-500.

 

I've managed to write and Excel script to remove rows 2-500, and trigger it on Sunday @ 10pm, but I'm stuck on the above with the IF() statement, which is what I really need to happen.

 

Any help with this would be appreciated.

Thank You...

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @william_wallace 

 

You could try adding logic to your office script.  I've based it on 50 and 25 and deleting the first 25 or 10 rows respectively.

 

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();

	let numrows = selectedSheet.getTable('Table1').getRowCount();
	if (numrows > 50) {
		selectedSheet.getTable('Table1').deleteRowsAt(0, 25);
	} 
	else if (numrows > 25) {
		selectedSheet.getTable('Table1').deleteRowsAt(0, 10);
	}
	else {}

}

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

View solution in original post

2 REPLIES 2
DamoBird365
Super User
Super User

Hi @william_wallace 

 

You could try adding logic to your office script.  I've based it on 50 and 25 and deleting the first 25 or 10 rows respectively.

 

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();

	let numrows = selectedSheet.getTable('Table1').getRowCount();
	if (numrows > 50) {
		selectedSheet.getTable('Table1').deleteRowsAt(0, 25);
	} 
	else if (numrows > 25) {
		selectedSheet.getTable('Table1').deleteRowsAt(0, 10);
	}
	else {}

}

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

@DamoBird365 Thanks so much! 

 

I changed it up a little for my needs, this is what I'll use going forward by kicking it off with a flow every weekend.

 

function main(workbook: ExcelScript.Workbook) 
{
  let selectedSheet = workbook.getActiveWorksheet();

  let numrows = selectedSheet.getTable('Audits').getRowCount();
  if (numrows > 1500) {
    selectedSheet.getTable('Audits').deleteRowsAt(0, 500);
  }
    else { }

}

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (2,093)