Google Apps Script Physics Problem Database

I tweeted out the other day an opinion about using google apps script (GAS from now on) as a web framework:

That led to some follow up from my awesome tweeps, including a nudge to write this blog post, so here you go.

This post will be mostly about how to use GAS as a data-driven, responsive website, with the Physics Problem Database really just the example I put together to show things.

Why GAS?

A data-driven website needs to store and retrieve data. Most of my other projects tend to use mysql databases for that (and PHP (yes, stop laughing and look up Laravel) for the html/interfacing) but that approach can have a pretty big startup cost (mental energy and time, not necessarily money). I certainly know how to spin up a new Laravel site and set up a new mysql database, but I know that’s a huge barrier for folks who want to just build something small-ish.

I’ve been using GAS for a long time now to help automate certain tasks (and you’ll note at that first link that I’ve thought about GAS as a website driver before – the difference in this post is that I don’t bother with the sketchiest part of that post in this new work – namely using the query command in a new sheet all the time). The way it can interact with a spreadsheet is what’s really driving this post. Basically I’m exploring how you might use a spreadsheet instead of a database to really get something up and running.

Benefits?

  • You don’t need a server! Or even a coding environment. I did nearly all of this coding on a chromebook because all you need is a google account and they provide the IDE (integrated development environment), the storage of the “database,” and the hosting of the pages
  • The “database” is a very user friendly environment. What sql would call tables, I just call different sheets. It’s very easy to see, edit, and delete the data in the “database”.
  • Both the server-side and client-side code is javascript. I’m not necessarily praising the language here, though it is fun to code in, but rather mostly praising the fact that you only have to know one thing (plus html, of course).
  • Authentication is basically built in. See below for more on that
  • AJAX (or the ability to update or query the “database” without reloading the whole page) is particularly easy

Drawbacks?

  • It’s not super fast. You’ll see how the physics problem database takes about ~5 seconds to load.
  • The spreadsheet can only get so big. I believe the relevant quota is 5,000,000 cells. I would guess that you could do fine with 1,000 – 10,000 main records.
  • You have to build your own unique ids, whereas sql will normally just do that automatically. You have to do this rather than just finding the row things are on to protect against someone changing the order of the cells in the spreadsheet (deletions, adds, sorting, etc). I suppose if you make it so that you’re the only one who can access the spreadsheet and make a promise to yourself never to change the record order, then you could skip this. This is especially important if you do some one-to-many or many-to-many relationships among the sheets.

Now I’ll shift over to using the Physics Problem Database as context to explain how you can stuff.

Physics Problem Database

Years ago the Global Physics Department put a fair amount of effort into a physics problem database. We thought it would be fun to both build such a thing for teachers to use, especially those doing Standards-Based Grading (who often have to give students new and different problems to try) *and* to help our members learn how to code. While a ton of people were interested, the barriers of learning how to get a database-driven webpage running were tough. So I thought I’d use that idea as context to really push this GAS approach.

For those of you who don’t care about what I have to say below about what I learned in doing this, here’s the direct link to the GAS Physics Problem Database

Goals:

  • Display physics problems that people could use
  • Allow only authenticated uses to be able to add problems
  • Develop a tagging system with a limited number of approved tags

The first thing I did was decide the data structure. After minimal thought, here’s what I came up with:

  • Problems
    • unique id
    • problem
    • user id
    • date
  • Tags
    • unique id
    • tag
    • user (didn’t end up using this)
    • date (didn’t end up using this)
  • Users
    • unique id
    • email
    • name
    • date
  • Problem_tag (this is the Laravel naming convention – it’s what some call a pivot table since this facilitates the many-to-many relationship between problems and tags)
    • unique id (not sure this is necessary)
    • tag id
    • problem id

Next I started by making the page that would just display all the problems. I wanted the display to show the problem and any tags that go with it. I think I meant to show who wrote the problem too, but I don’t think I coded that yet (though it would be super easy to do).

Ok, so how to manage the data? What I decided to do was to just load all the data in all the sheets into a massive javascript object. I actually do this a lot with other GAS projects that I work with. It seems that several hundred rows of data works just fine, so I think this is at least somewhat scaleable (which google insists is spelled wrong, by the way). Here’s the code that does that:

function loadData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets=ss.getSheets();
  var whole={};
  for (i=0; i<sheets.length; i++) {
    var data=sheets[i].getDataRange().getValues();
    // creates object with column headers as keys and column numbers as values:
    var c=grabheaders(data); 
    var list=data[0];
    ob={};
    for (j=1; j<data.length; j++) {
      ob[data[j][c["unique"]]]={};
      for (l=0; l<list.length; l++) {
        ob[data[j][c["unique"]]][list[l]]=data[j][c[list[l]]];
      };
    };
    whole[sheets[i].getName()]=ob;
  };
  return whole;
}

That produces and returns an object called “whole.” It has a key for every tab in the spreadsheet. The value for each key is an object with keys set to the unique ids. The values of those are objects whose keys are the the column headers in that tab. Say you wanted to find the problem associated with a particular problem_tag relationship. You’d get it with whole[“problems”][whole[“problem_tag”][unique-number-you-care-about][“problem id”]]. I know, it’s hard to read, but you can navigate all relationships this way.

