Python script to edit Google Sheets | Daily Python #7

This article is a tutorial on how to access and edit Google Sheets using Python and Google API.

This article is a part of Daily Python challenge that I have taken up for myself.
I will be writing short python articles daily.

First, we need to set up our Google API Console

Visit https://console.developers.google.com/ to set up the required APIs.

Snip of Google API Console Registration

Now let’s create a project for our application

Project Creation Google API Console

Search for Google Sheet API and Google Drive API, then enable these APIs

Snip of API Search

Go to Google Drive API, Create Credentials

Snip of Setting Up credentials for Google Drive

Snip of Setting Up credentials for Google Drive

Snip of the service account and key creation alert

The mentioned JSON file will be downloaded automatically. Place this file in the same folder as the python script (or your project). Rename this file to ‘client_key.json’

Snip of the JSON file

Copy the client email from the JSON file (as highlighted in the above image) and share your Google Sheet with this email address.

Snip of Google Sheet sharing settings

Install the following libraries

pip install gspread oauth2client

Let’s write the code to access and update the sheet

Import the required libraries

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint

Authorize the API by creating the ServiceAccountCredentials and passing the JSON file (client_key.json) downloaded earlier. Using ‘gspread’ we will authorize the API.

#Authorize the API
scope = [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/drive.file'
    ]
file_name = 'client_key.json'
creds = ServiceAccountCredentials.from_json_keyfile_name(file_name,scope)
client = gspread.authorize(creds)

API access permission requires authorization with at least one of the given URLs in the scope.

Now, let’s fetch the data from our sheet

Snip of the google sheet

#Fetch the sheet
sheet = client.open('Python Sheet').sheet1
python_sheet = sheet.get_all_records()
pp = pprint.PrettyPrinter()
pp.pprint(python_sheet)

get_all_records() fetches the entire sheet in JSON format.

pprint() provided by PrettyPrinter() beautifies the JSON response.

Snip of the output of the above code

We can also fetch any particular row, column, and even a cell.

#Fetch row
row = sheet.row_values(5)
print('\nFetched Row')
pp.pprint(row)
#Fetch column
col = sheet.col_values(2)
print('\nFetched Column')
pp.pprint(col)
#Fetch cell
cell = sheet.cell(3,3)
print('\nFetched Cell')
pp.pprint(cell.value)

Snip of the output of the above code

Now, we update the sheet

#Update Cell
cell = sheet.cell(3,3)
print('Cell Before Update: ',cell.value)
sheet.update_cell(3,3,'N')
cell = sheet.cell(3,3)
print('Cell After Update: ',cell.value)

Snip of the updated sheet

We can also insert a new row in the sheet

#Insert Row
row = ['7','https://daily-py.blogspot.com','Y']
index = 8
sheet.insert_row(row,index)

Snip of the google sheet after inserting a row

‘gspread’ has many more functions to play around with, which cannot be covered in this article. I hope this article was helpful and that it will help you to update some important sheets automatically.

For example, if you want to store stock market data from Google into this spreadsheet, then you can write a python script to fetch the data and update the sheet. Leave some claps if you liked the article.

Open to suggestions, do not hesitate to give your valuable feedback.

Ajinkya-Sonawane/Daily-Python

Login * Instagram