0A_obtaining_Google_drive_sheets_info.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9add3790-abab-4f39-aa24-8dedb2e79d5c",
   "metadata": {},
   "source": [
    "# Obtaining info from Google sheets\n",
    "\n",
    "In this notebook we are going to obtain info recopilated in google sheets of some manual analysis, which comes in handy for notebooks such as **7HM**. \n",
    "To do that we use the python API from Google sheets, and store the necessary information in variables that will be loaded in the respective notebooks.\n",
    "\n",
    "Therefore **load this notebook before loading any other notebooks in the analysis**.\n",
    "\n",
    "To load the notebook you may need to have a Google account. If you have any trouble in that process, contact me (@alexmascension in GitHub).\n",
    "Nonetheless, the parts of the notebooks that require these variables are not required for the analysis directly, but they come in handy to me to set information about some genes that, otherwise, would require a more lengthy searching process at the google sheets directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa57445e-dc8a-4fea-a0a2-410d1156c51d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb96c9b0-3fc1-4b47-9438-5a93abaa0855",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from __future__ import print_function\n",
    "\n",
    "import os.path\n",
    "\n",
    "from google.auth.transport.requests import Request\n",
    "from google.oauth2.credentials import Credentials\n",
    "from google_auth_oauthlib.flow import InstalledAppFlow\n",
    "from googleapiclient.discovery import build\n",
    "from googleapiclient.errors import HttpError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9aa2b09d-e06b-4cad-a51a-1097144b6fce",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55b9b3f6-9091-4e81-97e4-7271c6dec627",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# If modifying these scopes, delete the file token.json.\n",
    "SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']\n",
    "\n",
    "# The ID and range of a sample spreadsheet.\n",
    "SAMPLE_SPREADSHEET_ID = '1lfI6sgjEyg37BGL7VRMfW7KgwGKwX5QrCtnKYk1DXY4'\n",
    "SAMPLE_RANGE_NAME_HUMAN = 'Gene info (Human)!A:C'\n",
    "SAMPLE_RANGE_NAME_MOUSE = 'Gene info (Mouse)!A:C'\n",
    "JSON_FILE = \"data/TOKENS/client_secret_606804245862-te1c02pcqrhb9n298v5ja9as7r9qk5li.apps.googleusercontent.com.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce010ab1-b900-4d3f-9f93-6288606b1810",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# GET CREDS\n",
    "# time.\n",
    "if os.path.exists('token.json'):\n",
    "    creds = Credentials.from_authorized_user_file('token.json', SCOPES)\n",
    "# If there are no (valid) credentials available, let the user log in.\n",
    "if not creds or not creds.valid:\n",
    "    if creds and creds.expired and creds.refresh_token:\n",
    "        creds.refresh(Request())\n",
    "    else:\n",
    "        flow = InstalledAppFlow.from_client_secrets_file(\n",
    "            JSON_FILE, SCOPES)\n",
    "        creds = flow.run_local_server(port=0)\n",
    "    # Save the credentials for the next run\n",
    "    with open('token.json', 'w') as token:\n",
    "        token.write(creds.to_json())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaf2f51f-ec52-4b4a-8d4e-6b8740369b5d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "service = build('sheets', 'v4', credentials=creds)\n",
    "\n",
    "# Call the Sheets API\n",
    "sheet = service.spreadsheets()\n",
    "result_human = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,\n",
    "                            range=SAMPLE_RANGE_NAME_HUMAN).execute()\n",
    "\n",
    "result_mouse = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,\n",
    "                            range=SAMPLE_RANGE_NAME_MOUSE).execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b225d65-27d7-4b62-a3ba-94adc4d600d5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_human_genes_codes = pd.DataFrame({'gene': [i[0] for i in result_human['values'][1:]], 'code': [i[2] for i in result_human['values'][1:]]}).set_index('gene')\n",
    "df_human_genes_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9267539e-348f-47c0-b4ac-439995e2065d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_mouse_genes_codes = pd.DataFrame({'gene': [i[0] for i in result_mouse['values'][1:]], 'code': [i[2] for i in result_mouse['values'][1:]]}).set_index('gene')\n",
    "df_mouse_genes_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81014510-4097-495d-9de4-0d2019e01df3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%store df_human_genes_codes\n",
    "%store df_mouse_genes_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "221cc007-aacc-4d9e-b0dd-13fbdcd462e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.system('jupyter nbconvert --to html 0A_obtaining_Google_drive_sheets_info.ipynb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13b60ae5-9e25-485e-accf-a3310556b8ae",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:alex-base] *",
   "language": "python",
   "name": "conda-env-alex-base-py"
  },
  "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.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}