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

Question

I want to delete an entry from google spreadsheet using the gspread library.

Also, how 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 empty, but I only want to count the rows that have data.

Answer 1

Since gspread is version 0.5.0 (December 2016), you can delete a row with delete_row() .

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

 worksheet.delete_row(42) 

Answer 2

Reading the source code, it seems that there is no such method for deleting rows directly - there are only methods for adding them or .resize() for resizing the worksheet. When it comes to getting line numbers, there is a .row_count() method that should do the job for you.

Answer 3

Can you specify exactly how you want to delete rows / records? Are they in the middle of the leaf? Bottom? Upstairs?

I had a situation where I wanted to erase all the data except the headers as soon as I processed them. To do this, I just changed 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 to clean the entire sheet without removing the worksheet.

Counting Data Rows

One way is to load data in a json object using get_all_records() , and then check the length of this object. This method returns all the lines above the last non-empty line. It will return lines that are empty if the line after it is not empty, and not trailing spaces.

Answer 4

Try pygsheets , it has delete_rows method.

Answer 5

adding @AsAP_Sherb's answer:

If you want to count the number of rows, do not use get_all_records() - instead use worksheet.col_values(1) and get_all_records() length of this. (instead of getting the whole table, you will get only one column) I think it will be more time efficient (and will definitely be memory efficient)