Bridge: Way to delete all rows in a sheet

Joseph Pardo

I've found a few ways to delete all rows in a sheet using scripts and multiple workflows, but I was wondering if there's an easier way to merge this into one flow.

I just have the flow check if a row exists in get sheet, if there is a row then delete row 0, until the array = 0 then it stops. It works, but it's just super inefficient if I have over even 50 rows. I'm sure I could populate this into an array somehow and pull the row IDs but I'm new and not sure how to do that exactly or reference the rowID from the array after it's populated.

Genevieve P.

Hi @Joseph Pardo

Yes, this is possible to do in one workflow! The Delete Row module can accept a list of row IDs to delete, as long as they're formatted in a specific way.

Here's how I set up my workflow:

After getting the sheet, you'll have an Array of rows.

I extracted just the ID of the row by using Array Management: Extract Field From Array

Set this up to have a CSV output. This will return a list of the Row IDs, separated by a comma... however there's a space between the comma and the next ID.

The Smartsheet API needs there to be no space between the IDs and the comma, so we use Replace Text to get rid of any spaces:

What you can't see in this image is that I've added one space in the Match Text field, and added nothing in the Replace Text field. If you reference my first image, you'll see that this removes the space so all the IDs are formatted correctly.

Now I can reference the "Replacement" field from the run log as the Row IDs that I want to delete! 🙂

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Joseph Pardo

Yes, this is possible to do in one workflow! The Delete Row module can accept a list of row IDs to delete, as long as they're formatted in a specific way.

Here's how I set up my workflow:

After getting the sheet, you'll have an Array of rows.

I extracted just the ID of the row by using Array Management: Extract Field From Array

Set this up to have a CSV output. This will return a list of the Row IDs, separated by a comma... however there's a space between the comma and the next ID.

The Smartsheet API needs there to be no space between the IDs and the comma, so we use Replace Text to get rid of any spaces:

What you can't see in this image is that I've added one space in the Match Text field, and added nothing in the Replace Text field. If you reference my first image, you'll see that this removes the space so all the IDs are formatted correctly.

Now I can reference the "Replacement" field from the run log as the Row IDs that I want to delete! 🙂

Let me know if this makes sense and works for you!

Cheers,

Genevieve

jmyzk_cloudsmart_jp

Hi @Genevieve P.

Thank you for sharing the solution. 'Extract Field From Array' was the crucial step – it worked perfectly! Previously, I was unsuccessfully attempting to use 'Add Object to Array' in a child workflow.

Additionally, I tried extracting the fields as an 'Array' and used the results as the Row ID parameter in the 'Delete Row' method, which also proved successful.

Genevieve P.

Awesome! So glad to hear this helped.

Joseph Pardo

Thank you Genevieve, those steps were amazing and have worked great!

Neil Egsgard

@Genevieve P. , I am using this method with "extracting the fields as an 'Array'" to get a list of row IDs for the "Smartsheet: Delete Row". I am getting an error when I try to delete over 500 rows. Is 500 rows a limit?

Thanks,
Neil Egsgard

Genevieve P.

Hi @Neil Egsgard

I'm not sure if 500 is a limit, I don't believe it is. An alternate way to do this would be to indent all your rows under a Parent row, then just delete the Parent. 🙂

Neil Egsgard

@Genevieve P. , thanks, I will try it. Do you know if there is a row limit on indenting?

Neil

Genevieve P.

Hi @Neil Egsgard

I don't know about a set limit there either, but a colleague mentioned they did this with 10,000 rows so you should be fine!