code, howto

Working with Google Sheets in Twine

Recently, as part of the (En)Twine’d Game Jam, John Stewart asked me to help with some code he had found to access a Google Sheet from within Twine. In working with what he had, I said I would post my own write-up of how the code worked and some other solutions I’ve found. This is that post.

Accessing Google Sheets via Sheetrock.js

Months ago, I was asked by Keegan Long-Wheeler if it was possible to access Google Sheets from within Twine, Knowing, of course, that Twine is only limited by the browser in which it runs, I put together an example over a couple nights using a library called Sheetrock designed around accessing Google Sheets.

Note: Using this library requires some knowledge of jQuery to access the library as well as how to use SugarCube’s functions to load remote code. This Cookbook entry can help in reviewing how to load and use remote JS in Twine.

A pre-compiled version can also be found here. Download and import the Twine file in either the downloadable or online version of the editor to see the code and existing passages.

While Sheetrock is designed to access a Google Sheet, it does not provide functionality to add or update cells. For that, additional code on the server side (the Google Sheet side) is needed to understand incoming requests.

Adding Google Sheet Values Remotely

Google Sheets provides extendable functionality through macros. Additional code can be added through the Script Editor. Through its API, server-side code can be provided to react to incoming requests and create a database-like interface to get and set cells.

Screenshot 2018-05-16 20.53.59Create or open an existing Google Sheet. This will serve as the database and will be public to the internet. Do not put private information in this database.

Screenshot 2018-05-16 20.55.49

Open the Script Editor. This can be found under Tools –> Script Editor.

Replace any existing code with Code.gs contents. This will provide the necessary server backend to updating cells.

// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "DATA";

Replace the name of the sheet to use in the SHEET_NAME variable. By default, the first sheet will be called “Sheet1”. Following these direction, replace the string “DATA” with “Sheet1” in the code.

Screenshot 2018-05-16 21.06.04

Run the setup function. In the Script Editor, go to Run –> Run Function –> setup. This will prepare the code.

Screenshot 2018-05-16 21.07.27

Publish the web app. In the Script Editor, go to Publish –> Deploy as web app. This will open the dialog box for deploying the script as a web app.

Screenshot 2018-05-16 21.09.09

Fill in the project version, select to execute the app as “Me…”, and select “Anyone, even anonymous” as the access level. Add in a project version and select to execute the app and who has access. Be sure to select “Anyone, even anonymous” or the code will not be able to accept incoming requests. Click Publish to finish.

Screenshot 2018-05-16 21.13.13

Copy the provided URL and click “OK” to close. The code will now be deployed and ready.

 

Screenshot 2018-05-16 21.21.07

Return the Google Sheet and add in column names and verify the sheet name. The data passed to the server-side function MUST match existing column names or it will be ignored. In order to accept incoming data, add column names.

Note: This code adds a Timestamp column if one is not found. As part of the code, it will record changes and when they happened.

Create or open an existing Twine file in the editor view. In a passage designed for the purpose of sending data, copy the code. This will call the jQuery $.ajax() function to send the data to the Google Sheet. If no errors have occurred, the new value will appear in the sheet within seconds of the call.

Note: If using column names with spaces, as with the example, be sure to enclose them in quotations to properly escape them.

 

Warning: This is not production code and should never be used as such. Using Google Sheets in Twine is a good proof-of-concept use case. Additional precautions should be used beyond a testing environment and for live, public code. Being aware of cross-site vulnerabilities and different avenues of attack can help protect yourself, project, and code.