Exporting Variables into a Google Spreadsheet

Having read this post on how to export variables to be read in a google spreadsheet, I set about trying to get this working in my project, I soon ran into problems, I just could not get it to work as google have changed some of the ways they work their drive documents.


So, after a LOT of google searching and testing various methods, plus reading this article, I came up with the following method which is a combination of the two articles – and it works, hurrah!

1. Create a new google spreadsheet and change the sheet name (lower left hand corner) to DATA. Make sure your column names are the same as the variables you want to export (exactly matching case)


2. 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:
Eg: https://docs.google.com/spreadsheets/d/1gF0QCNA1TZCNY3qr2zNpWKQ8r43D39o-nqz56c7cQUs/edit#gid=1283040575
Key = 1gF0QCNA1TZCNY3qr2zNpWKQ8r43D39o-nqz56c7cQUs


3. Open the script editor (Tools ==> Script Editor) in your spreadsheet and paste the script from the attached file (I have copied and pasted this script and just kept in all the instructions)


4. There are two places in the script where it says “KEY” – copy and paste your key into these two places, between the “”.


5. Run the setUp script twice (Run menu). The first time it will ask for permission to run (grant it), then the second time you select to run it you won't get any popup indication it has run.

6. Go to 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).

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

8. In Articulate, add a trigger to run javascript and use the following code, replacing “Current web app URL” with your URL you copied in the previous step (in””):

var player = GetPlayer();

$.ajax({

url:
"Current web app URL",

type: "POST",

data: {"Name": player.GetVar("Name")
, "Rating1": player.GetVar("Rating1")
, "Rating2": player.GetVar("Rating2")
, "Rating3": player.GetVar("Rating3")
, "Rating4": player.GetVar("Rating4")
, "postRating1": player.GetVar("postRating1")
, "postRating2": player.GetVar("postRating2")
, "postRating3": player.GetVar("postRating3")
, "Postrating4": player.GetVar("Postrating4")},


success: function(data)

{

console.log(data);

},

error: function(err) {
console.log('Error:', err);
}

});

return false;

9. Publish your articulate project – you need to host it somewhere like SCORM cloud or a LMS. When it has finished publishing click to open the files and edit the story.html and story_html5.html files – add the following line in under the line <!-- version: X.X.XXX.XXX --> or somewhere after <head>:

<script src="//code.jquery.com/jquery-1.11.0.min.js"></script>

10. Go back to articulate and click ‘zip’ – then publish your zip file and hopefully it will work!

This isn’t for the faint hearted but it is so worth it if you can get it working! Good luck!

140 Replies
Ken Russom

Thank you Alison, I've been working on this and  followed the example exactly, even had a 'tech' guy look at my work and we're still not having any success.

This is hosted on wordpress and the site is Http:// not Https:// .  Someone told me that wordpress might strip all scripts.

In the process of setting up another server for testing, I'll let you know.

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.

Warrin Richins

I'm going to speak in non-technical terms because that is what I understand best. 

the two things that I want on my spread sheet are 1- students name and 2- students score on the quiz (game)

and possibly 3- how much time it took.

So I have made my spreadsheet and named the columns Student name, students score.

I have also followed the other directions but now my question is-

Where? and at what point? and how do I specify that these are the variables I want to have on the spreadsheet?

 

Thank you!

Kate Robertson

Hi Warrin,

