Intro
Google Apps Script is a powerful development platform that let you execute custom code in Google’s infrastructure. It main strengths are the tight integration with Google Apps services, the built-in security features and the straightforward developer environment and one click deployment. It started as a scripting tool for spreadsheets similar to macros but with the most recent additions it has become a full fledge Platform as a Service (PaaS).
Many of the enterprise applications tend to be very form centric doing CRUD operations most of the time, I figured it would be interesting to have the Apps Script version of the Django framework reference tutorial: the Polls web app. Doing this will help us understand how to write a complete Web application from the UI to the database.
The poll app is very very simple, it’s like a CRUD hello world. The domain model is made of a Poll class (a question) which have associated many Choices (or possible answers for the poll’s question), the user is presented with the available polls list when he click in one of them is redirected to the poll detail with the different choices as options, the users vote for the best choice and finally the results are presented, number of votes for each one of the choices.
Libraries and Versions
It is a good software practice in general to clearly separate concerns and responsibilities in different layers and in different modules, each one specializes in a certain function. It also helps the maintainability of the code when the application becomes bigger and more complex. Apps script applications are no different, but until recently you had to pack all your code in one big file, with the introduction of libraries you can reuse your code by modularizing it in different libraries (similar to JAR files in Java). Each library will have a version, so the library developer can release many different updates without breaking others code.
We’ll start the tutorial by defining the project layout in the following figure, where we will keep separate the presentation (views and templates) from the business logic (model and services) from the persistence, which in our case will be powered by the built-in ScriptDB JSON database.
It’s very easy to save a project as a library and also import it in another project. First you have to save a version of the project going to “File” -> “Manage Versions” then go to “File” -> “Project properties...” and copy the project key, a string that serve as the unique identifier for every apps script project. In the other project, go to “Resources” -> “Manage Libraries” and paste the previously copied key, you can select the right version and assign a string identifier which will be the one you use to invoke the library methods (you’ll see in the next section).
For now, let’s define our data model, as you can guess is very simple:
models.gs
function Poll(question, pubDate) {
// text of the question
this.question = question;
// date/time of publishing
this.pubDate = pubDate.getTime();
this.type = 'poll';
}
function Choice(pollId, choice, votes) {
// string generated unique id of the poll
this.pollId = pollId;
// text of the choice
this.choice = choice;
// integer number of votes
this.votes = votes;
this.type = 'choice';
}
ScriptDB
ScriptDB is a JSON objects database that supply all the efficient storage and query needs for our Apps Script app. Note that there is a field “type” in both entities of the models, that’s because ScriptDB will persist JSON objects without having the notion of type, table nor class, in other words, there is no enforced schema here. Querying, Inserting, updating and deleting are done with the ScriptDBInstance API. Query are done “by example” that means by giving an object filled with the query criteria.
As our project structure diagram shows, all the interaction with ScriptDB will be done by the service component, which will provide an API for retrieving and manipulating the data model objects.
services.gs
/**
* Get all the available polls
*
* @return {Result} the result set that contains the poll objects
*/
function getPolls() {
var db = ScriptDb.getMyDb();
return db.query({type: 'poll'});
}
/**
* Get the poll object for the given poll id
*
* @param {string} string unique id of the poll
* @return {Poll} the stored poll object
*/
function getPollById(pollId) {
var db = ScriptDb.getMyDb();
return db.load(pollId);
}
/**
* Get the choices associated with the given poll id
*
* @param {string} string unique id of the poll
* @return {Result} the result set that contains the choice objects
*/
function getPollChoices(pollId) {
var db = ScriptDb.getMyDb();
return db.query({type: 'choice', pollId: pollId});
}
/**
* Given the choice id retrieves the object from the DB and increment by one the vote attribute, finally saves
*
* @param {string} string unique id of the choice
*/
function recordVote(choiceId) {
var db = ScriptDb.getMyDb();
var choice = db.load(choiceId);
choice.votes++;
db.save(choice);
}
HTMLService
So we have the data model, and the services layer to make operations using the data model, but how do we display this to the user? HTMLService provide a way to render HTML by merging templates with data generated by scripts. There are two important methods in the view, doGet and doPost they are the initial point depending on the HTTP action that we are trying to make, since in our app we have only been assigned with one and only one URL path for the whole application, we need to make use of a special parameters to route and dispatch the view function to use.
views.gs
function doGet(e) {
Logger.log('Polls application reached, accessing with parameters = ' + e.queryString);
var evaluated = undefined;
if(!e.parameter || !e.parameter.view || e.parameter.view == 'index'){
Logger.log('Either no parameters, no view param or view equals index');
evaluated = getIndex();
} else if(e.parameter.view == 'detail'){
evaluated = getDetail(e.parameter.poll);
} else if(e.parameter.view == 'results'){
evaluated = getResults(e.parameter.poll);
} else {
Logger.log('Error, undefined view');
evaluated = undefined;
throw new Error('Error, undefined view');
}
return evaluated;
}
function doPost(e) {
Logger.log('Polls form submitted with vote for [' + e.parameter.choice + '] in poll [' + e.parameter.pollId + ']');
PollService.recordVote(e.parameter.choice);
return getResults(e.parameter.pollId);
}
function getIndex(){
Logger.log('Index view: returning all available polls.');
var t = HtmlService.createTemplateFromFile('index');
t.polls = PollService.getPolls();
t.serviceUrl = getServiceUrl();
return t.evaluate();
}
function getDetail(pollId){
Logger.log('Detail view: returning poll with id = ' + pollId);
var t = HtmlService.createTemplateFromFile('detail');
t.serviceUrl = getServiceUrl();
t.poll = PollService.getPollById(pollId);
t.choices = PollService.getPollChoices(pollId);
return t.evaluate();
}
function getResults(pollId){
Logger.log('Results view: returning poll');
var t = HtmlService.createTemplateFromFile('results');
t.serviceUrl = getServiceUrl();
t.poll = PollService.getPollById(pollId);
t.choices = PollService.getPollChoices(pollId);
return t.evaluate();
}
index.html
<html>
<? if (polls.getSize() > 0) { ?>
<h1>Polls</h1>
<ul>
<? while (polls.hasNext()) {
var poll = polls.next(); ?>
<li><a href="<?= serviceUrl ?>/?view=detail&poll=<?= poll.getId() ?>"><?= poll.question ?></a></li>
<? } ?>
</ul>
<? } else { ?>
<p>No polls are available.</p>
<? } ?>
</html>
detail.html
<html>
<h1><?= poll.question ?></h1>
<form action="<?= serviceUrl ?>" method="post">
<label>Choices:</label>
<ul>
<? while (choices.hasNext()) {
var choice = choices.next(); ?>
<li><label><input type="radio" value="<?= choice.getId() ?>" name="choice" /> <?= choice.choice ?></label></li>
<? } ?>
</ul>
<input type="hidden" value="<?= poll.getId() ?>" name="pollId" />
<input type="submit" value="Vote"/>
</form>
<a href="<?= serviceUrl ?>/?view=index">Back to poll list</a>
</html>
results.html
<html>
<h1><?= poll.question ?></h1>
<ul>
<? while (choices.hasNext()) {
var choice = choices.next(); ?>
<li><?= choice.choice ?> -- <?= choice.votes ?> votes</li>
<? } ?>
</ul>
<a href="<?= serviceUrl ?>/?view=detail&poll=<?= poll.getId() ?>">Vote again?</a><br/>
<a href="<?= serviceUrl ?>/?view=index">Back to poll list</a>
</html>
Deployment and Script Properties
Deployment in this platform is dead simple. First we have to publish a version of the views project (as we did with the library) and then click on “Publish” -> “Deploy as webapp...” this will ask you who will have access to the published app and who will the script be running as. Copy the generated URL and create a new script property “File” -> “Project Properties”-> “Script Properties” called serviceUrl, paste the URL as the value. In our code we need to pass this URL as a variable in each one of the views so add the constant and the getServiceUrl helper method to the views file.
views.gs
/**
* Key of ScriptProperties for saving the app URL.
* @type {String}
* @const
*/
var SERVICE_URL_PROPERTY_NAME = "serviceUrl";
/**
* @return String URL where this script is published as a service.
*/
function getServiceUrl() {
var url = ScriptProperties.getProperty(SERVICE_URL_PROPERTY_NAME);
if (url == null) {
url = "";
}
return url;
}
Deploy again and our app is ready for taking some votes.
If you want to see the source code it is published in two separate AppsScript projects:
- Poll Service https://script.google.com/d/1dUVPFK5t4n6N9k6Vn1kpNDdt7osJcynMcYTU7xTyMzxCYB1VBYtUC2hW/edit
- Poll Views https://script.google.com/d/1Cu1EVCWiVG6JOVF-flzaK0KCtYSj1F8VQ_OksdZ1RPimv2Ro6lt6ZjbJ/edit
The public URL for reaching this app is https://script.google.com/macros/s/AKfycbyR7fAgok_oFUxLhsX9N576sBiDbU7ocMo-D-w8c1nENKhoflrx/exec
Findings and Next Steps
Even though we have all the components and successfully built this simple but fully functional webapp, there is a need for a more comprehensive application framework, that takes care of MVC concepts, URL handling and dispatching, forms generation and validation, etc. If you have previously used Zend, or Spring or Django you’ll know what I mean.
Our app is clearly not complete at all, we’ve just made the user interactions but there should be an admin interface for managing the polls and the choices, that means we should have an ACL where only certain users (e.g admins) have permissions to do only certain actions. We could also record who made the voting when, in case of latter auditing needs. In the graphic design side our app is very raw, we could add some styles and graphics.
References
Development Manager
Eforcers S.A.
