CODEWITHSUNDEEP
pythonpython-3.xgspread
Goriss
Goriss

Reputation: 147

Getting all column values from google sheet using Gspread and Python

So i have a problem with the Gspread for python 3

when i do something like:

x = worksheet.cell(1,1).value
print(x)

Then i get the value of cell 1,1 which in my case is:

Nice

But when i do:

x = worksheet.col_values(1)
print(x)

Then i get all the results as in 

'Nice', 'Cool','','','','','','','','','','','','','',''

And all the empty cells as well which i don't understand since i am asking just for values why i do i get all the '', empty brackets and why the other results are also in brackets ? I would expect something like:

Nice
Cool

When i call for the values of a column and those are the only values. Anyone know how to get such results ?

According to this https://github.com/burnash/gspread documentation it should work but it dose not.

Upvotes: 3

Views: 14238

Answers (3)

pythonfanatico
pythonfanatico

Reputation: 1

You can also create a while loop and make something like this. Let's say you want column E to G, you can start the loop from x=5 and end it on x=7. Just make sure that you transpose the dataframe at the end before printing it.

columns = []
x = 5
while x < 8:
    data = sheet.col_values(x)[1:]
    x += 1
    columns.append(data)

    
df = pd.DataFrame(columns).T
print(df)

Upvotes: 0

d_-
d_-

Reputation: 1491

Looking again at the gspread-documentation, I was able to create a dataframe and then thereafter obtain the column-values:

gc = gspread.authorize(GoogleCredentials.get_application_default())
sht2 = gc.open_by_url('https://docs.google.com/spreadsheets/d/<id>')
worksheet = sht2.worksheet("Sheet-name")
dataframe = pd.DataFrame(worksheet.get_all_records())
dataframe.head(3)

Note: Don't forget to enable your gsheet's sharing-settings to "Anyone with a link", to be able to access the sheet from e.g. google colab.

Upvotes: 1

roganjosh
roganjosh

Reputation: 13185

You are getting all of the column data, contained in a list. It starts at row one and gives you all rows in that column to the bottom of the spreadsheet (1000 rows by default), including empty cells. The documentation tells you this:

col_values(col)
Returns a list of all values in column col.

Empty cells in this list will be rendered as None.

This seems to have been changed to return empty strings instead, but the principle is the same.

To get just values, use a list comprehension:

x = [item for item in worksheet.col_values(1) if item]

Noting that the above will remove blank rows between items, which might cause misalignment if you try to work with multiple columns where row number is important. Since it's a list, individual items are accessed with:

for item in x:
    print(item)

Upvotes: 4

Related Questions