Creating Twine variables from Google Sheets cells

Accomplished while creating the ELC4 author form (Weblink).

At its core, this method has to do a few things.

1. It has to take the passcode from the user (or any other bit of data that can correspond to a row or other element of G-sheet) and create a Javascript variable from it. I found a way to do this with document.getElementById.

2. It has to dynamically update the G-sheet QUERY for each desired data item in the row matching the passcode.

3. It has to create a TWINE-usable variable for each of those data items.

4. The variable has to be trimmed: strings from G-sheet cells are encoded in table-row and cell html tags, which can really throw stuff off when using them in Twine, and when feeding them back into G-sheets (as necessary).

(In Twine) Story Javascript & Log-in Passage

• Set up Sheetrock according to Log-in Page that Pulls codenames IDs from Google Sheet (Weblink).
• KEY DIFFERENCES:
  • In the log-in page, instead of using Twine’s *textbox *function for passcode entry use an html input box. The javascript doesn’t seem to want to read the Twine variables, but it will read the id of this html input box.
  • This also means that in the “submit” button, you must use the JS “getElementById” to create the Twine variable.
  • Included my now-standard “wait for Google to respond” repeat sequence because slow internet speeds can return undefined $response variables.

Please enter the code:

<<button "Submit”>> <<repeat .5s>> <<if $response != undefined && $revCk != undefined>>         <<set $code = document.getElementById(“code”).value>>         <<if $code == “”>>         <<replace “#textbox-reply”>>
            Please enter your code.
            <>         <<elseif response.includes($response.includes($code)>>         <<goto [[currentInfo]]>>         <>             <<replace “#textbox-reply”>>
                Incorrect. Please try again.
            <>         <> <> <> <>     <> CurrentInfo Passage

• So-named because in the ELC4 author form, I used the next passage after log-in to fetch the user’s information from the G-sheet according to the passcode they had just entered.
• If a passcode isn’t used, and there is no need to have dynamic information in the query strings, this can go in the Twine’s Story Javascript instead of an individual passage.

<> <> var cd = document.getElementById(“code”).value; // OR var cd = state.active.variables.code; // if Twine variable already exists var rowQ = “select * where B = '” + cd + “'”; var titleQ = “select C where B = '” + cd + “'”; var dateQ = “select D where B = '” + cd + “’”; var mySpreadsheet = “GSHEET_URL”; (document).ready(function () {         KaTeX parse error: Expected '}', got 'EOF' at end of input: …n () {         (“#title”).sheetrock({         url: mySpreadsheet,         query: titleQ,         callback: function (error, options, response) {                 var t1 = response.html;                 var t2 = t1.replace(//g, ‘’);                 var title = t2.replace(/</td></tr>/g, ‘’);                 State.variables.title = title;                 }     });         $(“#date”).sheetrock({         url: mySpreadsheet,         query: dateQ,         callback: function (error, options, response) {                 var dt1 = response.html;                 var dt2 = dt1.replace(//g, ‘’);                 var date = dt2.replace(/</td></tr>/g, ‘’);                 State.variables.date = date;                 }     }); }); <> <>

• Create a JS variable (“cd”) from the passcode (Id “code” from the log-in input) the user entered on the log-in page (which matches a particular row on the G-sheet).
  • UPDATE!!! Alternatively, if the Twine variable is already in use, use state.variables.VAR.
• Create JS variables to QUERY each cell you want to pull data from.
  • I used the variable name for the data that I would be using throughout the Twine work, then added a “Q” to it.
  • This creates the string for the GS Query “select” element that Sheetrock uses. I use Sheetrock to query and define each variable.
  • These are selecting for the intersection of the column (e.g., column C is “Title”) and the row in which the passcode (“cd”) appears. The exception in this example is “rowQ”, which returns all the data in the row (which didn’t turn out to be useful, other than in testing the method).
• Use instances of Sheetrock to create the individual data variables.
  • The GSHEET_URL is same url as the data/passcode.
  • The query line is using the JS variable created above.
  • In the callback function, the series of variable creation is removing the and table row/cell opening and closing tags from the string fetched from the GS cell (for some reason, it always returns with these).
  • “State.variables.var = var” is telling Twine to create a TWINE variable $var from the JS variable “var” (created in the line above).
• And there it is! A Twine variable with the value of the GS cell contents, usable throughout the rest of the story.