Articulate Storyline: Export to Google Drive

Nov 18, 2015

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

Use the button Source to download the Storyline project. If you want the same lay-out as the example above, please install the following fonts (see source file):

  • flaticons-stroke.ttf
  • fontawesome-webfont.ttf

Why do you want to make an export to Google Drive?

  • No LMS (or database) available;
  • Store more information than just the test results. You can export all Storyline variables to Google Drive.

Why using Javascript?

Besides using Javascript it's also possible to embed a Google form 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 form. In addition, we want to collect data across multiple slides and then store them in Google Drive.

UPDATE 2017-06-06:

There is also an article available for importing records of a Google Spreadsheet into Storyline:

Articulate Storyline: Import from Google Drive

UPDATE 18-10-2018: PROBLEMS WITH NEW SPREADSHEET, TRY TO USE A TEMPLATE

Google changed somthing within the mechanism of storing variables into a Google Spreadsheet. Creating a new spreadsheet can give you problems for storing variables. Before you try the steps below, please use a template:

Go to the page below and use the button Use this template:
https://drive.google.com/previewtemplate?id=1vrqb9ykwCSxzjcBIpYO7P9AXPyh37J3HviSwhZvYc_U&mode=public

And go further with step 4 below!

Export to Google Drive

The export consists of several steps. Some steps take place on the side of Google. For these steps you will need a Google account. The remaining steps take place in Articulate Storyline.

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

1. Login with your Google account

Go to the page below and login with your credentials:
https://docs.google.com/spreadsheets/

2. Create a new spreadsheet

Click on the Plus-sign (+) to create a new spreadsheet.

3. Rename the sheet to DATA

Give the spreadsheet a title and change the name of the sheet to DATA.

4. Add extra columns

Add extra columns you would like to use. Probably these columns will have the same name as the variables in Articulate Storyline. As example: the column names name, email and message like in the source project.

You can add the column date in the spreadsheet, if you would like to save the date when the form is sent to Google.

The column names needs to be identical to the variable names in Articulate Storyline. The column names are case sensitive.

 5. Copy the ID of your form

Find out your spreadsheet ‘key’ by looking in the address bar, the key is the long series of letters and numbers after /d/ and before /edit: Like:

https://docs.google.com/spreadsheets/d/1AzBuim89ma_ght1-O14cksVzXrQL5Vh4XnRqY9OM_gc/edit#gid=0

The KEY will be = 1AzBuim89ma_ght1-O14cksVzXrQL5Vh4XnRqY9OM_gc

Save this KEY in a Notepad file to keep safe, or other application, you will need this ID in step 8.

6. Open the Script Editor

Open the script editor Tools, Script Editor.

7. Paste custom script

If you are using the template, then you can skip this step. The code is already in the template available. If not, paste the script below, which is needed for importing the Storyline variables into this spreadsheet:

// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "DATA";

// 2. Enter the KEY of your form
var KEY = "KEY"

// 3. Run > setup

// 4. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)

// 5. Copy the 'Current web app URL' and post this in your form/script action

// 6. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function

function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}

function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.

try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty(KEY));
var sheet = doc.getSheetByName(SHEET_NAME);

// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e})) .setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}

function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty(KEY, doc.getId());
}

8. Paste your key

There is one place in the script where it says var KEY = "KEY". Copy and paste your key between the "".

9. Run the script

Run the script via Run, Run function, setup. The first time you run the script it will ask for permission to run. You will need to grant it. If you run the script for a second time you won't get any popup. This is an indication it has run successfully.

10. Deploy a web app

Go to Publish, Deploy as web app. Enter a project name (optional) and set security level. Choose for Me and access Anyone, even anonymously. Click on the button Deploy to create the web app.

11. Copy the Current web app URL

Copy the 'Current web app URL' and paste it in a Notepad file to keep safe.

Example URL:

https://script.google.com/macros/s/AKfycbyoP8c3_wlons5PSHx1W8PWJx4pn7t3ch-_IxTz0dVIKFw1AGLN/exec

12. Add jQuery library

In Articulate, add a trigger to run javascript (Execute Javascript) and use the code below.

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 exporting the information to Google Drive.

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

13. Store information

Add another trigger to run Javascript (Execute Javascript). You can use the code below.

Replace the value Current web app URL for the webapp url you've saved in step 11

Below the webapp URL, you can place the column names of the spreadsheet and the Storyline variables. Please be aware of the comma if you add multiple variables.

var player = GetPlayer();

//PLACE YOUR WEB APP URL
WEB_APP_URL = "Current web app URL";

// STORE ARTICULATE STORYLINE VARIABLES
// "Columnname_Google_Spreadsheet" : player.GetVar("Name_Storyline_Variable")
// ATTENTION: Use a comma if you use multiple Storyline variables
storyline =
{
"date" : new Date().toJSON().slice(0,10), //STORE DATE
"name" : player.GetVar("name"),
"email" : player.GetVar("email"),
"message" : player.GetVar("message")
}

Don't delete the row below, if you would like to save the date when the form is sent:

"date" : new Date().toJSON().slice(0,10), //STORE DATE

14. Export code to Google Drive

Latest Javascript code. Add another trigger to run Javascript (Execute Javascript). You can use the code below. This trigger will send the information from step 13 to Google Drive.

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

//Export to Google
$.ajax({
url: WEB_APP_URL,
type: "POST",
data : storyline,
success: function(data)
{
console.log(data);
},
error: function(err) {
console.log('Error:', err);
}
});
return false;
}, 1000);

