Getting Google Sheets to work with Pandas

GSheet_to_Pandas.ipynb

{
  "cells": [
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Notes\n- https://www.youtube.com/watch?v=7I2s81TsCnc (great how to)\n- https://github.com/burnash/gspread (CRUD stuff for gspread)\n- https://github.com/robin900/gspread-dataframe (easy conversion to pandas library)\n- https://docs.google.com/spreadsheets/d/1CpGtLrtVYeTLdCGeKEg0dNkhBLRNcI2MW2Eq3ES_rK8/edit#gid=1502667612\n- Extract the email address from the json file\n- Add that email address to the share list of the google spreadsheet"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "import gspread\nfrom oauth2client.service_account import ServiceAccountCredentials\nfrom gspread_dataframe import get_as_dataframe, set_with_dataframe\n\nscope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']\ncredentials = ServiceAccountCredentials.from_json_keyfile_name('domain-0b99361e8e0b.json', scope)\ngc = gspread.authorize(credentials)\n#wks = gc.open('Catalog Text 2018').sheet1\nwks = gc.open('Catalog Text 2018').worksheet('gsheet')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Load a Worksheet into Pandas"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "df = get_as_dataframe(wks)\ndf = df.dropna(thresh=2) # dropping null rows for now\ndf.head()",
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "version_major": 2,
              "version_minor": 0,
              "model_id": "dd918486bbff43eb842bb3e883946807"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Add a Worksheet (to a Spreadsheet) and insert a Dataframe into it"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "import pandas as pd\nfrom gspread_dataframe import get_as_dataframe, set_with_dataframe\n\nspreadsheet = gc.open('Catalog Text 2018')\n\nworksheet = spreadsheet.add_worksheet(title=\"Create_Test\", rows=\"100\", cols=\"2\")\ndf = pd.DataFrame.from_records([{'a': i, 'b': i * 2} for i in range(100)])\nset_with_dataframe(worksheet, df)\n\n#df2 = get_as_dataframe(worksheet)",
      "execution_count": 9,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Get 10 Values from Column  A of Worksheet Create_Test"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "values_list = worksheet.col_values(1)\nvalues_list[1:10]\n",
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 11,
          "data": {
            "text/plain": "['0', '1', '2', '3', '4', '5', '6', '7', '8']"
          },
          "metadata": {}
        }
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "conda-env-jupyter-py",
      "display_name": "Python [conda env:jupyter]",
      "language": "python"
    },
    "toc": {
      "threshold": 4,
      "number_sections": true,
      "toc_cell": false,
      "toc_window_display": true,
      "toc_section_display": "block",
      "sideBar": true,
      "navigate_menu": true,
      "moveMenuLeft": true,
      "widenNotebook": false,
      "colors": {
        "hover_highlight": "#DAA520",
        "selected_highlight": "#FFD700",
        "running_highlight": "#FF0000",
        "wrapper_background": "#FFFFFF",
        "sidebar_border": "#EEEEEE",
        "navigate_text": "#333333",
        "navigate_num": "#000000"
      },
      "nav_menu": {
        "width": "252px",
        "height": "84px"
      }
    },
    "language_info": {
      "name": "python",
      "version": "3.6.3",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "gist": {
      "id": "e1cbddaac4c242e5b701b841ae40fae9",
      "data": {
        "description": "Getting Google Sheets to work with Pandas",
        "public": true
      }
    },
    "_draft": {
      "nbviewer_url": "https://gist.github.com/e1cbddaac4c242e5b701b841ae40fae9"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}