exporting variables to be read in a Google docs spreadsheet

Dec 06, 2012

Dorothy Stark
By Dorothy Stark

I've seen Tom's helpful screencast on how to embed a Google docs form into a Storyline project. However, what I want to do is a little different. I'd like to be able to export the values of about a dozen variables from Storyline so I could see them in a spreadsheet the way I would if students entered those values into a Google docs form.

I've got a grammar assessment, and I want to export a student's name and either OK/NEED TO REVIEW/MIGHT WANT TO REVIEW for each of a dozen or so topics. Now, I could just use a final page screen in Storyline showing a student her status on these dozen topics and ask her to fill out an embedded Google docs form in which she basically copies the results manually. These are just initial assessment scores, and I'm not worried about honesty issues. However, this seems like a pretty klutzy way to do things. Would anyone be able to help me out with a suggestion for a better method? I don't know any JavaScript, and my familiarity with Storyline is fairly low.

Thanks!

Dorothy

142 Replies
aine meehan
over 9 years ago01/13/15 at 7:39 pm (UTC)

no, but i can send you my code. i will be testing it on ipad1 next week if
thats any good to you, or you could test my wonky test version yourself,
and put in your real name as student name, and i will check the google
form, then you will know if your name appears that the code works on ipad, and you can then copy my
code.
here is link;
http://library.itsligo.ie/screens/ilo/ilo

aine meehan
over 9 years ago01/14/15 at 7:45 pm (UTC)

yes, I uploaded an old version, and I can confirm that it does actually
work in chrome.
here is link (sorry about all the confusion)
I basically want students to enter their name and id, and I want the
results to go to a Google form
google form is here:
https://docs.google.com/spreadsheets/d/1_h_sJTgAf61ZGmhHknQ_u0d4OxlTKtXZYdR8adENC9s/edit?usp=sharing

and the story file is here: http://library.itsligo.ie/screens/ilo/old/story.html

i also want the results of some of my quizzes to go to the form, but that seems to be making a mess of things.

Nigel Young
9 years ago02/09/15 at 10:00 pm (UTC)

Thanks to everyone in this thread - I've been asked to do this for a client and got it working eventually following those who have gone before me.  Anyone wanting my 'steps' and script please direct message me and I'll happily share :) Great to say it tested on FF, Chrome, Safari, IE and mobile safari too.

aine meehan
9 years ago02/11/15 at 3:16 pm (UTC)

Hi everyone. I got this to work, the problem I had was that I was actually trying to export quiz results, which from reading around appears to be a protected field in Articulate. Can anyone collaborate that. So I spent days trying to figure it out, and it turned out I was not doing anything wrong, it was just the field I was trying to export as a variable. There was a work around somewhere that I found online but that didn't work either.

Mark Weingarten
9 years ago04/06/15 at 11:47 am (UTC)

Hi Everyone, 

I'm having a heck of a time finding the X, Y, and Z information as indicated in the steps outlined below:

"XXXXXXXXXXXXXXXXXXX - must be replaced by the form key (for more details chceck pdf file)

yyyyy, zzzzz - must be replaced by number from your's google form. You can find it with firebug (for more details chceck pdf file)"

I tried following the directions on the link (https://articulate-heroes.s3.amazonaws.com/uploads/attachment/attachment_url/4238/page.pdf?dl=true) that was shared but I can't find the form key or the y and z numbers.

Could anyone help? I'm hoping to get this working soon.

 

Thanks,

Mark

Laura Brunning
almost 9 years ago05/20/15 at 3:39 pm (UTC)

Hi,

I appreciate this was a while ago, but wondered if anyone can help.

I can get the variables to pass to the google spreadsheet, but I too am seeing the pop up error/message from website.  Although it does let you close this message and carry on with the course (and the variables go onto the spreadsheet) - I'm struggling to get rid of it.

Can any one help??

Cheers, Laura

PS - Exported as SCORM running in VLE where the users have to use IE 11 (created in SL1)

 PPS - should also have said that just using the scripts nicked from these forums without alterations except to add my spreadsheet/form and variable details (thank you!).

SCRIPT used in Google:

function myFunction() {

var SCRIPT_PROP = PropertiesService.getScriptProperties(); 

function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}

function handleResponse(e) {

var lock = LockService.getPublicLock();
lock.waitLock(30000);

try {

var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("1EMJiuiqZ7R8tX9MbeSrjrKEjgGfhw16LfLAYQ1_bOaY"));
var sheet = doc.getSheetByName(SHEET_NAME);


var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];

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(e.parameter[headers[i]]);
}
}

sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){

return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}

function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("1EMJiuiqZ7R8tX9MbeSrjrKEjgGfhw16LfLAYQ1_bOaY", doc.getId());
} }

SCRIPT USED IN STORYLINE

var player = GetPlayer();

$.ajax({

url: "https://docs.google.com/forms/d/1SzbvgcnV93aqOfHU4ycIlrJhD2BH4PpanB039l7Wgao/formResponse",

type: "POST",

data: {"entry.174521765" :player.GetVar("name"), "entry.1135805308":player.GetVar("id"), "entry.1806056227" :player.GetVar("colour")},

success: function(data)

{

alert(data);

}

});

return false;

THING ADDED TO STORY.HTML <HEAD>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

It works, the variable data gets filled into the Google sheet, it just brings up the error message attached - the user can close the error message and carry on as normal, it's just not a great user experience.  

Thanks in advance!

Tracy Parish
over 8 years ago08/06/15 at 7:55 pm (UTC)

I have been struggling with this all day.  Wow.  Thanks folks for all the files, sharing, collaboration, examples, helping, etc.  It made a big difference, but I missed one step somewhere along the line and finally figured out what was wrong.

Here is my 2 cents, for what it's worth now, on why mine didn't work.  Maybe it will help the next person along this path.

I had my folder where the Google Form and Google Spreadsheet set open to Public, but the form/spreadsheet themselves were not.  Without ensuring their share properties are public access as well, the variables from the course won't get written into the spreadsheet.


Sign In to Reply