Apps Script Developer Chat: Andrew Stillman

Uploaded by GoogleDevelopers on 31.08.2012


MALE SPEAKER: Shanghai GDG is a very
interesting developer community.
FEMALE SPEAKER: I'm glad somebody
has asked this question.
MALE SPEAKER: This is where the magic happens.
FEMALE SPEAKER: This is primarily a question and
answer show, so if any of you out there
would like to ask questions.

JAN KLEINERT: Hi there, I'm Jan from the Apps Script
Developer Relations team, and this is Developer Chat, the
first one that we've been doing of this.
We have Andrew Stillman with us today.
I'd like to introduce him a little bit, and thanks for
joining us.
So, Andrew provides instructional design,
technology support, project management, planning services,
execution support, all sorts of other things for
implementing blended learning strategies across multiple
schools as part of his job at New Visions for Public Schools
in New York City.
He works across multiple schools there, and one of his
primary accomplishments at New Visions has been the
conception, creation, and growth of UPD, which is an
ecosystem for teachers to share creative solutions to
problems that they face and to build things called hacks,
which I think are basically the solutions to those
problems so they can share solutions and promote things
through challenges and participate and present at
professional meetups across the city.
So he also works on developing scripts that can be used for
educators who are Google Apps for EDU customers as well, and
that's part of his work with New Visions' Cloud Lab.
So Andrew, thanks for joining us.
ANDREW STILLMAN: Thanks for having me.
JAN KLEINERT: We're really excited to do this.
I also want to mention my favorite part.
Andrew is also one of the probably most prolific
developers, publishers in the Apps Script script gallery.
You've written many of our most popular scripts that are
in the gallery, things like autoCrat, Doctopus, which is
my favorite name, and FormMule, which are really
useful scripts that we're seeing a lot of uptake with.
So I wanted to start by asking you how you got involved with
Apps Script.
So what prompted you to start using it?
ANDREW STILLMAN: Really, I credit most of this work, in
fact, most of the inspiration for this work, to one of our
most high achieving principals, a man by the name
of Mark Dunetz, who is at a school called the Academy for
Careers in Television and Film.
But he and his planning team started the school four years
ago, and they came at the whole planning process through
the use of Google products and just found that the
collaboration environment they were in ended up being one
layer after the next, the best way to solve a lot of their
planning and systems problems in school.
And so like layers of an onion, they just kept building
and sharing spreadsheets and forms and various systems
around everything from purchasing to tracking student
behavior issues to keeping a live copy of their online
grade book and mashing it all up, integrating it, and
basically creating their own custom
dashboards and work flows.
And so he was using Apps Script in a rudimentary way
just to run basic document and email merges off of triggers,
usually form triggers.
And I came along, and then he pushed my organization to say,
look, more schools should be doing this.
We're getting results here.
And his school has gotten insanely good results.
They just graduated their first cohort, and they're at a
97% four-year graduation rate, which is 30-plus points higher
than the city average.
And they're a non-selective New York City public high
school, which means their student population is very
much like the rest of the city's.
And so it's just an off-the-charts achievement.
And so Mark pushed us to support the work, which meant
that I had to spend some late nights learning.
And initially, we had subcontracted someone to do
some scripting for us.
And I don't have a coding background, or not a
particularly strong one, and so initially, I was hesitant,
and I just assumed we needed to find a professional
developer to do the work.
And gradually, I started hacking away, and I had a run
in with some folks on the Docs engineering team.
And they were like, dude, you can learn this.
Do it.
And so I started writing scripts, and it blew my mind
how quickly I could be up and running with things and just
how fast the development cycle was, particularly with the
code editor.
It was the first time I had a code editor that was saving
all my revisions, letting me debug in a very clear cut way,
and so it just transformed my own learning process.
And so suddenly, I was producing like the first
really robust document merge utility for Google Docs, and I
had fan mail.
And it was just an unexpected journey that just came about
through a demanding and successful principal who
pushed me to figure out how to make these things better.
So a lot of my challenge has been, ultimately--
like New Visions supports 76 New York
City public high schools.
So if we're going to take the esoteric script-based
workflows that exist in one school and move them into
other schools, we have to think about how to make them
as user friendly as possible.
And so my first step was to really learn UI App and figure
out how to put everything a menu.
So if something needed to be configured or a document
needed to be connected to the script or whatever, there was
no hard-coded dependencies.
And so I quickly got into trying to engineer Apps Script
solutions that would be accessible to nontechnical
users, and that was the motivation for the design of
most of the scripts I've developed.
JAN KLEINERT: And that's one of the things that strikes me
when I try your scripts.
I find that they are easy to set up and use.
You don't expect your users to be going into the code editor
and changing values and setting it up that way.
You have these nice, menu-driven, almost like a
Wizard-driven setup flows, which, I think, make life a
lot easier for your users.
Even though it's probably more work for you as the developer
to put that system into place, I think it's pretty helpful.
ANDREW STILLMAN: Yeah, I mean, I'm just not going to get
adoption, at least not by the people that I want adopting
the scripts, unless I put it in a
drop-down menu or a widget.
So, yeah, and initially, I was using ScriptProperties mostly.
We started using ScriptDb a bit more for storing, but just
figuring out how to store values and
have persistent memory.
We're also now using UserProperties a fair amount,
because we're interested in actually having analytics on
usage by school.
So those have been really important things to learn.

