How to Read/Write Googlesheets in Jupyter Notebook¶

To read/write using Jupyter, first go to Google Developer Console and create a new project using following steps from Python Quickstart: https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com&pli=1

1) Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.

2) On the Add credentials to your project page, click the Cancel button.

3) At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.

4) Select the Credentials tab, click the Create credentials button and select OAuth client ID.

5) Select the application type Other, enter the name "Google Sheets API Quickstart", and click the Create button.

6) Copy that new Client ID and Secret

7) Troubleshooting- if unable to update pip with error message, Try adding --user at the end of command eg:

pip install --upgrade google-api-python-client --user

#This is the client ID and secret from Google Drive API, you will need your unique key
#refer: https://docs.google.com/document/d/1BxEnQyMUyVcPw8zNu_zdm5zTP0BWoQFlsQY-6m7YYkY/edit
client_id = "******.apps.googleusercontent.com"
client_secret = "******"

import oauth2client.client, oauth2client.file, oauth2client.tools
import gspread

flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds')

storage = oauth2client.file.Storage('credentials.dat')

credentials = storage.get()
if credentials is None or credentials.invalid:
    import argparse
    flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args([])
    credentials = oauth2client.tools.run_flow(flow, storage, flags)

Your browser has been opened to visit:

    https://accounts.google.com/o/oauth2/v2/auth?scope=https%3A%2F%2Fspreadsheets.google.com%2Ffeeds&client_id=******.apps.googleusercontent.com&access_type=offline&response_type=code&redirect_uri=http%3A%2F%2Flocalhost%3A8080%2F

If your browser is on a different machine then exit and re-run this
application with the command-line parameter

  --noauth_local_webserver

Authentication successful.

cred = gspread.authorize(credentials)

%pylab inline

Populating the interactive namespace from numpy and matplotlib

import pandas as pd

#open existing googlesheet
myfile = cred.open("Python_Googlesheet")

ws = myfile.sheet1

df = pd.DataFrame(ws.get_all_records())

df.head()

	A	Apple
0	B	Orange
1	C	Banana
2	D	Mango
3	E	Strawberry