Pass quiz scores and other Storyline variables into Smartsheet - without an LMS

I'm often looking for ways to track learning outside an LMS and recently I struggled to find a way to pass a Storyline quiz score into Smartsheet. I scoured the forums and found little pieces here and there, so I thought I would give back to the community that's helped me so much through the years by writing down my steps for others who might need the same thing.

This solution uses Storyline variables and triggers, a little JavaScript, and Smartsheet forms to pass the Storyline variables into a Smartsheet sheet.

Storyline variables

In Storyline, set up variables to be passed to the Smartsheet. The two I want to pass are:

  1. Email address ("Email")
  2.  Quiz Score ("Score")

Storyline variables

Smartsheet columns

In Smartsheet, set up columns to hold the course data 

  1. Created Date (System-generated auto-numbered)
  2. Email Address (Primary)
  3. Full Name (Text/number)
  4. Quiz Score (Text/number)

Smartsheet

Smartsheet form

In Smartsheet, create a form for the sheet to capture the data

  1. Hide (but don't delete or remove) the fields that will be passed from Storyline (email address and score)
  2. Set the score field validation to "Percentage"
  3. Note: The form must contain at least one visible field, so in my example I have one asking for the full name.
  4. Save the form 
  5. Copy and save the form's URL (under Manage Forms) to be used later

Smartsheet form

Storyline Triggers & JavaScript

In Storyline, add a JavaScript trigger on the quiz results slide that collects the variables and attaches them to the the form's URL (creating a "smartURL").

  1. Note: Storyline's built-in variables cannot be accessed yet, so create one slide trigger that sets the new variable "Score" to the built-in SL variable (Results.ScorePercent).
  2. Execute JavaScript:
//This opens a new window with the pre-filled Smartsheet form.
var player = GetPlayer();
var score = player.GetVar ("Score");
var email = player.GetVar ("Email");
window.open("https://app.smartsheet.com/b/form/12345"+"?Score="+score+"&Email="+email);

How the JavaScript works

Here's how the JavaScript works (...at least in my limited understanding):

  1. The double forward slashes define the first line as a comment - not code.
  2. The "var player" line starts the communication with the Storyline player to get the variables.
  3. The "var score" line gets the Score variable from Storyline and puts its value into a JavaScript variable named score.
  4. The "var email" line does the same with the Email variable.
  5. The "window.open" line opens a new window with the Smartsheet form pre-filled with the score and email variables.
    1. The "" enclose the pieces that make up the smartURL.
    2. The + connects the smartURL pieces (without any spaces).
    3. The ? goes after the form URL and tells the form which fields to pre-fill.
    4. The & connects the URL fields if there is more than one. If there were a third variable named comment, this would come before the end parenthesis and semi-colon: +"&Comment="+comment 
  6. Just as in Storyline, the variables can be any combination of letters and numbers. Capitalization matters, so it helped me differentiate my Storyline and JavaScript that way. My Storyline variables used caps.
  7. I've learned that JavaScript is very "picky". One missing quote mark or an unintended space brings everything to a screeching halt. 

 

 

Be the first to reply