Django and Google Spreadsheet API: Automatically creating and deleting multiple tabs or sheets
Tags: webdev, python, tutorial, django
Published: 2022-06-19 04:43:19

## 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][3], [Google Sheets API][4], and [Using OAuth 2.0 for Server to Server Applications][5].

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

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:

```python
# 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()
```

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][1]. This block:

```python
...
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},
                }
            }
        }
    ]
}
```

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][2]. 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.

```python
# 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()
```

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

```python
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',
    }
```

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 ☕](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://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#deletesheetrequest 'DeleteSheetRequest'
[2]: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addsheetrequest 'AddSheetRequest'
[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'