So can you tell us a little bit about some of these
scripts that you've created?
ANDREW STILLMAN: Sure, yeah, and maybe a screenshare at
this point would make sense.
I want to show some of these what I would call modular
building blocks.
We've tried to think about--
I love LEGOs.
I feel like most good softwarers like LEGOs.
So just the idea that we have these flexible core building
blocks, two of which are merge utilities.
So anytime you see this mule icon, which I'll cut away to a
version-- oh, wrong one.
that's the autoCrat.
So this mule, which kicks and neighs and what not, is just
an animated GIF.
But this is the formMule, and this script is responsible for
taking form data on a trigger, or spreadsheet data, and
generating merged emails.
And there's a bunch of email merge utilities already in the
script gallery.
But this one, in particular, has the UI to configure it in
lots of different ways.
And currently it can send up to three templated emails.
The templates are totally configurable.
They can be sent on conditions.
And so ultimately, this becomes a
really important staple.
The other thing this does is it will create a merge
calendar event on a calendar ID of your choosing.
So if you want to have a combination of an email sent
and an event created, it can let you do that.
The last thing this script does that ends up being really
important is that it lets you select columns in which, when
the form data comes in, a formula can be copied down
from row to row automatically.
And what's powerful about that is that it means that your
forms can have a validated drop-downs of items that then
get looked up in another sheet.
And that little stitch ends up making any Google Form into
something that behaves much more like the front end of a
relational database, so things like student name, teacher
name, class period, anything that is a validated piece of
information that might have things looked up on.
So, student email, teacher email, start time with period,
end time with period, those kinds of things can be
referenced in secondary sheets that are running in the same
spreadsheet as the form.
And so what formMule does is it allows you to copy those
values down from row to row every time a form trigger
runs, and it means then that your merged emails suddenly
take on a whole new power.
Because you can email particular people based on
incident type, or you can only trigger a calendar event when
a particular drop-down is chosen in the form.
It just radically changes the power of what a
Google Form can do.
So that's one of the scripts.
The other big merge script is a document merge utility
called autoCrat.
And the reason it's called the autoCrat is so you don't have
to be the bureaucrat.
But that is Stalin's mustache that's twitching
around on the autoCrat.
And those little tags that you see on his chest are the
notation that we use for the merge tags
in a document template.
So this is a Google document merge utility.
It basically produces either a PDF or a Google Doc.
It merges in rows of data.
It merges into the header, the footer, the body.
It preserves formatting.
It's fairly robust, and the field mapping is all
accomplished through a UI.
So when I'm storing field mappings in a JSON string, and
then those settings are persistent, and it just works.
It also brings in sharing, so you can, in the process of
merging a document, also merge in Collaborators and also
organize the document in a folder of your choosing.
So it means the schools can now do things like have a
mastered lesson plan generator where lesson plans are
deposited in a lesson library for the school, pre-shared
with the teacher, and yadda, yadda, yadda.
So this is the second big building block.
I'm just going to cut away to this workflow.
This is an example system.
This is one of the more sophisticated systems that
schools I work with are playing with.
And again, these are designed as part of an initiative
called the Cloud Lab, which was something we just launched
this summer.
But it's essentially engaging schools as design partners in
systems rethinking.
And I think for the first time in the history of software, we
actually have enough flexibility and usability in
the basic systems building blocks that school level
people can actually make these things.
JAN KLEINERT: Well, that's interesting.
So the people that are working with these building blocks
that you've put together are not programmers.
They're teachers who just have an interest in making their
lives a little less complicated.
ANDREW STILLMAN: They're power users at the
school level, right?
So they're going to the script gallery saying, I've got a
Google Form that I want to have generate a document.
The secretaries need to have a way to generate a lateness
letter, blah, blah, blah.
There's a million and one different systems in schools
that there are no essentially provided systems for.
And a lot of the essentially provided systems, especially
in New York, are antiquated.
So many schools operate with tremendous inefficiencies.
And as soon as they start to see what these building blocks
can do, it opens up 50,000 different things that they
suddenly are like, why are we using a triplicate form with
carbon copy?
They just start cobbling these things together.
So this is a workflow that shows the logic for what's
called a conduct intervention communication log, which is a
catcall form for logging everything from school home
attendance outreach calls, to behavior incidents, to parent
teacher conferences, to whatever.
So it's like an anecdotal capture point.
And then it has all these emails triggers.
So we have all these conditionals built that have
to do with, do you want to notify the kid's adviser?
An adviser is usually, in most small New York City schools,
the point person for most social
emotional issues for kids.
So keeping that person in the loop is really important.
This radically changes the way you can do that.
JAN KLEINERT: So I have a question for you about this.
So prior to having a script-based solution like
this, the script in Docs and all of this workflow, how was
this process being managed?
Or was it being managed?
ANDREW STILLMAN: Prior to scripts?
JAN KLEINERT: Yeah, what was happening before?
ANDREW STILLMAN: It was a mess.
Oftentimes, it just wasn't happening.

