Articulate Storyline: Export to 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).

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

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

 

157 Replies
Elizabeth Dennis

I got it to work. Thanks for your help!

One thing to add to the list of dos and don'ts: I think only text variables work. 

I had it set up so that when a user clicks a box, the variable that feeds to to the spreadsheet is set to equal another variable that equals Yes or No. It wouldn't work, but when I set it to equal Yes when the user clicks Button 1 or No when the user clicks Button 2 it worked. 

Anneloes Krom

Hi,
I've read the post and followed the steps. I managed to make it work for open questions like described. But i would also like to use it for mc-questions (I use the freeform pick one/many). Would that be possible?
I couldn't find the answer yet, but variables and javascript are new for me, so excuse me if I missed it.

Thanks!

Elizabeth Dennis

This weekend/next week I need to try to have several different scenes with javascript exporting to different sheets within one spreadsheet or different spreadsheets. My SMEs have decided we need to combine three courses, all with the javascript export. 

Has anyone tried using multiple Javascript exports in one course?

If not, I'll let you know how it goes. :)

 

Bastiaan Timmer

Hi Elizabeth,

I've just created a version with numbered variables and this version is also working fine. Below you'll find the publication of a very simple Storyline 2 module:
http://download.courseware.nl/052016/Number/story.html

With the link below you can see the results in Google Drive:
https://docs.google.com/spreadsheets/d/1-qP42B3BAS35LXjACW9N2jsKWLyTKVPRst5EvnUQOUU/edit?usp=sharing

I didn't use any other step as described above. In the attachment you'll find the Storyline project.

I think you could use different sheets, because every sheet has his own WEB APP URL. 

Best Regards,

Bastiaan

Martin Lemieux

Hi All, 

Thanks for this ultra helpful post!  It took me a long time, but I've finally got articulate storyline 2 and google sheets talking to one another.   I used the method above.

The date and the name fields are coming through just fine, but I have a third field set up to record the course grade, and it keeps giving me a grade of zero, regardless of what the actual grade was. 

Here is what the sheet looks like, in terms of the column headers:

Google sheets capture

I've attached my test file that I'm using to communicate with google sheets.

Can someone point me in the right direction why the grade keeps showing up as zero?

Thanks!

-Martin

Bastiaan Timmer

Hi Martin,

Great to hear it worked.

The problem was caused by the order of the triggers. See screenshot below:

You will have to move the selected trigger one position up. So you will first set the variable and then go to the next page. 

Normally we place this trigger on the "Success" and "Fail" layer. And then with the setting "When Time-line starts".

Best regards,

Bastiaan

Martin Lemieux

Hi Bastiaan,

I've got one more question - I'm not sure if I'm searching for the right thing in the forums, so I haven't really seen this specific challenge come up yet.

I've got a single spreadsheet in google drive with multiple sub-sheets within it (1 for each course).  

Sub sheets

Is there a way to have the variables export to a particular sub-sheet?

The URL key used in the steps for the variable export does not change, no matter which sub-sheet you are in.  All that changes is an ID number that is appended to the end of it.

url

There is only a single script editor page for the entire sheet, not separate ones for each sub-sheet.

So, I'm wondering what would have to change in the google sheet's script editor or in the javascript in Articulate that would point the data to that sub-sheet? How are multiple sub-sheets handled when there is only a single script editor for the entire sheet?

Any and all help would be welcome! 

Thanks!

-Martin

Bastiaan Timmer

Hi Martin,

It's possible, but you will have to change something within the template. Right now the template is looking for a sheet called "Data". If you want to use another sheet, you can use the following steps within the template:

  1. Open the template
  2. Go to Tool, Script Editor. You will see the code which does all the magic :-)
  3. On line 2 you will see the Sheet name. Change the value "DATA" to the value of your sheet name.

I hope this wil help you to finalize your project! Success!

 

Martin Lemieux

Hi Bastiaan,

That part of the process is clear to me - as I mentioned, I did get it to work.  

But from what I see, the way this is set up, is that you can send the results from one course to one sheet, or even to multiple sheets, but I don't see the way to send the results from multiple courses to multiple tabs within a single sheet.

I've got multiple different Articulate Storyline Courses.  (let's call them Course A, Course B and Course C for now.)

I want to send the results of all those courses to a single spreadsheet.  (Lets call it the results spreadsheet)

But I want them to go into three different sub-sheets (or tabs), one for Course A, one for Course B, and one for Course C within this one results spreadsheet, rather than create three separate spreadsheets for Courses A, B and C. 

Is this possible to do? Or should I just be creating a separate spreadsheet for each course?

Sorry to keep beating away at this point - its not the end of the world if it can't be done, but it would be convenient.

-Martin

Bastiaan Timmer

Hi Martin,

I don't know a specific solution for that approach. But you could also add an extra variable (column) to the spreadsheet, so you could use one spreadsheet for multiple courses.

In this extra column you could placed the course-id or course name and you could use this specific column to sort your data.

Maybe that's a solution for you.

Best regards,

Bastiaan

Abhishek Roy

Thanks for the great tutorial.

a) Will it work for concurrent users ? I have several users taking the course at same time ?Will it create a seperate row in Google Spreadsheet for each user ? or overwrite in the same row ?

b) The most important aspect i want is that the same row in the sheet should be used for a particular user for that specific attempt / session. Not create a new row for the same session whenever the javascript trigger is executed in Storyline. The JS trigger is set on each question slide to keep track where the user has left the course if he/ she exits the course halfway without completing it. Currently it creates a new row for each question.

Bastiaan Timmer

Hi Abhishek,

It will create a separate row for each submit action. I don't know what will happen if two users submit the form at exact the same time. But I don't think this will be a problem.

This script won't update an existing record. So it will create a new record for each question and you will have to filter the records in Google Drive.

You suggestion could be something for version 2.0 :-)