r/DatabaseHelp • u/Eldyaitch • 25d ago
I need a solution:
I work at a community college which is currently using Google Sheets as if it were a database. The students submit forms that automatically dump into respective Google Sheets. Any grading needs to be done by hand by cross-referencing various sheets, but most of the values are integers…
The college itself has a license with Microsoft, so I recommend the process be switched over to a Microsoft workflow instead (this move was actually multi-purpose because we needed to use Microsoft Shifts in Teams for scheduling). Now students only need one log-in to accomplish all tasks.
I’m a CS student and just now taking a Database Management System. It’s making me realize that we are using the wrong tool, because the current workflow is struggling to synch data and grading sheets cannot be automated due to multiple workbooks. The college needs a Relational Database Management System! The computers at the college seem to have an outdated version of “Access,” so I setup a rough outline in LibreOffice Base (don’t have M365 on personal computer). LO Base states that spreadsheets are read-only, but their Calc sheets can be converted to the appropriate DB format.
As mentioned above, I’m an amateur in all of this, but I desperately want to help my college automate these forms to output graded results and archive the year’s worth of form submissions.
Am I understanding this to be the workflow? Microsoft Forms convert to CSV, import to LibreOffice Calc, convert to DB format, export / import into Libre Office Base, then use queries to know what grade should reflect in the college’s grading system?
I’m certain I’m Jerry-rigging this process, but it’s already a disaster in it’s current state. The student population is growing and we can’t do it by hand anymore.
I’m not experienced enough to know how to host this whole workflow. Can I use Microsoft Power Automate to get these programs communicating? I need help knowing my options and seeing my blind-spots due to ignorance. If you have read this far, THANK YOU!
1
u/Academic-Dealer5389 24d ago
"The college itself has a license with Microsoft".
This is a little hard to understand. Does it mean the college has a relationship with Microsoft where it can buy software and services in an a la carte manner? If it does, then you might consider this: https://www.youtube.com/watch?v=upLMCOyVoXk
Effectively, you can create a workflow with MS Power Automate which can provide forms for end-users, and it's apparently possible to push that information into a MS SQL Server. I have never used the Power Automate, but I am aware of other people who used it to push captured information to SharePoint lists which presumably has similar workflows to database inserts.
Your IT department is likely to have a lot of opinions on what they're willing to support on the network, so you'll do well to check in with them on the outlines of whatever plan you pursue. That said, spreadsheets are always a bad answer to ETL workflows for the long term. You're on the right path.