Firstly, you need to create variables called 'Student name' and 'students score' in Articulate. Then you create a trigger to execute the the following javascript (replace 'current web app url' with your own url as described in the original method':

var player = GetPlayer();
$.ajax({
url:
"Current web app URL",
type: "POST",
data: {"Student Name": player.GetVar("Student name")
, "students score": player.GetVar("students score")},
success: function(data)
{
console.log(data);
},
error: function(err) {
console.log('Error:', err);
}
});
return false;

Hope that helps?

Michael Menge

Hi there. This is really a great solutions and works almost fantastic for me. Sadly, just almost ;(

I got the script to send the name which a user enters at the beginning of the module to the google sheet. However scored points, calculated by answering a number of questions, are not transfered. If I change the field to receive the "Name" value (instead of "Points") it works fine. So I suppose it's just related to the variable not being mapped correctly? The quiz result slide does correctly show scored points.

Any help would be appreciated :)

data: {"entry.1309481895" :player.GetVar("Name"), "entry.774564750":player.GetVar("Points")},

Maricel Medina-Mora

Hi,

I am testing this code and I am getting the following error:

 XMLHttpRequest cannot load https://script.google.com/macros/s/AKfycbzrHjkMi9boQEsDCwNHdfXYZe5fTGx43n3xtHNhPxv4LYlOvOg/exec. No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'https://s3.ca-central-1.amazonaws.com' is therefore not allowed access. The response had HTTP status code 401.

 

1.  My storyline is hosted in AWS

2.  My google doc is in GoogleDocs

I have made everything public ... Any advice?

kristian chartier

My guess is that your browser is blocking the content because of CORS (Cross-Origin Request Sharing). You could try opening the link in IE and set your browser to display insecure content. 

You should be able to fix the problem by following the instructions outlined here: https://stackoverflow.com/questions/10636611/how-does-access-control-allow-origin-header-work

Michael Hinze

I have used Google spreadsheets several times in the past to store Storyline variables. I know what the process is and I never had any problems until now. With a new spreadsheet and new Storyline project I now get an error when I run the published content. In the browser console, I get :

"Failed to load https://script.google.com/macros/u/0/s/../exec: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://...com' is therefore not allowed access. The response had HTTP status code 404."

Note: I replaced the real URL with ..

After some research this seems to be a cross domain security issue (the SL content runs on one domain, while the Google spreadsheet is hosted on another). BTW, this also happens with Captivate-based content, so my guess is the issue is related to the "the Google end".

Does anyone have insights on the root cause and how to fix this issue? 

Steve Flowers

Hi, Michael - 

I stopped using the script-based submission for a variety of reasons awhile ago. Instead, I just use the formResponse target that the normal form uses with an AJAX post. This tends to work every time for me. I use the "Get Prefilled Link" feature to discover the expected form fields.

https://docs.google.com/forms/d/e/1FAIpQLSewmIMASgsR78EKGSEG1Y81TgPHJ2YjaunE2d4F6C5l9v2nrw/viewform?usp=pp_url&entry.1632817179=First&entry.764465594=Second

https://docs.google.com/forms/d/e/1FAIpQLSewmIMASgsR78EKGSEG1Y81TgPHJ2YjaunE2d4F6C5l9v2nrw/formResponse?entry.1632817179=First&entry.764465594=Second

Michael Hinze
Steve Flowers

Hi, Michael - 

I stopped using the script-based submission for a variety of reasons awhile ago. Instead, I just use the formResponse target that the normal form uses with an AJAX post. This tends to work every time for me. I use the "Get Prefilled Link" feature to discover the expected form fields.

https://docs.google.com/forms/d/e/1FAIpQLSewmIMASgsR78EKGSEG1Y81TgPHJ2YjaunE2d4F6C5l9v2nrw/viewform?usp=pp_url&entry.1632817179=First&entry.764465594=Second

https://docs.google.com/forms/d/e/1FAIpQLSewmIMASgsR78EKGSEG1Y81TgPHJ2YjaunE2d4F6C5l9v2nrw/formResponse?entry.1632817179=First&entry.764465594=Second

Hi Steve,

thanks for the info. It's funny, I had used fieldIDs for my very first test project. It did work fine but it felt like a hack...having to dig up the IDs, etc. I'll give it a try. Back to the future!

Claudine Caro

Steve, I'm very interested in this. My goal is to do the following:

  1. Pull email address from LMS into Storyline360
  2. Send email address from Storyline to Google Form -> Google Spreadsheet on course completion trigger
  3. Use Zapier to collect email address and send an email to the learner.

I've pulled in the email address from the LMS to Storyline. But I can't get it to spit out to the Google Form. 

I used the script you posted several years ago. Now I'd like to use your formResponse target but I don't know how to change my javascript. 

Can you help me figure it out?

The SL course is hosted on ScormCloud
https://cloud.scorm.com/sc/InvitationConfirmEmail?publicInvitationId=91fa6f1b-fd93-4f16-bfe4-fdc71f01b8c4

thanks-

Claudine