If I have a behavior incident with a child in one of my
classes and I want the adviser to know about it, it would
mean often physically finding that person in the midst of a
busy school day or sending them an individual email which
then gets lost in an inbox somewhere and that doesn't
have a clear counting attached to it.
So there was no centralized way for anyone to look at a
single student and understand a history of interactions and
a history of home contacts that might have happened.
It'd be like calling your credit card company and having
them not know anything about you, whereas in most other
industries, you have a persistence and a coherence of
just information management.
And this kind of stuff just doesn't exist in public
schools for the most part.
So this really changes the underlying fabric of how a lot
of these schools are able to work.
So this one seems to use not just autoCrat and formMule,
but another one as well.
ANDREW STILLMAN: Yes, the pushData.
JAN KLEINERT: My favorite icon.
ANDREW STILLMAN: So we need to show off
that icon for a second.
So pushData uses a hamster because one of my favorite
things about Apps Script is it works almost at the speed of a
human being.
It literally looks like a screen macro running
when you see it.
And so this script, in particular, it does
essentially the job of ImportRange.
ImportRange is a function that's unique to Google
Spreadsheets that lets you bring a specified range of
data into another spreadsheet just using
its spreadsheet key.
But it's limited in terms of the number of rows of data it
can handle, and it can really slow down your spreadsheets if
you're working with larger than 2,000
rows of data or something.
So pushData is a replacement for that that
pushes on a time trigger.
And it lets you choose a source and a destination
sheet, and it becomes a console for mapping all the
relationships between spreadsheets in a root folder.
So if you have a folder for a system that has, let's say, 10
or 15 different spreadsheets involved, I could maintain,
for example, a master student list in one location, and then
I could have certain columns of that pushed out to as many
other locations as I want on a regular time trigger.
So it then allows you to do things like VLOOKUPs on
student name in 50 different forms.
You could have as many different systems as you want
that are all keyed on the same up-to-date, synchronized
master list.
So it in some ways is a substitute for having a true
relational database.
School-level people are not going to be able to set up a
relational database.
They need a flexible spreadsheet environment.
JAN KLEINERT: Something where they don't have to worry about
hosting and all of that.
Right, so this is an underlying piece of
infrastructure for that.
So in this system here, pushData is syncing the master
student list and the master teacher list with these two
other spreadsheets, one of which is the back end to the
actual form, and then the other which is an attendance
tracker, which basically is in this case allowing the school
to easily see on a one-to-one basis whether a phone call
home was made by the advisers for any given the absence.
So it lets you really maintain a lot of transparency around
what kind of outreach is happening around attendance.
So anyway, really, what is that?
Three scripts that do all this.
JAN KLEINERT: That's fantastic.
So do you find that there have been any particular challenges
in getting teachers or schools to adopt
these types of workflows?
Or do you find that once they see one working, they kind of
get the value right away?
What are some of the challenges, I guess?
ANDREW STILLMAN: Yeah, some of the challenges have been--
I mean, initially when we were using these scripts--

