How to add Google sheets as your Django database in 4 minutes 📊

Adding Google sheets to Django as a basic database is extremely fast to do (and free).

This is great for building a quick product. You avoid the extra effort of setting up a full database, and get your idea out faster.

I’ll show you how to add Google sheets to your Django app as a lightweight database. We’ll be using gspread to access your Google sheets.

There are 5 steps. Let’s start 🚀

Here's what the frontend of our finished Django app with a Google Sheets database will look like:

And here's a full (optional) video following the guide (featuring me 🙂):

Photo of Tom Dekan giving a talk at the London Django Meetup 🇬🇧in November — Update: Here's me presenting this guide at the Django Meetup in London 🇬🇧 in November

1. Get your Google API credentials

This is fast to do.

Go to Google Developers Console and create a new project.
In the box labeled “Search for APIs and Services”, search for “Google Drive API” and enable it.
In the box labeled “Search for APIs and Services”, search for “Google Sheets API” and enable it.
Go to “APIs & Services > Credentials” and choose “Create credentials > Service account”.
Complete the form
Click “Create” and “Done”.
Press “Manage service accounts” above Service Accounts.
Press on ⋮ near recently created service account (it's currently under "Actions") and select “Manage keys” and then click on “ADD KEY > Create new key”.
Select JSON key type and press “Create”.
Download that key

2. Setup your Django app

Create a new virtual env
install Django and the packages that we'll need

pip install django python-dotenv gspread

Create a new Django project named core and app named sim

django-admin startproject core .
python manage.py startapp sim

Include your new app in your INSTALLED_APPS in settings.py.

# settings.py

INSTALLED_APPS = [
  ...
  'sim',
  ...
]

3. Add your Google credentials to Django

Create core/.env
Populate the below format with credentials from your download credentials json file and paste it into your .env file.

TYPE=service_account
PROJECT_ID=<YOUR_VALUE>
PRIVATE_KEY_ID=<YOUR_VALUE>
PRIVATE_KEY="<YOUR_VALUE>"
CLIENT_EMAIL=<YOUR_VALUE>
CLIENT_ID=<YOUR_VALUE>
AUTH_URI=<YOUR_VALUE>
TOKEN_URI=<YOUR_VALUE>
AUTH_PROVIDER_X509_CERT_URL=<YOUR_VALUE>
CLIENT_X509_CERT_URL=<YOUR_VALUE>
UNIVERSE_DOMAIN=googleapis.com

Load credentials into environment

Add these lines to the top of your core/settings.py to load the AWS keys from your .env file into your Django app as environment variables when you run your Django server.

from pathlib import Path
from dotenv import load_dotenv

load_dotenv()

4. Access your Google Sheets data

Create a services file at sim/services.py containing the below code:

import os
import gspread
from typing import List
from django.conf import settings

def initialize_gspread() -> gspread.client.Client:
  """
  Initialize a gspread client with the given credentials.
  """
  return gspread.service_account_from_dict(get_credentials())  # Note: we could move this to settings to do this once.

def get_credentials() -> dict:
  """
  Return gspread credentials.
  """
  return {
    "type": os.getenv("TYPE"),
    "project_id": os.getenv("PROJECT_ID"),
    "private_key_id": os.getenv("PRIVATE_KEY_ID"),
    "private_key": os.getenv("PRIVATE_KEY"),
    "client_email": os.getenv("CLIENT_EMAIL"),
    "client_id": os.getenv("CLIENT_ID"),
    "auth_uri": os.getenv("AUTH_URI"),
    "token_uri": os.getenv("TOKEN_URI"),
    "auth_provider_x509_cert_url": os.getenv("AUTH_PROVIDER_X509_CERT_URL"),
    "client_x509_cert_url": os.getenv("CLIENT_X509_CERT_URL"),
    "universe_domain": os.getenv("UNIVERSE_DOMAIN")
  }

def get_all_rows(doc_name: str, sheet_name: str = None) -> List[dict]:
  """
  Fetches all rows from a given Google Sheet worksheet.
  """
  sh = settings.GSPREAD_CLIENT.open(doc_name)
  worksheet = sh.worksheet[sheet_name] if sheet_name else sh.get_worksheet(0)
  return worksheet.get_all_records()

Add your views

In sim/views.py add the below code:

from django.shortcuts import render
from .services import get_all_rows

def photo_wall(request):
  photos = get_all_rows("Test sheet")
  return render(request, 'photo_wall.html', {'photos': photos})

Start your Gspread client when your Django server starts

Add your gspread client to settings.py:
Import our initialize method below your existing imports.

# settings.py
import os
from pathlib import Path
from dotenv import load_dotenv
from sim.services import initialize_gspread

Then add the below line at the bottom of settings.py to initialize the gspread_client

GSPREAD_CLIENT = initialize_gspread()  # Starting the gspread client when our server starts speeds things up; it avoids re-authenticating on each request

I’ve added this to show you how we can add longer processes to our Django setup to speed up our app.

Initializing the gspread client when our Django server starts means that setting up our server takes more time. But this speeds each page load by 30% (on my computer), compared to creating a new gspread client every time we fetch data from our spreadsheet. This is because we avoid re-authenticating each time we request data.

Add your urls

Update your core/urls.py:

from django.contrib import admin
from django.urls import include, path

urlpatterns = [
  path('admin/', admin.site.urls),
  path('', include('sim.urls')),
]

Create urls.py in the sim directory containing:

from django.urls import path
from . import views

urlpatterns = [
  path('', views.photo_wall, name='photo-wall'),
]

5. Create a frontend to show your photos

Create your template

Create templates dir in sim
Create photo_wall.html in sim/templates
Add this HTML to photo_wall.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Photo Grid</title>
  <style>
    /* Navbar styles */
    nav {
      background-color: #333;
      color: white;
      padding: 12px;
      text-align: center;
    }
    .logo {
      height: 24px;
    }
    /* Container and grid styles */
    .container {
      max-width: 1200px;
      margin: auto;
      padding: 20px;
    }
    .grid {
      display: grid;
      grid-template-columns: repeat(3, 1fr);
      gap: 16px;
    }
    /* Photo card styles */
    .photo-card {
      height: 300px;
      position: relative;
      overflow: hidden;
      border-radius: 12px;
      box-shadow: 0 4px 8px rgba(0,0,0,0.1);
    }
    .photo-card img {
      object-fit: cover;
      width: 100%;
      height: 100%;
    }
    /* Photo details overlay */
    .photo-details {
      position: absolute;
      bottom: 0;
      left: 0;
      right: 0;
      background-color: rgba(0,0,0,0.6);
      color: white;
      padding: 12px;
      opacity: 0;
      transition: opacity 0.3s;
    }
    .photo-card:hover .photo-details {
      opacity: 1;
    }
  </style>
</head>
<body>

<!-- Photo grid -->
<div class="container">
  <div class="grid">
    {% for photo in photos %}
    <div class="photo-card">
      <a href="{{ photo.url }}" target="_blank">
        <img src="{{ photo.url }}" alt="{{ photo.title }}">
        <div class="photo-details">
          <h4>{{ photo.title }}</h4>
          <p>{{ photo.description }}</p>
        </div>
      </a>
    </div>
    {% endfor %}
  </div>
</div>

</body>
</html>

Add data to your Google sheet database

Add data to your Google sheet that you want to render into your database. Include a column called title, description, and url (with an url to an image)

** Note: you must add at least 2 rows of data for get_all_records to work**. This is because it needs a row column names, which will be the first row of your sheet.

Here’s the data I added to my Google sheet. Add whatever data and images you’d like. (You can copy and paste this into your Google sheet; select it; and then click split into columns)

title,description,url
Andya,A good friend,https://source.unsplash.com/90FYTPYDDWM
Robatz,This was after our ball game last saturday,https://source.unsplash.com/6yU9MxrdWpA
Berto,I took this last autumn in Greece. What a great day!,https://source.unsplash.com/y3kC_7Qhmjk
Paweli,A close friend. We went and got popcorn afterwards,https://source.unsplash.com/HE7Bln39zDE
Elaso,Spending time with a dear friend is always a treasure.,https://source.unsplash.com/K84vnnzxmTQ
Mickin,Last Saturday, we had a fantastic time playing a game of ball together.,https://source.unsplash.com/F5_EDAt_BVo
Ragif,Post-Saturday game excitement.,https://source.unsplash.com/XHpgMMiOvuM
Aleyo,After a heavy workout together. #gains,https://source.unsplash.com/7GQHQwbuzfQ
Brinky,My grandfather’s old work colleague,https://source.unsplash.com/FlJbi-4DuFc

Share your Google sheet with the email address in your CLIENT_EMAIL in your .env file. This is critical.

6. Run your server to see your results

Run your Django server

python manage.py runserver

Visit http://127.0.0.1:8000 (or whatever local url you're using) to view your data.

Congratulations - you’ve added Google sheets as a super light database 🎉

You could expand this app to allow a user of your app to add more photos (a create) and to edit existing photos (an update).

You could build a portfolio of your images, or build the next Instagram. It would be cool to add videos as well, as well as letting the user upload videos.

To let the user upload photos or videos, you could use: 3 steps to upload files properly with Django (and HTMX).

Build your Django frontend even faster

I want to release high-quality products as soon as possible. Probably like you, I want to make my Django product ideas become reality as soon as possible.

That's why I built Photon Designer - an entirely visual editor for building Django frontend at the speed that light hits your eyes. Photon Designer outputs neat, clean Django templates.