In Part I of the tutorial, we handled very basic data retrieval from our "database", i.e., spreadsheet. Today, we will build a basic "REST-ish" API to access it. It will not adhere to all the rules of REST because of some Google-Apps-Script limitations, but it will be intuitive enough for anyone who has ever worked with REST. It will also be very simplistic, since the point of the tutorial is to give you a basic understanding of how this could easily be done, rather than building a production-ready app.
Before we delve into the not-so-gory details, we need to improve some details regarding our script from Part I.
First, having our script inextricably bound to our spreadsheet file is a bad idea. Thus, go to
https://script.google.com/ and start a new project there. This project will replace our previous file-bound script.
Name the project whatever you want. Then delete Code.gs and create the following files:
Before we continue, keep in mind, that you do not need to import the various files in one another. All the variables and functions you declare in all files share the same global scope, so be careful you do not overwrite any of them accidentally.
Note, everything is wrapped in functions, because, otherwise, the order of files influences the availability of variables, and we do not want to have to keep track of that at this time.
Now, the fun begins!
This file will, as you may have expected hold our "environment variables" and general config. Mine looks as follows:
function Config() {
spreadsheetId = '1RhG_TiNYXmZFqk7ocep9a9SGEcu7hg6etzAxi_XErhA'
spreadsheet = SpreadsheetApp.openById(this.spreadsheetId)
activeSheet = this.spreadsheet.getSheetByName('Sheet1')
return {
spreadsheetId: spreadsheetId,
spreadsheet: spreadsheet,
activeSheet: activeSheet
}
}
Nothing new or complicated here. Just make sure you set the spreadsheetId to the same one you used in Part I (or create a new copy thereof and set it to that).
This is where it gets interesting. Here is the beginning of the code. Let us go through it line by line:
function doGet(e) {
var result = route('get', e.parameter['q'])
return respond(JSON.stringify(result, 0, 2))
}
function respond(response) {
return ContentService
.createTextOutput(response)
.setMimeType(ContentService.MimeType.JSON)
}
GAS Web Apps support only GET and POST requests. When one of those is received, either the doGet(e) or doPost(e) function is executed. You can find a full property list of the event e here, along with a short general explanation of GAS Web Apps and how to make them happen.
Our first function, doGet(e) receives the GET request, calls the router's route function with the request type and the first query parameter under the key q (e.parameters contains an array for each key, while e.parameter contains only the first element of a potential multi-element key). It then JSONifies the result and passes it to respond(). The latter then fires up ContentService, which serves a TextOutput back to the client.
The most interesting is the route() function, however.
function route(method, path, data) {
if (!path) return { error: 'You will need to be a bit more specific...' }
var routingPath = path.split('/')
var routes = Routes()
if (!routes[routingPath[0]]) return { error: 'That route does not exist', routingPath: routingPath }
try {
return routes[routingPath[0]][method](routingPath[1], {data: data, path: path })
} catch(e) {
return e
}
}
This is the rudimentary router we will use to handle requests.
route() expects path to be something along the lines of https://script.google.com/.../exec?q=<REST-style-path>.
In our specific example, the REST-style-path should be something like priceList or priceList/a1, or priceList/a1:b3.
The function starts out with a simple check for q's existence, then splits the path and checks if we have any routes defined (in routes.gs) for whatever the first part of our desired route is (i.e. priceList in our case).
If it exists, we then call the priceList service, which we can find defined in svc_priceList.gs and referenced in routes.gs.
We will get to the service in a minute. First, we need to take a look at routes.gs. The route definition file is really simple.
function Routes() {
var priceListService = PriceListService()
return {
priceList: {
get: priceListService.get
}
}
}
Not much to explain here. A simple object with the routes defined inside.
And this gets us to the priceListService, i.e. svc_priceList.gs.
function PriceListService() {
var priceListRepository = PriceListRepository()
return {
get: function(id, data) {
return !id
? priceListRepository.get(null, data)
: priceListRepository.get(id, data)
}
}
}
The priceListService is the place you want to stick your business logic. Since we do not have any at the moment, it simply defines a get function and then passes the arguments onwards to the repository in a format that depends on the existence of the id argument — essentially, this is a get all vs get many vs get one decision.
And once the decision is made, we have to look at our final layer, the repo_priceList.gs, i.e. the priceListRepository.
function PriceListRepository() {
var activeSheet = Config().activeSheet
return {
get: function(id) {
return id
? activeSheet.getRange(id).getValues()
: activeSheet.getRange('a1:b')
.getValues()
.filter(function(row) { return !!row[0] })
}
}
}
Another simple file that holds the data CRUD logic. For now, it only contains the option to get data based on the presence of an id, which can be either null, a single cell in a1notation, e.g. a1, or a range in a1notation, e.g. a1:b4.
If the id exists, it will return the desired cell or range.
If the id does not exist, the function will return the complete A and B columns, filtered for empty rows.
And that is pretty much all there is to writing a REST-ish API for GAS. Of course, it is not an ideal system, but with a bit of imagination, you can do plenty.
All that is left for today is for you to click Publish -> Deploy as web app, set the desired values and click Deploy.
You will get the URL for the web app. To pass query parameters, simply add ?q= after /exec or /dev of that URL.
You may have to give the app some permissions, for which you will be prompted.
I will leave the implementation of doPost(e) to your imagination, but with the mini-framework we built, it should be trivial. Check this page to see what kind of parameters you get with the POST request.
This is all, for now. In Part III, we will cover our first cross-app integration, however, so make sure you check back soon.
Merry Coding!