Simple Google Spreadsheets to Pandas DataFrame in IPython Notebook

sample.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:5132c75b98b006eeff071000b2fbf4d6cadd1969abf44b24077e79a8c37ad200"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "This is a simple flow to authorize an IPython Notebook to fetch Google Spreadsheets.\n",
      "\n",
      "1. Go to the [Google Developer Console](https://console.developers.google.com/project)\n",
      "1. Create a new project\n",
      "1. Enable the Google Drive API\n",
      "1. Create a new Client ID of type \"Installed Application (other)\"\n",
      "1. Copy that new Client ID and Secret into the cell below\n",
      "\n",
      "`pip install oauth2client, gspread, and pandas`"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# these won't work for you :)\n",
      "client_id = \"142887107151-depemnnp9g4nvsi2j8eap63q3flr0e0o.apps.googleusercontent.com\"\n",
      "client_secret = \"cUq-lPM6Ly1bpLUEBc8krvAO\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import oauth2client.client, oauth2client.file, oauth2client.tools\n",
      "import gspread\n",
      "\n",
      "flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds')\n",
      "storage = oauth2client.file.Storage('credentials.dat')\n",
      "credentials = storage.get()\n",
      "if credentials is None or credentials.invalid:\n",
      "    import argparse\n",
      "    flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args([])\n",
      "    credentials = oauth2client.tools.run_flow(flow, storage, flags)\n",
      "\n",
      "gc = gspread.authorize(credentials)\n",
      "\n",
      "# when this cell is run, your browser will take you to a Google authorization page.\n",
      "# this authorization is complete, the credentials will be cached in a file named credentials.dat"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%pylab inline"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Populating the interactive namespace from numpy and matplotlib\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/io/excel.py:626: UserWarning: Installed openpyxl is not supported at this time. Use >=1.6.1 and <2.0.0.\n",
        "  .format(openpyxl_compat.start_ver, openpyxl_compat.stop_ver))\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sheet = gc.open(\"My Spreadsheet\").sheet1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.DataFrame(sheet.get_all_records())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>thing1</th>\n",
        "      <th>thing2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 1</td>\n",
        "      <td> 4</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 3</td>\n",
        "      <td> 6</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "   thing1  thing2\n",
        "0       1       4\n",
        "1       2       5\n",
        "2       3       6"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "plt.plot(df.thing1, df.thing2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 11,
       "text": [
        "[<matplotlib.lines.Line2D at 0x10ac79dd0>]"
       ]
      },
      {
       "metadata": {},
       "output_type": "display_data",
       "png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEACAYAAABI5zaHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADiFJREFUeJzt3G2IHOUBwPH/JrHYJKiUypWaQCQaqNDSaJsEo2aRVkiw\nfhLqFzUKemhthVKhHwo5o1U0BEVKNbTXIrRUioKmNqUm0BWhGA15qTVJ0SSCsRoL1peYD/Xi9sNs\nkslmX2Zn533+P1hud2du8zCOz/3vudkFSZIkSZIkSZIkSZIkSZKkyjsPeBrYB+wFVvTY5zHgDWAP\nsDS7oUmS4noSuLVzfw5wbtf2NcCWzv3lwMsZjUuSFNO5wMEh+zwBfD/0eD8wkdqIJEkDzYqwz4XA\nf4DfAjuBXwFzu/a5AHg79PgwsCCJAUqSRhdlcp8DXAr8svP1U+CnPfZrdD1ujzc0SVJccyLsc7hz\ne7Xz+GnOnNzfARaGHi/oPHfS4sWL2wcOHIg5TEmqrQPARaN+U5Ryf49gyWVJ5/F3gNe79tkM3NS5\nvwL4EDhy2ugOHKDdbntL6LZu3brcx1CVm8fS41mU2969bZYvb3P11W0OHQqeAxaPOrFHndwBfgj8\nnuAyx28ADwKTnRsEV8ocBN4ENgF3xhmMJNXR8ePw8MNw1VWwdi1s2waLFo33mlGWZSCY1L/d9dym\nrsd3jTcUSaqfffvglltg3jx49dXxJ/UTopa7CqbZbOY9hMrwWCbL4xlNGrUe1n2FS5ranfUjSaq1\ncK1PTw+e1BuNBsSYqy13ScpI2rUeFnXNXZI0hrTW1vux3CUpRVnWepjlLkkpybrWwyx3SUpYXrUe\nZrlLUoLyrPUwy12SElCEWg+z3CVpTEWp9TDLXZJiKlqth1nukhRDEWs9zHKXpBEUudbDLHdJiqjo\ntR5muUvSEGWp9TDLXZIGKFOth1nuktRDGWs9zHKXpC5lrfUwy12SOspe62GWuyRRjVoPs9wl1VqV\naj3McpdUW1Wr9TDLXVLtVLXWwyx3SbVS5VoPs9wl1UIdaj3McpdUeXWp9TDLXVJl1a3Wwyx3SZVU\nx1oPs9wlVUqdaz3McpdUGXWv9TDLXVLpWetnstwllZq13pvlLqmUrPXBLHdJpWOtD2e5SyqNcK3f\nfDNs3erE3o/lLqkUrPXRWO6SCs1aj8dyl1RY1np8lrukwrHWx2e5SyoUaz0ZlrukQrDWk2W5S8qd\ntZ48y11Sbqz19FjuknJhrafLcpeUKWs9G5a7pMxY69mx3CWlzlrPXtRyfwv4GDgOfAYs69reBJ4D\nDnYePwPcP/7wJJWdtZ6PqJN7m2AC/2DAPi8C1407IEnVcPw4bNwIGzbA+vUwOQmzXCvIzChr7o0x\nt0uqCWs9f1F/jraBbcAO4LY+2y8H9gBbgEsSGZ2kUnFtvTiilvtK4F3gfGArsB94KbR9J7AQOAas\nBp4FlnS/yNTU1Mn7zWaTZrMZY8iSishaT0ar1aLVao39OnGWUtYBR4GNA/Y5BFzG6Wv07Xa7HeOf\nk1Rkrq2nq9FoQIy5Okq5zwVmA58A84BrgHu79pkA3idYnlnWGcigP75KqgBrvbii/HydIFiC2Q1s\nB54HXgAmOzeA64HXOvs8CtyQ+EglFYZr68WX5RUuLstIFRCu9elpJ/W0xV2WcWVMUiTWern42TKS\nhnJtvXwsd0l9WevlZblL6slaLzfLXdJprPVqsNwlnWStV4flLslaryDLXao5a72aLHeppqz1arPc\npRqy1qvPcpdqxFqvD8tdqglrvV4sd6nirPV6stylCrPW68tylyrIWpflLlWMtS6w3KXKsNYVZrlL\nFWCtq5vlLpWYta5+LHeppKx1DWK5SyVjrSsKy10qEWtdUVnuUglY6xqV5S4VnLWuOCx3qaCsdY3D\ncpcKyFrXuCx3qUCsdSXFcpcKwlpXkix3KWfWutJguUs5staVFstdyoG1rrRZ7lLGrHVlwXKXMmKt\nK0uWu5QBa11Zs9ylFFnryovlLqXEWleeLHcpYda6isBylxJkrasoLHcpAda6isZyl8ZkrauILHcp\nJmtdRWa5SzFY6yo6y10agbWusrDcpYisdZWJ5S4NYa2rjCx3aQBrXWVluUs9WOsqu6jl/hbwMXAc\n+AxY1mOfx4DVwDFgLbBr/OFJ2bPWVQVRy70NNIGl9J7Y1wAXARcDtwOPJzE4KUvWuqpklDX3xoBt\n1wFPdu5vB84DJoAjMcclZcpaV9WMUu7bgB3AbT22XwC8HXp8GFgw3tCk9Fnrqqqo5b4SeBc4H9gK\n7Ade6tqnu+zb3S8yNTV18n6z2aTZbEb856Xknaj1uXPhlVfgwgvzHpEErVaLVqs19usMWmrpZx1w\nFNgYeu4JoAU81Xm8H1jF6csy7Xb7jPleytzx47BxI2zYAOvXw+QkzPK6MRVUo9GAGHN1lHKfC8wG\nPgHmAdcA93btsxm4i2ByXwF8iOvtKiDX1lUXUXplgmAJZjfBH0ufB14AJjs3gC3AQeBNYBNwZ+Ij\nlcbg2rrqJs6yTFwuyygX4VqfnnZSV7nEXZZxpVGVZa2rzvxsGVWSa+uqO8tdlWKtSwHLXZVhrUun\nWO4qPWtdOpPlrlKz1qXeLHeVkrUuDWa5q3SsdWk4y12lYa1L0VnuKgVrXRqN5a5Cs9aleCx3FZa1\nLsVnuatwrHVpfJa7CsVal5JhuasQrHUpWZa7cmetS8mz3JUba11Kj+WuXFjrUrosd2XKWpeyYbkr\nM9a6lB3LXamz1qXsWe5KlbUu5cNyVyqsdSlflrsSZ61L+bPclRhrXSoOy12JsNalYrHcNRZrXSom\ny12xWetScVnuGpm1LhWf5a6RWOtSOVjuisRal8rFctdQ1rpUPpa7+rLWpfKy3NWTtS6Vm+Wu01jr\nUjVY7jrJWpeqw3KXtS5VkOVec9a6VE2We01Z61K1We41ZK1L1We514i1LtWH5V4T1rpUL5Z7xVnr\nUj1Z7hVmrUv1ZblXkLUuyXKvGGtdEljulWGtSwqLWu6zgR3AYeB7XduawHPAwc7jZ4D7kxicorHW\nJXWLWu53A3uBdp/tLwJLOzcn9oxY65L6iVLuC4A1wM+BH/fZp5HYiBSJtS5pkCjl/ghwD/B5n+1t\n4HJgD7AFuCSZoamXmRlrXdJww8r9WuB9YBfB2novO4GFwDFgNfAssKTXjlNTUyfvN5tNms1+L6le\n9u2DtWth/nxrXaqqVqtFq9Ua+3WGLac8ANwIzABnA+cQ/MH0pgHfcwi4DPig6/l2u91vyV6DzMzA\nxo2wYQPcdx9MTsIsr3OSaqHRaECMpe9RvmEV8BPOvFpmgqDu28Ay4I/Aoh7f7+QeQ7jWp6etdalu\n4k7uo/bfidl5snMDuB54DdgNPArcMOogdKaZGXjoIbjyymByd21d0iiyvMrFco/IWpd0QlblrhRZ\n65KS4mfLFES41nfscFKXNB7LPWfWuqQ0WO45stYlpcVyz4G1LiltlnvGrHVJWbDcM2KtS8qS5Z4B\na11S1iz3FFnrkvJiuafEWpeUJ8s9Yda6pCKw3BNkrUsqCss9Ada6pKKx3MdkrUsqIss9JmtdUpFZ\n7jFY65KKznIfgbUuqSws94isdUllYrkPYa1LKiPLfQBrXVJZWe49WOuSys5y72KtS6oCy73DWpdU\nJZY71rqk6ql1uVvrkqqqtuVurUuqstqVu7UuqQ5qVe7WuqS6qEW5W+uS6qby5W6tS6qjypa7tS6p\nzipZ7ta6pLqrVLlb65IUqEy5W+uSdErpy91al6QzlbrcrXVJ6q2U5W6tS9JgpSt3a12ShitNuVvr\nkhRdKcrdWpek0RS63K11SYqnsOVurUtSfIUrd2tdksZXqHK31iUpGYUod2tdkpKVe7lb65KUvKjl\nPhvYBfypz/bHgDeAPcDSKC9orUtSeqJO7ncDe4F2j21rgIuAi4HbgceHvdi+fbByJbzwQlDrd9wB\nswqxQFQerVYr7yFUhscyWR7PYogypS4gmMB/DTR6bL8OeLJzfztwHjDR64Ws9eT4P1ByPJbJ8ngW\nQ5Q190eAe4Bz+my/AHg79PgwwQ+EI907rlzp2rokZWFYuV8LvE+w3t6r2k/o3tZr+cZal6SMDJqw\nAR4AbgRmgLMJ6v0Z4KbQPk8ALeCpzuP9wCrOLPc3gcXjDVeSaucAwd81U7OK3lfLrAG2dO6vAF5O\ncxCSpOFGvc79xHLLZOfrJoKJfQ1BmX8K3JLM0CRJkiSl5jcEa+2vDdhn5Dc81diw49kEPiL4g/cu\n4GfZDKuUFgJ/A14H/gn8qM9+np/RRDmeTTw/ozqb4FLy3QTvKXqwz365nZ9Xdv7BfpNReH1+Oa7P\nDzPseDaBzZmNpty+Anyzc38+8C/ga137eH5GF+V4NvH8HMXcztc5BOfeFV3bRzo/k35f6EvAfwds\nj/yGJwHDjycMv+JJgfcIqgjgKLAP+GrXPp6f0UU5nuD5OYpjna9fIPjIlw+6to90fmb9pv9+b3hS\nPG3gcoJf0bYAl+Q7nNJYRPAb0fau5z0/41lE7+Pp+TmaWQQ/MI8QLHnt7do+0vmZx6dCRnrDkyLZ\nSbD2eQxYDTwLLMl1RMU3H3ia4POSjvbY7vk5mkHH0/NzNJ8TLHWdC/yVYFmr1bVP5PMz63J/h+A/\n9gkLOs8pnk849avcX4CzgC/lN5zCO4vgTXi/I5hounl+jmbY8fT8jOcj4M/At7qeH+n8zHpy38yp\nd7euAD6kx2fQKLIJTv0kX9a5371Op0ADmCb4VffRPvt4fkYX5Xh6fkb3ZYI1dIAvAt8luMIoLNfz\n8w/Av4H/EawN3UrwhqfJ0D6/IHjD0x7g0qwGVlLDjucPCC5D2w38neA/uHq7guDX3t2cujRvNZ6f\ncUU5np6f0X2dYBlrN/APgg9rBM9PSZIkSZIkSZIkSZIkSZIkSZIkSUX1f6Z0BKuyNxcoAAAAAElF\nTkSuQmCC\n",
       "text": [
        "<matplotlib.figure.Figure at 0x10ac56910>"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}

sample.pdf

How exactly does one retrieve valid Client ID and Client Secret? I attempted to do so by parsing the JSON returned by "Create Credentials" > "Service Account Key" at the following link?
https://console.developers.google.com/apis/credentials?project=PROJECT-NAME

The JSON file I've retrieved only has the following keys (and the "client_id" is only an integer, with no domain after a hyphen):
private_key
private_key_id
token_uri
auth_provider_x509_cert_url
auth_uri
client_email
client_id
project_id
type
client_x509_cert_url

The following link might be helpful: https://developers.google.com/identity/protocols/application-default-credentials?hl=en_US

Thanks in advance for any clues. If this is a common stumbling block for people, I could add further instructions to the documentation.

TS

btw, if you are (like us) on python3 with a buggy httplib2 that refuses to connect to a proxy server, the hack below makes oauthlib2 use requests instead of httplib2

class DropInHttplib():
    def __init__(self):
        self.sess = requests.session()

    def request(self, uri, method='GET' , body=None , headers=None):
        resp = None
        if method.lower() == 'get':
            resp = self.sess.get(uri, headers=headers)

        elif method.lower() == 'post':
            resp = self.sess.post(uri,data=body,headers=headers)
        if resp.status_code == 200:
            resp.status = httplib2.http.client.OK
        else:
            resp.status = httplib2.http.client.BAD_REQUEST
        return (resp,resp.text)

then, one can do

httpclient = DropInHttplib()
credentials = oauth2client.tools.run_flow(flow, storage, flags, httpclient)

and we needed to modify the flags so that it would work on a server (where we could not open a browser window)

flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args(["--noauth_local_webserver"])