In this article, we will go through the whole process -step by step- of the interface between Python and Google sheets for reading and writing data using a simple systematic way.

Step 1: Create Google Service account

The first thing that you should do to interface with Google APIs in general is to create a service account using your google account, to be able to use Google services for developing. To do that, the following steps should followed:
1- Go to the Google APIs Console
2- Create a new project.
3- Click Enable API. Search for and enable the Google Drive API.
4- Create credentials for a Web Server to access Application Data.
5- Name the service account and grant it a Project Role of Editor.
6- Download the JSON file.
7- (Optional) You can copy that JSON file to your code directory or any other directory and rename it to client_secret.json

The process of creating Google service account

Step 2: Share the sheet with the service account

In this step you should share the sheet that you would like to either read from or write to with the service account; XXXX@XXXX.iam.gserviceaccount.com . That means that you give access to your service account to edit that sheet.

Step 3: Read Function

For the reading part, we will use ‘gspread’ library then convert it to dataframe using ‘pandas’ as follows:

import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
import gspreadpath = '/nfs/home/data/'
scopes = ['https://www.googleapis.com/auth/spreadsheets', 
          "https://www.googleapis.com/auth/drive.file", 
          "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name(path+'client_secret.json', scopes)
client = gspread.authorize(creds)

def read_googlesheet(sheet_name, sheet_title):
    
    sh = client.open(sheet_name)  # open the spreadsheet
    wks = sh.worksheet(sheet_title)  # choose the sheet name
    #Convert table data into a dataframe
    df = pd.DataFrame(table[1:], columns=table[0]) 
    
    return df

So just 3 lines of code enable us to read from a Google Sheet.

Example:
So now to test the previous reading function, all we need to do is just passing the sheet name and the sheet title. Consider the following screenshot of a sheet:

Therefore, the usage of the previous function in the main code will be:

df = read_googlesheet('Python_Googlesheets','Sheet1')

Step 4 Write Function

For the writing part, ‘df2gspread’ python’s library will be used, and the write function is as follows:

import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
from df2gspread import df2gspread as d2g# The path of client_secret json file that you have downloaded
path = '/nfs/home/data/'
 
scopes = ['https://www.googleapis.com/auth/spreadsheets', 
          "https://www.googleapis.com/auth/drive.file", 
          "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name(path+'client_secret.json', scopes)
def write_googlesheet(df,spreadsheet_key,sheet_title,starting_cell,overwrite):
    d2g.upload(df,
           spreadsheet_key,
           sheet_title,
           credentials=creds,
           col_names=overwrite,
           row_names=True,
           start_cell = starting_cell,
           clean=overwrite)

Where, you have to pass 5 parameters to the functions; dataframe (df), speadsheet key ID (will be shown in the following example), sheet title, the cell number that you would like to start writing from (normally it should be A1), and finally ‘overwrite’ parameter, where it should be True for overwriting and False for appending. Important note for appending, make sure that the column that you will start writing into (column A by default) is empty as it will be index column.

Note, if you are using both functions together, don’t duplicate your imports to make your code looks better 😉.

Overwriting Example:
Let’s suppose that we have the following sheet and we would like to write in it a certain data. The information that we need from that sheet is highlight in the screenshot; spreadsheet key ID (is got from the URL of the sheet), and sheet title.

So, let’s create a demo dataframe and save it to that sheet:

age = [24,25,26,27]
names = ['Looka','John','Messi','Sameh']
df = pd.DataFrame(names,columns=['name'])
df['age'] = agewrite_googlesheet(df,'1N26QwWaDowYwl-c8HAsqIc3dZ0RMTc78JOqiYZunsk4','Sheet1','A1',True)

Appending Example:
Now the sheet after writing in the previous example (overwriting example) looks like that:

So, to append new data (set overwrite to False), and we should start writing from A6 cell.

age = [28,29,30,31]
names = ['Ronaldo','Salah','Mane','Alex']
df = pd.DataFrame(names,columns=['name'])
df['age'] = agewrite_googlesheet(df,'1N26QwWaDowYwl-c8HAsqIc3dZ0RMTc78JOqiYZunsk4','Sheet1','A6',False)

Then the final output is as follows:

Finally, I hope that the article was helpful for you, if you like my content and want to support me you can buy me a coffee: https://www.buymeacoffee.com/sambadie