Discovery Through Elearning – Blog

How To Send Variables from Storyline to Google Docs

There is a process that can be used to export variables from a Storyline file into a Google Doc Spreadsheet.  It has been discussed several times within the Articulate Community Hereos forum and is a bit complicated and hard to follow if you don’t understand the “coding language” being shared there.  However, once you have completed the steps I’m fairly confident you’ll be able to create this process over and over.

Note: You’ll have to create a Google Account to create a Google Form and spreadsheet.  Good news is you get another email account (should you choose to use it) and you’ll get 15MB on Google Drive where your form, spreadsheet, and any other files can be stored.  This is also useful if you need a place to host your course, as you’ll have to host online in order for this method to work.

To start, create your Storyline file/course and be thinking while you are creating it which variables you might want to store into a spreadsheet.  For my example I am going to use FirstName, LastName, VolNumber (this might be an employee number), and Score (the final mark they get on their quiz).

Here are the variables I created:

Blog post 1

Here is how I used them on a slide:

blog post 2

Once my course is complete, and in this case including my final quiz with results layers, I create a Google Form.

The form is required to get special entry field numbers that are used to identify which spreadsheet and which cells on the spreadsheet the information will be sent to.  All that is needed is simple text entry boxes for each of your fields you wish to capture from the Storyline file.  It’s best if you name them the same.

Here is an example of a Google Form with the last “question” open so you can see how they were each created.
blog post 3

When you are done, give the form a name and this will automatically create a spreadsheet that the form data will be saved.  Click the VIEW LIVE FORM button to see your form complete.

From the live form you will not get all the data you need to plug into the javascript we will be adding into our Storyline file.

In the URL address, at the top of the screen, you will see something that looks like this:

https://docs.google.com/forms/d/1cvChKs5fs6klKfTNAgRQLrwphD35kM5SD19xFlv1-_M/viewform

This bold number (after the d/ and before /viewform) is the code that identifies your form and the form automatically knows which spreadsheet to send the data.

Still in the live form view complete these steps:

  1. Right-click on the first field where someone would answer your first form question. On my example it is the FirstName field.
  2. Left-click INSPECT ELEMENT.

You will get a screen that looks like this (if you are using Chrome).  The number you see here after the word entry is the number you are looking for that we will be adding to your javascript.  You need only to make note of the number.

Example: <input type=”text” name=”entry.1547279093” value=”” class=”ss-q-short valid” id=”entry_1547279093″ dir=”auto” aria-label=”FirstName  ” title=””>

blog post 4

 

Complete these same 2 steps for the other fields on your form.

Once you have all of these numbers then you’re ready to go back to your Storyline file.

Somewhere in your storyline file you will now need to add some javascript.  You can trigger this script to start how/whenever you wish.  It might be when a timeline starts, when a button is clicked, etc.  In my example we are added it to the final results slide.

In my example I also want to send the learner’s grade to the Google spreadsheet.  The Results.PassPercent, Results.PassPoints, Results.ScorePercent, Results.ScorePoints are all variables that you cannot pass out of Storyline.  However, you can set another variable equal to these and pass that one out.

To achieve this, when I created my all my variables earlier, I also created a number variable called: Score.  On my Results slide I have added a trigger:

On this layer I have also placed a trigger to Execute JavaScript when the timeline starts:

The script you want to enter here (by clicking the … button in the trigger wizard – see above image) is:

  var player = GetPlayer();

$.ajax({

url: “https://docs.google.com/forms/d/1cvChKs5fs6klKfTNAgRQLrwphD35kM5SD19xFlv1-_M/formResponse”,

type: “POST”,

data: {“entry.1547279093” :player.GetVar(“FirstName“), “entry.1823882308“:player.GetVar(“LastName“), “entry.2004574816“:player.GetVar(“VolNumber“),

“entry.1244726711“:player.GetVar(“Score“)},

success: function(data)

{

alert(data);

}

});

return false;

 

UPDATE courtesy of Mike F, Articulate Community Member:

I wanted to add something here. Tracy mentioned that she is using IE8. So am I, but here is how I got it working. I added a dataType: “jsonp” to the script. It now works for me in IE8.

