( Dotted . Pair ): Making your own Google Forms

Summary

For a short assignment, we wanted to replicate the basics of Google Forms without using the actual product. The assignment was to set up a form on our own server but feed the results into a Google Sheets (the name for their spreadsheet product). Another requirement was to set this up as fast a possible, so we had to take some shortcuts. We navigated a lot of new terrain and in this blog post, I will discuss our findings and the solution we arrived at.
If you want you can jump straight to An outline of the code or to the repository

Why we chose this approach

Using Google Forms

We decided to not use the standard Google Forms interface:

Forms is the standard interface to sheets, which does exactly what we want: It allows you to set up form with fields and the results will automatically be posted to a google spreadsheet. The users may have to log in to get access to the form (at your option), but the data are always saved in one central form that is owned by the creator of the form.
In other words, for writing to sheets, form creator credentials are used, not user credentials.
Forms does not support full html formatting out of the box, and does not easily allow you to run javascript functions to automatically update the page based on the user's entries.
By default, if you Submit a form, this leads to one row in the spreadsheet. We needed a form that would lead to 1..6 lines in the spreadsheet.

In retrospect, this particular assignment could probably have been solved by dropping a few of the requirements and using Google Forms after all. Little did we know… However, the solution discussed here provides you much more flexibility than a pure Google Forms one.

Google sheets API

Google Sheets has its own API, which we did not use either:

Sheets has its own legacy API, but using GAS (below) is recommended
Sheets own legacy API is not as powerful, available for Java and .Net only, requires you to install Google jars, and requires you to set up Oauth. These were all reasons to reject this for a quick solution.

GAS: Google Apps Scripts

