Setting up a Google Project

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.

1. Navigate to the Google Developer Console

2. Create a project (you will be redirected to the project page)

3. Click on APIs & Auth

4. Click on Consent screen and set a Product Name

5. Now click on credentials.

6. Click Create new Client ID and select Installed Application > Other

Getting a Token

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.

Editing a spreadsheet

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  =R[0]C[-3] * (1+R[0]C[-2]) ^ R[0]C[-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  =R[0]C[-3] * (1+R[0]C[-2]) ^ R[0]C[-1]    TRUE