Google Sheets and Python

Google sheets and python

When you need to store and retrieve data on the go, I prefer using Google Sheets as it turns out to be very powerful than we think.

Think about the scenario where your team is updating the email addresses of the potential client in google sheets regularly. Now, to perform some automation (example: send a mail to all the prospects), you either need to download the sheets every time to ensure that you are working on the latest copy. Instead of doing that, you can easily integrate google sheets in your python code and retrieve the data which you can use to perform any sort of automation. Also, if your application is collecting some sort of information from user(example: a form that a user fills if they find your product useful), then you can also update the sheets with the new prospect’s details.

However, before integrating Google sheets to your Python code, you need to follow some steps in-order to get it successfully done. There are other tutorials for this but I found it to be little outdated. Hence, I am writing this article today.

Let’s start the tutorial in the form of steps:

  1. Go to Google Console, login with your google account and click on API & Services and then select Dashboard

2. Click on “Enable APIs and Services” on the top and search Google Drive API and Enable it.

3. Now, you’ll be redirected to Google Drive API dashboard where you need to add credentials which works as a API key. Click on “Create Credentials” on the right and fill the data as shown in the screenshot below. Then click on “Next”

4. Now, you’ll be taken to Service account details page where you need to provide Service account name. Give a suitable name and click on Create.

5. On the permission page, you’ll need to grant the Editor role. Click on Role and you’ll find “Editor” under “Basic”. Select it and then continue.

6. Click Done. You’ll be redirected back to Credentials Page where you’ll see your Service account created. Click on it and go to the “Keys” tab on the top. Here, you’ll have to Add key. So, click on the “Add Key” and select “Create New Key” option. Select JSON and click on Create.

7. A file will be downloaded which will hold the secrets that you’ll need later. Save it somewhere safe. You can use the file as the json file or copy and paste the required key in “.env” file of your python project.

8. IMPORTANT — Open the newly downloaded JSON file and you’ll see a key called “client_email”. Copy the value of the it and go to the Google sheets you’re trying to access, click on “Share” and share the google sheet with the email address given in the “client_email”.

9. Again, go to dashboard, click on “Enable APIs and Services”, search “Google Sheets API” and enable it. You don’t need to create another service account and add credentials again.

That’s it. You have completed the setup part. Now, to the actual coding.

Copy and paste the following code in a new file for testing purpose.

import os
import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from dotenv import load_dotenv
load_dotenv()def get_sheet():
# use creds to create a client to interact with the Google Sheet API
scopes = [“https://spreadsheets.google.com/feeds"]
json_creds = os.environ.get(“GOOGLE_SHEETS_CREDS_JSON”)
creds_dict = json.loads(json_creds)
creds_dict[“private_key”] = creds_dict[“private_key”].replace(“\\\\n”, “\n”)
creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scopes)
client = gspread.authorize(creds)
# Find the spreadsheet by URL
spreadsheet = client.open_by_url(“<YOUR_DOCUMENT_URL_HERE>")
sheet = spreadsheet.sheet1
return sheet

Don’t forget to install python-dotenv, gspread and oauth2client using pip.

Now, if you just call the get_sheet function, you’ll be be able to access the sheet that you’re trying to access.

sheet = get_sheet()
rows = sheet.get_all_records()

This is how, you can retrieve all the data in the sheet as a list of dictionaries.

You can do lot more things that retrieving the data from Google sheets.

Adding new data to Google Sheet

sheet = get_sheet()
rows = sheet.get_all_records()
row = ['1st column value', '2nd column value']
sheet.insert_row(row, len(rows)+2)

This will insert the new data in the bottom of the google sheet. Note that I am saying to insert in len(rows) + 2 because rows will have the headers of the sheets as keys of dictionary so if you have 10 lines of data present in the sheet, then it would be 11 lines including the headers and I want to insert the new data in the 12th line, that’s why I am adding 2.

Removing data from Google Sheet

sheet = get_sheet()
sheet.delete_row(5)

I am deleting the 5th row of data from the google sheet from the above code snippet.

There are lots of other cool things that you can try out according to your requirements. You can go through the official documentation of gspread to understand more. I used this in my Flask project and it works like a charm. You can try it out too. I hope you liked this article. Please share this with others if you find this helpful.

Shoutout to the Greg Baugues for documenting this in twillio blogs. This article is updated version of his work with slight modifications. Also, a huge shoutout to Anton Burnashev for creating gspread.

Senior Software Engineer | Python Developer | Avid Learner | Blockchain Enthusiast