how to delete all the rows of a sheet using python at once

Dhruv PAtel · 02/18/22

I am trying to build an automation that updates an excel file to smartsheet once I run it.

I have build that program but issue i am facing is that they are getting updated below the previous one. So I want to delete the rows first .Is there any way to do it without requiring row id.?

Chre Teitelbaum · 02/18/22

There doesn't seems to be a way for bulk deletion through the API without using row ids. I handle this by using a function similar to the one below, prior to uploading new rows. Depending on how many rows are in your sheet, you may have to do it in chunks because you may not be able to fit all the row ids in the URL. I have found chunks of 300 row ids to be the largest.

def delete_existing_data(sheet, chunk_interval=300):
    rows_to_delete = [row.id for row in sheet.rows]
    for x in range(0, len(rows_to_delete), chunk_interval):
        smart.Sheets.delete_rows(sheet.id, rows_to_delete[x:x + chunk_interval])

Is there a reason you can't use the row id?

Chre Teitelbaum · 02/18/22

There doesn't seems to be a way for bulk deletion through the API without using row ids. I handle this by using a function similar to the one below, prior to uploading new rows. Depending on how many rows are in your sheet, you may have to do it in chunks because you may not be able to fit all the row ids in the URL. I have found chunks of 300 row ids to be the largest.

def delete_existing_data(sheet, chunk_interval=300):
    rows_to_delete = [row.id for row in sheet.rows]
    for x in range(0, len(rows_to_delete), chunk_interval):
        smart.Sheets.delete_rows(sheet.id, rows_to_delete[x:x + chunk_interval])

Is there a reason you can't use the row id?

DanS · 03/08/22

Thank you for this code, I modified it a bit -

Screen Shot 2022-03-08 at 11.23.33 AM.png

Dhruv PAtel · 03/08/22

https://community.smartsheet.com/discussion/comment/320099#Comment_320099

Yes there are over 2021 rows that needed to be added everytime and the again deleted when data changes so using row id of 2021 rows would be very hectic according to me

Brian_Richardson · 10/18/22

@Chre Teitelbaum I ran across this older thread while looking for a way to bulk delete rows from a sheet using Bridge. Would you or someone happen to have a translation of the Python to Jscript to use with the Bridge Jscript module?

Nathan Lloyd · 01/31/23

Older thread but I was browsing and figured I would chime in. One trick for carrying out a bulk deletion that doesn't require iteration over each row is to first complete a bulk row update to add a parentId to all rows, then delete that parent row.

With this approach you would need to keep in mind that you may need to batch the row update step if the number of rows to be updated exceeds 500.

@Brian_Richardson tagging since you followed up recently. Here's a GIF showing this with a basic example in postman + test scripts.

TL;DR of the steps:

GET the sheet.
(Optional) Add a new row to serve as the parent (or just use an existing one).
Build the Update Rows request body (iterate through each row and return an object with just the rowId and parentId properties (that's all you'll need).
Run the Update Row request.
Run the Delete Row request.

Brian_Richardson · 01/31/23

@Nathan Lloyd ooooh. That's awesome I didn't think of that. So you just do a single row delete for the parent row and it wipes all the children too? If there's more than 500 children under a parent, will it delete all of them in one step if all 4000+ are under the same parent?

Brian_Richardson · 01/31/23

@Nathan Lloyd BTW I was giving you kudos in a recent Community Bridge call as I was stepping through the "bulk add" Javascript that you sent me back at Engage. I tweaked it to also pick up the Resource Management custom fields by name, and mash together the RM tags. Lots of people interested on the call in exactly how to batch in chunks of 500 rows. I posted a walkthrough in Community here if anyone is interested.

https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api

Nathan Lloyd · 01/31/23

https://community.smartsheet.com/discussion/comment/359614#Comment_359614

That's correct. The 500-row consideration only applies when you're trying to add or update (POST/PUT) to rows in a single request. Since you're only deleting a single parent row though, that's not a factor for the deletion step.

Appreciate the kudos! Glad to hear that's been useful.

Duc Tran · 01/31/24

https://community.smartsheet.com/discussion/comment/359627#Comment_359627

@Nathan Lloyd Sorry for bringing this back but I'm also in the same situation where I need to delete 13000 rows. Already created a parent row and assigns parent id to all the children rows. But calling API to delete that one top row returns

{"response": {"statusCode": 500, "reason": "Internal Server Error", "content": {"errorCode": 4002, "message": "Server timeout exceeded. Request has failed."

Do you have any idea to solve this?

how to delete all the rows of a sheet using python at once

Best Answer

Answers

Categories