make a copy of a Google spreadsheet that
has a script in it.
And the script comes with it.
And that's great.
But these scripts are all dependent on
ScriptProperties being set.
ANDREW STILLMAN: And the configuration of these scripts
is not obvious to--
I mean, even though it's run through a UI, you have to
figure out what it's even asking you.
Like what is a field mapping?
Your average school employee, there's a learning curve.
ANDREW STILLMAN: One of the things we had to overcome was
the fact that ScriptProperties don't copy with scripts.
ANDREW STILLMAN: And so, I developed a way to get the
ScriptProperties to output as a code string, essentially,
that could then be pasted back into a function that would run
on install.
JAN KLEINERT: Oh, interesting.
ANDREW STILLMAN: These are self-configuring scripts.
So once someone takes the three or four hours to build
one of these systems, it can then be packaged in such a way
that the next person who comes along and has to set up that
system has a copy doesn't have to do any of the configuration
steps in the scripts.
The scripts are preconfigured.
So that was a really important implementation shift that we
had to make.
The other has been in preserving relationships
between spreadsheets.
Like many of these systems require multiple spreadsheets.
So you've got to have pushData sending things across
And any dependency on document ID was
creating a huge problem.
Because you could go to set up the new system, and everything
would be pointing to the old system.
JAN KLEINERT: Right, I see what you're saying.

ANDREW STILLMAN: In creating pushData, what I did was I
made it refresh all of the document IDs on
install using file name.
So I was referencing the root folder that it was sitting in,
quickly going through all the files and pulling in their
document IDs so that every single mapping that exists in
pushData is valid in the new copy.
Those kinds of things ended up being really important.
And then lastly, just making the distribution of these
things really super easy.
Like if you ask someone to download six different
spreadsheets and put them in a folder, you've
already lost them.
So we built, recently something called the Cloud
Labs Systems Replicator.
It's kind of an intimidating name.
JAN KLEINERT: I like it.
ANDREW STILLMAN: I should probably change it.
But essentially, this is a private login-only web app,
which I recently learned how to make.
And it's essentially accessing a subset of
folders in my Docs list.
And these are folders that each contain a fully packaged
system, including documentation.
So I can go, for example, into After School Credit Recovery
and Attendance System, which is the system we were just
looking at.
Oh, no, this was a different system.
And you can see underneath the documentation is previewed, so
you can make a decision about whether this
system is for you.
We try to include systems diagrams, because we find
these are really helpful for less technical users to
understand, and installation instructions, so making all
this stuff abundantly clear.
And then the other thing we did is this is a single
function that copies an entire folder's
contents to your drive.
So there's no folder copy native to
Docs, as far as I know.
So when I click this, it literally will
generate the folder.
And you can see--
I'll blow this up for the sake of our users.
You'll see the system being replicated right into the
person's drive.
ANDREW STILLMAN: So this is another way that we have kind
of lowered the barrier for adoption for schools.
JAN KLEINERT: Reducing the chances of user error,
forgetting to copy one of the important pieces of the
puzzle, and things like that.
ANDREW STILLMAN: It also means that any school is now
potentially a publisher of a system.

It completely shifts the innovation.
We'll call it an ecosystem.
You no longer are dependent, necessarily, on a software
developer to do the systems thinking side of it.
You have LEGO building blocks, and then you have people on
the ground working with the kids who understand the
problems deeply and who can then design the system the way
it needs to be.
JAN KLEINERT: And then share it.
And there's no delay.
There's no six months of waiting for the issue queue to
be updated or for the big student information company to
decide that this is a priority.
The school has it tomorrow.
JAN KLEINERT: That's exciting.
ANDREW STILLMAN: Yeah, it's a lot of fun.
So we have a few minutes left, and I have one more
question for you.
But first I just want to see if there's anything on our
Moderator page.
Let's see.
This morning, we didn't have anything specifically about
this session.
It looks like we still don't.
There is a support-related question there that we'll
answer separately.
So my last question for you was what would you like to see
from Apps Script in the future?
What are some things-- oh, I see your eyes twinkling--
that would make your life easier in developing these
types of solutions?
And specifically since you specifically seem to work
mostly with educators, what in that area would make your
development life easier?

