Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Connect Jupyter Notebook with Google Sheet
{
"cells": [
{
"metadata": {
"trusted": true
},
"cell_type": "code",
"source": "# Import libraries \nimport pandas as pd\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n# For connect to google sheet\nimport gspread\nfrom oauth2client.service_account import ServiceAccountCredentials\nfrom df2gspread import df2gspread as d2g",
"execution_count": 19,
"outputs": []
},
{
"metadata": {
"trusted": true
},
"cell_type": "code",
"source": "# Configure the connection \nscope = ['https://spreadsheets.google.com/feeds']\n\n# Give the path to the Service Account Credential json file \ncredentials = ServiceAccountCredentials.from_json_keyfile_name('key/service_account_GS.json',\n scope\n )\n# Authorise your Notebook\ngc = gspread.authorize(credentials)\n\n# The sprad sheet ID, which can be taken from the link to the sheet\nspreadsheet_key = '1pwJDzIl1O8gQSHD8SekW6442rrLRgLVTYA90dQb7chg'",
"execution_count": 20,
"outputs": []
},
{
"metadata": {
"trusted": true
},
"cell_type": "code",
"source": "# Read some data from a csv file into a pandas dataframe\n# In this case, we read in the client IDs we got in the Blog \n#\"Get Google Analytics data to your Jupyter Notebook by using Analytics Reporting API\"\ndf = pd.read_csv('client_ids.csv',index_col = False)",
"execution_count": 21,
"outputs": []
},
{
"metadata": {
"trusted": true
},
"cell_type": "code",
"source": "df.head()",
"execution_count": 22,
"outputs": [
{
"output_type": "execute_result",
"execution_count": 22,
"data": {
"text/plain": " client_ID\n0 1.001033e+09\n1 1.001090e+09\n2 1.007582e+09\n3 1.010111e+09\n4 1.014999e+08",
"text/html": "<div>\n<style scoped>\n .dataframe tbody tr th:only-of-type {\n vertical-align: middle;\n }\n\n .dataframe tbody tr th {\n vertical-align: top;\n }\n\n .dataframe thead th {\n text-align: right;\n }\n</style>\n<table border=\"1\" class=\"dataframe\">\n <thead>\n <tr style=\"text-align: right;\">\n <th></th>\n <th>client_ID</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>1.001033e+09</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1.001090e+09</td>\n </tr>\n <tr>\n <th>2</th>\n <td>1.007582e+09</td>\n </tr>\n <tr>\n <th>3</th>\n <td>1.010111e+09</td>\n </tr>\n <tr>\n <th>4</th>\n <td>1.014999e+08</td>\n </tr>\n </tbody>\n</table>\n</div>"
},
"metadata": {}
}
]
},
{
"metadata": {
"trusted": true
},
"cell_type": "code",
"source": "# Set the sheet name you want to upload data to and the start cell where the upload data begins \nwks_name = 'Sheet1'\ncell_of_start_df = 'A2'\n# upload the dataframe of the clients we want to delete\nd2g.upload(df,\n spreadsheet_key,\n wks_name,\n credentials=credentials,\n col_names=False,\n row_names=False,\n start_cell = cell_of_start_df,\n clean=False)\nprint ('The sheet is updated successfully')",
"execution_count": 23,
"outputs": [
{
"output_type": "stream",
"text": "The sheet is updated successfully\n",
"name": "stdout"
}
]
}
],
"metadata": {
"kernelspec": {
"name": "python3",
"display_name": "Python 3",
"language": "python"
},
"language_info": {
"name": "python",
"version": "3.6.4",
"mimetype": "text/x-python",
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"pygments_lexer": "ipython3",
"nbconvert_exporter": "python",
"file_extension": ".py"
},
"latex_envs": {
"eqNumInitial": 1,
"eqLabelWithNumbers": true,
"current_citInitial": 1,
"cite_by": "apalike",
"bibliofile": "biblio.bib",
"LaTeX_envs_menu_present": true,
"labels_anchors": false,
"latex_user_defs": false,
"user_envs_cfg": false,
"report_style_numbering": false,
"autoclose": false,
"autocomplete": true,
"hotkeys": {
"equation": "Ctrl-E",
"itemize": "Ctrl-I"
}
},
"gist": {
"id": "",
"data": {
"description": "Connect Jupyter Notebook with Google Sheet",
"public": true
}
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment