Articulate Storyline: Import from Google Drive

Jun 06, 2017

 

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.

54 Replies
Guilherme Batista

Perfect Bastiaan! I'm using another code to post variables at google spreadsheet and it works smoothly.

But now I have another 'challenge' is it a way to search the spreadsheet and return a specific line?

Basically, a login/password system at articulate that return data from spreadsheet from the specific line with the same login and password, but that line only.

Thanks so much! =)

Bastiaan Timmer

Hi Michelle,

Did you use the steps on the following page:
https://community.articulate.com/discussions/articulate-storyline/articulate-storyline-export-to-google-drive

Do you see the values "undefined"? Or do you see nothing, only empty cells?

In the first case, there must be something wrong with the variable names.

Best regards,

Bastiaan

Brett  Schlagel

Hi Bastiaan,

I'm also having troubles getting this to work. I get no data in the cells. I'm working in a document that also sends data to the same Google sheet. The sending of information works as it should, but the return of information doesn't seem to be making the trip from the sheet to SL.

I've ran through this 4 times, all new sheets from scratch. Not sure what I'm missing. Any help would be super appreciated. Thanks!

Bastiaan Timmer

Hi Brett,

Do you use a public spreadsheet? Could you try to open the JSON result? I can open the JSON result of my example with the link below:

https://spreadsheets.google.com/feeds/list/1Xt9PSUmjHkQl2xPrD8n_JDTTc3VwnZltPAuBWEgDH0A/od6/public/values?alt=json

If you want to test your spreadsheet, then you will need the change the ID of the spreedsheet. You will get the following link:

https://spreadsheets.google.com/feeds/list/ + ID Spreadsheet + /od6/public/values?alt=json

Could you test this link? If this link is empty, then you can't import data into Storyline.

Brett  Schlagel

I get an error that says "We're sorry, this report is not published." when I try to open the JSON result.

My document is set to public, anyone with a link can view. The script from the export tutorial has been published as a web app, also.

 

The sheet is published, but it still doesn't work. 


Thanks for the help!

Bastiaan Timmer

Hi Brett,

Could you please take a look at the following URL:
https://ctrlq.org/code/20004-google-spreadsheets-json

You will have to take a look at the sharing permissions of the specific spreadsheet and you will need to publish the spreadsheet (File -> Publish to the web -> Publish).

Brett  Schlagel

Hey,

I appreciate your patients. I've tried from scratch again. The doc is published to the web, the script is deployed as a web app. The SL files are being hosted through AWS. 

 

There isn't anything personal in this test spreedsheet. Here's a link to the JSON info:

 

https://spreadsheets.google.com/feeds/list/1aswcUlVDGRtGDd39LNOxYj0NN2qlEDnB1etHlW7Y9GQ/od6/public/values?alt=json

Thanks again for the help.

Bastiaan Timmer

Hi Luisa,

That's possible. You will have to write variables from different courses to the same spreadsheet. Than you can use the spreadsheet as gamification database. So you can make a ranking or show the users how much time an object or answer is clicked.

Google changed something in the mechanism to write variables to a spreadsheet (see my article about exporting variables to Google Drive), so it isn't working at the moment. I will need to find some time to figure out what's going wrong.

Another method for gamification is using xAPI. Only then you will need a Learning Record Store (LRS) to store the data.

With xAPI it's also possible to show information from other customers.

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