ANDREW STILLMAN: Honestly, at this point, there is much more
for me still to learn about Apps Script than
I've had time to learn.
I mean, I'm not just a developer.
So I spent a lot of my time in schools, and
I still want to learn scriptDb inside and out.
I've had the pleasure of working with some Google
Summer of Code interns this summer.
My understanding is that there's still some reliability
issues with scriptDb, not major, but in particular, when
dealing with large batches of data.
And I think there's already an issue in the tracker on this,
but it sort of severely hampers the way it works when
you can't write large numbers of rows.
A lot of what I want to be able to do is allow the user
to maintain their data set in a spreadsheet, but have a
secondary warehouse where that data is stored for longevity.
JAN KLEINERT: Interesting.
ANDREW STILLMAN: There are limits to the number of rows
in a spreadsheet.
ANDREW STILLMAN: In particular, when you start
adding formulas, and the performance of spreadsheets
degrades so that they become almost unusable beyond like, I
don't know, 6,000 or 7,000 rows if you have things like
VLOOKUP running.
JAN KLEINERT: Very complicated.
ANDREW STILLMAN: It's just impossible, yeah.
So I would really like to make sure that scriptDb can do
things like that.
And I almost feel like there needs to be a clearer library
where you can just literally just send a spreadsheet range
into ScriptDb, and pull it back--
JAN KLEINERT: I like that idea.
That's a nice one.
ANDREW STILLMAN: --without having to think too much.
And I'm sure there's already a library written to do that,
and I just don't know about it.
But I guess the other thing I would love to see is just a
ready-made HTML editor UI element.
Because a lot of what I'm having folks do is develop
things like an email merge template, or a document merge
template, or whatever.
And particularly in the email side of things, it would be
really cool if people could author HTML.
And I know this is now possible through--
I think it's through HTML service.
JAN KLEINERT: Yeah, it should technically be possible, yes.
So I would love to see proof of concept on that.
JAN KLEINERT: So like a rich text editor that you could--
ANDREW STILLMAN: Rich text editor that--
JAN KLEINERT: Widget that you could just stick in there.
ANDREW STILLMAN: Yeah. because right now, I have folks
editing email templates in a spreadsheet cell.
ANDREW STILLMAN: Which is really tricky for folks who,
even figuring out how to get a hard return in a spreadsheet
cell is not self-evident.
Because I want to allow HTML, I'm having to have them create
break tags.
And I don't want my end users to have to code in HTML to
produce their templates.
So that's a big one.
And then I think lastly we depend so
much on Google Forms.
ANDREW STILLMAN: And often, the response I get when I say
Google Forms needs to have things like range-dependent
Like hugely important for us, because we're dealing with
ever-changing student registers.
We want to be able to validate form data when it comes in.
And so we can't just have open text fields.
And to be able to preserve a 3,000-student list in such a
way that it's always exactly a match with our student
register in a school requires all kinds of ludicrous
I can imagine.
So more powerful forms and then better integration.
ANDREW STILLMAN: Yeah, I think the Forms product itself, but
then yeah, to be able to modify Google Forms through an
API would be phenomenal.
I mean, that and additional form widgets, like date, be
able to validate something like an email.
So yeah, I mean, forms are huge for us.
Every time I've talked to somebody who's even remotely
in the education world, they always mention Forms.
And wishing Forms could do x, y, or z.
So we definitely hear the--
JAN KLEINERT: Yeah, I mean, I was showing someone a Google
Form the other day.
And they were like, oh, so this could
replace Microsoft Access?
And I was like, yes, it can.
But there's a number of workarounds you have to
develop still.
And so getting rid of those would be awesome.
Well, we'll definitely pass that feedback
along to the team.
Well, thank you so much for joining us today.
And I just really appreciate you spending your time here
with us, and also everything that you do for the Apps
Script community, and for all these teachers out there.
ANDREW STILLMAN: Thanks so much for having me. it's been
a pleasure.
JAN KLEINERT: Thank you.
All right.