@tomgranuja due to my own activities, I coded an small library called gspread-ormhttps://github.com/ralamosm/gspread-orm, which is about turning a Google Spreadsheets' sheet into a pydantic model. For example, in our case of the talleres spreadsheet, we can code a model like this
from typing import Optional
from gspread_orm.models import GSheetModel
class PeriodSheet(GSheetModel):
"""Model representing each row of the `titles` spreadsheet"""
periodo: int
name: Optional[str] = None
teacher: Optional[str] = None
cycle: Optional[str] = None
c1: Optional[str] = None
c2: Optional[str] = None
c3: Optional[str] = None
quota: Optional[int] = None
full_name: Optional[str] = None
description: Optional[str] = None
def get_cycles(self):
"""Method to return list of Cycle objects associated with this row"""
from cayuman.models import Cycle
raw_cycles = self.cycle.split(' y ')
output = []
for raw_cycle in raw_cycles:
output.append(Cycle.objects.get_or_create(name=raw_cycle.title()))
return output
def get_teacher(self):
from cayuman.models import Member
# return Member object associated with self.teacher string
class Meta:
# holds pointers to connect with the google spreadsheet
spreadsheet_url = url # url of the spreadsheet
worksheet_name = period_str # "periodo 3" or any sheet we want to fetch data from
configuration = {...} # configuration dict
So this model connects with spreadsheet_url and reads worksheet_name from it. The permissions to access that sheet are stored in the configuration dict (it's not complicated to do, you need to create a google app, give it access to the Spreadsheet API and share the talleres spreadsheet with an automatic user created by this configuration, more info here https://docs.gspread.org/en/latest/oauth2.html).
Then, the whole sheet is loaded into the model and each field of the sheet is represented by one of the fields of the model. So for example we can work with it like this
...
def import_workshops(request, worksheet_name):
"""Admin view at charge of importing workshops from `worksheet_name`"""
from cayuman.models import PeriodSheet
# connect to the sheet
PeriodSheet.Meta.configuration = conf # get configuration from somewhere, most likely read from a local file
PeriodSheet.Meta.worksheet_name = worksheet_name # "periodo 3" or whatever
# fetch row with id = 26
salva_workshop = PeriodSheet.objects.query(id=26)
assert salva_workshop.name == 'Web' # this is true
for row in PeriodSheet.objects.all(): # iterate over all rows of the sheet
teacher = row.get_teacher() # get Member object which is the teacher of this row
cycles = row.get_cycles() # get Cycle objects for this row
# do your magic to import this workshop just as in the maintenance script
...
This way we can add a "Import" button in the "Period" screen of django admin, to easily import and re-import workshops directly from the admin instead of logging in and running a script by hand. Also, most likely (although I'm not sure) the combination of my module's gspread-orm and Google's gspread is more light than pandas and odfpy. This way we can work in #42 removing pandas which takes too much space in our server.
Would you like to try this? The steps of the task are like this:
Add gspread-orm as a dependency to the project (explained at the bottom)
Add a model like the one I showed above (actually it's a good skeleton)
Write a new view under the PeriodAdmin class, like this one I wrote under WorkshopPeriodAdmin, to receive a worksheet name and then use the PeriodSheet model to import the data using a logic very similar to yours from "add_workshops_from_ods.py".
Add a field to PeriodAdmin.list_display that will use this new admin view to show an "Import from gsheet" button on each period's row.
Add some security so only superusers can use the view, etc.
What do you think? I believe it looks more complicated than it really is.
@tomgranuja due to my own activities, I coded an small library called
gspread-orm
https://github.com/ralamosm/gspread-orm, which is about turning a Google Spreadsheets' sheet into a pydantic model. For example, in our case of thetalleres
spreadsheet, we can code a model like thisSo this model connects with
spreadsheet_url
and readsworksheet_name
from it. The permissions to access that sheet are stored in theconfiguration
dict (it's not complicated to do, you need to create a google app, give it access to the Spreadsheet API and share thetalleres
spreadsheet with an automatic user created by this configuration, more info here https://docs.gspread.org/en/latest/oauth2.html).Then, the whole sheet is loaded into the model and each field of the sheet is represented by one of the fields of the model. So for example we can work with it like this
This way we can add a "Import" button in the "Period" screen of django admin, to easily import and re-import workshops directly from the admin instead of logging in and running a script by hand. Also, most likely (although I'm not sure) the combination of my module's
gspread-orm
and Google'sgspread
is more light thanpandas
andodfpy
. This way we can work in #42 removingpandas
which takes too much space in our server.Would you like to try this? The steps of the task are like this:
gspread-orm
as a dependency to the project (explained at the bottom)PeriodSheet
model to import the data using a logic very similar to yours from "add_workshops_from_ods.py".PeriodAdmin.list_display
that will use this new admin view to show an "Import from gsheet" button on each period's row.What do you think? I believe it looks more complicated than it really is.
How to add
gspread-orm
to the projectAdd this line to pyproject.toml
then run
poetry lock
andpoetry install
.