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.

 

196 Replies
ELO UPEI

I was really hoping to have had a solution on this one by now...so can I open this back up to the Community?

I'd like to connect my project to a Google sheet. That sheet would record input from users when they first start (collecting their name and email). It would also automatically record a time stamp of their starting time. Then when the user exits, it would automatically record a second time stamp to show the user's finish time. This way I could track who is coming in and how long each user spends there.

Bruce Roberts

Fix Found:
Not sure if something had changed, but, having tried out the template,  I  selected "Test web app for your latest code" in the Script Editor and I was getting the error message:

{"result":"error","error":{"message":"Invalid argument: id","name":"Exception","fileName":"Code","lineNumber":37,"stack":"\tat Code:37 (handleResponse)\n\tat Code:21 (doGet)\n"}}

If like me, this is a revisit to an existing spreadsheet, but you are adding this code, At Step 9, when you select >Run, >Setup, you will need to press the small triangular "play icon", just to the left of where you selected "Setup" (Beside the "Debug" icon")

After saving and Publishing, the self check works fine.

Ryan Thomson

Thus isn't really any different than the stick example. I would suggest
rather than trying to store the start time and user details right away then
add more later, to just internally store that stuff in storyline, then at
the end add everything to the dock.

This way you avoid the hassle of having to code something to find the line
in the sock to continue adding thing to. Does that make sense? I built
something very similar to this.

john ball

Hello, this is great thank you! Could I ask is there a way of using the correct/incorrect feedback layers to submit a variable to google drive? I've been playing around with this for a while now and can only get the radio buttons to work. Some of my questions are multiple drag and drop - what would be the best approach for this please?
Regards,

John.

Dev Davidson

Hi, I used this method with great success. Thanks a lot Baastian. I published the course on my laptop and used it to get my score for the test in google spreadsheet. But how do i map the learners. Say i have 10 learners. I have a link to access my course on my website (i do not use LMS). If all 10 learners take the course, how do i map the score of each leaner to the learner, how do i achieve to get learner A's score is X and learner B's score is Y and so on on my spreadsheet. Thanks in advance.

Anderson Rumuy

I already tried but still doesnt work out, please anybody could tell me where did I go wrong.

I set trigger (execute javascript) when time line start at my final/last slide

others JS work fine at the same slide: $(".volume").hide(); & others 2 JS 

here is my code for Export to Google spreadsheet:

first JS when time line start:

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)


Second JS when time line start:

var player = GetPlayer();

//PLACE YOUR WEB APP URL
WEB_APP_URL = "https://script.google.com/macros/s/AKfycbwz8K0yH2XFKK44vPg5ncTQT8FztUivgAM-0DzvK85G7T1vaLc/exec";

// 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
"Tanggal" : player.GetVar("Tanggal"),
"Nama" : player.GetVar("Nama")
}

Third JS when time line start:


//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);

and here is a Link to my Course:

https://s3.amazonaws.com/tempshare-stage.storyline.articulate.com/sto_1dmv4pqcq7g57j61f8r5dkrv09/story.html


any though will be appreciate. Thank You


Simon Richards

Good day all

I'm trying this with limited success and wondered if anyone might be able to help me on a consultancy basis? 

Basically I have data entry boxes across my course that users fill out with diff info. They then need to take what they have created to a face to face session so need to be able to access a personalised google sheets doc.

If anyone is able to help me achieve this give me a shout

 

Thanks

Matthew Bibby
Simon Richards

Good day all

I'm trying this with limited success and wondered if anyone might be able to help me on a consultancy basis? 

Basically I have data entry boxes across my course that users fill out with diff info. They then need to take what they have created to a face to face session so need to be able to access a personalised google sheets doc.

If anyone is able to help me achieve this give me a shout 

Thanks

Hey Simon,

Rather than using Google Docs here, it might make more sense to generate a PDF for each user that contains their responses. 

Eswarkumar Kantheti

Hi Anderson Rumuy thank you for sharing. I want to do a small modification.

--------------------------------------------------------------------------------------------

There is a line like below in "first JS when time line start:" section.

script.src = '//code.jquery.com/jquery-1.11.0.min.js';

The edited line like below.

script.src = 'http://code.jquery.com/jquery-1.11.0.min.js';
--------------------------------------------------------------------------------------------

I just added "http:" in the above line. Please give me your valuable replay on this.

Jack Waller

Hi Lucia, 

I've taken a look at your file and spotted a couple of things. 

1. In your code I can see you've commented other variables. I would try removing them completely to see if that changes anything. But also you have a comma (",") after the variable. The last variable shouldn't have a comma. So remove that and see what happens. 

2. I would also try executing the javascript when the timeline begins rather than on a button click. 

Before you go into Articulate you can test it in google sheets by testing the web app link that is generated. If that shows undefined its not going to work, if it shows success then you're ready to go into Articulate. 

Hope this helps, 

Jack

David Guillemette

Hi there!

I've been using this method to send variables to Google Sheets for several months and it worked very well. But strangely, since we migrated our website to a new host (which uses nginx servers), it doesn't work anymore. They confirm to me on their side that no external communication  blocked, but ultimately, the information doesn't go to Sheets anymore.

Here's what my browser console says :
---From the first slide---
user.js:81 Uncaught SyntaxError: Invalid or unexpected token
/favicon.ico:1 Failed to load resource: the server responded with a status of 404 ()

---From the end / trigger slide---
app.min.js:3 actionator::exeJavaScript - this.execUserJs is not a function

David Guillemette

Hi Steve, thanks for catching up!

In bold below, this is the part Firefox highlight as the issue. 

storyline =
{
"date" : new Date().toJSON().slice(0,10), //STORE DATE
"prénom" : player.GetVar("prenom3"),
"nom" : player.GetVar("nom1"),
"courriel" : player.GetVar("courriel1"),
"Le développement du cerveau" : player.GetVar("01developpementducerveau"),
"Les grands principes du neurodeveloppement" : player.GetVar("02grandsprincipesdeneurodeveloppement"),
"L'environnement social" : player.GetVar("03environnementsocial"),
"Les facteurs de protection": player.GetVar("04facteursdeprotection"),
"L'histoire de Catherine: player.GetVar("05LhistoireDeCatherine"),
"Complété": player.GetVar("fin")
}

And then, you know what ? It was the missing " after Catherine who blocked everything! It's working well now. Thanks for pointing this out!