gistfile1.md

layout	title	date	tags	categories	comments
post	Using Google Apps Scripts for a simple backend	2016-01-30 18:00:00 +0100	google apps go	go google	true

I'm not that much of a frontend guy, but I was encouraged by the technology pace used in browsers to play with it. My goal was simple - make a website that imitates a native app. I didn't have any brilliant ideas at the time therefore my choice fell on a simple student app for my school featuring:

• Timetable based on group (the original plan is in excel format)
• Lucky number - every student in a group has it's number assigned based on alphabetical order of his name and every day a number is drawn resulting in some protection from lack of homework and small unannounced exams
• News scraped from school's website rss
• Teachers quotes - students have made a facebook page which stores funny teacher statements

I needed a backend for it, and it's what this article is all about.

Finished product can be found here: vlo.maciekmm.net, your eyes may hurt from viewing this on desktop thus I encourage you to view it either on your mobile phone or shrink the viewport in developer console of some sort.

Making choices

I didn't want to use technology I know well. The biggest wrinkle was parsing the timetable. I was googling about parsing xls files and stumbled upon google sheet and scripts. My question was how am I going to extract parsed data, the answer was simple: Apps Script Execution API. It's free and lets you execute any script you create. That meant I could use forms to enter lucky-number (it's drawn by hand every morning so I can't do much about it) so I didn't have to waste time on creating UI and authentication.

Disclaimer

Google Apps Script are not guaranteed to have a constant uptime and low access time. Therefore it's not recommended to use it in production environment for serious projects.

Writing scripts

First and foremost you need to create a Google Apps Script project. You can do this by going to your google drive, connecting Google Apps Script in New -> More -> Connect more apps tab and creating an apps script afterwards like any other document.

All scripts I've created for the student app are located on project's github repository.

I've picked a lucky number one, as it's fairly straight-forward and has a dirty workaround for form authentication. I simply made an additional validation field (Do not name it password though, that will result in banning your form automatically).

var password = "";
//Google forms can't be protected, so we decided to add additional password field
var luckyNumber = SpreadsheetApp.openById("10RW_TNyLvqrueEiBxcmob4SbJEsJU9S5UWpG6Tj6a1I").getSheets()[0];

/**
 * Gets latest lucky number
 **/
function getLuckyNumber() {
  //timestamp,number,password
  for (var i = luckyNumber.getLastRow(); i > 0; i--) {
    var range = luckyNumber.getRange(i, 1, 1, 3).getValues();
    if (range[0][2] != password) {
      continue;
    }
    return JSON.stringify({
      date: new Date(range[0][0]).getTime(),
      number: parseInt(range[0][1])
    });
  }
}

As you can see the script is pretty straight-forward:

• It gets the form answer sheet
• Loops through answers from the end and picks the latest one with correct password there is
• Takes the timestamp and value of the form submitted and encodes it to JSON string

The spreadsheet reference can be found here

Finish up by clicking Publish -> Deploy as API executable, make sure to choose Only myself in Who has access to the script, specify the version and click Update. Copy the Current API ID as it will be referenced in the next section of this article.

Proxifying requests

Executing any function in our script requires us to be authenticated. To do that we will some kind of a authentication proxy.

I went with go and created pretty simple app based on the example found in execution api docs.

It provides a simple API for registering endpoints which are essentially script functions. Function output is redirected to endpoint. It also supports parameters.

Running aforementioned proxy requires client credentials which can be generated on developers console of our script project.