Help with John Stewart data grab

Del · January 22, 2021, 5:14am

Hey, I’m trying to use the grab code from here:

Here is the code in my twine page:

Thanks for playing! 

Type your answer here and it will print on a reciept in the gallary!

(input-box:2bind _var1,"=XX=",1,"Name")
(input-box:2bind _var2,"=XX=",1,"Answer")


<script src=”https://code.jquery.com/jquery-3.3.1.min.js”></script>


<script>
var sendData = JSON.stringify({
"var1": harlowe.State.variables['name'], 
"var2": harlowe.State.variables['answer']
});
 
$.ajax({
url:"https://script.google.com/macros/s/AKfycbysbEmHp0IwxdcmuBMJ74VIsrzpWl3vsr32N_CXUw/exec",
method:"POST",
dataType: "json",
data: sendData
}).done(function() {});
</script>

And here is the code in my google sheet.

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "DATA";
 
//  2. Run &gt; setup
//
//  3. Publish &gt; 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());
}

I know its a stupid error and you probably get the exact same question all the time but my fly brain cant figure it out.

HiEv · January 22, 2021, 7:51am

Unfortunately, it appears that that code broke sometime in 2020. The code I wrote based on that article, which used to work, no longer works.

I haven’t looked into why it doesn’t work anymore, though, so it may be fixable, though I don’t know how.

Let us know if you figure it out!

Greyelf · January 22, 2021, 8:25pm

The Google Sheets API has changed at least once since that original Twine & Sheets article was written, and searching with Google shows there are a number of others having issues with getting older code to work correctly.

Del · January 23, 2021, 4:47am

Hey,
The google parts seems to be somewhat working, i think the problem is getting the input from these textboxes

(input-box:2bind _var1,"=XX=",1,"Name")
(input-box:2bind _var2,"=XX=",1,"Answer")

to the part where it sends it to the form.
When i type somthing in the textboxes an entry like this appears in the form: 1/21/2021 undefined undefined

I would be happy to pay you for your time as this is critical for an art installation at the end of this month.

-Del

Greyelf · January 23, 2021, 9:18pm

There are a number of potential issues with your Passage content:

1: You are storing the input values in two Temporary variables named _var1 & _var2, but you are storing the current values of two Story Variables named name & answer to the properties of the sendData object.

2: You are executing contents of the <script> element during the Passage content processing step, however the Reader doesn’t get to interact with the two input fields until after the Passage content processing step has finished and the generated HTML elements have been added to the web-page.
eg. you are sending the current value of the two named Story Variables to the Google Sheet before the Reader can enter values into the input fields.

To allow the Reader time to interact with the input fields before you seen the entered values to the Google Sheet you need to delay the execution of the <script> element. One method you can use to do that is to place that element within an interactive element like a link.

warning: the following code has not been tested, because I don’t have a Google Sheet setup to receive data from a Twine project.

Thanks for playing! 

Type your answer here and it will print on a reciept in the gallary!

(input-box: 2bind $name, "=XX=", 1, "Name")
(input-box: 2bind $answer, "=XX=", 1, "Answer")

(link-repeat: "Send Date")[
	(if: $name is not "" and $answer is not "")[
		<script>
			var sendData = JSON.stringify({
				"var1": harlowe.State.variables['name'], 
				"var2": harlowe.State.variables['answer']
			});
			
			$.ajax({
				url:"https://script.google.com/macros/s/AKfycbysbEmHp0IwxdcmuBMJ74VIsrzpWl3vsr32N_CXUw/exec",
				method:"POST",
				dataType: "json",
				data: sendData
			}).done(function() {});
		</script>
	]
]
<script src=”https://code.jquery.com/jquery-3.3.1.min.js”></script>

Del · January 23, 2021, 11:12pm

Hey,
The problem is still the same

I know that editing the receiving code will be a trek so i am prepared to pay you 20 bucks (USD) for your effort once it works.

Thanks
-Del

Greyelf · January 24, 2021, 8:26pm

Did you add the following to the Story JavaScript area of your project?

if (!window.harlowe){
	window.harlowe = {"State": State};
}

Which version of Harlowe are you using?

Del · January 25, 2021, 2:45am

Yes, i have that in the JavaScript area, I don’t know which version. Would you be willing to fix the code for $20?

Greyelf · January 25, 2021, 7:15am

You have named the columns in your spread sheet name & answer, but you have named the properties of your sendData object var1 & var2 …

			var sendData = JSON.stringify({
				"var1": harlowe.State.variables['name'], 
				"var2": harlowe.State.variables['answer']
			});

…and it is this difference in column & property naming that is the likely cause of the issue.

The following variation of your original Passage content has been changed so the properties of the sendData object are now named the same as the spreadsheet’s name & answer columns.

Thanks for playing! 

Type your answer here and it will print on a reciept in the gallary!

(input-box: 2bind $name, "=XX=", 1, "Name")
(input-box: 2bind $answer, "=XX=", 1, "Answer")

(link-repeat: "Send Date")[
	(if: $name is not "" and $answer is not "")[
		<script>
			var sendData = JSON.stringify({
				"name" : harlowe.State.variables['name'], 
				"answer" : harlowe.State.variables['answer']
			});
			
			$.ajax({
				url: "https://script.google.com/macros/s/AKfycbysbEmHp0IwxdcmuBMJ74VIsrzpWl3vsr32N_CXUw/exec",
				method:"POST",
				dataType: "json",
				data: sendData
			}).done(function() {});
		</script>
	]
]
<script src=”https://code.jquery.com/jquery-3.3.1.min.js”></script>

Del · January 26, 2021, 7:02am

Thanks so much, that worked! If you give your paypal I will send you the promised money.
-Del