Pimping a Google spreadsheet form, to create a bespoke control form with sliders

Link here to download complete Processing sketch on Github

This expands on the previous post, which explained how to use the RSS feed from a published spreadsheet to extract remote control data from that feed and use it in a Processing sketch.

Here this idea is extended to use a Google form, instead of typing into the sheet. This has the advantage that all the values to be sent can be prepared at once, then the form submitted.  It is still a bit clunky, but it does imporve it slightly. A mthod of controlling the inputs to submit data between fixed upper and lower values for both horizontal and vertical position variables is described below.

The Google spreadsheet showing form data and control data based on the last form entry

The values in the fields F2, G2 and H2 are the data that will be used remotely by the sketch.  The data in these fields are being constructed from the control character ¬ in E2, and the last instance of a form return from columns B (representing up/down eyeball data), C (representing left/right eyeball data) and D (speech text) for the Twitr_janus puppet.

The Google expression in cell F2 to return the last NUMERIC value of column B is...

=trim(E2&(FILTER( B2:B ; ROW(B2:B)=MAX(FILTER( ROW(B2:B) ; ISNUMBER(B2:B))))))

The Google expression in cell G2 to return the last NUMERIC value of column C is
=trim(E2&FILTER( C2:C ; ROW(C2:C)=MAX(FILTER( ROW(C2:C) ; ISNUMBER(C2:C)))))

and the Google expression in cell H2 that returns the last TEXT value of column D is
=trim(E2&FILTER( D2:D ; ROW(D2:D)=MAX(FILTER( ROW(D2:D) ; ISTEXT(D2:D)))))


The puppet is not yet built, so a surrogate virtual face is being used to react to the data. It is rather basic...

The standard form for the spreadsheet was set up as usual and has text boxes. This is  slightly better than direct typing into the sheet, in that the 3 values can be sent in one go. However, it requires the user to have to know the values needed to make the eyeballs move and allows any value to be submitted, which will cause problems.

To get round this, the source code of the form needed to be adjusted. It looked like this:

Source code for standard Google form

The post method used here will only work if you are logged in as an editor of the sheet, but if you are logged in the form code can be used on any webpage. This means you can copy it and hack it, then embed on your page.

Source code for the pimped Google form

Original dull ole' Google form

Beautiful pimped form with
range limited sliders - nice!

The main thing to note here was the adjustments to the input controls for the two eyeball data fields.
The original input has the attribute type="text". This is shown on the left astext boxes.

The pimped version on the right (from an iPad screen) uses the attribute type ="range".

Range is an HTML5 feature that displays a slider.

It also allows for min and max attributes to be set, which limit the data entry to between those two values. Just what was needed!

ta-da!