exporting variables to be read in a Google docs spreadsheet

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

131 Replies
Chris Freebairn

Hi everyone

I have implemented the above and it does get the variable from Storyline to the googlesheet (when launched in IE), however I get a Message from webpage (attached). 

 I have also noticed that it won't progress to next screen too.  It doesn't seem to work at all in Firefox.

Any advice would be greatly appreciated as I am working on a project over the Christmas period, ready for January

Thanks

Ashley Terwilliger

Hi Chris,

It looks like in that image you're also testing the published version locally? You'll want to test the published content by placing within the intended environment to properly test all features.  If that doesn't work for you, I'm hoping that Michael will be able to share the files with you. 

Ashley Terwilliger

Hi Chris, 

Where are you testing the content from? On your previous image, it appeared that you were testing the published version that was on your local drive. You'll want to publish the course and then place it within the intended environment to properly test elements such as Javascript. If you're publishing for web, there are a few free options you could consider testing in if you don't want it to be publicly accessible yet. 

  • Tempshare: This is a free service provided by Articulate for testing Storyline content. Note: Uploaded files will be deleted after 10 days.
  • Amazon S3: Amazon S3 offers free hosting with generous usage limits. If you go over your limit, you'll be charged a graduated fee.
  • Dropbox: Place your published output in the Public folder in your Dropbox account to share it with others.
  • Google Drive: Change the story.html file to index.html, and set the sharing permissions to PublicHere's how.
Klemen Stepišnik

Andrzej Rudnik said:

oh noo :(

I'm really sorry that I did not respond earlier but I had no access to the Internet - long duration holidays.

I don't know if I can do it but I have saved this page for all of you to pdf. There is note who is author of this article.

Function in Storyline can look like this (you can put it on Click event or timline starts):

var player = GetPlayer();    

    $.ajax({

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

           type: "POST",

           data: {"entry.yyyyy" :player.GetVar("your_verible1"), "entry.zzzzz":player.GetVar("your_verible2")},

           success: function(data)

           {

               alert(data); 

           }

         });

    return false; 

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)

your_verible1, your_verible1 - veribles from Storyline

If you want to check how it is work you have to put content on server. 


Thanks Andrzej! Your solution is very helpul!

Michael Hinze

Jacek Chrabąszcz said:

Hi Chris,

the solution is simple - download michael's site then open user.js and have a look at lines 31-52. Voilà. I hope I hope that's not top secret, otherwise I'm sorry.

Jacek


Hi Jacek, NOTHING is top secret in web development The code snippet in my sample is very close to Andrzej's earlier post, that's why I didn't bother posting it here. Of course, the URL and entryIDs are specific to my test file and would have to be adapted anyway.

Jason Dowd

Hi Everyone,

My first post here, but I've been using the product since it was released (and Studio before that).  I've been coming back to this thread again and again, but I just can't get this to work.  I'm simply trying to export variable data into a Google Sheet.

I've read all the documentation on this thread (several times!), tried multiple variations of the script provided above, and even extracted Michael's user.js in effort to adapt his code -- nothing works!  I must be missing something here..  Can someone please take a look at my code and story?  I've opened my sheet for public review/editing, so you should be able to see everything from front-to-back. 

Thanks!

.Story Attached

My form:

https://docs.google.com/forms/d/1AdbAVu3_ER3cL7o6LcDaMSRUfT5Loo6Hlpvc6TuatLc/viewform

Response Sheet:

https://docs.google.com/spreadsheets/d/15S30zAuxVKzDkW2h9f6EZRHhoW8Id-xln2NvoHzNcUM/edit#gid=130666735

Code:

var player = GetPlayer();

var Name = player.GetVar("Name");

var Event = player.GetVar("Event");

                $.ajax({

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

                    data: {"entry.495789985": Name, "entry.647210599": Event},

                    type: "POST",

                    dataType: "xml",

                    statusCode: {

                        0: function() {

                            //Success message

                        },

                        200: function() {

                            //Success Message

                        }

                    }

                });

EDIT:

Removed extra line from original script;

Updated attached .STORY;

added 2nd slide with alternate script:

var player = GetPlayer();    

    $.ajax({

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

           type: "POST",

           data: {"entry.495789985" :player.GetVar("Name"), "entry.647210599":player.GetVar("Event")},

           success: function(data)

           {

               alert(data); 

           }

         });

    return false;

EDIT2:

I see some activity in the sheet from an hour ago that I didn't do, so I hope someone has made some progress   Still unable to get it working my end. 

S. Yu

Curious if there's any new information on this process, particularly to Jason Dowd's issue in the above post. Here is my code, but it doesn't send the data to the Google Sheet. Any ideas? 

var player = GetPlayer();

var first = player.GetVar("TextEntry");

