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.
Now let’s create a project for our application
Search for Google Sheet API and Google Drive API, then enable these APIs
Go to Google Drive API, Create Credentials
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’
Copy the client email from the JSON file (as highlighted in the above image) and share your Google Sheet with this email address.
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
#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.
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)
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)
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)
‘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.