0 votes
by (230 points)

Some context: https://twinery.org/questions/19743/saving-twine-data-into-google-sheets-twine-2-2-1-harlowe-2-1

I followed the instructions on John Stewart's workaround and published my test game on philome.la. When I tried playing it, when reaching the passage where the code for transferring the variables onto google sheets, it produces this error. So my issue is that the test game works except for the passage where it should execute the recording process.


Putting the codes I used, if it would help. Thanks! :)

Code I used on Google Sheets Script editor:

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Test_Data";
 
//  2. Run > setup
//
//  3. Publish > Deploy as web app 
//    - enter Project Version name and click 'Save New Version' 
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) 
//
//  4. Copy the 'Current web app URL' and post this in your form/script action 
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
 
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
 
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}
 
function doPost(e){
  return handleResponse(e);
}
 
function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
 
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
 
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    //var headRow = e.parameter.header_row || 1; Hawksey's code parsed parameter data
    var postData = e.postData.contents; //my code uses postData instead
    var data = JSON.parse(postData); //parse the postData from JSON
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(data[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}
 
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

 

Twine Passage Code that might have caused the issue:

Recording your data. Please click proceed and then close the game.

[[Proceed]]

<script src="jquery-3.3.1.min.js">
</script>

<script>

var sendData = JSON.stringify({
"inputName" : harlowe.State.variables['inputName'], 
"history"   : harlowe.State.variables['history'],
"now"       : Date.now()
});
 
$.ajax({
url:"https://script.google.com/macros/s/AKfycbx4QEGzlayKEZ_nXcThEPBEGm4tRMjXaQXoJCeEVWzv90bcsPo/exec",
method:"POST",
dataType: "json",
data: sendData
}).done(function() {});

</script>

 

1 Answer

0 votes
by (159k points)

You left out two important pieces of information that may help us debug your code:

1. What exactly is being stored in the $inputName and $history story variables?

eg. what data-types (String, Array, Data-Map, etc) are they, what values are they storing, and if they are collection objects of some sort what exactly do they contain?

2. How do we locate the relevant passage in the version that is on the philome.la site?

eg. a List of the series of links we need to select to reach that passage.

note: I created a local Harlowe project using your example code, I assigned the two variables simple String values, and I was able to successfully send that data to your spreadsheet.

by (230 points)

May bad. Sorry I missed that!


For #1, $inputName is basically the player name (string; based from this twine-QA) that I ask the player to enter.

$history stores an array produced by (history:) macro based on the passages that the player has visited (variable located on the 3rd last passage).

For #2: the passage with the issue is the 2nd to the last one, all links will lead you to it. Here's a link of the local project.

Hopefully this would help in finding the bug. thanks!

by (159k points)

First a couple of things about your existing code.

1. You are assigning different default values to the $fname story variable and the related fname input field, this will cause an issue if the Reader decides not to edit the Player value and clicks the Confirm link without clicking on the Save button first.

You should assign the same default value to both the story variable and the input field.

Please enter your player name:
(set: $fname to "Player")
<input type="text" name="fname" value="Player">
<button type="submit" onclick="CustomScripts.updateVariable('fname')">Save</button>


2. You don't need to load jquery-3.3.1.min.js within your Submit passage.

Harlowe already contains a copy of jQuery so you don't need to load another copy, and even if you did need to load a different version of jQuery the URL you are using won't work because there isn't a copy of the jquery-3.3.1.min.js file within the same location as your story HTML file.

I suggest removing the following from your Submit passage.

<script src="jquery-3.3.1.min.js">
</script>


3. Using wrong (and undefined) story variable name in Submit passage.

The Player's name is stored within the $fname story variable, but you're incorrectly trying to use harlowe.State.variables['inputName'] to access that value in your sendData related JavaScript.

Change your sendData related code to the following..

var sendData = JSON.stringify({
	"inputName" : harlowe.State.variables['fname'], 
	"history"   : harlowe.State.variables['history'],
	"now"       : Date.now()
});


I suggest making the above changes and testing again.

by (230 points)

Heya! I followed your suggestions and published a ver 2 of the test story. There are no more errors! Thanks to you! :)

However, my google sheet didn't record any data :( I double checked the URL of my script and also made sure that the viewing permission is set to anyone on the web. The variables in the submit passage also matches the column names of my sheet so I can't point my finger on what's wrong.

I also waited a couple of mins just in case my connection's the problem. Sadly, it's not :(

by (100 points)

Hello,

Did you find a resolve to this? As I am struggling with the same issue, Google Sheet not recording any data. I followed all the steps here. And followed the amendments here and here.

Thanks

 

 

 

by (530 points)

IHello!

 

I don't know if you managed to resolve this issue but I recently had a similar problem that I managed to solve.

The issue for me was that in the Google Sheets code I'd messed up over the sheet name:

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

Where I had written the name of my whole sheet, I actually needed to write the name of the sheet - likely to be Sheet1 unless you've changed it - at the bottom of the sheet page.

Once I'd changed that in the code to 

var SHEET_NAME = "Sheet1";

it worked perfectly!

Hopefully this was of some help to you.

...