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,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    You have a unique skill of sandwiching potentially useful information between gratuitously condescending and hectoring comments. I'm not sure where you learned this rhetorical technique, but THAT teacher has a lot to answer for.

    So thank you and go pound sand. :)
     
  2. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    Thanks for the other comments and advice, guys. It's difficult to explain the situation precisely, and I already felt like I was writing a book in the OP, but to put things into perspective:

    We're talking about a set with maybe 1500 data points (about 12 teachers, and each one has a maximum of 5 different courses in a given trimester and a maximum of maybe 15 different columns for each entry). Basically, as a spreadsheet, the whole thing would probably fit on 4 tiled laptop monitors. So it's really just a bit too big to navigate it by scrolling around, and I just want to be able to sort this stuff and make a few very simple calculations for the numerical data.

    It would be updated about 4 times a year, and accessed maybe a dozen times a year.

    So for me to learn how to SQL my Engima Identity Relators into a jscript to nominalize the data parsers is a bit overkill for the scope of the job.

    Talk about rabbit holes...
     
    tfarny likes this.
  3. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    That is clearly a good rule of thumb, but the whole enterprise uses, well, not any one particular thing. Academic affairs uses Access, but this DB is not going to be linked to theirs, and they would never be looking at it. Teachers are kinda on their own, either using Google apps or MS Office, and nobody else is maintaining any kind of proper database, as far as I know. Very good caveats about choosing add-ons with an eye to what companies are still going to exist in a couple years...

    You've probably got a much bigger chore than mine, since I only have about 12 teachers to keep track of!

    Thanks for that link! It might have promise. I just looked at the website for a minute, but it seems to be designed for this type of situation.

    I just learned what a pivot table was a couple days ago, but honestly, that might be all I need for this.

    Yeah, even a dentists' office is miles beyond what I'm trying to do here.
     
    uriah1 likes this.
  4. Mark the Moose

    Mark the Moose Tele-Meister

    Age:
    44
    Posts:
    321
    Joined:
    Sep 4, 2018
    Location:
    Erie, CO
    We have an admin who manages a massive excel spreadsheet.
     
  5. ppg677

    ppg677 Tele-Meister

    Age:
    42
    Posts:
    212
    Joined:
    Oct 16, 2018
    Location:
    WI
    Reminds me of my neighborhood association. The tech director insisted they needed a relational database to keep track of 500 to 1000 contacts/addresses/phonenumbers. Um...no. A spreadsheet will fricking work fine. I offered to write whatever App Script (in Google Sheets) they needed...for free. Nope, insisted on a database. I'd use a spreadsheet for pretty much anything up to a few thousand rows.
     
  6. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    To tell the truth, if I could get the school to spring for a big ol monitor, we might not even have this thread. lol
     
    getbent likes this.
  7. imwjl

    imwjl Poster Extraordinaire

    Posts:
    6,960
    Joined:
    Mar 21, 2007
    Location:
    My mom's basement.
    If you have Microsoft Office users and up to date licensing you can have your spreadsheet data display as a PowerApp.
     
  8. tfarny

    tfarny Friend of Leo's

    Posts:
    2,467
    Joined:
    Sep 4, 2008
    Location:
    Hudson Valley, NY
    You programmer guys are WAY overthinking things. 1) We educators have lots to learn in our own fields already, and in general becoming a computer programmer would take more time to accomplish than this relatively simple task
    2) Doesn't your university have the ability to pay $100 a year for a full MS Office subscription that you can use on 5 machines? Surely the task is important enough to not have to seek a free solution when there is a very low cost solution right in front of you.
    3) I have a very similar task to do in my own school, and it can all be done in Excel which is still a much better tool than Google Sheets - which I also use but only for limited tasks. Excel can also be used in a cloud-shared format if you need.
     
    getbent, ppg677 and Tonetele like this.
  9. raito

    raito Poster Extraordinaire Silver Supporter

    Posts:
    5,468
    Joined:
    Nov 22, 2010
    Location:
    Madison, WI
    Since I'm purging myself of old workplace bile, I worked at a place where, as a developer, I was allocated a single 17" monitor. The receptionists got dual 30" flat screens. Back when those were tousands a piece and the cards to drive thenm were about the same.
     
  10. MattyK-USA

    MattyK-USA Tele-Holic Silver Supporter

    Age:
    57
    Posts:
    960
    Joined:
    Feb 22, 2018
    Location:
    Savannah GA
    Good lord, there's a bunch of overkill in here! The OP's dataset isn't even a big spreadsheet, and you all want ERD's and a relational DB? Good grief!

    MS Excel is available on the web, on your PC, and other platforms like iPads etc. Pivot tables will do exactly what you're looking for, and as a bonus they will generate charts simultaneously. You'd also get free use of One Drive to access the spreadsheet from anywhere. I suspect that if you used those tools (and if you're familiar with pivot tables), you'd already be done :)

    Whatever you decide, do remember to create a backup or two of your file / files...
     
    Jupiter likes this.
  11. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    I don't know what a PowerApp is, but you make it sound encouraging so I'm gonna look into it! ;)

    Yeah we have MSOffice at school, and I even have the Mac version at home, but since this is looking like it's going to bleed over into summer break, and I'll be traveling then with just an iPad without much storage, I was looking first at Sheets. It also does the pivot thingie nowadays...
     
  12. jvin248

    jvin248 Poster Extraordinaire

    Posts:
    7,134
    Joined:
    Apr 18, 2014
    Location:
    Lions & Tigers oh Mi !
    .

    One simple method for ensuring your spreadsheet can expand/contract and not screw up all your formulas is have the formula range go one line beyond your table data at the top and bottom. You can put a warning on that line "do not edit this line!!!" and change the background color of the protected line to help avoid mistakes. Then any new information is done with "insert line above" one of the other lines (sometimes formatting is copied from the line below, depending on the software, so don't insert just before the protected formula line).

    Pivot tables exist in Libreoffice
    https://help.libreoffice.org/Calc/Creating_Pivot_Tables

    Pivot tables exist in Google Sheets
    https://support.google.com/docs/answer/1272900?co=GENIE.Platform=Desktop&hl=en

    As long as you keep your charting simple you can use spreadsheets for many tasks.

    If you want to go free and elegant ... get a pc server, install Linux, install the open source database software. Here is one way.
    https://www.aboutdebian.com/database.htm It could be a pc that is too slow to run the current Windows as Linux is much more efficient.

    However, for your purposes, the spreadsheet path will be more practical.

    .
     
    SolidSteak and Jupiter like this.
  13. tfarny

    tfarny Friend of Leo's

    Posts:
    2,467
    Joined:
    Sep 4, 2008
    Location:
    Hudson Valley, NY
    I still think Excel is just so much more powerful than Google sheets, even just in its sorting and filtering options, and it is now cloud-friendly in various ways. I also am sometimes offline (shock!) - I commute by train and cell service is pretty spotty in places - and I like to be able to work offline without any problems. Enter your raw data into one or two sheets (maybe one four courses and one for instructors) and do all your calcs and tables elsewhere, and other simple changes to how a newbie would use it, and you should be fine. I wish I could share my own with you - I do almost exactly what you're looking to do.
     
    uriah1 and Jupiter like this.
  14. getbent

    getbent Telefied Ad Free Member

    Posts:
    35,656
    Joined:
    Mar 2, 2006
    Location:
    San Benito County, California

    create a google form (which will generate a sheet) from there, you can complete the form for each teacher (or better have them complete it) once the data is input, you can pull all manner of groovy, colorful graphic about any aspect of the data...
     
  15. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    Yeah, I’ve used google forms a fair bit, but since I’m the one getting the whole one hour a week of release time as dept head (and since I don’t actually know if this is gonna end up going anywhere...), it’s sorta my job, and now that I’ve created a bunch of pulldown menus for most of the columns, it’s actually quicker and easier to input right into the sheet I reckon—less scrolling at least!
     
  16. Jupiter

    Jupiter Telefied Silver Supporter

    Posts:
    23,645
    Joined:
    Jun 22, 2010
    Location:
    Osaka, Japan
    And they are getting quite cheap now, but not cheap enough to score one from Tech Purchasing, I guess...
     
  17. SolidSteak

    SolidSteak Friend of Leo's

    Posts:
    2,664
    Joined:
    Apr 27, 2016
    Location:
    USA
    It does sound to me like all you need is a well-templated Excel workbook with Pivot Tables, but OTOH I use Excel every day, and it could be a case of "When all you use is a hammer, everything looks like a nail."
     
    Jupiter and uriah1 like this.
  18. magicfingers99

    magicfingers99 Tele-Afflicted

    Posts:
    1,521
    Joined:
    May 7, 2015
    Location:
    atlanta
    just 12 years of public education and a lifetime of trying to get it out of my head.


    I did database programming for 15 years or so, it paid for the house anyhow.

    you have a general idea of what you want.
    basically in Information Technology you define

    1. the scope of the project - what you want to accomplish and how to define when it is accomplished.

    ie produce reports monthly of how many widgets were made of each kind, or in your case how many students and what courses and grades. so you need to define what data you want to capture.
    a. grades
    b.student id's
    c time periods
    e. race/gender/religion/immigrant status
    f. special notes, teacher comments
    g. type of weapon by carried by student

    so anything that represents a specifice aspect of whatever elements of the educational process you want to measure and quantify.

    2. the sources of the data. can it be captured and exported from an existing system? Does it need to be imported by hand? Can it be scanned? do we need bar codes etc.

    3. how to get the data from its sources into the sort of system we desire. (the real heavy lifting)

    4. the feedback loop, is the output of the project what you desire, if not tweak and retry (this basically goes on until the money or interest runs out, because even if you build a perfect system, next week someone will ask can we sort by shoe size or hair color? so if the system works the scope will become a moving target impossible to ever satisfy. welcome to information technology, the illusion of wisdom produced by reams of dry data.

    so to ask someone to read your mind and do it for free, causes laughter or anger from programmers, and salivation from software consultants because they love someone who doesn't know what they want or how to get it because, billable hours.

    there's lots of retired farts from the data trade that love a chance to practice their arcane arts. if you can write up what you want to do, how you expect to acquire the data, and what your minimum expectations of output would be, I'm sure you could locate someone who might take it on for minimal expenses, but anyone would expect some compensation for their efforts, just as you would.

    start by defining the scope of the project and be as specific as you can be, because those details are the difference between expecting a sailboat and receiving a submarine.

    good luck.

    you might want to try a smale scale test of what you want to do, maybe 20 rows of data in a spreadsheet, you can do graphing in most modern spreadsheets, try libre office if you don't have excel.

    off to pound some sand, take care..
     
    awasson and Jupiter like this.
  19. magicfingers99

    magicfingers99 Tele-Afflicted

    Posts:
    1,521
    Joined:
    May 7, 2015
    Location:
    atlanta
    you could do that in outlook and share the contacts list amongs the people who need the contact info. its already got all the fields and you can export it and also you can print it and have a copy for offline use.
    thats what I do. Print my contacts and stuff them in my briefcase and then when the old management boot goes up my arse I've got that info for my next endeavour.. (pdf, thumbdrive,.pst file backedup)
    PIM pro is a good outlook like personal information manager as well it has a free version and a paid version

    https://www.essentialpim.com/pc-version/business-edition
     
  20. magicfingers99

    magicfingers99 Tele-Afflicted

    Posts:
    1,521
    Joined:
    May 7, 2015
    Location:
    atlanta
    libre office is free and works well with ms office products
     
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.