How to delete/remove row from the google spreadsheet using gspread lib. in python?

Question

I want to delete a record from a google spreadsheet using the gspread library.

Also, how to can I get the number of rows/records in google spreadsheet? gspread provides .row_count(), which returns the total number of rows, including those that are blank, but I only want to count rows which have data.

Crepuscule

Answer 1

Since gspread version 0.5.0 (December 2016) you can remove a row with delete_row().

For example, to delete a row with index 42, you do:

worksheet.delete_row(42)

Nadianadine

I am trying delete_row(15) on a spreadsheet with 1000 rows, and still after 10 minutes it's running. Is it expected to take that long? Does it do any copy paste of the rows below when deleting a row? – Placid

Answer 2

Can you specify exactly how you want to delete the rows/records? Are they in the middle of a sheet? Bottom? Top?

I had a situation where I wanted to wipe all data except the headers once I had processed it. To do this I just resized the worksheet twice.

#first row is data header to keep    
worksheet.resize(rows=1)
worksheet.resize(rows=30)

This is a simple brute force solution for wiping a whole sheet without deleting the worksheet.

Count Rows with data

One way would be to download the data in a json object using get_all_records() then check the length of that object. That method returns all rows above the last non blank row. It will return rows that are blank if a row after it is not blank, but not trailing blanks.

Thomasinethomason

I found this answer really helpful. and the speed is the best thing about it. – Sergeant

Same for me, it is indeed far more efficient than deleting all rows sequentially with delete_row() – Vickievicksburg

This method is really helpful, I feel they should update the usage of this resize function in documents – Hubblebubble

Answer 3

worksheet.delete_row(42) is deprecated (December 2021). Now you can achieve the same results using

worksheet.delete_rows(42)

The new function has the added functionality of being able to delete several rows at the same time through

worksheet.delete_rows(42, 3)

where it will delete the next three rows, starting from row 42.

Beware that it starts counting rows from 1 (so not zero based numbering).

Dragonroot

Answer 4

Reading the source code it seems there is no such method to directly remove rows - there are only methods there to add them or .resize() method to resize the worksheet. When it comes to getting the rows number, there's a .row_count() method that should do the job for you.

Dinosaurian

Thanks for reply.. i knowe that there is no method to remove/delete records and in second point, row_count gives number or rows including blank data but i want only count of those rows which have data – Crepuscule

Then your question isnt detailed enough. In such case, you can iterate over each row, check if it contains any data and count it if it does. – Dinosaurian

Thanks again, but assume there are 1000 of records (including blank data) in spreadsheet and to check all rows one by one.... its take too much time... its not work for me... – Crepuscule

Have you actually tried it? Have you measured how long will it take? Doing iteration over 1000 of records is almost nothing for today's computers cpu's, you should have the results in a blink. – Dinosaurian

The same - try it first, measure and complain later ;) Also, it seems that: a) you dont have other option, b) google spreadsheet is limited to 400,000 cells, so you wont find yourself in a 1000k rows situation ever. – Dinosaurian

If this answers your question ad doubts, vote it as a valid answer to your question. – Dinosaurian

Answer 5

adding to @AsAP_Sherb answere:

If you want to count how many rows there are, don't use get_all_records() - instead use worksheet.col_values(1), and count the length of that. (instead of getting the entire table, you get only one column) I think that would be more time efficient (and will definantly be memory efficient)

Cosentino