Connect Jupyter Notebook with Google Sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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