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

Chre Teitelbaum

Feb 18, 2022

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

Feb 18, 2022

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

Feb 18, 2022

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?

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.

Use Javascript in Bridge to efficiently import data from an API

Brian_Richardson

Nov 10, 2022

Hi all - I'm excited to share a custom Javascript solution with my fellow Bridge users. I am not a Jscript coder - all credit for this goes to the awesome @Nathan Lloyd at Smartsheet - Large Enterprise Technical Sales Engineer, who gave me the basis for this script at ENGAGE 2022. Thank you Nathan!
In this thread I'll explain how you can leverage this script for any API. My example is with the Resource Management API, but it will work (with tweaks) for just about anything.
THE PROBLEM
I needed to load the user list from Resource Management into a Smartsheet sheet, in an automated way via the RM API.
Issue 1) It took forever. I had a parent-child workflow setup, where the parent got the users from the RM API, and then a child workflow pushed each user object into a Sheet. It took about 1.5 hours to go through 1000 users.
Issue 2) Custom fields. Custom fields via the RM API come back as an array of custom field objects, with each object having a field name and a value. That meant I couldn't just reference the "Cost Center" custom field, I had to "find" the Cost Center object and get it's value. I did that by extracting a field from the array and then sorting it, for every user, which is maybe why things took so long.
Issue 3) Tags. I needed to group a bunch of tags together, each tag was a separate object with a id and value. I could have used the Bridge "Extract Field" to get this tag list, but this Jscript is faster.
THE SOLUTION
With the Custom Jscript module, I'm able to load 1000 users in under 1 minute into Smartsheet! It used to take 1.5 hours. And I can find any custom field I want, even if I add more later.@Nathan Lloyd
You can implement this solution for any API, not just Resource Management. I'll explain below how to setup the workflows and the script to accomodate any need.
SETUP
But first, a caveat. This method uses Add Row to bulk add data into a sheet. It will not Update existing rows, that's a whole other ball of wax that I don't have a solution for (yet). Also there's no guarantee of add order. It should load in the same order as you get the data from your API, but it's not guaranteed by Smartsheet. So if that's critical for you, you may need another solution.
1) Create a Sheet: create a sheet to hold your data and add all the columns you plan to populate. Click File..Properties and record the sheet ID. Note that you can have more columns than just the API populated ones - you can run formulas and do everything as normal in this sheet.
2) Add a "Clear Sheet" Automation: optional. This solution works by Adding Rows each time it runs. So - if you want to replace information in your sheet, you'll need to clear out the old info. To do that automatically you can create a Move Row automation on a schedule or trigger, that moves the row to another sheet to clear it out. Or you can use Data Shuttle to import a blank file to the sheet on a schedule, selecting "Delete rows that don't match filter criteria" as the option.
3) Tweak and test your Javascript code. This is the trickiest (for me at least-not knowing much Jscript). You'll need to adjust the code to work for you. You may need to read up on some Jscript basics to do this. More on tweaking the code further down this thread. You can test the code to make sure it works prior to loading it into Bridge. I used JSFiddle for this. Copy/paste the code from this thread into JSFiddle's JavaScript box. You'll also need to add two arrays with sample data, you can get sample data using Postman or Bridge. Add the source data to the top of the code as const sourcedata = [ your sample array]. Add the column data to the top of the code as const targetColumns = [ your sample columns array]. Also change the very last step from return chunkArray(rows) to console.log(chunkArray(rows)) and enable the console in Settings, so you can see the results or errors. As you tweak, look for red dot errors next to the code lines.
4) Add the script to Bridge. In Bridge, click the Integrations on the lefthand panel, then scroll down and click Javascript. In the popup box click Scripts tab. Click Scripts and then Add to Scripts. Give your script a name that you can remember (write it down as you need to refer to it in the workflow). In the Script Body section copy/paste the code from this post or the code that you tweaked in JSFiddle. Note: do not copy in any testing data arrays for sourcedata or targetColumns. If copying from JSFiddle, change the very last step back from console.log to return chunkArray(rows) Now Save the script and close the Javascript module box.
5) Build your child Workflow. (yes, child first). In Workflows click New Workflow. Add the module to the bottom with the settings shown:
Utilities...Utility Functions...HTTP Call
URL: https://api.smartsheet.com/2.0/sheets/yourdestinationsheetidnumber/rows
Method: POST
Headers
Header 1: Authorization Value 1: Bearer yoursmartsheetapitoken
Header 2: Content-Type Value 2: application/json
Body: {{runtime.data}}
Response Handler: checked
6) Build your parent Workflow. In Workflows, click New Workflow. Add the following modules with the settings shown:
Utilities...Utility Functions...HTTP Call
URL: your API URL. For RM I used https://api.rm.smartsheet.com/api/v1/users?per_page=2000&auth=yourtokenhere&fields=custom_field_values,tags
Method: GET
Response Handler: checked
Integrations...Smartsheet...Get Sheet
Sheet: sheet ID of your destination sheet
Utilities...Javascript...Run Script
Script Name: the name you gave your script in the previous step
Script Parameters:
Key 1: sourcedata Value #1: {{states.startstate.utilities.httpcall.data}} (note that your exact data reference might be slightly different. Run your workflow and get the data reference for the array you want to import. It very well may just be {{states.startstate.utilities.httpcall}}
Key 2: targetColumns Value #2: {{states.startstate.smartsheet.get_sheet.sheet.columns}}
Run Child Workflow
Name: Push Rows (or whatever you want)
Child workflow name: Name of the child workflow from step 5
Number of runs: {{states.startstate.javascript.run_script.result}}
7) Now run your parent workflow to test it. If all is well, you should see the data appear in your sheet within a minute or two!
CODE IN NEXT THREAD REPLY

Nathan Lloyd · 01/31/23

Brian_Richardson

Jan 31, 2023

@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?

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

Nathan Lloyd

Jan 31, 2023

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.

@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?

API & Developers

API & Developers

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

Best Answer

Answers

Use Javascript in Bridge to efficiently import data from an API

Quick Links

Categories

Trending in API & Developers

Intermittant Smartsheet Login Issues Using Power Automate

PHP integration for Smart sheet.

How can I use API to attach a local PDF to a Smartsheet row through Kofax/Tungsten?