{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/lyin'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " os.path.expanduser('~')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import httplib2\n",
    "import os\n",
    "\n",
    "from apiclient import discovery\n",
    "import oauth2client\n",
    "from oauth2client import client\n",
    "from oauth2client import tools\n",
    "import pandas as pd\n",
    "\n",
    "# If modifying these scopes, delete your previously saved credentials\n",
    "# at ~/.credentials/sheets.googleapis.com-python-quickstart.json\n",
    "SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'\n",
    "CRED_PATH = 'creds/'\n",
    "CLIENT_SECRET_FILE = os.path.join(CRED_PATH,'client_secret.json')\n",
    "AUTHENTICATED_CRED = os.path.join(CRED_PATH,'access_token.json')\n",
    "\n",
    "API_VERSION = 'v4'\n",
    "API_SERVICE_NAME = 'sheets'\n",
    "APPLICATION_NAME = 'Holy Sheet'\n",
    "\n",
    "DISCOVERYURL = ('https://sheets.googleapis.com/$discovery/rest?'\n",
    "                'version=v4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_credentials():\n",
    "    \"\"\"Gets valid user credentials from storage.\n",
    "\n",
    "    If nothing has been stored, or if the stored credentials are invalid,\n",
    "    the OAuth2 flow is completed to obtain the new credentials.\n",
    "\n",
    "    Returns:\n",
    "        Credentials, the obtained credential.\n",
    "    \"\"\"\n",
    "\n",
    "    storage = oauth2client.file.Storage(AUTHENTICATED_CRED)\n",
    "    credentials = storage.get()\n",
    "    if credentials is None or credentials.invalid:\n",
    "        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)\n",
    "        flow.user_agent = APPLICATION_NAME\n",
    "        \n",
    "        credentials = tools.run_flow(flow, storage)\n",
    "\n",
    "        print('Storing credentials to ' + AUTHENTICATED_CRED)\n",
    "    return credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sheet_2_df(id,sheet,range):\n",
    "    \"\"\"Shows basic usage of the Sheets API.\n",
    "    \n",
    "    Creates a Sheets API service object and prints the names and majors of\n",
    "    students in a sample spreadsheet:\n",
    "    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit\n",
    "    \"\"\"\n",
    "    credentials = get_credentials()\n",
    "    http = credentials.authorize(httplib2.Http())\n",
    "\n",
    "    service = discovery.build(\n",
    "        API_SERVICE_NAME, \n",
    "        API_VERSION, \n",
    "        http=http,\n",
    "        discoveryServiceUrl=DISCOVERYURL\n",
    "    )\n",
    "    \n",
    "    spreadsheetId = '1STwGq15wd5zCz8_dnDCM7Rcsij4kY10tEMVR2xwfdVA'\n",
    "    rangeName = 'Spotify Claims!A2:AB'\n",
    "    rangeName = '{SHEET}!{RANGE}'.format(SHEET=sheet,RANGE=range)\n",
    "    result = service.spreadsheets().values().get(\n",
    "        spreadsheetId=id, range=rangeName).execute()\n",
    "    values = result.get('values', [])\n",
    "\n",
    "    if not values:\n",
    "        print('No data found.')\n",
    "    else:\n",
    "        return pd.DataFrame(\n",
    "            data = [row for row in values[1:] if row],\n",
    "            columns = values[0]\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = sheet_2_df(\n",
    "    id = '1STwGq15wd5zCz8_dnDCM7Rcsij4kY10tEMVR2xwfdVA',\n",
    "    sheet='Spotify Claims', \n",
    "    range='A2:AB'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df[[col for col in df.columns if col]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df[df['Deadline']!='1/5/1900']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from oauth2client.client import flow_from_clientsecrets\n",
    "from oauth2client.file import Storage\n",
    "from oauth2client.tools import argparser, run_flow\n",
    "from apiclient.discovery import build"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "CRED_PATH  = '/home/lyin/Documents/datalytics/Store_Specific/Youtube/creds/'  #rbox\n",
    "CRED_PATH  = 'creds/'  #local\n",
    "CLIENT_SECRETS_FILE = os.path.join(CRED_PATH,\"client_secrets.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'\n",
    "APPLICATION_NAME = 'Google Sheets API Python Quickstart'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "MISSING_CLIENT_SECRETS_MESSAGE = \"\"\"\n",
    "    WARNING: Please configure OAuth 2.0\n",
    "    \n",
    "    To make this sample run you will need to populate the client_secrets.json file\n",
    "    found at:\n",
    "    \n",
    "       %s\n",
    "    \n",
    "    with information from the Developers Console\n",
    "    https://console.developers.google.com/\n",
    "    \n",
    "    For more information about the client_secrets.json file format, please visit:\n",
    "    https://developers.google.com/api-client-library/python/guide/aaa_client_secrets\n",
    "    \"\"\" % os.path.abspath(CLIENT_SECRETS_FILE)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_authenticated_services(SCOPES):\n",
    "    \"\"\"\n",
    "    Before using the Youtube API, we must be be authenticated.\n",
    "    The authentication process is as follows:\n",
    "    1. request access token using your CLIENT_SECRETS_FILE,\n",
    "       to perfom tasks in YOUTUBE_SCOPES.\n",
    "    2. store access token in local directory.\n",
    "    3. check for valid credentials from the access token.\n",
    "    4. Based on the actions wished to perform, a different youtube object\n",
    "       is built for reporting or querying.\n",
    "    \"\"\"\n",
    "    # 1\n",
    "    flow = flow_from_clientsecrets(\n",
    "        CLIENT_SECRETS_FILE,\n",
    "        scope = \" \".join(SCOPES),\n",
    "        message = MISSING_CLIENT_SECRETS_MESSAGE\n",
    "    )\n",
    " \n",
    "    # 2\n",
    "    storage = Storage(\n",
    "        os.path.join(CRED_PATH,'authentication.json')\n",
    "    )\n",
    "    credentials = storage.get()\n",
    "\n",
    "    # 3\n",
    "    if credentials is None or credentials.invalid:\n",
    "        credentials = run_flow(\n",
    "            flow, storage)\n",
    "\n",
    "\n",
    "    return credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "SystemExit",
     "evalue": "\n    WARNING: Please configure OAuth 2.0\n    \n    To make this sample run you will need to populate the client_secrets.json file\n    found at:\n    \n       /User/lyin/Documents/datalytics/Store_Specific/Youtube/creds/client_secrets_installed.json\n    \n    with information from the Developers Console\n    https://console.developers.google.com/\n    \n    For more information about the client_secrets.json file format, please visit:\n    https://developers.google.com/api-client-library/python/guide/aaa_client_secrets\n    ",
     "output_type": "error",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[0;31mSystemExit\u001b[0m\u001b[0;31m:\u001b[0m \n    WARNING: Please configure OAuth 2.0\n    \n    To make this sample run you will need to populate the client_secrets.json file\n    found at:\n    \n       /User/lyin/Documents/datalytics/Store_Specific/Youtube/creds/client_secrets_installed.json\n    \n    with information from the Developers Console\n    https://console.developers.google.com/\n    \n    For more information about the client_secrets.json file format, please visit:\n    https://developers.google.com/api-client-library/python/guide/aaa_client_secrets\n    \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "To exit: use 'exit', 'quit', or Ctrl-D.\n"
     ]
    }
   ],
   "source": [
    "get_authenticated_services([SCOPES])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "root_py3",
   "language": "python",
   "name": "root_py3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}