How do you send that to be parsed in the html document? First note that all GAS projects can be made up of javascript documents and html documents. They’re all actually stored in the single script document. I use templated html where you can intersperse <? useful server-side javascript ?> into your html. So the table for the problems is done with this code (ugh, the html syntax highligher is failing on all of the “>” characters, replacing them with &gt – sorry about that):

<table class="table table-striped">
     <thead>
      <tr>
       <th>Problem</th>
       <th>tags</th>
      </tr>
     </thead>
     <tbody>
      <? Object.keys(data["problems"]).forEach(function(key) { ?>

       <tr>
        <td><?= data["problems"][key]["problem"]?></td>
        <td><?!= findTags2(key,data) ?> </td>
       </tr>
      <? }) ?>
     </tbody>
    </table>

The “forEach” part is going through all of the problems in the problems object (also note that I’m passing “whole” as “data” – don’t ask why.) Then each one adds a row to the table, displaying the text of the table with data[“problems”][key][“problem”]. Then it runs a function (on the server, before the page is rendered) called findTags2 that accepts the key (unique id for the problem) and the full data object and then returns a list of hyperlinked tags that, when clicked, show a page with just problems with that tag. That page does that filter by doing the “loadData” above and then deleting any elements that aren’t connected to that tag before sending data to a very similar html page. Note that to add in the creater of the problem I would just add something like <td><? data[“users”][data[“problems”][key][“user id”]][“name”] ?></td>

The only other thing the page does right now is allow authenticated users to add problems. That page is given all the tags and grabs the user’s email (you have to be logged into a google account to use the page). There’s a simple text entry box for the problem and the user can then select any appropriate tags. When they hit submit there’s an AJAX call to update the spreadsheet. All that means is that the page doesn’t have to reload to do it. The data is sent to the server, it updates the spreadsheet (in several ways – see below) and then returns a note saying it was successful. That updates some text on the page. It all takes about a second. The spreadsheet updates are:

  • Put the problem into the “problems” tab. For that you can use the “append row” method in Google’s SpreadsheetApp. For the unique id I just use the number of milliseconds since January 1, 1970, making the assumption that I won’t run the script twice in the same millisecond.
  • Then the “problem_tag” tab is updated, with a new row for every tag that was checked by the user. This is where I use the unique id for the problem (the unique id for each tag is embedded in the form to allow them to be passed to the server correctly).

The authentication is super easy if you’re doing this in a google-school domain. Basically you set the script to run as you (the developer) and use the users tag to check to see if the user email (that google provides for any user visiting the page) is in your approved list. That way you’re letting google do all the authentication (they have to be in your domain) and you can only allow those who are in your users tab to be able to even access the page.

Unfortunately the authentication is a little harder for normal consumer google accounts, but still doable. Unfortunately the command that returns the visitors email only works if you allow the scripts to be run as the person visiting the page. That means they need access to the spreadsheet, something you don’t have to do in the domain version. What’s cool, though, is that you can just give the whole world “view” access and this script will still work. What you have to do in addition to updating the “users” tab is to give those people “edit” access to the spreadsheet. Then everything works!

When users visit the page for the first time they have to go through a permissions check. Basically google checks your script to see what things you’re doing and makes sure the user is ok with that. The first time I did what’s described above for my consumer google account I noticed that the permission warning said that the script would have the ability to view, edit, and delete any of my drive files. Now I know I’m a trustworthy guy, but I figured even my friends would have a problem with that. Luckily I found this page that made it clear you can limit the access to just the associated spreadsheet, something I already was doing by giving them “view” access! Problem solved.

So, I think I’ve got a roughly-working beta version up and running. Please let me know your thoughts. Here are some starters for you:

  • I like this, especially how I could develop a web page typing a new line on every computer I stumble onto without having to load a full development environment.
  • I hate this: I need to do all my coding on my own local machine before even thinking about putting it up on the web. It’s too bad there’s no way to do that with GAS.
  • I like this but I’m nervous about whether it would scale. Why haven’t you just pasted in a bunch of nonsense problems to see when it breaks?
  • I got a “you’re not authorized” message when I tried to hack in and load a bunch of crap into your crappy database. Can you please give me access?
  • Your tag choices are dumb. Instead I think you should use . . .
  • I think you didn’t need to bother with restricting the permissions scope to just the one spreadsheet. I trust you!
  • If I’m at a google school can I build something that people outside of my domain could use?
  • Can users select problems and save them? Print them? LaTeX them?
  • What happens if you share the script with someone? Can they collaboratively edit? At the same time?
  • I’ve been laughing so hard at the fact that you sometimes code in PHP that I haven’t been able to digest the rest. Can you make it a flash video and put in on your myspace page?
  • I think it’s dumb to load the whole spreadsheet into memory. Just load in all the unique numbers and the rows they’re on and load stuff when you need it!
  • I just tried to email you about this at your job and got an away message saying you’re on vacation. You do this crap for fun?!
  • I see you have LaTeX in one of the problems. Are you just using MathJax?

About Andy Rundquist

Professor of physics at Hamline University in St. Paul, MN
This entry was posted in HUWebApps, physics problem db, programming. Bookmark the permalink.

6 Responses to Google Apps Script Physics Problem Database

  1. Pingback: Google Apps Script Physics Problem Database - AppsScriptPulse

  2. bretbenesh says:

    I am filing this away for future use. I love these types of posts!

  3. Jenn Broekman says:

    Are the lines that show up when you “show all problems” the entire problem or just the first line? Also, the tags link seems to be broken.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s