working with google sheets and django on laptop

Introducing Django-GSheets, an Easy Way to Sync to and from Google Sheets

  • Devops

The django-gsheets Django app makes two-way data sync from models to Google Sheets simple.

by Bobby Steinbach 23 April 2020

Django ships with a great admin interface, it’s one of the top reasons the framework is chosen. Often though, the need (or “need”) arises to view and manipulate model data in a spreadsheet instead of the web admin. Rather than go through the traditional ETL process of exporting this data to a CSV to be loaded into a Google Sheet (or visa versa), django-gsheets makes syncing data between Google Sheets and your Django app as simple as executing: python manage.py syncgsheets

Two-way data sync from Django to Google Sheets

Here are some common reasons you might want two-way sync in your app.

  • performing running audits of data quality in a table or set of tables
  • providing an interface for external users to upload structured data (e.g. think mechanical turk)
  • giving non-technical users an efficient way to scrub data in bulk

Using django-gsheets in your project

Getting started with django-gsheets should be a familiar task for the Django developer. We start by installing the necessary dependencies then add some configuration to our | settings.py | to integrate the app in our project. Note: this guide is basically a long-form version of the repo README, so if you’re just itching to get started you should probably stop reading and start writing!

Step 1: Install django-gsheets

pip install django-gsheets

install django-gsheets from PyPi

Step 2: Add django-gsheets to INSTALLED_APPS and update app settings

INSTALLED_APPS = [
    ...
    'gsheets',
    ...
]

...

GSHEETS = {
    'CLIENT_SECRETS': '<PATH TO DOWNLOADED CREDENTIALS>'
}

settings.py

After adding, be sure to run: python manage.py migrate

Step 3: Add django-gsheets URLs

urlpatterns = [
    ...
    path('', include('gsheets.urls')),
]

urls.py

Step 4: Use ngrok or similar to expose your dev environment

Google OAuth2 only support HTTPS redirects, which shouldn’t be a problem in a production environment. However, for development and testing you’ll need something like ngrok to expose localhost over https. If you want to just test directly in a HTTPS enabled environment, skip this step.

Step 5: Create a Google Cloud Platform project and create credentials

Google has a pretty good guide for this step (you only need to do Step 1). The important part here is to set the project redirect URI to an HTTPS location running your app (see Step 4 above if testing on localhost).

Step 6: Load Access Credentials

In order to authenticate future requests to the sheets API, the app needs to load an access token from Google. The django-gsheets app makes this easy by handling the token negotiation handshake and future token refreshes. All you’ll need to do is visit /gsheets/authorize on your app one time, going through the standard consent flow.

Add django-gsheets mixins on model(s) to sync

To add two-way syncing from Django to Google Sheets, we simply need to add SheetSyncableMixin to the relevant models along with – at a minimum – the spreadsheet_id which the model will sync to/from. For example, consider the following representing a person and the cars they own.

from django.db import models
from gsheets import mixins
from uuid import uuid4


class Person(mixins.SheetSyncableMixin, models.Model):
    spreadsheet_id = '18F_HLftNtaouHgA3fmfT2M1Va9oO-YWTBw2EDsuz8V4'
    model_id_field = 'guid'

    guid = models.CharField(primary_key=True, max_length=255, default=uuid4)

    first_name = models.CharField(max_length=127)
    last_name = models.CharField(max_length=127)
    email = models.CharField(max_length=127, null=True, blank=True, default=None)
    phone = models.CharField(max_length=127, null=True, blank=True, default=None)

    def __str__(self):
        return f'{self.first_name} {self.last_name} // {self.email} ({self.guid})'


class Car(mixins.SheetSyncableMixin, models.Model):
    spreadsheet_id = '18F_HLftNtaouHgA3fmfT2M1Va9oO-YWTBw2EDsuz8V4'
    sheet_name = 'Sheet2'

    owner = models.ForeignKey(Person, related_name='cars', on_delete=models.CASCADE, null=True, blank=True, default=None)

    brand = models.CharField(max_length=127)
    color = models.CharField(max_length=127)

    def __str__(self):
        return f'{self.color} {self.brand} // Owned by {self.owner} ({self.id})'

