Google Apps Script full stack?

A few weeks ago I brushed off my google apps scripting rust and managed to learn how to do a mass ownership change for a colleague who was leaving the institution. It always takes me a while to remember how everything works but I got it done (note: it’s doable, but the script has to be run by the original owner and the new person has to be in the same institution. Also, stupidly in my opinion, google gives the new person ownership of the entire directory structure, but it also puts the top-level label on every file and folder so it seems like all that stuff is at the top level — hence a second script I wrote for the new owner to run that gets rid of those labels).

Then my brother was interested in a web page that would work on his phone that would help him check in his bike shop customers. So I dug in a little deeper in to google apps script web apps. That’s what got this current fire really going (note: I made a page that’s driven by a simple spreadsheet that has items and price estimates in one sheet and work-order quotes in another sheet. He calls up the page and sees checkboxes for every item in that first sheet. He checks whatever makes sense and hits submit. He’s then shown a cost estimate where he can add notes (like customer name, etc) and hit submit again to save it in the second sheet).

Ok, so here’s what I’m working on and wondering about: Could I use google apps script web apps on some some small-scale full stack problems I’ve been working on? I do a lot of PHP/Laravel/MYSQL/LAMP/Javascript/CSS full stack programming, but it’s often overkill for a simple thing (like my brother’s problem). I do Laravel instead of python/Django, Ruby on Rails, or Meteor mostly because it’s easiest to get the sys admins at my institution to support PHP. Whatever, they all have basically the same functionality (and the same rabid fan bases). So I know how to do fully-functional database-driven web sites. That’s not my problem. Instead I’m interested in GAS web apps because they offer an intriguing list of opportunities:

  • No server to set up. It’s just google
    • baked in reliability etc
  • Super easy authentication/authorization. It’s already built in to the google ecosystem
  • The data layer looks and acts like a spreadsheet
    • Note that google sheets are promoted as spreadsheets but they’re really quite powerful due to “query” (see below) and the interconnectedness with all the other google stuff
    • End users are way more willing to engage with a data layer that looks like a spreadsheet than a mysql database. Take my brother, for example. I didn’t have to make a front-end script to allow him to change his price list. He’s perfectly happy to do that right in the spreadsheet
  • emailing is easy.
    • In Laravel, for example, you have to set up the right package, turn on SMTP stuff, and make sure you’re not pissing off your sys admins
  • Single page apps
    • I’m not actually sold on this, but I notice it in the PR sites I’ve been perusing. Basically you can just load one site and then interact with the server to change portions of the page. I did a ton of this with my “myTurnNow” app that lets up to 100 people engage with each other without having to raise their hands. But it sure it easier to use old fashioned “submit” buttons with multiple pages (yes, Meteor users, I know, I know, . . . shut up!)

So I decided to write this blog post not so much as a “how you do it” as “should I do it?” Most of those points above are interesting, but maybe I shouldn’t be so afraid to just fire up a fresh Laravel app and do even little stuff.

Here’s some downsides:

  • It’s kind of slow. You are having the script access a google drive doc and do stuff. That access is what’s seemingly pretty slow. If you just do non-data-layer stuff it’s pretty quick, but it’s noticeable so I thought I’d mention it.
  • Really playing with the data almost always requires running google sheets formulas. You don’t have to do this. Most of the web sites suggest just sucking all the data in and dealing with it in javascript. I think that’s fine unless you think the data’s going to scale a little. If you google “use google apps script to run sheets formulas” you’ll see a few “impossibles” in your results, but don’t despair! You can do a very dumb sounding thing:
    • Create a new sheet programmatically
    • Set the top left cell of the new sheet to something like “=query(mycoolsheet!A:E,  \”select max(A), B group by B\”)”
    • Read any data on that sheet into javascript/GAS
    • Delete the sheet
  • Ok, yes, I know, that seems really dumb. But I’ve done it a bunch now and it seems to work. It gives you access to the fantastically useful “query” formula and it dramatically reduces the amount of data you’d suck into javascript. Also you don’t have do basically rewrite your favorite spreadsheet formulas into javascript.
  • Weird urls: these are crazy looking but who cares (tinyurl exists, after all)

My current project: I need to write a bunch of reviews for a bunch of folks (I am in the dean’s office these days, after all). I want to be able to access both the formal stuff I’ve written and any notes that I have for this year and all years for every person I’m reviewing. I could do this in a heartbeat (ok, a day) in Laravel, but then I’m the forever owner, even when I’m out of the dean’s office. Doing this in GAS seemed like a fun project and, if it’s successful, I can just transfer ownership to someone else.

I’ve got it working, after lots of fits and starts, and now I’m writing to you, dear reader, to find out if it’s worth exploring more and putting this tool in my tool chest.

So what do you think? Here’s some starters for you:

  • Laravel sucks. If you’re not doing Ruby you’re just dumb
  • This is really interesting. What’s the learning curve like?
  • Laravel sucks. If you’re not doing Django you’re just dumb.
  • I really like the ______ aspect of this. Do you think that you could also _______?
  • Laravel sucks. If you’re not using Meteor you’re just dumb.
  • Tell me more about myTurnNow, that sounds really useful
  • Laravel sucks. If you’re not using carrier pigeons you’re just dumb.
  • I’ve used GAS and have come to the conclusion that . . .
  • You never explicitly said that GAS was google apps script so I stopped reading. You suck.

About Andy Rundquist

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

6 Responses to Google Apps Script full stack?

  1. bretbenesh says:

    This is really interesting. What’s the learning curve like?

    • Andy "SuperFly" Rundquist says:

      It’s kind of a hassle because the standard references are organized by (object-oriented) class so you constantly have to dig around. But the lack of overhead (setting up a server, etc) balances that pretty well.

  2. tjohnsen8 says:

    I like the idea of using GAS to handle authentication, security and data storage in sheets. Do you know the details of the implementation on the Google side? Are they spinning up a container each time the script is triggered? How does data access work on the same spreadsheet if a script is triggered twice very close together?

    • Andy "SuperFly" Rundquist says:

      I’m dying to know how that works as well. I’ve only tested things that don’t suffer from that possibility.

  3. Pingback: Google Apps Script Physics Problem Database | SuperFly Physics

Leave a Reply

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

You are commenting using your 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