15. 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.

Thank You

Thanks Kate Robertson for making the blog post below:
Exporting Variables into a Google Spreadsheet

I started with your setup and then have tried to simplify the steps by changing the Javascript code and importing the JQuery Library.

 

277 Replies
Sam Lincoln

Hi Bastiaan, I hope you're still monitoring this excellent article.

I've downloaded your source file. Created my own spreadsheet as instructed and used your source file to populate my spreadsheet (making the necessary URL amendments of course).

When I submit the answers I get the following webpage message: {"result": "success", "row: 8}

On checking the spreadsheet there are in fact 8 rows pupulated but all entries are "undefined".

I've double-checked variable/row head naming and they are identical so no case sensitive conflict.

Can you diagnose the problem from this description please?

Bastiaan Timmer

Hi Sam,

I'm still reading this topic, so I hope I can diagnose the problem. Undefined means that the export to the Google Spreadsheet is working fine, but there must be something wrong with the variables.

Is it possible to send me your Storyline project so I can take a look at the scripts and the variables?

Thanks in advance!

Bastiaan Timmer

Hi Sam,

Do you also have the URL of your spreadsheet? In the Storyline project I only find the following URL, but this is the URL for submitting the variables:

https://script.google.com/macros/s/AKfycbxV70H2o5wE-AmhZFCrjIrGMnkmKbbcEHv5qE1SO3FY31Aym-px/exec

Is there a column "input" available in your spreadsheet?

Bastiaan Timmer

Hi Sam,

And I noticed you've changed the last Javascript trigger. You don't have to add the WEB APP URL into this trigger. You can use the code below. The WEB APP URL is added on the second trigger.

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

//Export to Google
$.ajax({
url: WEB_APP_URL,
type: "POST",
data : storyline,
success: function(data)
{
console.log(data);
},
error: function(err) {
console.log('Error:', err);
}
});
return false;
}, 1000);

 

Simos Christodoulou

Hi all,

I connected my storyline with Google Sheets and it works fine. My question is, would it be possible to connect storyline with an offline excel sheet saved in a local directory or even inside a server directory in which I will keep both the published storyline files and the Excel sheet?

I want to be able to save user input from my company's employees inside a file but I cannot use Google Docs or any other "external" similar provider for security reasons. Do you have any alternative solutions as where to store the data from storyline and how?

 

Best regards,

Simos

tom parks

Hi all, this is a great solution for passing variables from articulate, thanks for the tutorial. Sorry to change the topic from the above but, I'm curious as to how secure the data is once it has been passed to google docs? As the web app is set to allow access from 'anyone even anonymous' is this leaving the data freely available /vulnerable?

Bastiaan Timmer

Hi Tom,

Thanks for your reply. Yes, the data is freely available if they know the address of the spreadsheet. You will need to make it public otherwise you can't export data to the spreadsheet. In your email you also referred to the variables in the Storyline project. They can't access the variables of your storyline project. They can only access (read) the spreadsheet if they know the address of the spreadsheet.

Alison Gretchen Stander

Hi Bastiaan, I hope you are still monitoring this thread. If not, maybe someone else can help, please??

I have used the template and everything works fine, thank you for making it available.

Within my project there are a couple of variables being sent back to the spreadsheet. What I have found though is that the student will need to complete the whole workshop before the data is sent to the spreadsheet.

I would like to know is it possible to update the spreadsheet every 60seconds so that the data that is "captured" within storyline can be sent to the spreadsheet, so that I can track their progress?

If that is not possible how can I break the script down so that as they progress the answers be sent to the spreadsheet?

Thanks

Bastiaan Timmer

Hi Alison,

Thanks for your reply. In this example the variables will be send to the spreadsheet when you fire the following JavaScript code:

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

//Export to Google
$.ajax({
url: WEB_APP_URL,
type: "POST",
data : storyline,
success: function(data)
{
console.log(data);
},
error: function(err) {
console.log('Error:', err);
}
});
return false;
}, 1000);

Note: It will always create an extra spreadsheet row, with the information of the variables.

If you got multiple variables, and you would like to submit them separately, then you will get multiple rows for one particular user. It won't update the existing record.

So this solution won't be the best solution for you.

Maybe someone else knows another script to update an existing record. In this case, you can send multiple variables to the spreadsheet and it will update the existing record of this specific user. Otherwise you will have to make a filter within the spreadsheet to find all the details of one user.

Alison Gretchen Stander

Thanks Bastiaan,

I agree, and I understand everything you've mentioned.

My scenario is this, we run our storyline workshops/"outputs" on a LMS (Blackboard), but sometimes our students get "kicked" out of Blackboard before they finish the workshop. Since the example code only appears at the end of a workshop, the code cannot execute and I do not know (cannot track) where they were within the workshop.

So, basically I am looking for a way for the information to be sent to the spreadsheet within certain time intervals? So yes, I'm looking for some script to update an existing record every few minutes.

Anyone??

Bastiaan Timmer

Hi Alison,

If you are publishing for Blackboard, then you could also use SCORM as publication format. If you use SCORM 2004 4th edition, then you could save the variable information between sessions. These variables will be stored in the Suspend Data table of the LMS.

So if a user comes back to follow their course, all the information will be retrieved. At the end of the module you could use the export to Google Drive.

Important: Within the settings of the player you will find a resume functionality (see menu Other within the player). This functionality need to be set to "Always Resume". Otherwise users can reset the variable information.