GAS stands for Google Apps Scripts, which is a javascript environment that allows you to access all of the Google Apps (mail, docs, sheets, etc.) from the cloud.
GAS is great, but it is really its own thing with a lot of domain specific knowledge built into it.
GAS is geared towards having scripts operate on the users own documents, the case of multiple users all writing to the same sheet is supported but less well documented
GAS is really good for creating new formula operators in Sheets, or to set up fancy dashboards on top of sheets.
GAS scripts have access to a wealth of built-in functionality which you can just use, without any includes or injection. Critical to our project were PropertiesService (to store information between invocations of the script), `LockService (to prevent concurrent access to the spreadsheet), `ContentService` (to construct and format http replies).
GAS scripts can render html forms, but the support for it is rather involved (you have to write a doGet method that returns the html, as if you are a writing a web server in javascript). It is unclear to us whether you can use CSS, standard libraries and other tools for modern fast-paced development.

GAS as an API

We did not use GAS directly, for reasons outlined above. Instead we will write an application which interacts with a GAS script:

There is no set API for GAS, instead you write a javascript script that has a doGet and/or a doPost method. You publish this script and a URL is generated that functions as the external call point for your script. You can then add parameters to the GET or add a body to the POST to pass information into the GAS script.
It seems that GAS wants its POST data in the old fashioned form-data format, which requires you to do some work on the Spring side (see code below).
This may be a misunderstanding on our end, the documentation on the parameters to a external call make it sound like other post body types are possible too (csv in the example on GAS). However, this Stack Overflow posting makes it clear that x-www-form-urlencoded (aka old-fashioned forms) is the best choice and that it is the default.
GAS restricts input parameters to a few primitive javascript types, more than enough to work with but it is good to be aware of the limitations.
GAS scripts are not easy to debug, esp. when it comes to the interaction with the outside world. There is a logger but we had mixed success with it; you may also try the other options under the View menu.
GAS as an API has a few idiosyncrasies: It seems necessary to give your script a new version number every time you publish it, there is a `/dev` endpoint that you cannot use from the external world, and so on
There are subtle differences between publish as web app and 'publish as API' which are not clear to me. We ended up using the 'publish as web app' for our API, even though we only use our app as an API.
GAS does not provide any headers that say it is ok to use this API from another website (ie. it does not disable CORS with Access-Control-Allow-Origin: *). CORS is Cross Origin Resource Sharing, a browser technology that prevents scripts on host A to touch endpoints on host B, unless host B specifically allows this. Because CORS is not specifically allowed on GAS, you cannot call your GAS API from javascript or anywhere in the browser. We thought we would build a front-end only app, but we ended up building a simplistic backend just to avoid CORS.
GAS calls are very slow, they easily take up to five seconds on our state of the art machines on a fast network.

An outline of the code

You can of course just peruse the repository, but here is an overview and some details that took us longer than they should have.
In a nutshell:

We build a front end app with angular, bootstrap and ui-router to have a three page web form, much like in this blog post on scotch.io.
This front end was served by a standard Spring Boot application, with exactly one endpoint for posting data to our GAS script.
The back end was pushed out to Pivotal Web Services, our in-house CloudFoundry instance.
We used a slight variation on the GAS script provided by Martin Hawksey. The GAS script is a bit slow to run so we would like to submit all rows of data simultaneously, but for now we make multiple calls to the GAS script.

Spring project, pom.xml

Via start.spring.io, we created a standard project with test and web options.

Manifest for cloudfoundry

The repo has a manifest.yml.template file which you should rename to manifest.yml after filling in the blanks, which define where your app will run on pws and where your GAS script lives (Google will display this URL when you publish your GAS script).

---
applications:
  - name: <YOUR APP NAME HERE>
    memory: 512M
    path: target/vacationform-0.0.1-SNAPSHOT.jar
  env:
    VACATIONFORM_URL: <YOUR GAS SCRIPT URL HERE>

With this in hand, you should be able to `cf login` to your PWS account and `cf push` the code.
We did not specify a buildpack as java was automatically recognized.

Packaged files

We included the necessary files to run bootstrap, jquery, angular and angular ui-router. Style sheets for the bootstrap-theme. These files are `src/main/resources/static/`.

Main page: index.html

This is a ui-view and nothing more (well, add headers and script includes)

<body ng-app="vacation-form">
<div class="container">
    <div ui-view></div>
</div>

The steps

There are three steps, named step1.html etc with associated controllers also named step1 etc. In step 1, we create a factory that provides us with a shared data structure vacationData:

app.factory("vacationData", function() { // to share information
    var vacationData = {};    
    vacationData.name = "Michael Dirk";    
    vacationData.weeks = 0;
    //   [[ etc ]]
    return vacationData;});

Each of the steps starts by calling the factory and putting the vacationData object on the scope, as we will need it there:

app.controller("step1", function($scope, vacationData) {
    $scope.vacationData = vacationData;});

Step 1 and 2 contain forms that are linked to fields of our vacationData object. We also have a few computed fields, which are resolved in the init of the step 3.

<form>    
  <input type="text" ng-model="vacationData.name" > &nbsp; Your name

(Apologies to the bootstrap designers for the nbsp there)
You can move to the next step through a button with a symbolic state target:

<a ui-sref="step2">    
   <button class="btn btn-primary">Next Step</button>
</a>

Step 3 deals with sending the data to GAS, asking the user to wait, and displaying the result of the GAS call.

<p class="row">    
We have submitted your information for you.  
This request returned status:  {{returnStatus}}
</p>

javascript, index.js

For maximum hackiness, we put all our javascript in one file. It is a total of 70-some lines. It includes a dog standard router:

app = angular.module("vacation-form", ['ui.router']);

app.config(function($stateProvider, $urlRouterProvider) {
    $stateProvider
 .state('step1', {
     url: '/step1',            
     templateUrl: 'step1.html'        })
    // [[ stuff deleted ]]

In step 3, we have all the data and we make a call to our Spring backend:

$scope.returnStatus = "pending";
var url = "/api/setVacationPlans";
$http.post(url, vacationData).then(
    function (success) {
 $scope.returnStatus = success.data; },    
    function (failure) {
 $scope.returnStatus = failure.data; });

The endpoint on the backend

The POST endpoint takes a vacationRequest in its body:

@RequestMapping(path = "/api/setVacationPlans", 
  method = RequestMethod.POST, 
  consumes = {"application/json"})
public ResponseEntity vacationPlans(@RequestBody VacationRequest vacationRequest) {

Note the consumes argument to RequestMapping. The actual input to this endpoint is the vacationData over on the JSON side.

VacationData data structure

I have not detailed it above, but vacationData is a JSON object (aka dictionary or hashmap) with a few user details at the top level, such as the name. It has an embedded list of vacationPlan objects, each of which have dates in them. We want to generate a row in the google spreadsheet for each vacation plan that is submitted.
We rely on Jackson JSON to parse this JSON object for us. To enable this, we created a VacationRequest object, which contains a list of VacationRequest objects.

Posting to GAS from the endpoint

Continuing on the end point, we make one call to GAS for each vacation plan. First, we need to set up a RestTemplate to make the call for us. Setting this up feels unnecessarily complicated to me, but these calls are apparently necessary to make the RestTemplate ship the data we put into it with the form-field convention (which begs the question why there is no such thing as a FormFieldTemplate):

RestTemplate restTemplate = new RestTemplate();
HttpMessageConverter formHttpMessageConverter = new FormHttpMessageConverter();
HttpMessageConverter stringHttpMessageConverter = new StringHttpMessageConverter();
restTemplate.getMessageConverters().add(formHttpMessageConverter);
restTemplate.getMessageConverters().add(stringHttpMessageConverter);

We then make the call to GAS by making a map of arguments and passing it into the restTemplate. There are probably neater ways to do this, but this worked. The only bit of sophistication is that we declare a class variable URL which is filled in from a environment variable (or application.properties, if you feel so inclined).

@Value("${VACATIONFORM_URL}")
private String url = "https://example.com/exec";

 // stuff deleted

 @RequestMapping(path = "/api/setVacationPlans", method = RequestMethod.POST, consumes = {"application/json"})
 public ResponseEntity vacationPlans3(@RequestBody VacationRequest vacationRequest) {

 // stuff deleted

MultiValueMap<String, String> map = new LinkedMultiValueMap<String, String>();    
map.add("name", vacationRequest.getName());    
map.add("fromDate", plan.getFromDate());
 // stuff deleted
String response = restTemplate.postForObject(url, map, String.class);

The GAS script

This is a universal "add to spreadsheet" script, that matches the column headers in the spreadsheet to the incoming data. An new row in the spreadsheet is created and for each column that has a matching input parameter, the value is copied to the spreadsheet. Columns that are not in the input parameters are set to 'undefined' and input parameters that do not correspond to any column header are ignored (note that the match is case sensitive, and mind trailing spaces).
We used a very slight variation on the script by Martin Hawksey, we are using the updated version in this gist. Line numbers below refer to that version:

The script will receive a request on doPost (line 39). Google calls the request an event. It is stuffed into the 'e' parameter, documented here.
Before doPut can run, the script requires you to run setup once (from the GAS dashboard) so that the PropertyService has the spreadsheet's ID value (setup function on line 85).
After a lock is required, the script opens the spreadsheet specified by the ID (line 49-54).
A row array is created and populated by looping over the names in the header row (which is normally row 1) (line 57-66)
In the successful case, the number of the new row is returned
A reasonable error case is created, and the lock is released.

Unsolved problems

This code works but it still has a few issues:

The dates that come out of the bootstrap date picker are not in the format that Sheets expects.
There is no check on the user, we wanted to require google authentication with pivotal.io, but the current version does not do that. You can add that requirement to the GAS script when you publish it, but that will break things as you end up on our SSO login page.
If a user does not list any specific plans in step 2, their submission is effectively ignored
The code has zero tests. Not great but in our defense, most of it is devoid of any logic and the interesting parts (submission to google Sheets) will be hard to test mechanically.

There is a larger authentication issue which we found hard to solve: The user is authenticated with Google and Pivotal on the front end, but it is the backend that is contacting the GAS script. The back end does not have access to the cookies and authentication tokens that exist on the front end. This is a standard browser security precaution: Our web page lives on a site that is not part of Google or Pivotal, so it cannot read cookies or authentication tokens from those sites. We can therefore not ship the authentication to the backend and the backend has to contact GAS as an anonymous user.

In conclusion

It is a lot of explanation but not that much code. In the end, I think using the GAS script was the best way forward: I dislike adding a third layer to the project (frontend, backend and GAS) but it is a lot easier than going the Sheets API way, which requires you to set up Oauth. Because the GAS script is basically a universal add-to-sheet routine, it can be used as a standard component.

Thanks

To Mike Oleske who went on a three-day deep dive in Apps land with me. All the good code is his, all the mistakes are mine.

Updates

2016-03-24 Reformatted to look like my other posts

( Dotted . Pair )

Thursday, March 17, 2016

Making your own Google Forms