Django and Google Spreadsheet API: Using Django model data to update spreadsheet
Tags: python, webdev, tutorial, django
Published: 2022-05-23 11:26:09

## 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][1] and [this][2] youtube videos. They cover in detail the basics of what we will integrate with Django in this project. Also, checkout [Python Quickstart][3], [Google Sheets API][4], and [Using OAuth 2.0 for Server to Server Applications][5] 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][6].

## Source code

The entire source code for this article can be accessed via:

{% github Sirneij/django_excel %}

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:

```python
# 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 &mdash; our headers &mdash; 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:

```python
# 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:

```python
# 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:

```bash
(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:

```bash
====================================================== 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 ☕](mailto:sirneij@gmail.com). You can also connect with/follow me on [LinkedIn](https://www.linkedin.com/in/idogun-john-nelson/). Also, it isn't bad if you help share it for wider coverage. I will appreciate...

[1]: https://www.youtube.com/watch?v=4ssigWmExak 'Google Sheets - Python API, Read & Write Data'
[2]: https://www.youtube.com/watch?v=OZDGVTmQ45Q 'Python - Google Sheets API, Append (Add) Rows & Clear Data'
[3]: https://developers.google.com/sheets/api/quickstart/python 'Googlesheet API: Python Quickstart'
[4]: https://developers.google.com/sheets/api/reference/rest 'Google Sheets API'
[5]: https://developers.google.com/identity/protocols/oauth2/service-account#python 'Using OAuth 2.0 for Server to Server Applications'
[6]: https://docs.celeryq.dev/en/stable/django/first-steps-with-django.html 'First steps with Django'