gsheetapi.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7469ba66",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from df2gspread import df2gspread as d2g\n",
    "from oauth2client.service_account import ServiceAccountCredentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "25940215",
   "metadata": {},
   "outputs": [],
   "source": [
    "import gspread "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0b772b43",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The scope is always look like this so we did not need to change anything\n",
    "scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "41cfaf83",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Name of our Service Account Key\n",
    "google_key_file = 'service_key.json'\n",
    "\n",
    "\n",
    "credentials = ServiceAccountCredentials.from_json_keyfile_name(google_key_file, scope)\n",
    "\n",
    "gc = gspread.authorize(credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "926d538c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sr_sheet_key = \"1FJArBsbQ12_Gq_R31wAByVFJ7yg3svKk\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "6b64a75f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Opening the worksheet by using Worksheet ID\n",
    "workbook = gc.open_by_key(sr_sheet_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a65c1a13",
   "metadata": {},
   "outputs": [
    {
     "ename": "APIError",
     "evalue": "{'code': 400, 'message': 'This operation is not supported for this document', 'status': 'FAILED_PRECONDITION'}",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAPIError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/vj/g80ksz1978s35cytnn9ypypm0000gn/T/ipykernel_6119/499366340.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#Selecting which sheet to pulling the data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0msheet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mworkbook\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mworksheet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'adjudicated_valuesonly'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gspread/models.py\u001b[0m in \u001b[0;36mworksheet\u001b[0;34m(self, title)\u001b[0m\n\u001b[1;32m    372\u001b[0m         \u001b[0;34m>>\u001b[0m\u001b[0;34m>\u001b[0m \u001b[0mworksheet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msht\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mworksheet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Annual bonuses'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    373\u001b[0m         \"\"\"\n\u001b[0;32m--> 374\u001b[0;31m         \u001b[0msheet_data\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetch_sheet_metadata\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    375\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    376\u001b[0m             item = finditem(\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gspread/models.py\u001b[0m in \u001b[0;36mfetch_sheet_metadata\u001b[0;34m(self, params)\u001b[0m\n\u001b[1;32m    293\u001b[0m         \u001b[0murl\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mSPREADSHEET_URL\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mid\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    294\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 295\u001b[0;31m         \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclient\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrequest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'get'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    296\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    297\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/gspread/client.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, endpoint, params, data, json, files, headers)\u001b[0m\n\u001b[1;32m     74\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     75\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 76\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mAPIError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     77\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     78\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mlist_spreadsheet_files\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtitle\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAPIError\u001b[0m: {'code': 400, 'message': 'This operation is not supported for this document', 'status': 'FAILED_PRECONDITION'}"
     ]
    }
   ],
   "source": [
    "#Selecting which sheet to pulling the data\n",
    "sheet = workbook.worksheet('adjudicated_valuesonly')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89d06fcc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}