Manipulate Google Sheets Using Pandas DataFrames
pgsheets is a Python3 library for interacting with Google Sheets. It makes use of Pandas DataFrames, 2-dimensional structures perfectly suited for data analysis and representing a spreadsheet.
This library can be integrated easily with your existing data to present dashboards, update documents, or provide quick data analysis.
Simply install with pip:
$ pip install pgsheets
If you haven’t already you will need to create a project in Google’s Developer Console and get your Client ID and Client Secret.
Using your Google client id and client secret we can get a authorization URL to present to a user:
>>> from pgsheets import Token, Client >>> c = Client(my_client_id, my_client_secret) >>> c.getOauthUrl() 'https://accounts.google.com/o/oauth2/auth?...'
By visiting this URL a Google user can consent to your application viewing and modifying their Google sheets. After consenting to this an access code is returned, which we use to get a token:
>>> my_token = c.getRefreshToken(access_code) >>> type(my_token) str >>> t = Token(c, my_token)
You need to save my_token for future use.
Create a spreadsheet (and make sure you save it) and copy the url. Now we can access the Spreadsheet:
>>> import pandas as pd >>> from pgsheets import Spreadsheet >>> s = Spreadsheet(t, my_url) >>> s <Spreadsheet title='test' key='.....'> >>> s.getTitle() 'test' >>> s.getWorksheets() [<Worksheet title='Sheet1' sheet_key='.....'>] >>> w = s.getWorksheet('Sheet1') >>> w.getTitle() 'Sheet1' >>> w.setDataFrame( pd.DataFrame([['money', 'interest', 'years', 'result'], ['1000', '0.015', '3', '=A2 * (1+B2) ^ C2']]), copy_columns=False, copy_index=False, resize=True) >>> w.asDataFrame() interest years result money 1000 0.015 3 =RC[-3] * (1+RC[-2]) ^ RC[-1] >>> w.asDataFrame(values=True) interest years result money 1000 0.015 3 1045.678375 >>> df = w.asDataFrame() >>> df['checked'] = "TRUE" >>> w.setDataFrame(df) >>> w.asDataFrame() interest years result checked money 1000 0.015 3 =RC[-3] * (1+RC[-2]) ^ RC[-1] TRUE
The library has only been tested in Python3.4. It will almost certainly not work in Python2.
Currently the following cannot be done with pgsheets:
Finally the Google API has some limitations. Ideally this code should not cause any changes to a worksheet:
Unfortunately, there are certain edge cases. For example, with a Formula such as the following
which displays across two cells:
There is no clear way to know that the cell on the right wasn’t input as a ‘2’ by the user. Thus the above code would cause the following output:
which displays as:
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
|File Name & Checksum SHA256 Checksum Help||Version||File Type||Upload Date|
|pgsheets-0.0.1-py3-none-any.whl (11.4 kB) Copy SHA256 Checksum SHA256||py3||Wheel||Jul 18, 2015|
|pgsheets-0.0.1.tar.gz (10.7 kB) Copy SHA256 Checksum SHA256||–||Source||Jul 18, 2015|