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:
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
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()
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},
}
}
}
]
}
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()
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',
}
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...
Top comments (0)