Articulate Storyline: Export to Google Drive
Nov 18, 2015
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
Hi Bastiaan, thank you for this post and for providing the Google sheet template and updates! I've been using this for several years. My existing exports are still working, but this week I've not been able to set up a new export, despite rechecking and starting afresh.
I've checked the usual suspects - that the sheet is public, google permissions, javascript commas, order of triggers in Storyline.
Do you think google has made a change that could stop new instances from working? Has anyone else made this work recently using Bastiaan's template and steps?
Likewise I got this to work last year no problem. For some reason I used the same method again this year with a new template and it will not work anymore. Any suggestions folks?
It seems fairly recent, I set up an SL to Google export without any problems in April.
Woohoo! I tried again but left the google sheet set to restricted. It's working now. The folder that the sheet sits in is also set to restricted.
If I need to give clients access to the sheet via a link, I keep the original google sheet as a backup and start a new sheet that pulls data in from the backup sheet. To do this:
=QUERY({IMPORTRANGE("SHEETID", "DATA!$A$1:$D")},"SELECT * WHERE Col1 is not null",1)
Note: The new sheet may have a pop up asking you to confirm that you want to connect the sheets.
Unfortunately still won't work for me.
Hello,
It is need one more step.
Step 16: publish your course. Then, add the following line of code before the closing </head> tag in the story.html file in your published output.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
Note: If you republish your course, you’ll need to add the code to your published output again.
Thank You, this is work for me :)
You're welcome 🌸
Get Outlook for Android
Dear Mr. Timmer and Articulate Community,
thank you very much for your solution. I actually manage to export the date variable to a Google Sheets file - however, I need the results of my quiz variables. More specifically, I need to know for each quiz variable whether it was answered correctly or not. Do you know how I export this information to the Google doc?
Best regards and stay healthy
Johannes Zensen (using Articulate Storyline 360)
Hi Johannes,
You can export additional variables but you can not export any built-in variables. If you require the score, you would need to create a new number variable and create a trigger to amend the variable value to the built-in score variable.
To track whether a user has got questions right or wrong, you can create text or true/false variables for each question and on the 'That's right' or 'That's not right' layers, you can create a trigger which will update that variable based on your requirement.
Add those variables into your trigger which sends over the data to the spreadsheet and you can populate your GoogleSheet with all the information you need.
I hope that helps. If you require more in depth info, drop me a message and I can assist.
Thanks, Aaron
Thank you very very (very) much! :) Your information helped me a lot. Instead of true/false variables, numeric variables (default value = 0) have proven helpful. This allows me to directly calculate how many correct answers (value = 1) the participants had.
Thanks again, Johannes
One more question came to my mind after all - is it possible to add a timestamp for the start and end of the e-learning unit? So to program one variable each for the first slide and for the result slide, which secures at what time the slides were reached?
Thanks very much Bastiaan!
I used this link to deal with the JQuery problem, which was a blocker for me also:
https://articulate.com/support/article/Storyline-How-to-Reference-the-jQuery-Library#individual-references
This post is still very relevant and useful today. Thank you so much. I attached some updated visuals since Google changed some of its design.
This post was removed by the author
Hi there.
I can't get this to work. A couple of years ago it was working, but I've followed the steps exactly and it's no longer working. I've tried with both my own spreadsheet and the template and also tried the above suggestion about putting the JavaScript triggers on a separate slide after the variables have been set. Any ideas? Have Google changed something that means it no longer works?
Hi. Not sure if it helps, but make sure to select "setup" before clicking Run in Apps Script. Last time I messed up at this step.
Hi. Yes that was done correctly. I've just done all the steps again, and it's still not working. Nothing is being published to the spreadsheet.
I've attached my test Storyline file I'm trying it on, and here's the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1mNF6jhNByamBKG1pcdIqeTNzY4iOdiOgBhs8pXJC6VA/edit#gid=0
If you open up a console... you get an error $ not defined. That means you miss the jQuery library.
Hi Math. Thanks for the response. I'm not a programmer, so I don't know what that means. Could you explain what I need to do?
Hi Articulate 3, I've posted my process below including what I do when I get that error you mentioned.
Google has moved things around a little, but the original template supplied still works for me (or I copy one of my forms that is currently working and then do the below).
1. Copy the Google sheet ID/key (characters after /d/ and before /edit)
2. Select the 'Extensions' tab drop-down and select 'Apps Script'
3. Paste the Google sheet key between the quote marks (where it says, 2. Enter the KEY of your form...)
4. Select the 'doGet' drop-down arrow and choose 'setup'
5. Select 'Run'. It will ask you to Review permissions - select 'Review permissions', select the Google account you want to use, select 'Advanced', live dangerously and select 'Go to GoogleASL (unsafe)', select the 'Allow' button.
6. Click on 'Run' again - you're looking to see that you don't get a warning (no news is good news)
7. Click on the blue 'Deploy' drop-down button and select 'New deployment' (same as the original post. Execute as = Me; Who has access = Anyone).
8. To test things are working, click again on the blue 'Deploy' button and select 'Test deployments' then click on the URL to open it. You want to see '{"result":"success","row":550}'.
If you get an error, go back and repeat steps 4 and 6-8.
If you get 'Authorization is required to perform that action', try again from step 5.
Once you've got that success message you can focus on making sure the column headings match the variables you are exporting, and that the Storyline triggers to execute the JavaScript are all ok.
Hope it helps and makes sense :)
Fixed it for you.
Here you can test it...
https://360.articulate.com/review/content/0193b4a4-afd6-40a0-bb55-3d38d210e687/review
Basically what was wrong with your NewTest is that the jQuery library never got loaded. I am no programmer either, but if you want this kind of thing working you need to learn something about HTML and how things work. You donot need to be a programmer...im neither, but you do need to know how to test and debug your projects so you can figure out whats wrong... and solve it. Or hire someone who knows.
To load external stuff you can either rely on some CDN or online setup or use WebObjects to load it into Storyline. I prefer WebObjects, because when setup properly it always works. Independent of changing URLS or downtime of servers.
In the zip added is the Storyline fixed and ready and a folder the WebObject points too. If you want to use this in another Storyline you need to copy the scene 2 'Javascript Libraries' to your project... and get the first 2 triggers on the startSlide. Also you need to be aware of the way WebObjects work. When publishing the first time ( as Web ) you need to open up your local folder and go into the WebObjects folder to get the name/number of it...
As seen here...
Copying the name of that folder... then you go to the first Javascript where the libraries will be loaded... and change the number in that script to match that folder. As seen here...
Adding a zip with both the Storyline and the folder...
Excellent. Thank you very much for that!
Ooops I think I just reported the post by accident instead of clicking 'Reply'. Sorry!
Just got a question - do I need to add the two JavaScript triggers that reference the jQuery library to every slide that's going to be submitting variables, or can I just add them to the first slide in the project?
Thanks!
This post was removed by the author