9

Is it possible to fetch the number of filled rows without fetching the whole spreadsheet, or find the first empty row number?

I am using the python api wrapper if it matters.

canerbalci
  • 1,239
  • 1
  • 12
  • 16
  • if you want to insert a row at the end, you can use "list feed". It is very much like an SQL table, the first row contains "field" names. – eddyparkinson May 13 '13 at 01:05
  • @eddyparkinson thanks for the suggestion. I actually use the list feed. But there is a problem I couldn't manage to solve with the list feed api. The list feed api uses an undocumented way to resolve column names when inserting data. Let's say the column header in the spreadsheet is "My Column", you have to insert data to "mycolumn"; you have to strip characters such as **"?!'"/()[]{ <>,:+@#$%^&*€£¥"**. There are edge cases where I couldn't wrap my head around; chinese characters, russian characters, html tags etc. so I am using cell feed as a fallback when insertion with the list api fails. – canerbalci May 13 '13 at 12:22
  • Good examples of using the different feeds https://code.google.com/p/gdata-java-client/source/browse/trunk/java/src/com/google/gdata/data/spreadsheet/?r=93 they should help regards knowing what is and is not possible. – eddyparkinson May 14 '13 at 00:52

4 Answers4

4

The main google API docs say you can do it using the WorksheetFeed. So must be possible from python.

Google Data APIs Client Library https://developers.google.com/gdata/javadoc/com/google/gdata/data/spreadsheet/WorksheetFeed (updated link 2021/10/31)

Edit: @alvaro nortes correctly pointed out the link is broken. Update the link.

eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
  • Thanks for the response. Although the worksheet feed contains an attr named "row_count", it will count the rows even if they are empty. What I was trying to get the number of filled rows. I think I should have been more clear about what I needed. – canerbalci May 14 '13 at 09:57
  • is the issue data or speed? I suspect you don't gain much speed using a different type of call, and may as well do a read of the sheet, then a write, but the idea wants testing to be sure. – eddyparkinson May 15 '13 at 08:17
  • 1
    let's say the sheet contains five filled rows. i want to add another row of data. when i run into the problem i mentioned with the list feed api(column names), i fallback to cell feed to fill each column in the sixth row. at that point if i ask the worksheet feed abou the number of rows, it returns 20. there are 20 rows in the sheet but only five of them are full. i ended up using worksheet row number and if its 20, i download the whole sheet to count filled rows. if its greater than 20, i trust the worksheet row_count, and continue to fill the next line. feels hacky :( – canerbalci May 16 '13 at 12:01
  • Is this still the approach people are using? – William Entriken Oct 09 '13 at 14:32
  • 1
    @FullDecent google-apps-script is another option, but if you use the spreadsheet-api then yes, this is the way. – eddyparkinson Oct 10 '13 at 04:53
4

Using pygsheets, this will return the number of filled rows in the spreadsheet

import pygsheets

gc = pygsheets.authorize(service_file='client_secret.json'). #authorization
worksheet = gc.open('Sign Up').sheet1. #opens the first sheet in "Sign Up"
cells = worksheet.get_all_values(include_tailing_empty_rows=False, include_tailing_empty=False, returnas='matrix')
end_row = len(cells)
print(end_row)
Omobolaji
  • 71
  • 5
1

I think This will work. You need to use ListFeed getEntries() to find rows having entry. 

URL listFeedUrl = worksheets.get(x).getListFeedUrl();
ListFeed feed = googleservice.getFeed(listFeedUrl, ListFeed.class);
System.out.println("Number of filled rows"+feed_L.getEntries().size()+1);
System.out.println("First Empty row"+feed_L.getEntries().size()+2);

I Assume there is no blank row in between the rows. Blank cells cause no issues, I mean a row must have at least one cell filled.

Joachin Joseph
  • 333
  • 5
  • 18
0

You can use the library Sheetfu and its method get_data_range. It basically returns the range that contains data only.

from sheetfu import SpreadsheetApp

sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet = spreadsheet.get_sheet_by_name('Sheet1')
data_range = sheet.get_data_range()

This will calculate the coordinates the range that contains data. You can then set the values, notes, backgrounds, etc.. using the following:

values = data_range.get_values()
notes = data_range.get_notes()
backgrounds = data_range.get_backgrounds()

The Range object contains plenty of methods. Check the documentation here.

Philippe Oger
  • 1,021
  • 9
  • 21
  • If you take a look at the implementation of get_data_range, you will see that it actually pull all the values, so it is exact answer to getiing number of rows without fetching the whole sheet – Karen Fisher Nov 09 '22 at 12:48