var player = GetPlayer();
$.ajax({
url: “https://docs.google.com/forms/d/1ebGN0lrq2_DU24UtsQqpD9Zf64TZiPAFNDN9gu8MjlE/formResponse“,
type: “POST”,
dataType: “jsonp”,
data: {“entry.340255159” :player.GetVar(“FirstName”),

 

You’ll notice in this script that I have added the “form number” we got from the form URL earlier.  The numbers are those we noted when we “inspected element” on each of the form entry fields. Finally, note that the GetVar pieces, where the variable names we are adding in there are the same ones we created in Storyline earlier.

UPDATE 01/05/2016 courtesy of Virgina Dricsoll, Univ. Iowa

Virgina and I did some intense trouble shooting to see why her trial of this solution wasn’t working. One thing I notice different in her code than mine was the indentation of the javascript code.  This does happen if you straight copy/paste the code above into SL.  If  you run into the issue that yourvariables are not passing to Google, try using the code found in the text file (below) and subtitute your form id, and variable ids it should work. 

SL variables to Google javascript code (text file)

 

Once this is call created you can save your Storyline file and publish it to the web.  When you do this you will get a set of files that look similar to this.

blog post 7

You need to edit the story.html file (and/or the story_html5.html file if you published for it as well) slightly to make this all work. “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.”  I’m no coder so in all honesty I don’t completely understand what that means precisely, but I know you have to do it to make this work.

You can edit this file by right clicking on it and click OPEN WITH WordPad, NotePad (in Windows) or some other html editor.

This html file will be quite long; I am only showing the first few lines of the html code below.  It will look something like this below and you will need to add the text that is bold.  Editing/adding this line in the future may require you to change the “1.11.1/jquery.min.js” number.  I have seen it written as 1.10.1, 1.9.1, etc.  for whatever is the latest version of jquery for your (your learners’) current browser.  I used 1.11.1 and had no issues in Chrome, but I believe at the time of writing this the current version is 1.11.3

<!DOCTYPE HTML>

<html style=”height: 100%;” lang=”en”>

<head>

<meta http-equiv=”Content-Type” content=”text/html;charset=utf-8″ >

<script src=//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js></script>

<!– Created using Articulate Storyline 2.5 – http://www.articulate.com  –>

<!– version: 2.5.1505.515 –>

 

<title>Volunteer Test Data Capture</title>

<style>

Updated Note: I have when I copy and paste this text into my story.html files that I need to delete and replace the quotation marks, indicated in red.  It maybe the font I am using here on my page.  The quotes have a little “curl” to them.  When they are pasted into the .html file they should be just straight verticle marks.

Once you have made this change to the file you can save it and close it.

The last step it to place your course online for learners to access.  This can be done if you have access to a site where you can upload and host the course, or if that’s not an option there are several other places, but I have use the method that lets me host on Google Drive.  That process is clearly described if you follow the link to the Articulate Community Forum so I wouldn’t duplicate it here.

Once your course is uploaded it’s ready to be tested or distributed.

Findings:

What I have tested has resulted in:

  • If I set the javascript to run when then timeline starts on a particular slide or when a button is clicked then when viewing in:
    • Chrome
      • The javascript runs and sends the data to the spreadsheet
      • Clicking next at the end of the course does not “close” the course/browser window.
    • Explorer
      • I could not get either trigger on slide, or by clicking a button to work. I am using IE8 at work and I tried changing the jquery version to 1.9.1, and 1.9.0 as I found through forums, but neither seemed to work with my work browser.

28 Comments

  1. Daran GisbyDaran Gisby09-03-2015

    Hi Tracy.
    I just wanted to say thank you for putting this process together. I am not new to Storyline but I am very new to JavaScript. I have spent days trying to get my head around sending quiz variables to Google docs. Your page has made it simple and though I am still non the wiser on JavaScript coding, I can now get all the user data I need thanks to you.

    • tparishtparish09-03-2015

      Great to hear Daran. It took me some time to wrap my head around it all as well. Glad you were able to make use of my instructions.

  2. LongLong09-05-2015

    Hi Tracy , thanks for your help . But i still cant make it work , the storyline 2 do not send the variable to google form , so can you please help me by upload a sample ? I dont know where the problem is T.T , thank you again.

  3. JessicaJessica09-14-2015

    I have been trying this all day to no avail. When I click on the button to execute the javascript, absolutely nothing happens, no errors nothing at all. Any help is appreciated!!

  4. MonkeyJackMonkeyJack09-15-2015

    This is driving me crazy. I have been trying this for some time now with no luck. I’ve tried directly to google spreadsheets through the web app approach and also this technique. Followed every tutorial step by step and still nothing.
    When trying to post to spreadsheet the command appears to happen in the network inspector (but nothing goes into the spreadsheet). However when sending to a form the trigger appears to do nothing.
    Any thoughts or ideas? Thanks

  5. Jane JamesJane James09-24-2015

    Hi –
    Thanks so much for putting this together! It’s an amazing tutorial and the images do help in getting the process started…
    Unfortunately, I still can’t get my version to work… I’ve made a simple course with a single text variable, but can’t make anything report to the Google form… Help please? Thanks!

  6. BryanBryan10-07-2015

    I haven’t had much luck despite following the directions. Would you mind taking a look at my files and let me know if perhaps I’ve overlooked something?

    Thank you!

  7. Daniel WongDaniel Wong10-15-2015

    Hi,
    i am facing the same issue.
    I could not get the response from the articulate to go into the google form.

    can email me what is missing?

    thanks

  8. TobyToby11-12-2015

    Hi Tracy,

    thanks for compiling this. There are so many partial descriptions of how to do this it’s good to finally find a comprehensive how to. I got it to work I just had one snag to find and fix. When I inserted the jquery script line into the file header the quotations were styled in a way they weren’t recognised so the jquery file wasn’t loading. (I think I may have copied the latest version from the google dev site or elsewhere). This wasn’t clear in notepad but when I checked using my server side editor (I hosted the file on my webserver) it was very clear they were different. Once changed the script worked fine.

    cheers, Toby

  9. Ryan ParishRyan Parish01-12-2016

    Hey Tracy! It’s nice to see other members of the global Parish family involved in eLearning.

    I am having trouble with this, and would love some guidance! It seems as if the google form is registering that there is a response incoming, but is not filling in the values properly.

    Thanks so much!

  10. Sarah HadfieldSarah Hadfield03-29-2016

    Oh my – this has caused me sleepless nights and stress. Thanks to your great instructions I now have a working test. You don’t know how happy this has made me. Thank you thank you for putting this all together.

  11. DavidDavid05-24-2016

    Absolutely LOVED this little tutorial you put together as it was completely easy to understand and follow. I unfortunately cannot get it to work 🙁 I’ve checked, re-checked and checked again all avenues and possibilities but to no avail. Any suggestions? Forms and spreadsheet all set up, HTML docs edited, all java and variables looked at and edited but nothing populates. Can I send my file for review? Once again, I really do appreciate the time you put into creating this page, I wish more were like this.

  12. Scott SyerScott Syer06-09-2016

    Hi Tracy, It looks to me like a ton of people would like to be able to accomplish this. Google docs have upgraded and I was hoping you could give me some instruction based on those changes. I cannot seem to get the function to work. I appreciate any help you can offer.

    • tparishtparish07-18-2016

      Hi Scott:

      I haven’t yet retried this process with the new/upgraded Google docs. Would love to hear from anyone that has tried. Adding this to my project list.

  13. Jason NagelsJason Nagels06-16-2016

    Hi Tracy –

    I’m not sure if you’re still monitoring this post, but I’ve been banging my head trying to figure this out and I’m very close, but still no cigar.

    I have created a test training: http://nagelsconsulting.com/Lockout/story.html
    The idea is to have the firstname, lastname and score output to the google form.

    After following your instructions, I can see now that after completing the course the timestamp is entered, but none of the values are passing to the google form. Here is my spreadsheet results:
    https://docs.google.com/spreadsheets/d/1MTo41Vb8Vkfd6jaCPRzkdGg0LSHLUxk7GtRvBBrhvso/edit#gid=361735704

    I’ve tried it with and without – dataType: “jsonp”,
    and I’ve made sure to edit the story.html as per your instructions.

    Any idea what I might be doing wrong?

    • tparishtparish07-18-2016

      Sorry I missed your post Jason. Did you get it to work? I did see a final entry in the excel sheet seem to pass through.

      • Sammi KosarSammi Kosar08-29-2016

        I have the same issue as Jason. The timestamp is entered, but not the data. Any idea why? I have used dataType: “jsonp” and altered the story.html and html5.

        If I try to find the entry id number: it does not look as in your guide. For example is says “i.desc.557585583 i.err.557585583”. What should I type in the javascript?

        • tparishtparish08-29-2016

          Hi Sammi:

          I’m fighting with it all myself at the moment. One try I have it. I tweak something and then it doesn’t publish. It’s a frustrating process and one I wish, as well, was much similar to achieve.

          Google has changed thier forms up a bit so the entry ID is harder to find. You have to go lower into the code to see it. I use Chrome, view the form as if you were entering information into it. Right click on the first entry, on the menu click Inspect. You’ll let a bunch of code at the bottom of the screen. Where you are seeing i.desc.5575……. look down about 10 more linkes and you will see:
          ….input type=hiddent” name=”entry.xxxxxxxxxx”…….

          If you don’t see it there, the area might be collapsed and you’ll need to click the “black arrows” to the left of the code area. These areas can collapse and expanded by these arrows.

          Hope that helps a bit.

          • Sammi KosarSammi Kosar08-29-2016

            Thank you Tracy! I figured it out 🙂 I found the right entry-ID, and then it worked. So now we can use it for our live high score list – Gamification i the new black 😉

  14. AnthonyAnthony06-17-2016

    Hi Tracy
    Thank you for all the work you have done on this and for sharing.
    Can your steps be used to have user results from multiple Storyline projects sent to the ONE Googlesheet?
    Thanks.

    • tparishtparish07-18-2016

      My understanding would be yes, you could just add the javascript code into another storyfile and replace the “number” that would match the field of the excel form you would be sumbitting the data into.

  15. Tim HIllierTim HIllier08-01-2016

    Tracy;

    Thank you for these instructions. I have read through them and it seems to be exactly what I am looking for.

    I note in your reply to Scott that you are unsure if it works in the new Google docs. Before I try this I was wondering if you know if it does work in this environment yet.

    Thanks

    Tim

  16. Tim HIllierTim HIllier08-01-2016

    Sorry Tracy;

    I just relooked at the date of your response. You probably dont have any further information yet.

    Tim

  17. EmEm12-04-2016

    Thanks so much! I was feeling defeated when I saw our note on adding the latest version of JS to the script.

    Out of interest I used 3.1.1
    🙂
    #winning

Leave a Reply