Working on Google Sheets with Python

Why?

Google sheets are widely used due to various reasons such as being free of charge and allowing multiple people to work on them. But if you want to document each move for your future self and/or for your colleagues, connecting to Google sheets via Python can be a good idea. The benefit of having such documentation is that your work will be reproducible, and if you explain what you do well enough in the code, it will also be understandable for anyone looking at your work.

Installing and importing the necessary libraries

We will use gspread, gspread_dataframe, pandas libraries (Click on the libraries to visit their documentation if you want to find out more about them) for working on Google sheets. If you do not have these libraries in your system, then install them in your terminal by writing:

pip3 install gspread 
pip3 install gspread_dataframe
pip3 install pandas

After installing the required libraries, we should import them in whichever editor (I mostly prefer Jupyter Notebook unless I am working on a large project) that we are using.

import pandas as pd
import gspread
import gspread_dataframe as gd

API connection

You will need to save your credentials to a file. If you do not already have that file, here is a good source explaining the ways to get the API credentials from Google.

Create a data frame in the Google sheet

I personally liked gspread library for working on Google sheets. (However, there are also other options such as pygsheets for such purposes.) Below, we first connect to our Google sheet through our credential file, then open it by google sheet URL, and then we create a data frame in Sheet3.

gc = gspread.service_account(filename='path to the file where you saved your API credentials')
sh = gc.open_by_url('The complete url of your google sheet')
ws = sh.worksheet('Sheet3')
data = [['A',11],['B',13],['C',14]]
df = pd.DataFrame(data,columns=['Name','Age'])
gd.set_with_dataframe(ws, df)

Read the data frame with Pandas

With the code below, we can see/read our data frame.

df = pd.DataFrame(ws.get_all_records())
df.head()

Modifying the data frame —Adding a column

From this point onwards, you can do anything that you do with Pandas in the google sheet. I will exemplify adding and dropping a column (named Test) into the data frame below.

df['Test'] = [1,2,3]

Dropping a column

df = df.drop(columns = ['Test'])

Exporting the data frame to google sheets

So far, we were modifying our data frame by adding or dropping a column, however, we need to export these modifications to the google sheet (if we want to see these changes in there). We can do that by using the code below. The number in the last part of your google spreadsheet URL represents the id of that document. So, you will need to put the id of your own document into the value part below.

export_data_sheet = sheet3.worksheet(property='id', value='0')
export_data_sheet.set_dataframe(df, start=(1, 1), extend=True, copy_head=True, nan='')

Adding a worksheet

If we want to add a worksheet named Testsheet, with 4 rows and 2 columns, the code below will work.

worksheet = sh.add_worksheet(title="Testsheet", rows="4", cols="2")

Deleting a worksheet

To delete the worksheet we created:

sh.del_worksheet(worksheet)

Have a productive workday!