Databases for Dummies: Google Sheets question

Discussion in 'Bad Dog Cafe' started by Jupiter, May 14, 2019.

  1. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,535
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    attention data nerds!

    As the head of my department, I need to keep track of what courses we are offering, to make sure that we are getting appropriate coverage in terms of course offerings at each grade and English ability level, to keep track of teacher workloads, to plan for curriculum renewal, and to make sure that the Academic Affairs department isn't screwing up our schedule.

    Right now, we have several different documents/files, in various formats and at varying stages of currency, for those different purposes, with a bunch of redundant info and several conflicts/errors that are a real pain to track down. So I want to create the One Database to Rule Them All, where I can make changes that are then reflected in documents formatted for those various purposes.

    I would like to use Google Sheets (because it's free and platform-independent and I can work on it from several different devices wherever I am) to input the data for every course that we offer, with columns something like this:

    Lastname, Firstname, Coursenumber, Coursetitle, Trimester, Hrs/wk, Level, Grade, Assessments, etc

    Then I want to be able to whip out different charts, such as
    how many hours per week each teacher is teaching in a given trimester or over the whole year
    how many intermediate-level high school courses are being offered each trimester
    how many offerings involve research paper writing, slide presentations, debate, etc.

    I found a google add-on called Autocrat that lets me pull, for example, all the data from a given row and plug it here and there into a PDF or Google Doc, but it can't total teaching hours for each teacher, for example, or give me output sorted by the values in cells (anyway, I think it can't...).

    So I want to know how I can crunch some numbers and manipulate this data to generate a variety of tables without SCRIPTING (or paying money). Also I want to be able to add/delete course rows over time or re-sort the whole SS (by teacher last name, for example) as offerings change without breaking the output, so output can't be keyed to any specific cells of the SS, only to specific columns and/or the contents (for example, I want to be able to call out courses that are offered in even or odd years). It wuold be nice if I could easily generate teacher-specific PDFs and such.

    I have started building the data with my own course offerings for the year, using drop-down menus to make input of subsequent courses easier, but it's still going to end up being a huge project (for me), even before I do any google-fu to the data.

    So does anybody know of google-friendly apps/add-ons that would let me use a google Sheets doc to run pretty basic database functions?

    I don't need a whole big walk-through (well probably I do, but I wouldn't ask for it), but just want to know if such a solution exists and where to start my explorations.

    Thanks in advance!
     
  2. Larry F

    Larry F Doctor of Teleocity Vendor Member

    Posts:
    15,972
    Joined:
    Nov 5, 2006
    Location:
    Iowa City, IA
    My knee-jerk academic response is to check and see what your peer institutions and departments are using.
     
    Tonetele and awasson like this.
  3. Ironwolf

    Ironwolf Poster Extraordinaire Gold Supporter

    Age:
    63
    Posts:
    7,500
    Joined:
    Mar 11, 2008
    Location:
    Boise, Idaho
    I always did my databases in PHP and MySQL. They, too, are free.
     
    Skamania, brogh, uriah1 and 1 other person like this.
  4. ppg677

    ppg677 Tele-Meister

    Age:
    41
    Posts:
    196
    Joined:
    Oct 16, 2018
    Location:
    WI
  5. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,535
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    they are using, hmm let me see, nothing
    Other departments don't have to cope with delivering 4 parallel curricula based on ability levels, or have the faculty numbers that we do, so they can basically keep track of stuff on one sheet of a legal pad.
    Besides, why would I ask THEM when I got all y'all? :)

    Am I gonna have to type scripts? Cuz then that's not my definition of free... ;)

    The thing is that I'm hoping I don't have to become a database expert just to do these pretty rudimentary functions. I don't want to spend the whole summer figgering this stuff out.

    Hmm, this might be the thing, except it's not actually out yet...
     
    Skamania likes this.
  6. ppg677

    ppg677 Tele-Meister

    Age:
    41
    Posts:
    196
    Joined:
    Oct 16, 2018
    Location:
    WI
    It's sort of amazing that there is no replacement for Microsoft Access, which is probably perfect for this kind of thing.

    Setting up a MySQL database and running SQL queries over it is surely overkill and requires too much tech expertise.

    Scripting Google Sheets would also do the job, but that too involves programming.

    Sorry...I can't think of an easy/free alternative.
     
  7. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,535
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    I actually have, um, access to Access, but not online, so I can only use it when I am at my laptop at school. But isn't that also a scripting situation?

    I think Pivot tables might do some of what I'm hoping for, though my initial experiments with it aren't generating the results I'm expecting to see. It is weird that there's not already a click-and-drag solution for this stuff...
     
    Skamania likes this.
  8. teleaddicted

    teleaddicted Tele-Holic Gold Supporter

    Age:
    66
    Posts:
    533
    Joined:
    Nov 5, 2013
    Location:
    Roma Urbe
    You will need an Entity Relationship Diagram before even thinking of programming the output. Collecting, arranging and normalizing raw data would be your first goal.
    Many tools out there to help you on what we once did on paper.
    A short googling showed this:
    https://erdplus.com/
    but there are many others.
    Just my opinion.
     
    ndcaster and awasson like this.
  9. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,535
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    you lost me at Entity lol but I'll check that link thanks
     
  10. ppg677

    ppg677 Tele-Meister

    Age:
    41
    Posts:
    196
    Joined:
    Oct 16, 2018
    Location:
    WI
    Nope, you don't need lame diagrams. I work for one of the most successful/famous software companies of the modern era. And we just write code. Maybe a design document after-the-fact to help a promotion case.
     
  11. FenderGuy53

    FenderGuy53 Poster Extraordinaire

    Posts:
    5,662
    Joined:
    May 14, 2008
    Location:
    Marion, NC
    I use the Libreoffice suite on my Linux Mint OS laptop. The suite mimics the Microsoft Office suite in functionality, with the major difference being price - Libreoffice is FREE!

    I would suggest uploading a Libreoffice Sheets file to a shared folder on Google Drive. This way, the Sheets file will be accessible, via smartphone or computer, from any location, AND, the ability to define cell formats allows you to do calculations on those cells!

    Good luck!
     
  12. imwjl

    imwjl Poster Extraordinaire

    Posts:
    6,843
    Joined:
    Mar 21, 2007
    Location:
    My mom's basement.
    Start with what platforms the whole enterprise uses. An individual doing a lot of work for the institution but out of their own private space is not a good idea. You'll have more buy in and leave something for the future if you consider the group as a whole.

    As far as Google, I'm a G Suite and an an Azure/365 admin. I could fault Google for simplicity as much as I could fault Microsoft for having tools that are too much for a non-tech pro. More and more Microsoft is giving you better tools and good tools for the not too technical person. If your institution has Office 365 a few tools there would give you more capability but still result in something simple to use.

    As an administrator for those main platforms, I've already seen some add-ons disappear or change their pricing model. That makes bigger feature set Microsoft has look good. If you used Excel for your easy to use non-pro data source but had your users work with a PowerApp you would have more than G Suite does, no extra costs, and little risk that your data format would be obsolete.

    If you do Google add ons do some homework to make sure it's something with a lot of market share and/or from someone making money. That would apply to Azure and Office 365 add ons if you look at them.

    Most of all make sure you're doing work that follows the organization's standards to make sure your efforts will be worth it in the long run.
     
  13. ndcaster

    ndcaster Poster Extraordinaire Silver Supporter

    Posts:
    7,063
    Joined:
    Nov 14, 2013
    Location:
    Indiana
    I've just become chair of a college department, so I can't believe how timely this thread is. Thanks Jupiter for posting it, and good luck to you in your position! I'll be reading this thread with great interest.
     
  14. raito

    raito Poster Extraordinaire Silver Supporter

    Posts:
    5,438
    Joined:
    Nov 22, 2010
    Location:
    Madison, WI
    In general, using a spreadsheet to mimic a database is a pretty poor idea. And yet so many still try to do it.

    That said, I don't think you're going to find a non-scripted solution for Google Sheets.

    All that said, you might have a look at https://sheetsu.com/
     
    demon, W.L.Weller and awasson like this.
  15. magicfingers99

    magicfingers99 Tele-Afflicted

    Posts:
    1,408
    Joined:
    May 7, 2015
    Location:
    atlanta
    an educator who doesn't want to take the time to learn something new. why am i not surprised?

    try a relational database. and learn structured query language,
    there's plenty of old timers still using Dbase and rbase and of course microsofts SQL


    https://www.rbase.com/

    (they have programmers that can build what you want and give you an estimate of what it would cost)
    write up the scope of your project and send it to them for a bid.

    there are report writers that can automate repetive tasks, like monthly reports. and data mining software like Monarch from datawatch that can scan existing reports and subtotal and provide charts and queries or pull the data and feed it to program that can provide charting the way you want it.

    Any thing good requires effort. your own or someone elses. This may require either learning on your part, or paying someone who has already spent time learning these things. its how the world works.

    we call it division of labour.
     
  16. awasson

    awasson Friend of Leo's Gold Supporter

    Age:
    55
    Posts:
    4,904
    Joined:
    Nov 18, 2010
    Location:
    Vancouver
    What a rabbit hole. I’ve attempted to write some ideas and discussion in point form several times but because this is a complicated subject, I just can’t put it in point form. Sorry about the essay of ideas.

    This is my wheelhouse. Designing custom systems to manage data about people and activities (exams, work experience logs, continuing Ed reporting) and that sort of thing make up a large part of how I generate my guitar buying capital.

    For what you’ve described, I’d recommend a proper database rather than spreadsheets. If it’s designed sensibly it’ll allow more flexibility in the long run and lessen the chance that you’ll paint yourself into a corner which is what most people do when they embark on a plan like this.

    Unless you want to embark on a learning curve in database design, interface design and reporting, I strongly recommend you hire someone to assist you with this project. Maybe a student but it needs to be someone who’s really jazzed about data and database design and it needs to be someone who’s open to looking at software, auditing it for your need some and moving onto the next offering if it won’t fulfil your needs.

    I’ve never seen a universal piece of open source software that will just do what you want out of the gate. You might be able to design your application in something like FileMaker Pro. A lot of my clients start with FileMaker Pro which does allow them to get started quickly. The problem with FileMaker is that once you hit the wall of its limitations, you’re stuck.

    Starting with an entity relationship diagram is a good idea to map out your data so that you can visually see what’s going on with it. I used to have a tool that would allow me to interact with the database right from the ER Diagram. The goal as @teleaddicted said is to normalize you’re data, which means instead of having a gigantic flat spreadsheet where things are repeated, you break up the data into numerous tables so that you have all of your teachers in a table (each teacher can be identified by a unique key, usually a number), all of the courses are in a courses table and can be identified by a unique key, anything that is going to be used in many places is stored in a single point and is referred to in your queries by a unique key so that if a course or teacher’s information is changed, it gets updated at a single point and the update is reflected globally. So, if I were tasked with this, even before I decided what technologies I was going to employ, I’d start with a diagram to figure out how to approach it and where my choke points are going to be. I tend to look at it from the point of view of generating my reports so if I want to generate a report of each teacher’s workload, what data am I going to draw upon? If I want to generate a report of all courses available for semester 2, what does that look like? If I want to generate a calendar, what does that look like?

    I tend to do a lot of work with CRM’s (contact relationship management) systems where I can build out a really good contact database with contact types for teachers, parents, admin, even students. I could probably build out my courses in custom fields or an array of custom fields.

    To Start:
     
    teleaddicted likes this.
  17. awasson

    awasson Friend of Leo's Gold Supporter

    Age:
    55
    Posts:
    4,904
    Joined:
    Nov 18, 2010
    Location:
    Vancouver
    Further to my post above, I’d suggest going on a discovery stage where you spend some time looking at the features of the software I suggested above. You’ll more than likely find references to other software solutions as well so keep an open mind, create a hit list and eventually you’ll find a foundation to build your system upon. There’s a lot of interest in this area but I haven’t seen a package that does what you want right out of the box yet.
     
  18. uriah1

    uriah1 Doctor of Teleocity Ad Free Member

    Posts:
    19,550
    Joined:
    Feb 12, 2011
    Location:
    Around
    Since excel went to 1mil records, I recommend to many people to just
    use excel and a few pivot tables or macros.
     
    Skamania likes this.
  19. raito

    raito Poster Extraordinaire Silver Supporter

    Posts:
    5,438
    Joined:
    Nov 22, 2010
    Location:
    Madison, WI
    So you're admitting to being part of the problem? :)

    In another lifetime (here comes a story), I worked at a place that did industrial control of chemical processes. One part of our package did a pile of reporting. And it kept crashing. I was told to fix it by writing a Delphi VCL component that could execute an Excel macro. I didn't do that, and literally got screamed at by my boss's boss. Who 6 months later apparently forgot what a waste of space I was because he was using the feature I did add.

    Here was the actual problem:
    Time to generate a report. So we load up Excel, which loads up Microsoft Query which loads an Access database which then sucks in all the database data into the Excel table. Then it fires up Microsoft Word and Excel uses the Word API to write the entire report. No wonder it kept crashing. Not only were we practically loading the entire Office Suite at the same time (in a 4 meg machine a long time ago), but we were duplicating the entire database 3 times (Access, Excel, Word).

    I solved this by doing 2 things. The first was that I showed our apps guy how to write the report directly in Access (he was a bright guy, he just hadn't had to do it before). The second was that I did write a VCL control. But not one that just fired off Excel Macros. I made it able to execute any Windows Script Host script. Then the apps guy could use it to fire off the report in Access. And do practically everything else he needed to do. He (the apps guy actually doing the work) loved it.

    Use the right tool for the job.

    Just a horror story about using a spreadsheet as a database.
     
    SolidSteak and awasson like this.
  20. uriah1

    uriah1 Doctor of Teleocity Ad Free Member

    Posts:
    19,550
    Joined:
    Feb 12, 2011
    Location:
    Around
    Yea..been there.. Access is great for large vol.. yes, I use daily
    I also use msqry to access and msqry to extra large metalfiles. and
    send to excel. (purchase orders, invoices, inventories)

    Just saying, some stuff can be managed with smaller footprint..the
    regular Joe in a shoeshop, dentist, or Marys bowling league can use.
    (not dealing with dcs,plc) or real time plant info.
     
IMPORTANT: Treat everyone here with respect, no matter how difficult!
No sex, drug, political, religion or hate discussion permitted here.


  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.