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/Demobestanden/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).

UPDATE 2022-02-01

As you may know Google changed something in the process of retrieving data. They switched to a new API. Therefore you will need to change the code at STEP 3. Below you'll find step 3 again with the new 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";
 var apiKEY = "AIzaSyCBXpOcgY9lqs4iWs28p21Qu3mE0gaOBH0";

// Make sure it is public or set to Anyone with link can view 
 var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetID + "/values/DATA?key=" + apiKEY;

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

 $.getJSON(url, function(data) {  
 console.log(data.values.reverse());

player.SetVar("vDate01",data.values[0][0]);
player.SetVar("vName01",data.values[0][1]);
player.SetVar("vEmail01",data.values[0][2]);
player.SetVar("vMessage01",data.values[0][3]);

player.SetVar("vDate02",data.values[1][0]);
player.SetVar("vName02",data.values[1][1]);
player.SetVar("vEmail02",data.values[1][2]);
player.SetVar("vMessage02",data.values[1][3]);

player.SetVar("vDate03",data.values[2][0]);
player.SetVar("vName03",data.values[2][1]);
player.SetVar("vEmail03",data.values[2][2]);
player.SetVar("vMessage03",data.values[2][3]);

player.SetVar("vDate04",data.values[3][0]);
player.SetVar("vName04",data.values[3][1]);
player.SetVar("vEmail04",data.values[3][2]);
player.SetVar("vMessage04",data.values[3][3]);

});

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

apiKEY: The API KEY is necessary for authenticating the spreadsheet. These keys are personal and created for a specific project. You can learn more about this with the link below:
Use API KEYS

The Storyline variables: the Storyline variables are case sensitive. Below you'll find one line with explanation:

player.SetVar("vDate01",data.values[0][0]);

vDate01: This is the Storyline variable.

[0][0]: With these number you can specify which record you want to see in Storyline. In this case you will get the date from the last record of the speadsheet (the most recent record). The first number is a reference for the row and the second number for the column. If you want the name of the third record, you will have to use a [2][1].

As an example for the last record:

• [0][0] = this is the data field
• [0][1] = this is the name field
• [0][2] = this is the email field
• [0][3] = this is the message field

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 is on your Title screen, so you can use the information on all slides within your title. 

Note/DISCLAIMER: 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.