Using Webscript and Google Spreadsheets with Twine

Recently, I blogged about how I used Webscript and my new Twine to alert me to when my garage door is open too long.

This got me to thinking about tracking the ups and downs of my garage door. I'm not sure what I'd do with the data, but since I'm generating it now, I might as well store it. The question was, "Where?"

I decided that I'd like to keep the data in a Google spreadsheet. Fortunately, Google (unintentionally?) makes this easy for anybody who can generate an HTTP POST. Webscript can generate those, so we're good there.

The way to do this is to create a Google Spreadsheet. Then, for that spreadsheet, create a "Form" that would be useful if a human were going to be supplying the data. Create a form by choosing "Create a form" from the "Tools" menu.

Within the Form wizard, simply add a question for every value that you want stored in the spreadsheet. Make sure that you choose "Text" as the Question Type for each question.

Once you have saved your form, choose "Go to live form" from the (newly-added) "Form" menu above the spreadsheet. This will bring up the form in your browser. All you will need from this is the "formkey" parameter in the form's URL. For example, in the URL https://docs.google.com/spreadsheet/viewform?formkey=dGJKVUthaGtiU0RndktuifjrgdFFRdWc6MQ#gid=0, the formkey is everything between the "=" and the "#".

This key is used in the POST from Webscript that adds another row to this spreadsheet.

The Webscript code to add a row to the spreadsheet now is simple:

http.request {
        url = "https://docs.google.com/spreadsheet/formResponse",
        method = "POST",
        params = {
                formkey= "<Value of the Form's formkey>"
        },
        data = {
                ["entry.0.single"] = "<Value of 1st form entry>",
                ["entry.1.single"] = "<Value of 2nd form entry>"
                -- include as many parameters as there are form entries
        }
}

Executing that statement will add a single row to the spreadsheet. Google automatically includes a timestamp in the added row, so there's no need to do that explicitly.

With that, it's easy for me to fill a Google Spreadsheet with Twine events. In this case, I'm just recording the changes in orientation resulting from my garage door going up and down:

twine.webscript.io/change

-- First, record the actions locally for active monitoring
storage.orientation = request.query.orientation
storage.alerted = "false"
storage.time = os.time()

-- Then, record the actions as a new row in the Google Spreadsheet
http.request {
        url = "https://docs.google.com/spreadsheet/formResponse",
        method = "POST",
        params = {
                formkey= "<Value of the Form's formkey>"
        },
        data = {
                ["entry.0.single"] = request.query.orientation,
        }
}
return "ok"

This all works because Google has created a way to have public forms that fill in private spreadsheets. I simply figured out the POST semantics from the HTML of the form. When you use this technique, you are creating a public form—if you share your formkey with anybody, they can write to your spreadsheet as well.

Twines are excellent devices for monitoring their environment. Combined with Google Spreadsheets and Webscript, they can track those changes easily in a convenient and powerful cloud document. If you find interesting uses for this, please let us know what you come up with. As always, we can be reached at team@webscript.io.