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 Michael,
The spreadsheet is read-only. But if you use the steps on the link below, you could add new records to an existing spreadsheet:
Thanks Bastian for sharing all those steps here as a new post.
- MichaelAnder569Community Member
Is this method secure at all or can anyone make changes to the spreadsheet if they have the spreadsheet ID?
- PhilMayorSuper Hero
THANKS FOR SHARING THIS IS GREAT!
Oops sorry for shouting :-)
- BastiaanTimmerPartner
Thanks Phil :-)
- NigelRibeiro-4aCommunity Member
Thanks for sharing, This is great, just what I was looking for. I am going to try and use it for game ranking!
- GuilhermeBat206Community Member
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! =)
- TinaMarieLalondCommunity Member
Bastiaan. Thank you so much. I greatly appreciate you sharing your expertise. :-)
- MichelleJime146Community Member
Hi there, I created my own sheet but no values are displayed. I followed everything but still no luck! please help :-) thanks
Hey there, Michelle! Just in case, you can also use the Contact Me option on Bastian's profile to send him follow up questions on his example.
- MichelleJimenezCommunity Member
Hi Crystal,
I sorted it out, many thanks.
Michelle
- BastiaanTimmerPartner
Hi Michelle,
Did you use the steps on the following page:
https://community.articulate.com/discussions/articulate-storyline/articulate-storyline-export-to-google-driveDo 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
- BrettSchlage313Community Member
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!