20

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.

Moshe
  • 9,283
  • 4
  • 29
  • 38
Chintan
  • 1,204
  • 1
  • 8
  • 22

5 Answers5

13

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)
Burnash
  • 3,181
  • 2
  • 31
  • 35
  • 1
    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? – apadana Apr 19 '17 at 23:20
10

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.

Community
  • 1
  • 1
AsAP_Sherb
  • 1,661
  • 1
  • 13
  • 16
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).

Arash
  • 63
  • 5
1

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.

SpankMe
  • 836
  • 1
  • 8
  • 23
  • 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 – Chintan Jan 31 '13 at 12:35
  • 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. – SpankMe Jan 31 '13 at 12:41
  • 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... – Chintan Jan 31 '13 at 12:48
  • 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. – SpankMe Jan 31 '13 at 12:53
  • 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. – SpankMe Jan 31 '13 at 13:04
  • If this answers your question ad doubts, vote it as a valid answer to your question. – SpankMe Jan 31 '13 at 13:21
1

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)

Alon Gouldman
  • 3,025
  • 26
  • 29