Articulate Storyline: Import from Google Drive

 

PREVIEW  |  SOURCE

On the Articulate user days in Utrecht (2015), we held a session about exporting Articulate Storyline variables to Google Drive (Spreadsheet). This export is achieved via JavaScript (jQuery). The details of this method is described on the page below:

Articulate Storyline: Export to Google Drive

Some people asked me if I also could import these results back into Storyline. This article described the steps for importing data from a Google Spreadsheet. I used the same spreadsheet as we used for exporting data to Google Drive.

Use the button Source to download the Storyline project. If you want the same icons as in the example above, please install the following font (see source file):

  • flaticons-stroke.ttf

Why do you want to make an import from Google Drive?

  • You can make a Storyline module more interactive. Users can compare their answers with real-time answers of others.
  • You can use this setup for "gamification" elements, like a high score or something else.

Why using Javascript?

Besides using Javascript it's also possible to embed a Google spreadsheet as WEB object. This is a lot easier than using Javascript.

We have chosen for Javascript because we would like the ability to change the appearance of the result. And we only want to see the last 4 records of the spreadsheet, not all records.

Add an extra record?

If you want to test a new Google Spreadsheet record, you can add a new comment with the form below:

http://download.courseware.nl/Articulate/GoogleDrive/EN/story.html

Import from Google Drive

The import consists of several steps. All these steps take place within Storyline. This example is created with Storyline 360, but you can also use these steps within Storyline 2.

Note: You can only use this import functionality with public Google spreadsheets.

Below you'll find the steps to create the import from Google Drive: 

1. Add jQuery Library

In Articulate, add a trigger to run javascript (Execute Javascript) and use the code below. You can run this trigger when the timeline starts.

This code will add the jQuery library to this project, so you won't have to change the HTML files after publishing the project. The jQuery library is needed for importing the information to Storyline.

var head = document.getElementsByTagName('head')[0];
var script = document.createElement('script');
script.src = '//code.jquery.com/jquery-3.1.1.min.js';
script.type = 'text/javascript';
head.appendChild(script)

2. Create the Storyline variables to store the data

Before you can import the data in Storyline, you will have to create the Storyline variables. In this case, we've created 16 variables:

  • vDate01 - vDate04
  • vEmail01 - vEmail04
  • vName01 - vEmail04
  • vMessage01 - vMessage04

 All variables are text variables. The default value is empty.

3. Add script to store information

Use the script below for saving the information from Google Spreadsheet into the Storyline variables:

var player = GetPlayer();

// ID of the Google Spreadsheet
var spreadsheetID = "1Xt9PSUmjHkQl2xPrD8n_JDTTc3VwnZltPAuBWEgDH0A";

// Make sure it is public or set to Anyone with link can view
var url = "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/values?alt=json";

$.getJSON(url, function(data) {

var entry = data.feed.entry.reverse();

//DELAY SO JQUERY LIBRARY IS LOADED
setTimeout(function (){

player.SetVar("vDate01",data.feed.entry[0]['gsx$date']['$t']);
player.SetVar("vName01",data.feed.entry[0]['gsx$name']['$t']);
player.SetVar("vEmail01",data.feed.entry[0]['gsx$email']['$t']);
player.SetVar("vMessage01",data.feed.entry[0]['gsx$message']['$t']);

player.SetVar("vDate02",data.feed.entry[1]['gsx$date']['$t']);
player.SetVar("vName02",data.feed.entry[1]['gsx$name']['$t']);
player.SetVar("vEmail02",data.feed.entry[1]['gsx$email']['$t']);
player.SetVar("vMessage02",data.feed.entry[1]['gsx$message']['$t']);

player.SetVar("vDate03",data.feed.entry[2]['gsx$date']['$t']);
player.SetVar("vName03",data.feed.entry[2]['gsx$name']['$t']);
player.SetVar("vEmail03",data.feed.entry[2]['gsx$email']['$t']);
player.SetVar("vMessage03",data.feed.entry[2]['gsx$message']['$t']);

player.SetVar("vDate04",data.feed.entry[3]['gsx$date']['$t']);
player.SetVar("vName04",data.feed.entry[3]['gsx$name']['$t']);
player.SetVar("vEmail04",data.feed.entry[3]['gsx$email']['$t']);
player.SetVar("vMessage04",data.feed.entry[3]['gsx$message']['$t']);

});

return false;
}, 1000);

In the above code there are a couple of important things:

  • Spreadsheet ID: You will need to add the spreadsheet ID to the code above, otherwise you won't get any result. Below you'll find the link of the spreadsheet. The ID is bold:
    https://docs.google.com/spreadsheets/d/1Xt9PSUmjHkQl2xPrD8n_JDTTc3VwnZltPAuBWEgDH0A/edit#gid=0
  • The Storyline variables: the Storyline variables are case sensitive. Below you'll find one line with explanation:
    player.SetVar("vDate01",data.feed.entry[0]['gsx$date']['$t']);
    • vDate01: This is the Storyline variable
    • 0: With this number you can specify which record you want to see in Storyline. In this case you will get the last record. If you want to third record, you will have to use a 2.
    • Date: This is the column name within the Google Spreadsheet.

After importing the variables into Storyline, you can use these variables within text fields or shapes with the following parameters:

  • %vDate01%
  • %vEmail01%
  • %vName01%
  • %vMessage01%

The best way is to import these variables on your Title screen, so you can use the information on all slides within your title. 

4. Final step publish to SCORM or WEB format

Publish your articulate project to WEB or SCORM format. You need to host it on a WEB server or somewhere like SCORM cloud (or a LMS).

This export will work in Flash and HTML5 output. You can't use the Articulate Mobile Player, because it won't support Javascript code.

Note: When you use external scripts or data from an external source, then you can get problems if the external source made some changes. In this case we use Google as external source, so if Google change something this script can be broken.

 

44 Replies
Bastiaan Timmer

Hi Luisa,

Melissa Milloway has created an article about Articulate Storyline and xAPI. It's an introduction, which uses a trial version of the Yet Analytics LRS. You can find the article with the link below:

https://www.linkedin.com/pulse/send-xapi-statements-from-storyline-5-short-videos-milloway-msit

 

Brian McCann

This is awesome, Bastiaan! But I have question. I want people to submit a text entry variable in one course, that I'll export to Google Sheets and then have a separate course where I want to import the variable from Google Sheets. I'd like them to use a unique employee number when they submit the initial text entry variable and have them use that same employee number in course #2 so it displays on their variable from #1, rather than someone else's or the last four comments, as seen in the example.

Is that possible? I don't know JavaScript much at all. Thank you.

Nicholas Soldatenko

Well, each user can pull only his/her own data. So if you have fields X and Y set up in the table, they can pull either X or Y independently of one another. As the widget owner, you have access to everyone's records through the Stencil interface. There's also a community forum if someone has questions about how the widgets work: https://cluelabs.com/community/forum/

 

Nicholas Soldatenko

Are you using the same table for both courses? If the tables are separate then no, each course has access to its own table. I guess you could make it happen if you used one table for both courses, but then you would also need to find a way to port the value of the stencilusertoken variable between the courses, so each learner keeps his/her unique token across courses.

BENOIT GUILLO

Hello everybody,

I have successfully send variables from storyline to google sheet thanks to Kate Robertson's solution. But I didn't succeed in riding the return path even I scrupulously followed Bastiaan Timmer's steps. May be something has changed since the scripts were written ?

Is there someone who, recently, experimented retrieving datas from google spreadsheet to populate storyline variables ?

Thank you all for your work, tips and tricks !