Last updated: 20 Nov 20 22:26:25 (UTC)

Displaying gameplay data (leaderboard) in Twine from Google Sheets

I used this with the escape room. I wanted to be able to show an updated leaderboard at the end of the game that isn’t completely ugly (the Google Sheets embedding is super ugly). Given what I learned from doing dynamic team names (More Twine + Google Sheets), I knew I could pull in the data as a native html table, and then use CSS to style.

This meant I had to use multiple sheetrock instances, which was tricky at first (b/c I know shit about Javascript).

Here’s the edits I made:

Google sheets has unique urls for each worksheet in a sheet, so it’s easy to pull from/to multiple worksheets in the same sheet (handy). Create a variable for each sheet url.
Identify the sheetrock function instances (e.g., “$(”#teams").sheetrock({“). This is important, because otherwise it won’t run multiple instances.

/*
        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.

        var mySpreadsheet = 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1897704297';

        var myBoard = 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1208363435';

        // Run sheetrock using a NON-EMPTY selector
        $("#teams").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.reppy = response.html;
                // Convert the response.html string (a table) to an array
                // Step 1: replace the html with comma-separations
                var rep1 = response.html;
                var rep2 = rep1.replace(/<\/td><td>/g, ' \- ');
                var rep3 = rep2.replace(/<\/td><\/tr><tr><td>/g, ', ');
                var rep4 = rep3.replace(/<\/td><\/tr>/g, '');
                var rep5 = rep4.replace(/<tr><td>/g, '');

                // Step 2: split the resulting string into an array at the commas

                var spl = rep5.split(', ');
                // Step 3: 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"
        });
        // Run sheetrock using an empty selector
        $("#board").sheetrock({
            // Set the URL of the Google Spreadsheet
            url: myBoard,
            // 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.board = response.html;
            },
            // 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 A,B where A 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.

        var mySpreadsheet = 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1897704297';

        var myBoard = 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1208363435';

        // Run sheetrock using a NON-EMPTY selector
        $("#teams").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.reppy = response.html;
                // Convert the response.html string (a table) to an array
                // Step 1: replace the html with comma-separations
                var rep1 = response.html;
                var rep2 = rep1.replace(/<\/td><td>/g, ' \- ');
                var rep3 = rep2.replace(/<\/td><\/tr><tr><td>/g, ', ');
                var rep4 = rep3.replace(/<\/td><\/tr>/g, '');
                var rep5 = rep4.replace(/<tr><td>/g, '');

                // Step 2: split the resulting string into an array at the commas

                var spl = rep5.split(', ');
                // Step 3: 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"
        });
        // Run sheetrock using an empty selector
        $("#board").sheetrock({
            // Set the URL of the Google Spreadsheet
            url: myBoard,
            // 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.board = response.html;
            },
            // 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 A,B where A is not null"
        });
    }

});

Previously, the timing of Google Sheets loading vs Twine loading hadn’t been a problem (Google Spreadsheet Testing from Dan Cox’s tutorial notes this at the bottom). It finally came into play here. The game finishes on one passage, they get their time, and then they can go on to check the leaderboard. It’s not enough time for the game to write their time to Google Sheets, sheets to process the data into ordering the board from the DATA worksheet to the leaderboard worksheet, and for the data to get back to Twine for the leaderboard.

Plus, the leaderboard variable called in sheetrock at the beginning of the game doesn’t update throughout the game; it’s still the same. So we have to call sheetrock again in a new instance on passage load, so that the updated variable can populate the leaderboard with the most recent time just finished.

/* Holder span while leaderboard updates, gets replaced when it does */
<span id="print">Updating leaderboard...</span>

/* This keeps checking the Google sheet leaderboard till the just-finished team appears on the list */

<<repeat .5s>>

/* This is necessary to define the variable. If it is not defined first, the whole passage tries to run with a null variable for $leader, and it returns Javascript errors */

<<set $leader = $board>>

/* call a new instance of sheetrock to run - basically, refresh sheetrock until the new team name is on the list */

<<script>>$().sheetrock({

  url: 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1208363435',

  query: "select A,B",
  callback: function (error, options, response) {
        State.variables.leader = response.html;
                }
                })
  <</script>>
<<if $leader != null>>
<<stop>>
<</if>>
<</repeat>>

/* Checks to see if the recently finished team name is on the list, and replaces the span with the leaderboard if it is */

<<repeat .5s>>
<<if $leader.includes($teamname)>>
<<replace "#print">><table><tr><td>Team</td><td>Time</td></tr>$leader</table>
<</replace>>
<<stop>>
<</if>>
<</repeat>>

/* Holder span while leaderboard updates, gets replaced when it does */
<span id="print">Updating leaderboard...</span>

/* This keeps checking the Google sheet leaderboard till the just-finished team appears on the list */

<<repeat .5s>>

/* This is necessary to define the variable. If it is not defined first, the whole passage tries to run with a null variable for $leader, and it returns Javascript errors */

<<set $leader = $board>>

/* call a new instance of sheetrock to run - basically, refresh sheetrock until the new team name is on the list */

<<script>>$().sheetrock({

  url: 'https://docs.google.com/spreadsheets/d/1At3JvqOWIjLtc5xYIUo_Qb8SsafOq7vSsNV0v7x4fy4/edit#gid=1208363435',

  query: "select A,B",
  callback: function (error, options, response) {
        State.variables.leader = response.html;
                }
                })
  <</script>>
<<if $leader != null>>
<<stop>>
<</if>>
<</repeat>>

/* Checks to see if the recently finished team name is on the list, and replaces the span with the leaderboard if it is */

<<repeat .5s>>
<<if $leader.includes($teamname)>>
<<replace "#print">><table><tr><td>Team</td><td>Time</td></tr>$leader</table>
<</replace>>
<<stop>>
<</if>>
<</repeat>>

Then the tables can be styled with CSS.