Forum Discussion
Articulate Storyline: Import from Google Drive
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.
- BastiaanTimmerPartner
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:
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.
- MichelleJimenezCommunity Member
Hi Basti,
I managed to make it work, values got displayed when I published the sheet. Thanks a lot.
Hi Brett,
Try to publish the google sheet and will it work :-)
Thanks all for sharing :-)
Michelle
- BrettSchlage313Community Member
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!- BastiaanTimmerPartner
Hi Brett,
Could you please take a look at the following URL:
https://ctrlq.org/code/20004-google-spreadsheets-jsonYou 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).
- BrettSchlage313Community Member
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:
Thanks again for the help.
- BastiaanTimmerPartner
Hi Brett,
If I use the above link I see the JSON code, so I think it's working now. This JSON code can be used to retrieve data in Storyline.
Only the last rows in the spreadsheet are empty I think. I don't see any value in the name or email field.
- BrettSchlage313Community Member
Thanks for looking again. I've placed data in all columns in the sheet and still cannot get it to import data. I think I'm going to tap out for now. I might revisit this when I can dedicate more time. Thanks again for the help.
- LuisaPetrilloCommunity Member
Thanks Bastian for sharing this method.
Is it possible to use it to write and read a set of variables through different courses?
For example, to set up a gamification system through many different scorm? - BastiaanTimmerPartner
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.
- StephenGoreyCommunity Member
Hi, was their ever a fix for this? Not working for me at the moment, many thanks!
- LuisaPetrilloCommunity Member
Thanks a lot. This is very nice. But do you know if there is a tutorial to get the javascript code to use xapi?
- BastiaanTimmerPartner
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
- PatriciaNunallyCommunity Member
Can I import a quiz from Google into Storyline?