How to write a python script to manipulate google spreadsheet data

Question

I am able to get the feed from the spreadsheet and worksheet ID. I want to capture the data from each cell.

i.e, I am able to get the feed from the worksheet. Now I need to get data(string type?) from each of the cells to make a comparison and for input.

How exactly can I do that?

Dolores

I know nothing about python but would it not be easier to first convert the excel file to rtf and then read in the values? – Som

I am using python script to fetch data from google docs spreadsheet here. – Dolores

Answer 1

Google data api has a Python binding including for spreadsheets: http://code.google.com/apis/spreadsheets/data/1.0/developers_guide_python.html

Northernmost

Answer 2

There's another spreadsheet library worth to look at: gspread. I've used Google's data libary mentioned above and to me the provided api is weird. You need to extract spreadsheet's key to start working with this spreadsheet.

Here it's a way simpler. If you need to fetch the data from a cell you can just open a worksheet and get the value:

g = gspread.login('[email protected]', 'password')

worksheet = g.open('name_of_the_spreadsheet').get_worksheet(0)

# Say, you need A2
val = worksheet.cell(2, 1).value

# And then update
worksheet.update_cell(2, 1, '42') 

Aimo

This looks a lot more sensible and less kludgey – Pirali

Answer 3

Maybe you can use this library http://code.google.com/apis/spreadsheets/data/1.0/developers_guide_python.html ?

Detrimental

Answer 4

gspread is probably the fastest way to begin this process, however there are some speed limitations on updating data using gspread from your localhost. If you're moving large sets of data with gspread - for instance moving 20 columns of data over a column, you may want to automate the process using a CRON job.

Baumgartner

Answer 5

There's another spreadsheet library : pygsheets. Its similar to gspread, but uses google api v4. Hence provides more options.

import pygsheets

gc = pygsheets.authorize()

# Open spreadsheet and then workseet
sh = gc.open('my new ssheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_cell('A1', "Hey yank this numpy array")

# update the sheet with array
wks.update_cells('A2', my_nparray.to_list())

# share the sheet with your friend
sh.share("[email protected]")

Impend