var live = player.GetVar("TextEntry1");

$.ajax({

url: "https://docs.google.com/forms/d/1HvAn2xcVBDcvN1stb2e7YIt1dZ-BW3oba7PgZFowCOk/formResponse",

           type: "POST",

           data: {"entry.985481521" : first, "entry.437746786" : live},

           success: function(data)

           {

               alert(data); 

           }

         });

return false; 

Jedidiah Esposito

Thanks for this! I was able to get it working after a little fussing. 

To anyone having trouble, make sure you add the jQuery library to your story.html after the <head> tag.

It should look something like this:

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

Also, you'll have to throw it online somewhere before anything actually feeds into the google form.

Ben T

Hmmmm still having no luck. I have used this exact syntax (below), and replaced the google link, column numbers, and variable names to match my module. No error message appears, but no results post to the google doc either.

The connectivity and form are functioning, as I directly embedded the form into the same module on a different slide and that generates results.

Any ideas? Any help would be greatly appreciated.

var player = GetPlayer();

$.ajax({

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

type: "POST",

data: {"entry.495789985" :player.GetVar("Name"), "entry.647210599":player.GetVar("Event")},

success: function(data)

{

alert(data);

}

});

return false;

Vu Hoang

Ben T said:

Hmmmm still having no luck. I have used this exact syntax (below), and replaced the google link, column numbers, and variable names to match my module. No error message appears, but no results post to the google doc either.

The connectivity and form are functioning, as I directly embedded the form into the same module on a different slide and that generates results.

Any ideas? Any help would be greatly appreciated.

var player = GetPlayer();

$.ajax({

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

type: "POST",

data: {"entry.495789985" :player.GetVar("Name"), "entry.647210599":player.GetVar("Event")},

success: function(data)

{

alert(data);

}

});

return false;


Can you show me your project ?

Jedidiah Esposito

Howdy,
Sorry for my slow response here.

Someone asked for clarification on my post above. This script is actually making use of the jQuery library, so in order for it to work properly you've got to add a link to the library to your html file.

So you'll want to add the line of code (below) to the "story.html" and "story_html5.html" files after publishing the module for web. This line of code should be added immediately after the <head> tag in both files. It allows them to find and access the jQuery library which contains the instructions needed for the Javascript code to use Ajax to manipulate the Google Spreadsheet.

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

Note: You may need to alter the 1.9.1 portion of this link to the latest version depending upon when you're seeing this message.

I hope that helps. I'm happy to take a look at projects that aren't working properly to see if I can suss it out.

Kind regards,
Jedidiah

aine meehan

I feel so stupid re this. Would someone be kind enough to check my code. This is literally all I have in the execute javascript area. I have two text input boxes for a name and student id. The trigger is that when the next button is clicked the javascript is executed. I have tested the entry.37118808 numbers in a web page and it works. I also have the jquery part in the story.html file.  I'm just missing something obvious. I think I will be learning javascript :)

I would really appreciate any help, 

p.s. I more or less copied Jedidiah's code, so thanks for that Jedidiah.

thanks,

aine


var player = GetPlayer();

$.ajax({

url: "https://docs.google.com/forms/d/1MjBkCNiANT18g9X-CyXoCoP58KkynJusV47ETBNwAp4/formResponse",

type: "POST",

data: {"entry.239328797" :player.GetVar("StudentName100"), "entry.37118808":player.GetVar("IDNumber100")},

success: function(data)

{

alert(data);

}

});



Jason N

Hi All - 

I've been toying around with this for the past couple days, and I'm pretty close but I think I need some assistance.

I have two variables at the beginning of the session for the first name and last name, as well as a variable for the quiz results which was created when I made the quiz results slide. 

the name  variable are able to post to google docs with no issue, however I can't get the quiz results to post. I have confirmed it's not an issue with the google docs data element, as tested with creating a variable to manually enter a number and that posted successfully.

Has anyone seen this where the default variables don't post as expected?

the Javascript I have is:

var player = GetPlayer();
$.ajax({
url: "https://docs.google.com/forms/d/1X8rIvOE9F3cSyCGXpvFwUzug_oUNkz6q1kiUZQJ7f5o/formResponse",
type: "POST",
data: {"entry.1159969422" :player.GetVar("FirstName"), "entry.1107543477":player.GetVar("LastName"), "entry.1692446738":player.GetVar("Results.ScorePoints")},
success: function(data)
{
alert(data);
}
});
return false;

Michael Hinze
Jason N

I'm fairly new to storyline and I've played around with this suggestion,
but I'm not sure how to store the value of one variable
(Results.ScorePoints) to another custom built Variable....
I'm only familar with how to have a user manually enter a value for a
variable.

You could use a trigger like tis: