DEV Community

Cover image for Django and Google Spreadsheet API: Automatically creating and deleting multiple tabs or sheets
John Idogun
John Idogun

Posted on • Updated on

Django and Google Spreadsheet API: Automatically creating and deleting multiple tabs or sheets

Motivation

Following the previous article in this series where we successfully populated a Google spreadsheet with our model's data, there came a potential problem or feature.
Consider a hypothentical (maybe) situation where your data has millions of data points and as we continuously append data to the sheet, our browser may crash or not respond due to data volume. In the same vain, consider another situation where you need to create multiple tabs or sheets that contain different instances of the data being passed and also delete older sheets that are no longer needed automatically to avoid crashing the browser. These are the problems/features this article will address/implement.

Assumptions and Recommendations

It is assumed and recommended that you have gone through previous articles in this series and checked-out Python Quickstart, Google Sheets API, and Using OAuth 2.0 for Server to Server Applications.

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.

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) sirneij@pop-os ~/D/P/T/django_excel (main)> py.test --nomigrations --reuse-db -W error::RuntimeWarning --cov=core --cov-report=html tests/
Enter fullscreen mode Exit fullscreen mode



Aside this, the application is live and can be accessed via https://django-excel-export.herokuapp.com/.

Implementation

The only files we'll be altering significantly in this article are the core/tasks.py and tests/core/test_tasks.py. The former holds the business logic for our implementation whereas we'll be testing our logic in the latter. We'll also change some small settings variables. Let's get into it.

Step 1: Alter populate_googlesheet_with_coins_data function in core/tasks.py

The logic implemented in the previous article in populate_googlesheet_with_coins_data will be modified to accommodate the new reasoning. Make the function look like the following:

# django_excel/core/tasks.py
...

@shared_task
def populate_googlesheet_with_coins_data() -> None:
    """Populate Googlesheet with the coin data from the database."""
    response = requests.get(settings.GOOGLE_API_SERVICE_KEY_URL)
    with open('core/djangoexcel.json', 'wb') as file:
        file.write(response.content)
    service_account_file = 'core/djangoexcel.json'
    creds = service_account.Credentials.from_service_account_file(
        service_account_file, scopes=settings.GOOGLE_API_SCOPE
    )
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()

    sheet_metadata_values = sheet.get(spreadsheetId=settings.SPREADSHEET_ID).execute()
    csheets = sheet_metadata_values.get('sheets', '')
    datetime_format = '%a %b %d %Y %Hh%Mm'
    if csheets:
        for csheet in csheets:
            sheet_title = csheet.get('properties', {}).get('title', '')
            date_segment_of_the_title = ' '.join(sheet_title.split(' ')[0:5]).strip()
            parsed_datetime: Optional[Any] = None
            try:
                parsed_datetime = datetime.strptime(date_segment_of_the_title, datetime_format)
            except ValueError as err:
                print(err)
            now = timezone.now().strftime(datetime_format)
            if (
                parsed_datetime
                and (datetime.strptime(now, datetime_format) - parsed_datetime).seconds
                > settings.SPREADSHEET_TAB_EXPIRY
            ):
                sheet_id = csheet.get('properties', {}).get('sheetId', 0)
                batch_update_request_body = {'requests': [{'deleteSheet': {'sheetId': sheet_id}}]}
                sheet.batchUpdate(spreadsheetId=settings.SPREADSHEET_ID, body=batch_update_request_body).execute()

    coin_queryset = Coins.objects.all().order_by('rank')
    coin_data_list: list[Any] = [
        [
            'Name',
            'Symbol',
            'Rank',
            'Current price',
            'Price change',
            'Market cap',
            'Total supply',
        ]
    ]
    for coin in coin_queryset:
        coin_data_list.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),
            ]
        )

    new_sheet_title = f'{timezone.now().strftime(datetime_format)} Coin data'
    batch_update_request_body = {
        'requests': [
            {
                'addSheet': {
                    'properties': {
                        'title': new_sheet_title,
                        'tabColor': {'red': 0.968627451, 'green': 0.576470588, 'blue': 0.101960784},
                        'gridProperties': {'rowCount': len(coin_data_list), 'columnCount': 7},
                    }
                }
            }
        ]
    }
    sheet.batchUpdate(spreadsheetId=settings.SPREADSHEET_ID, body=batch_update_request_body).execute()
    sheet.values().append(
        spreadsheetId=settings.SPREADSHEET_ID,
        range=f"'{new_sheet_title}'!A1:G1",
        valueInputOption='USER_ENTERED',
        body={'values': coin_data_list},
    ).execute()
