Google app scripts and Captivate

Report · Jun 26, 2016

Hello,

I'm trying to set up a Google spreadsheet and pass values from that into variables in my Captivate project using Javascript. Is this quite difficult to do? And could anyone recommend a website or video that explains this ? Thanks.

Bill

Report · Jun 26, 2016

Hi Bill

Have you seen this

How to Create a Leaderboard for eLearning with Google (Part 2) » eLearning Brothers

I have not used it but it appears to be what you're looking for.

Regards

Donal.

Report · Jun 26, 2016

Thank you - yes I did, and it worked. I guess I just don't see how to reverse the process - how to type the value in Google Sheet and have that show up in my Captivate slide variable.

Here is the code I took from Github with my spreadsheet URL included. Do I modify this, or look elsewhere for the JS?

- Bill

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

//what is the URL of your google spreadsheet?

var sheetURL = 'MY URL';

var scoreVarName = 'userScore'; //what is the name of the score varible in Captivate?

var userVarName = 'userName'; //what is the name of the user name varible in Captivate?

var topTenVarName = 'topTenMsg'; //what is the name of the top ten message varible in Captivate?

/*

*** Do not edit below this line unless you are confident about your JS skills. ***

*/

var userScore = window.cpAPIInterface.getVariableValue(scoreVarName); //Get the score from the player

var userTag = window.cpAPIInterface.getVariableValue(userVarName); //Get the user name from the player

window.cpAPIInterface.setVariableValue(topTenVarName, 'Loading...'); //Temporarily set the top ten message

var topTenMsg; //We will build the top ten message in this var.

//Set up our AJAX

var xhttp;

if (window.XMLHttpRequest) {

xhttp = new XMLHttpRequest();

} else {

xhttp = new ActiveXObject('Microsoft.XMLHTTP');

}

xhttp.open('GET', sheetURL + '?id=' + userTag + '&score=' + userScore, true);

xhttp.send();

xhttp.onreadystatechange = function () {

//If we get a successful reply from our spreadsheet:

if (xhttp.readyState == 4 && xhttp.status == 200) {

var topTenJson = JSON.parse(xhttp.responseText);

//This is the begining of the top ten message. Edit carefully

//First we create the results for this learner (<br> creates a new line)

var userResults = 'You placed ' + topTenJson.user.rank + ' with a score of ' + topTenJson.user.score + '<br>';

//Then we introduce the top ten.

var topTenUsers = 'The top ' + topTenJson.users.length + ' gamers are: <br>';

//We append the second line of text to the message

topTenMsg = userResults + topTenUsers;

//Now we loop through each of the top 10

for (var i = 0; i < topTenJson.users.length; i++) {

//convert the date to something friendly

var recordDate = new Date(topTenJson.users.date);

var recordDateString = recordDate.getDate() + '/' + (recordDate.getMonth() + 1) + '/' + recordDate.getFullYear() + ' ' + recordDate.getHours() + ':' + recordDate.getMinutes();

//Append the users info (rank, name, date) to the message.

topTenMsg += (i + 1) + ' - User: ' + topTenJson.users.id + ' Score: ' + topTenJson.users.score + ' When: ' + recordDateString + '<br>';

}

//Push the message back into Captivate

window.cpAPIInterface.setVariableValue(topTenVarName, topTenMsg);

}