Bridge: Way to delete all rows in a sheet

Options
Joseph Pardo
Joseph Pardo ✭✭✭
edited 01/04/24 in Add Ons and Integrations

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.


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    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.
    Options

    Awesome! So glad to hear this helped.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Joseph Pardo
    Options

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