Motivation
Having discussed exporting Django model data as a .xlsx file using openpyxl, I then realized that many individuals using excel nowadays prefer the popular online reader provided by Google. Therefore, to have a complete tutorial, I decided to share how I would periodically update a Google spreadsheet data with data coming from my application's database.
Assumptions and Recommendations
To successfully follow this tutorial, I recommend you take a look at this and this youtube videos. They cover in detail the basics of what we will integrate with Django in this project. Also, checkout Python Quickstart, Google Sheets API, and Using OAuth 2.0 for Server to Server Applications as recommended by the videos.
Since we'll primarily be working on spinning up a celery task that periodically runs, it's therefore sensible to recommend going through celery documentation for django.
Source code
The entire source code for this article can be accessed via:
Sirneij
/
django_excel
Exporting Django model data as excel file (.xlsx) using openpyxl library and Google Spreadsheet API
django_excel
This repository accompanies this tutorial on dev.to. It has been deployed to Heroku and can be accessed live via this link.
NOTE: If you use Coingecko's API, when you use my code, CGSIRNEIJ, I get some commissions. That can be a good way to help me.
Run locally
It can be run locally by creating a virtual environment using any of venv, poetry, virtualenv, and pipenv. I used virtualenv while developing the app. Having created the virtual environment, activate it and install the project's dependencies by issuing the following command in your terminal:
(env) sirneij@pop-os ~/D/P/T/django_excel (main)> pip install -r requirements.txt
Then, migrate the database:
(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py migrate
Thereafter, run the project:
(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py run
Run tests locally
To run the tests, run the following in your terminal:
(env)…Aside this, the application is live and can be accessed via https://django-excel-export.herokuapp.com/.
Implementation
Step 1: Write the task
Let's get into it! Open up your core/tasks.py file and append this following:
# core -> tasks.py
...
from google.oauth2 import service_account
from googleapiclient.discovery import build
...
@shared_task
def populate_googlesheet_with_coins_data() -> None:
"""Populate Googlesheet with the coin data from the database."""
scopes = ['https://www.googleapis.com/auth/spreadsheets']
spreadsheet_id = config('SPREADSHEET_ID', default='1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc')
service_account_file = 'core/djangoexcel.json'
creds = None
creds = service_account.Credentials.from_service_account_file(service_account_file, scopes=scopes)
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
coin_queryset = Coins.objects.all().order_by('rank')
data: list[Any] = []
for coin in coin_queryset:
data.append(
[
coin.name,
f'{coin.symbol}'.upper(),
coin.rank,
str(currency(coin.current_price)),
str(currency(coin.price_change_within_24_hours)),
str(currency(coin.market_cap)),
str(coin.total_supply),
]
)
sheet.values().clear(spreadsheetId=spreadsheet_id, range='Coins!A2:G').execute()
sheet.values().append(
spreadsheetId=spreadsheet_id, range='Coins!A2:G2', valueInputOption='USER_ENTERED', body={'values': data}
).execute()
It basically does what the videos explained. We imported the required packages to interact with Google spreadsheet APIs, defined the scope of the API. In this case, we are limiting it to only spreadsheet. We then included the google spreadsheet ID we will be using. Your sheet ID is the second-to-the-last part of your sheet URL. For instance, if your sheet URL is https://docs.google.com/spreadsheets/d/1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc/edit#gid=0, the sheet ID is 1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc. Simple! After that, we provided our service account file which is important for authentication via OAuth 2.0. Ideally, this file shouldn't be made open for others to have access to it. It should be kept secretely. Then, we generated the credentials required for authentication by feeding into Credentials our service account file and the scope of the API we'll be working with. Thereafter, we built the service and initialized the sheet we've selected. We then made available the QuerySet of the data we want to populate the sheet with and defined an empty data list. It should be noted that the values attribute of Google spreadsheet API requires your data to be list of lists.
To populate this empty list with the required data, we looped through our QuerySet and appended the data appropritately. For this use case, all our data are constantly changing so I opted to first clear out previous data available on the sheet and then append new ones via the clear and append APIs respectively. Both take the range variable which is the title of your sheet, then an exclamation mark, !, then the range of our sheet's rows and columns. For the clear API, we passed 'Coins!A2:G' which picks our Coins sheet and transverses through the second row of the first column, A2, to the last column, G, downwards without restriction. For the update call, we didn't want the updates to be applied to the first row — our headers — but the rest of the sheet hence the 'Coins!A2:G2' range value. The other values are readily documented on the API documentation links provided.
Next, let's append the following to our CELERY_BEAT_SCHEDULE settings variable:
# django_excel -> settings.py
...
CELERY_BEAT_SCHEDULE: dict[str, dict[str, Any]] = {
...
'populate_googlesheet_with_coins_data': {
'task': 'core.tasks.populate_googlesheet_with_coins_data',
'schedule': crontab(minute='*/1'),
},
}
...
The task will be run every one minute based on that configuration.
Step 2: Test the Google Spreadsheet API integration
As our new normal, lets take the effort to write a test for our task. Locate your test_tasks.py file in core subdirectory of our tests directory and append the following:
# tests -> core -> test_tasks.py
...
from core.tasks import (
export_data_to_excel,
get_coins_data_from_coingecko_and_store,
populate_googlesheet_with_coins_data, # bring the task to scope
)
...
class CoinTasksTests(TestCase):
...
def test_populate_googlesheet_with_coins_data(self):
"""Test populate_googlesheet_with_coins_data."""
Coins.objects.create(
name='bitcoin', symbol='btc', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000
)
Coins.objects.create(
name='etherum', symbol='eth', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000
)
Coins.objects.create(
name='xrp', symbol='xrp', current_price=12000000, price_change_within_24_hours=500, market_cap=210000000
)
with patch('core.tasks.build') as mock_build:
with patch('core.tasks.service_account.Credentials') as mock_service_acount_credentials:
mock_service_acount_credentials.from_service_account_info.return_value = '123'
mock_build.return_value.spreadsheets.return_value.values.return_value.append.return_value.execute.return_value = {
'values': []
}
populate_googlesheet_with_coins_data()
mock_build.assert_called_once()
It's nothing much, we created some data and mocked both the build and Credentials APIs used in the task. This is to prevent network dependence of our tests. Then, run the tests via your terminal:
(virtualenv) sirneij@pop-os ~/D/P/T/django_excel (main)> py.test --disable-socket --nomigrations --reuse-db -W error::RuntimeWarning --cov=core --cov-report=html tests/
If everything goes well, you should see something like:
====================================================== test session starts ======================================================
platform linux -- Python 3.10.4, pytest-7.1.2, pluggy-1.0.0
django: settings: django_excel.settings (from ini)
rootdir: /home/sirneij/Documents/Projects/Tutorials/django_excel, configfile: pytest.ini
plugins: django-4.5.2, socket-0.5.1, cov-3.0.0
collected 7 items
tests/core/test_models.py . [ 14%]
tests/core/test_tasks.py ... [ 57%]
tests/core/test_views.py ... [100%]/home/sirneij/Documents/Projects/Tutorials/django_excel/virtualenv/lib/python3.10/site-packages/coverage/inorout.py:519: CoverageWarning: Module src was never imported. (module-not-imported)
self.warn(f"Module {pkg} was never imported.", slug="module-not-imported")
/home/sirneij/Documents/Projects/Tutorials/django_excel/virtualenv/lib/python3.10/site-packages/coverage/control.py:793: CoverageWarning: No data was collected. (no-data-collected)
self._warn("No data was collected.", slug="no-data-collected")
WARNING: Failed to generate report: No data to report.
/home/sirneij/Documents/Projects/Tutorials/django_excel/virtualenv/lib/python3.10/site-packages/pytest_cov/plugin.py:308: CovReportWarning: Failed to generate report: No data to report.
warnings.warn(CovReportWarning(message))
======================================================= warnings summary ========================================================
virtualenv/lib/python3.10/site-packages/kombu/utils/compat.py:82
/home/sirneij/Documents/Projects/Tutorials/django_excel/virtualenv/lib/python3.10/site-packages/kombu/utils/compat.py:82: DeprecationWarning: SelectableGroups dict interface is deprecated. Use select.
for ep in importlib_metadata.entry_points().get(namespace, [])
-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html
---------- coverage: platform linux, python 3.10.4-final-0 -----------
================================================= 7 passed, 1 warning in 22.20s =================================================
Voila! That's it for this series! Hope you enjoyed it and learnt something. See you in the next article.
Outro
Enjoyed this article? Consider contacting me for a job, something worthwhile or buying a coffee ☕. You can also connect with/follow me on LinkedIn. Also, it isn't bad if you help share it for wider coverage. I will appreciate...
Top comments (0)