You’ll notice that in the above, Person also defines a model_id_field. This value defaults to 'id' but can be overridden to define a different ID field that uniquely identifies model instances.

Considerations for the synced Google Sheet

There aren’t too many requirements for the synced Google Sheet besides the following:

  • In a push context, the sheet must contain column headers with field names exactly matching model fields. In a pull context, if the field doesn’t match it can still be parsed in a post-processing signal handler (see below).
  • The sheet must contain a header column called Django GUID. This field stores the ID of the Django model instance and is used to prevent duplicates and keep updates in sync. You may modify the name of this column by setting the sheet_id_field to a new value.

Run sync sheets management command

django-gsheets ships with a magic command to sync all models using any of the mixins shipped with django-gsheets. To execute, run python manage.py syncgsheets. Hint: Putting this on cron is a good idea!

One-way syncing Django to Google Sheets or Google Sheets to Django

Sometimes a two-way sync is undesired. Perhaps you just want to provide business user’s with model data for analysis (push only). Conversely, maybe you just want a bulk editing interface for model data without sending any model data to Sheets (pull only). With django-gsheets, you can use the SheetPullableMixin and SheetPushableMixin separately to granularly control whether a model is pullable or pushable, respectively. For example, let’s update our example above to make the Person model pullable only (Google Sheets to Django).

from django.db import models
from gsheets import mixins
from uuid import uuid4

class Person(mixins.SheetPullableMixin, models.Model):
    spreadsheet_id = '18F_HLftNtaouHgA3fmfT2M1Va9oO-YWTBw2EDsuz8V4'
    model_id_field = 'guid'

    guid = models.CharField(primary_key=True, max_length=255, default=uuid4)

    first_name = models.CharField(max_length=127)
    last_name = models.CharField(max_length=127)
    email = models.CharField(max_length=127, null=True, blank=True, default=None)
    phone = models.CharField(max_length=127, null=True, blank=True, default=None)

    def __str__(self):
        return f'{self.first_name} {self.last_name} // {self.email} ({self.guid})'

easy

Post-processing inbound rows

Related fields are hard and django-gsheets doesn’t (yet) have any support for them. However, the app does fire a signal immediately after a row has been pulled from the Google Sheet and an instance created or updated which gives us a hook to add some related field functionality. As an example, let’s operate off our running example and imagine we want to tie Car instances to their owner after pulling from the spreadsheet. To do so, we’ll do two things:

  1. Add columns to uniquely identify the owner in the Google Sheet. Of course this could just be an ID, but to make things more interesting we’ll use the owner’s first and last name.
  2. Add a signal receiver to listen for sheet_row_processed events.

Assuming the columns added to the sheet are named owner_first_name and owner_last_name, our signal handler will be the following:

from django.dispatch import receiver
from django.core.exceptions import ObjectDoesNotExist
from gsheets.signals import sheet_row_processed
from .models import Car, Person


@receiver(sheet_row_processed, sender=Car)
def tie_car_to_owner(instance=None, created=None, row_data=None, **kwargs):
    try:
        instance.owner = Person.objects.get(first_name__iexact=row_data['owner_first_name'], last_name__iexact=row_data['owner_last_name'])
        instance.save()
    except (ObjectDoesNotExist, KeyError):
        pass

signals.py

Work in progress

django-gsheets is very much a work in progress! If you’d like to help in development, we’d appreciate any and all contributions.

Other News plus icon Back to all
Useful Resources plus icon Get your freebies
legal marketing resources

Check out our Free Resources for marketing tips and tricks.

There’s no better time to transform your firm than today. Take the first step by downloading our free marketing checklists and ebooks.

We’ll never turn our backs on you again

Don't be shy. Fill out the form and we'll be in touch shortly

Hidden
Hidden
Hidden
Hidden