Articulate Storyline: Export to Google Drive

Nov 18, 2015

Bastiaan Timmer
By Bastiaan Timmer

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
Mike Taylor
over 3 years ago09/16/20 at 2:33 pm (UTC)

I'm having the same issue as Elyse. I tried her updated code and that didn't work for me. (BTW should that https line include a colon? (ie. https://code.jquery.com/jquery-1.11.0.min.js) 

I get the date to populate but the variables are blank. (I'm running it from SCORM Cloud.) 

Any suggestions or ideas about what I'm missing? 

Thanks,
Mike 

Residential Community Standards
over 3 years ago09/23/20 at 4:08 pm (UTC)

Hello, 

I am trying to get the record of the learner's record exported from the storyline 360 courses to google sheet for backend record tracking. I have created the demo version where I have created two slides, the first slide to collect the student's record using 'text entry'. On the second slide, I have used the variables to get the student's record on the slide and set up the javascript to export the data using the option "When the timeline starts on this slide".

I have set up the google sheet as per the instruction on this article and set up the javascript variables on my Storyline 360 course as well. But somehow the data is not pushed from the storyline to the google sheet. 

Here is my google sheet script code as per this article:


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

// 2. Enter the KEY of your form
var KEY = "1I-2bT8Nh5rwUCtDF3bnsUic_usfNcepsJ_bKc1GBq18";

// 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] == "SystemDate"){ // 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());
}

 

Here are the variables I have used to collect the data:

StudentFirstName

StudentLastName

StudentID

BuildingRoomNumber

StudentEmailID

Here is the javascript-based on this article:

Javascript 1:

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)

 

Javascript 2:

var player = GetPlayer();

//PLACE YOUR WEB APP URL
WEB_APP_URL: "https://script.google.com/macros/s/AKfycbzZtaVUYgduE6riPnPUSsajbQYdnJ2y16-GoH4egGSXpyB0Ruw/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
"StudentFirstName" : player.GetVar("StudentFirstName"),
"StudentLastName" : player.GetVar("StudentLastName"),
"StudentID" : player.GetVar("StudentID"),
"BuildingRoomNumber" : player.GetVar("BuildingRoomNumber"),
"StudentSbuEmailID" : player.GetVar("StudentSbuEmailID")
}

Javascript 3:

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

 

Can someone help me to find the problem of why this thing is not working? I also want to populate the student's the percentage/score received in quiz using the inbuilt variable. Is there any changes/updated has been done on the google's side or Articulate side, so the codes are not responding? 

Thank you,

Pratik Parekh

 

 

 

Bastiaan Timmer
over 3 years ago10/06/20 at 6:31 am (UTC)

Hi Pratik,

Did you make the form public? So everyone can access your form? If I use the form code within the link below, I need to have access to access the form:
https://docs.google.com/spreadsheets/d/1I-2bT8Nh5rwUCtDF3bnsUic_usfNcepsJ_bKc1GBq18/edit#gid=0

And if you login, don't you see any results?

I've just followed all the steps again and it's still working. Below you can find my result with the latest version of Storyline 360:
https://download.courseware.nl/Articulate/GoogleDrive/2020/story.html

My link seems to be private to, but there is a result within my spreadsheet.

I will update this article and add my source file to this project. Right now the link isn't working anymore.

Best regards,

Bastiaan

Bastiaan Timmer
over 3 years ago10/06/20 at 7:27 am (UTC)

Good morning,

The article has just been updated. The link to the source package did not work anymore. This link has just been corrected.

I went through the step-by-step plan again with the latest version of Articulate Storyline 360. If all steps are followed correctly, it still works correctly.

Best regards,

Bastiaan

Martha Garcia
over 3 years ago11/30/20 at 8:41 pm (UTC)

Please! what am I doing wrong, I've tried a dz edits and I just cant seem to get anything else other than the date. What am i doing wrong? My brain hurts

 

sheet  - https://docs.google.com/spreadsheets/d/1YM_fmqh29Gn3kr2p8R-bf6vL6hOOfSIZ7eaDD6YbqyE/edit#gid=0

Scott Syer
3 years ago02/09/21 at 3:43 pm (UTC)

This may have been already handled in this thread, but I am running into an issue sending variables to my Google sheet from a Workday Learning popup window. I have tested in a regular web scenario, and all works fine. It seems as though the popup window is preventing the variables from being transferred. Thanks as always for the assistance.

Andreas Fischer
3 years ago03/22/21 at 2:34 am (UTC)

Hi there,

First of all I'd like to say a big THANK YOU to Bastiaan for the detailed description of this process. Not sure if this post is still active and followed but I'm struggling to get the data export work. I tried the step by step process with my own project without success. Then I used the actual templates (Storyline and the Excel template) to make sure I'm not missing any steps --> No success. Instead of the template I tried to copy the code into the script editor but also no success. After reading all the comments twice I'm a bit at the end and wonder if our companies LMS is the issue. We use SuccessFactors. Did anyone else experience the LMS maybe blocking the transfer of data via a Javascript code? I'd be grateful for any hints or tips as I'd really like to use this function. Happy to share the Storyline file but it's basically exactly the same as the template (other than the Web App address. Thank you ....

Math Notermans
3 years ago03/22/21 at 4:42 am (UTC)

Allthough i cannot ensure this will help solve your issues, reading up on comments i do notice a few things returning.

Make sure you are running from https protocol to prevent security errors.
A popup window might block because of security errors (CORS)
Try replacing all jQuery related calls by Vanilla Javascript ( https://tobiasahlin.com/blog/move-from-jquery-to-vanilla-javascript/ )
Also you can add jQuery to the html in your Program folder for Articulate360.
(C:\Program Files (x86)\Articulate\360\Storyline\player\unified) then the need for adding it in Storyline disappears.

Jessica Fraga
3 years ago03/26/21 at 3:31 pm (UTC)

Hello, i used this method and it worked perfectly, thanks for that! 

I was wondering if there's any way to update entries? I'm working on a course that has multiple modules and each of them will add a new information to the user's entry, but i have no idea how to achieve that without creating new rows... any help will be appreciated!

Ryan Bothwell
almost 3 years ago05/25/21 at 1:44 pm (UTC)

I have tried following this multiple times, my document is public and I have used the exact templates as provided at the beginning as well as the SOURCE articulate package also provided here. 

Despite setting up everything exactly as mentioned this does not work. It will not write anything whatsoever to the google sheet yet when I use the OP example as a reference it notes my details in OPs form fine. This would lead me to believe google have not changed anything.

Does anyone have any idea why this may not be working?

Sign In to Reply