TheLazy.Dev
Portfolio GitHub

Blog

python |webdev |tutorial |django

Django and Google Spreadsheet API: Using Django model data to update spreadsheet

sirneij

John Owolabi Idogun

Posted on May 23, 2022

Django and Google Spreadsheet API: Using Django model data to update spreadsheet

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:

GitHub logo Sirneij / django_excel

Exporting Django model data as excel file (.xlsx) using openpyxl library and Google Spreadsheet API

django_excel

main Issues Forks Stars License

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
Enter fullscreen mode Exit fullscreen mode

Then, migrate the database:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py migrate
Enter fullscreen mode Exit fullscreen mode

Thereafter, run the project:

(env) sirneij@pop-os ~/D/P/T/django_excel (main)> python manage.py run
Enter fullscreen mode Exit fullscreen mode

Run tests locally

To run the tests, run the following in your terminal:

(env)
…
Enter fullscreen mode Exit fullscreen mode
View on GitHub

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()
Enter fullscreen mode Exit fullscreen mode

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'),
    },
}
...
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode

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 =================================================
Enter fullscreen mode Exit fullscreen mode

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...

💖 💪 🙅 🚩
sirneij
John Owolabi Idogun

Posted on May 23, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Django create your first web application 😀
django Django create your first web application 😀

October 14, 2022

Django and Google Spreadsheet API: Using Django model data to update spreadsheet
python Django and Google Spreadsheet API: Using Django model data to update spreadsheet

May 23, 2022

Django Nedir? Nasıl Kurulur?
django Django Nedir? Nasıl Kurulur?

January 21, 2022

Django REST API - CRUD Tutorial
django Django REST API - CRUD Tutorial

October 12, 2020

Django & React Tutorial For Beginners #2 : Django Commands & Admin Panel
python Django & React Tutorial For Beginners #2 : Django Commands & Admin Panel

October 6, 2020