Last updated: 19 Jan 24 19:49:32 (UTC)

More Twine + Google Sheets

For an escape room game, teams could either sign up in advance, or just enter team names. I wanted to be able to check against the google sheet where they signed up in advance.

I needed a few things:

A list of the teams that had already signed up.
The ability to select from that list to enter the team name.
On the passage where teams who hadn’t pre-registered, I needed to check those entries against previous ones, to make sure there wasn’t confusion between teams with the same name.

I used the Google sheets/Sheetrock interface I’ve used previously (Log-in Page that Pulls codenames IDs from Google Sheet). A couple of adjustments got me what I needed (after HOURS of trial and error, of course!)

For the list of teams, all I needed was to wrap the $response variable in a

. It’s already in html form, and it turns out it already has all the and

tags. Once it’s wrapped in tags, it works fine and can be styled with CSS. So in the passage it looks like (I added a header row in):

<table>
<tr><td>Team</td><td>Team Leader</td></tr>
$response</table>

<table>
<tr><td>Team</td><td>Team Leader</td></tr>
$response</table>

For the drop-down list of pre-registered teams, I had a bit of fiddling to do. Turns out, though, you can take that same $response variable, replace the existing html tags (with regular expressions, or here) so that it’s just a comma separated list in a string, and then use .split() to create an array. (Below is all the Sheetrock code in the Javascript section - see especially the section starting “// Convert the response.html string (a table) to an array”.)

/*

        A short example of how to load the Sheetrock.js library
        using the existing jQuery library as part of Twine 2
        and add a on-the-fly variable in SugarCube using
        State.variables.

        Sheetrock.js (https://github.com/chriszarate/sheetrock)

*/
// Call this function during the "ready" event
$(function() {

    // Set the URL to load
    // (In this case, the Sheetrock.js library for
    //  accessing public Google Spreadsheets.)
    var URL = "https://cdnjs.cloudflare.com/ajax/libs/jquery-sheetrock/1.1.4/dist/sheetrock.min.js";

    // Use getScript() to fetch and run remote JS

  $.getScript( URL )

        // If everything went well, run done()

  	.done(function( script, textStatus ) {

        // Call loadsheet()
        loadSheet();

  })

        // If it failed, run fail()

  .fail(function( jqxhr, settings, exception ) {
    console.log("Remote loading failed!");

    });

    function loadSheet() {

        // Define spreadsheet URL.
        // (This example holds baseball data.)
        var mySpreadsheet = 'https://docs.google.com/spreadsheets/d/1j2GdoC5Sr-2Hbt8foW7h0Z1VRsgzWaIJ6DyGPpVimQo/edit#gid=0';
        // Run sheetrock using an empty selector
        $().sheetrock({
            // Set the URL of the Google Spreadsheet
            url: mySpreadsheet,
            // Set a callback from the results
            callback: function (error, options, response) {
                // Response holds the data.
                // For rows, .rows
                // For HTML, .html
                // (https://github.com/chriszarate/sheetrock#callback)

                // In SugarCube, we can access variables through
                //  State.variables
                // This can be wrapped in <table> tags in the body
                State.variables.response = response.html;
                // Convert the response.html string (a table) to an array
                var rep1 = response.html;

                                // replace the tags between row cells with a dash

                var rep2 = rep1.replace(/<\/td><td>/g, ' \- ');

                                // replace the tags between rows with a comma and space

                var rep3 = rep2.replace(/<\/td><\/tr><tr><td>/g, ', ');

                                // delete the ending tags

                var rep4 = rep3.replace(/<\/td><\/tr>/g, '');

                                // delete the opening tags

                var rep5 = rep4.replace(/<tr><td>/g, '');
                // split the resulting string into an array at the commas
                var spl = rep5.split(', ');
                // convert that array to a Twine variable array
                State.variables.txt = spl;
            },
            // Query is based on the SQL-like Google Query Language
            // (https://developers.google.com/chart/interactive/docs/querylanguage)
            // In this example, select (get) the columns B, and C where B is not blank
            query: "select B,C where B is not null"
        });

    }

});

/*

        A short example of how to load the Sheetrock.js library
        using the existing jQuery library as part of Twine 2
        and add a on-the-fly variable in SugarCube using
        State.variables.

        Sheetrock.js (https://github.com/chriszarate/sheetrock)

*/
// Call this function during the "ready" event
$(function() {

    // Set the URL to load
    // (In this case, the Sheetrock.js library for
    //  accessing public Google Spreadsheets.)
    var URL = "https://cdnjs.cloudflare.com/ajax/libs/jquery-sheetrock/1.1.4/dist/sheetrock.min.js";

    // Use getScript() to fetch and run remote JS

  $.getScript( URL )

        // If everything went well, run done()

  	.done(function( script, textStatus ) {

        // Call loadsheet()
        loadSheet();

  })

        // If it failed, run fail()

  .fail(function( jqxhr, settings, exception ) {
    console.log("Remote loading failed!");

    });

    function loadSheet() {

        // Define spreadsheet URL.
        // (This example holds baseball data.)
        var mySpreadsheet = 'https://docs.google.com/spreadsheets/d/1j2GdoC5Sr-2Hbt8foW7h0Z1VRsgzWaIJ6DyGPpVimQo/edit#gid=0';
        // Run sheetrock using an empty selector
        $().sheetrock({
            // Set the URL of the Google Spreadsheet
            url: mySpreadsheet,
            // Set a callback from the results
            callback: function (error, options, response) {
                // Response holds the data.
                // For rows, .rows
                // For HTML, .html
                // (https://github.com/chriszarate/sheetrock#callback)

                // In SugarCube, we can access variables through
                //  State.variables
                // This can be wrapped in <table> tags in the body
                State.variables.response = response.html;
                // Convert the response.html string (a table) to an array
                var rep1 = response.html;

                                // replace the tags between row cells with a dash

                var rep2 = rep1.replace(/<\/td><td>/g, ' \- ');

                                // replace the tags between rows with a comma and space

                var rep3 = rep2.replace(/<\/td><\/tr><tr><td>/g, ', ');

                                // delete the ending tags

                var rep4 = rep3.replace(/<\/td><\/tr>/g, '');

                                // delete the opening tags

                var rep5 = rep4.replace(/<tr><td>/g, '');
                // split the resulting string into an array at the commas
                var spl = rep5.split(', ');
                // convert that array to a Twine variable array
                State.variables.txt = spl;
            },
            // Query is based on the SQL-like Google Query Language
            // (https://developers.google.com/chart/interactive/docs/querylanguage)
            // In this example, select (get) the columns B, and C where B is not blank
            query: "select B,C where B is not null"
        });

    }

});

The reason so many replace() segments were needed was because I had two columns (a team name and team leader) and I wanted to be able to list them as “Teamname - Leader” in the selections.

Then in the passage, use the <> macro for the dropdown, and to call the $txt array defined in the Javascript:

<<listbox "$regname">>
<<optionsfrom $txt>>

<</listbox>> <<button "Submit">><<set $teamname to $regname>><<goto [[ready]]>><</button>>

<<listbox "$regname">>
<<optionsfrom $txt>>

<</listbox>> <<button "Submit">><<set $teamname to $regname>><<goto [[ready]]>><</button>>

In the passage where they can enter a new team name, I checked it against registered team names:

Enter your team name: <<textbox "$teamname" "" autofocus>> <<button "Submit">>
        <<if $teamname == "">>

        <<replace "#textbox-reply">>\
            Please enter a teamname.\
            <</replace>>
        <<elseif $response.includes($teamname)>>
            <<replace "#textbox-reply">>\

                That name is taken. Please try again. If you already signed your team up with that name, [[click here|Yes]].\

            <</replace>>
        <<else>>

        <<goto [[ready]]>>

        <</if>>
    <</button>>
<span id="textbox-reply"></span>

Enter your team name: <<textbox "$teamname" "" autofocus>> <<button "Submit">>
        <<if $teamname == "">>

        <<replace "#textbox-reply">>\
            Please enter a teamname.\
            <</replace>>
        <<elseif $response.includes($teamname)>>
            <<replace "#textbox-reply">>\

                That name is taken. Please try again. If you already signed your team up with that name, [[click here|Yes]].\

            <</replace>>
        <<else>>

        <<goto [[ready]]>>

        <</if>>
    <</button>>
<span id="textbox-reply"></span>