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-gsheetsinstall 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 on your app one time, going through the standard consent flow./gsheets/authorize
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 thesheet_id_fieldto 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:
- 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.
- Add a signal receiver to listen for
sheet_row_processedevents.
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):
passsignals.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.