Enter fullscreen mode Exit fullscreen mode

The first 9 lines in the function should be familiar. They are just trying to create credentials for authentication and then the spreadsheet was instantiated. Moving on to the next line, we fetched all the meta data from our spreadsheet and then get all the sheets currently available in the spreadsheet. We then define the datetime format we will be using. This format outputs datetime as for instance, Fri Jun 17 07h34m, so that our sheet's title will have something like Fri Jun 17 07h34m Coin data. We then loop through these sheets and from the sheet's title, parse the datetime segment of it. The purpose of this is to know the datetime such sheet was created so that we can delete all sheets that are older, in this case, all sheets older than 6 mimutes (360 seconds). If any sheet is older than 6 minutes, we passed its iD to Google spreadsheet's deleteSheet request so that it can be deleted automatically as documented here. This block:

...
new_sheet_title = f'{timezone.now().strftime(datetime_format)} Coin data'
batch_update_request_body = {
    'requests': [
        {
            'addSheet': {
                'properties': {
                    'title': new_sheet_title,
                    'tabColor': {'red': 0.968627451, 'green': 0.576470588, 'blue': 0.101960784},
                    'gridProperties': {'rowCount': len(coin_data_list), 'columnCount': 7},
                }
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

creates a unique title based on the date and time the request is made and passed this title into the addSheet request as documented here. To create different tabs, distinct title is required. As a matter of fact, if a title is duplicated, an error will occur. The next line creates the sheet and data was added in the following line. That's it!!!

Step 2: Update tests/core/test_tasks.py

The ensure that our logic works, let's update the test previously written.

# tests/core/test_tasks.py
...
    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'
                datetime_format = '%a %b %d %Y %Hh%Mm'
                mock_build.return_value.spreadsheets.return_value.get.return_value.execute.return_value = {
                    'spreadsheetId': '1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc',
                    'sheets': [
                        {
                            'properties': {
                                'sheetId': 0,
                                'title': 'Coins',
                            }
                        },
                        {
                            'properties': {
                                'sheetId': 1305535527,
                                'title': f'{timezone.now().strftime(datetime_format)} Coin data',
                            }
                        },
                    ],
                    'spreadsheetUrl': 'https://docs.google.com/spreadsheets/d/1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc/edit',
                }

                today_datetime_now = timezone.now() + timedelta(minutes=7)
                with patch('django.utils.timezone.now', return_value=today_datetime_now):
                    populate_googlesheet_with_coins_data()

        mock_build.assert_called_once()
Enter fullscreen mode Exit fullscreen mode

We needed to mock the response gotten from the get request to the spreadsheet API hence this block:

mock_build.return_value.spreadsheets.return_value.get.return_value.execute.return_value = {
        'spreadsheetId': '1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc',
        'sheets': [
            {
                'properties': {
                    'sheetId': 0,
                    'title': 'Coins',
                }
            },
            {
                'properties': {
                    'sheetId': 1305535527,
                    'title': f'{timezone.now().strftime(datetime_format)} Coin data',
                }
            },
        ],
        'spreadsheetUrl': 'https://docs.google.com/spreadsheets/d/1AFNyUKcqgwO-CCXRubcIALOC74yfV716Q5q57Ojjicc/edit',
    }
Enter fullscreen mode Exit fullscreen mode

To capture time above 6 minutes, we also mocked the timezone.now() to give us a particular time in the future. When you run the test, it should cover all the function's code...

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

Discussion (0)