Using Python 🐍 with Google Sheets as a database

Imagine you want to build an internal progamm with Python and need a small database. Why not use Google Sheets? In this tutorial I want to show you how to use Google Sheets in Python 3.

Requirements

For this tutorial you need * Python 3 * Google API credentials & active Drive API * A spreadsheet

Difficulty

Let's say it's Intermediate.

Programming

Installation

First of all we have to install some dependencies.

$ pip install gspread oauth2client

After this quick installation we have to create a new file. Let's call it grocery-list.py. Insert a quick Python barebone into it and give it a try with python grocery-list.py.

#!/usr/bin/python3

def main():
  print('Hello world!')

if __name__ == '__main__':
  main()

Create a client

First be sure that you've shared your spreadsheet with the client_email you find in your Google API credential JSON. Rename this JSON into client-secret.json and modify your script.

#!/usr/bin/python3
import gspread
import os.path
import sys
from oauth2client.service_account import ServiceAccountCredentials

client_secret = 'client-secret.json'

def main():
  if not os.path.isfile(client_secret):
    print('Client secret not found. Create one here: https://console.developers.google.com/apis')
    sys.exit(1)

  client = get_client()

def get_client():
  scope = ['https://spreadsheets.google.com/feeds']
  creds = ServiceAccountCredentials.from_json_keyfile_name(client_secret, scope)
  client = gspread.authorize(creds)

  return client

if __name__ == '__main__':
  main()

At this point we have successfully created a client for the Google Drive API. Furthermore we have ensured that our client-secret.json does exist, before we try to use it.

Fetch all rows

It's time to get some data and pretty print it into the console.

#!/usr/bin/python3
# ...
import pprint

def main():
  # ...

  client = get_client()
  sheet = client.open('grocery_list').sheet1
  results = get_all(sheet)
  pp = pprint.PrettyPrinter()
  pp.pprint(results)

def get_all(sheet):
  results = sheet.get_all_records()
  return results

# ...

If we did it all right it should look something like this:

Append a row

Now we want to write a new row as last element into the spreadsheet.

#!/usr/bin/python3
# ...

def main():
  # ...

  last_id = results[-1].get('id')
  insert_new(sheet, [(int(last_id) + 1), 1, 'food', 'Mango'], len(results) + 2)

def insert_new(sheet, row, index):
  sheet.insert_row(row, index)

# ...

Execute the script with python grocery-list.py and open the spreadsheet in your browser. It should look like this:


À voilà! A simple database like structure using Python and Google Sheets.

With that in mind, happy coding.

(() => {
  const colors = [
    '001f3f', '0074d9', '7fdbff', '39cccc',
    '3d9970', '2ecc40', '01ff70', 'ffdc00',
    'ff851b', 'ff4136', '85144b', 'f012be',
  ];
  const contents = ['%cI', '%c❤', '%cweb', '%cdev'];
  const options = Array.from(
    new Array(contents.length),
    () => `
      color:#${colors[Math.floor(Math.random() * colors.length)]};
      font-size:64px;
    `
  );
  console.log.apply(console, [contents.join('')].concat(options));
})();

Posted on Utopian.io - Rewarding Open Source Contributors