Last active
September 29, 2017 12:18
-
-
Save thedavidmullett/54a5153f6298539ce05d to your computer and use it in GitHub Desktop.
The code behind a system for generating and issuing Open Badges (http://www.openbadges.org) using a mix of Google Forms/Sites and some externally hosted scripts.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
UPDATED - (AUG 2014) | |
-- Each issued badge is now referenced using an 8 character unique ID instead of row number | |
(Can now remove erroneous rows in DB). | |
-- Can now set expiry date for issued badges. | |
For a quick guide of how to implement the code used below | |
please visit --> https://docs.google.com/document/d/12qRhAc9rzUH7FXBW0J4NtLkY-8RoQVw-Z4yOk3mTJmA/edit?usp=sharing | |
The code below is syntax highlighted as javascript however these .js files | |
site in a Google Apps Script Project where they sit with the .gs extention. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Simply run this to set the active spreedsheets ID. If you change this, remember to also change the doc variable in the doGet function to match. | |
// No Edits Necessary | |
function setup(){ | |
ScriptProperties.setProperty('eobg', SpreadsheetApp.getActiveSpreadsheet().getId()); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// This function must be setup as a trigger --> onFormSubmit --> Spreadsheet --> On form submit | |
// Function pulls information out of the submitted form and emails the recipient a URL to claim their badge. | |
function onFormSubmit(e) { | |
// Next three lines used to get last row number (might fail on simultanious form submits) | |
var doc = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = doc.getSheetByName("DATA"); | |
var lastrow = sheet.getLastRow(); | |
var lastCol = 7; // Take the last col of your responses to fill with the UniqueID | |
// List existing UIDs | |
var UIDArray = [sheet.getRange(2,lastCol,sheet.getLastRow(),1)]; | |
// Collect a random string to use as a Unique ID | |
var randomID = genRandomString(8,16); | |
// Iterates collected random string to see if it's already been issued, if not a new one is created. | |
for (var i = 0; i < UIDArray.length; i++){ | |
if (randomID == UIDArray[i]){ | |
var randomID = genRandomString(8,16) | |
i = 0 | |
} | |
} | |
sheet.getRange(lastrow,lastCol,1,1).setValue(randomID); | |
// Next 4 lines read the form values submitted | |
var timestamp = e.values[0]; | |
var badgename = e.values[1]; | |
var name = e.values[2]; | |
var email = e.values[3]; | |
var claim_code = []; | |
//This is where the Issuer Gadget is hosted | |
var baseUrl = "https://sites.google.com/site/<<YOURSITEHERE>>/"; | |
// The claim code holds the row number and the type pf badge, which for now is a static &type=openbadge as the 2nd variable isn't used | |
var claim_code_base = "uniqueid=" + randomID; | |
claim_code.push(Utilities.base64Encode(claim_code_base + "&type=openbadge")); | |
// Build the URL to send | |
var url = baseUrl + "?claim_code=" + claim_code; | |
// Compose text for the email | |
var emailText = "Hi "+name+",\n\nCongratulations on obtaining the '" + badgename + "' Badge. To claim your badge visit \n\n" + url + "\n\nThis open badge is essentially a visual recognition of your learning journey which you can store and display online for others to view. The badge is stored inside a Mozilla Backpack. To find out how to set up your Mozilla Backpack, please visit http://www.openbadges.org\n\nMany Thanks\n\nThe Elevate Team (Learning Services)\n\nPlease visit our blog for more information on how we are using Open Badges at UCS - http://ucselevate.blogspot.co.uk."; | |
// Using the MailApp function of Apps Script to send the email to the person | |
MailApp.sendEmail(email, "Claim your Badge - " + badgename + "!", emailText); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Initialising some of these as blank to to keep human readable structure | |
var badgeTemplate = { | |
"recipient": "", | |
"salt": "", | |
"issued_on": "", | |
"expires": "", | |
"badge": { | |
"version": "0.5.0", | |
"name": "", | |
"image": "", | |
"description": "", | |
"criteria": "", | |
"issuer": { | |
"origin": "http://www.ucs.ac.uk", | |
"name": "Learning Services", | |
"org": "UCS", | |
"contact": "elevate@ucs.ac.uk" | |
} | |
} | |
}; | |
// The doGet function builds the Assertion JSON blob for each badge and gets called when the deployed web app gets hit | |
function doGet(e){ | |
// To access spreadsheet data we need to get by id (stored by running setup function) | |
var doc = SpreadsheetApp.openById(ScriptProperties.getProperty('eobg')); | |
var response = doc.getSheetByName("DATA"); | |
var badgeinfo = doc.getSheetByName("BADGES"); | |
// Decode claim_code | |
var claim_encode = Utilities.base64Decode(e.parameter.claim_code); | |
// Need to convert byte array to string | |
var claim_code = bin2String(claim_encode); | |
// Extract uniqueid and type passed in claim_code | |
var data = getQueryString("?"+claim_code); | |
var UID = data.uniqueid; | |
Logger.log(UID); | |
// Find out the row from the UID | |
var column = response.getRange('G:G'); | |
var values = column.getValues(); | |
var row = 1; | |
while (values[row-1] != UID) { | |
row++; | |
} | |
var name = response.getRange(row, 2).getValue(); | |
// Based on code from community member Serge Insas | |
// http://stackoverflow.com/questions/13327069/search-spreadsheet-column-for-text-in-a-string-and-return-a-result-in-another-co | |
// Read all data in the sheet | |
var badgedata = badgeinfo.getDataRange().getValues(); | |
// Iterate row by row and examine data in columns based on the name string | |
for(n=0;n<badgedata.length;++n){ | |
if(badgedata[n][0] == name){ | |
var description = badgedata[n][1]; | |
var image = badgedata[n][2]; | |
var criteria = badgedata[n][3]; | |
}; | |
} | |
var timestamp = response.getRange(row, 1).getValue(); | |
var email = response.getRange(row, 4).getValue(); | |
var expires = response.getRange(row, 5).getValue(); | |
// Fill in the badge assertion details | |
badgeTemplate.salt = "G00g1e"; | |
badgeTemplate.recipient = hashEmailAddress(email, badgeTemplate.salt); | |
badgeTemplate.issued_on = Utilities.formatDate(timestamp, "GMT", "yyyy-MM-dd"); | |
if(expires == ""){ | |
badgeTemplate.expires = ""; | |
} | |
else | |
{ | |
badgeTemplate.expires = Utilities.formatDate(expires, "BST", "yyyy-MM-dd"); | |
} | |
badgeTemplate.badge.name = name; | |
badgeTemplate.badge.image = image; | |
badgeTemplate.badge.description = description; | |
badgeTemplate.badge.criteria = criteria; | |
// Now that Assertion is complete next lines publish it to the web ready to send back to the issuer gadget on Google Sites | |
var output = ContentService.createTextOutput(); | |
output.setMimeType(ContentService.MimeType.JSON); | |
output.setContent(JSON.stringify(badgeTemplate)); | |
return output; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Modified from Monchai_ppp's code on Google Product Forums | |
function chooseBadge(){ | |
// Get the form item | |
var form = FormApp.openById("<<PROPERTY VALUE OF BADGES ISSUER FORM>>"); | |
var agentList = form.getItemById("<<ELEMENT ID OF CHOOSE BADGE FIELD>>").asListItem(); | |
// Get first column' values (ignore first row which is the header). | |
var ss = SpreadsheetApp.getActive().getSheetByName("BADGES"); | |
var agentValues = ss.getRange(2, 1, ss.getMaxRows() - 1).getValues(); | |
// Values array | |
var agentNames = []; | |
for(var i = 0; i < agentValues.length; i++) | |
if(agentValues[i][0] != "") | |
agentNames[i] = agentValues[i][0]; | |
// Simply set the list items with the list of string | |
agentList.setChoiceValues(agentNames); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
CryptoJS v3.0.2 | |
code.google.com/p/crypto-js | |
(c) 2009-2012 by Jeff Mott. All rights reserved. | |
code.google.com/p/crypto-js/wiki/License | |
http://crypto-js.googlecode.com/svn/tags/3.0.2/build/rollups/sha256.js | |
*/ | |
// No Edits Necessary | |
var CryptoJS=CryptoJS||function(i,p){var f={},q=f.lib={},j=q.Base=function(){function a(){}return{extend:function(h){a.prototype=this;var d=new a;h&&d.mixIn(h);d.$super=this;return d},create:function(){var a=this.extend();a.init.apply(a,arguments);return a},init:function(){},mixIn:function(a){for(var d in a)a.hasOwnProperty(d)&&(this[d]=a[d]);a.hasOwnProperty("toString")&&(this.toString=a.toString)},clone:function(){return this.$super.extend(this)}}}(),k=q.WordArray=j.extend({init:function(a,h){a= | |
this.words=a||[];this.sigBytes=h!=p?h:4*a.length},toString:function(a){return(a||m).stringify(this)},concat:function(a){var h=this.words,d=a.words,c=this.sigBytes,a=a.sigBytes;this.clamp();if(c%4)for(var b=0;b<a;b++)h[c+b>>>2]|=(d[b>>>2]>>>24-8*(b%4)&255)<<24-8*((c+b)%4);else if(65535<d.length)for(b=0;b<a;b+=4)h[c+b>>>2]=d[b>>>2];else h.push.apply(h,d);this.sigBytes+=a;return this},clamp:function(){var a=this.words,b=this.sigBytes;a[b>>>2]&=4294967295<<32-8*(b%4);a.length=i.ceil(b/4)},clone:function(){var a= | |
j.clone.call(this);a.words=this.words.slice(0);return a},random:function(a){for(var b=[],d=0;d<a;d+=4)b.push(4294967296*i.random()|0);return k.create(b,a)}}),r=f.enc={},m=r.Hex={stringify:function(a){for(var b=a.words,a=a.sigBytes,d=[],c=0;c<a;c++){var e=b[c>>>2]>>>24-8*(c%4)&255;d.push((e>>>4).toString(16));d.push((e&15).toString(16))}return d.join("")},parse:function(a){for(var b=a.length,d=[],c=0;c<b;c+=2)d[c>>>3]|=parseInt(a.substr(c,2),16)<<24-4*(c%8);return k.create(d,b/2)}},s=r.Latin1={stringify:function(a){for(var b= | |
a.words,a=a.sigBytes,d=[],c=0;c<a;c++)d.push(String.fromCharCode(b[c>>>2]>>>24-8*(c%4)&255));return d.join("")},parse:function(a){for(var b=a.length,d=[],c=0;c<b;c++)d[c>>>2]|=(a.charCodeAt(c)&255)<<24-8*(c%4);return k.create(d,b)}},g=r.Utf8={stringify:function(a){try{return decodeURIComponent(escape(s.stringify(a)))}catch(b){throw Error("Malformed UTF-8 data");}},parse:function(a){return s.parse(unescape(encodeURIComponent(a)))}},b=q.BufferedBlockAlgorithm=j.extend({reset:function(){this._data=k.create(); | |
this._nDataBytes=0},_append:function(a){"string"==typeof a&&(a=g.parse(a));this._data.concat(a);this._nDataBytes+=a.sigBytes},_process:function(a){var b=this._data,d=b.words,c=b.sigBytes,e=this.blockSize,f=c/(4*e),f=a?i.ceil(f):i.max((f|0)-this._minBufferSize,0),a=f*e,c=i.min(4*a,c);if(a){for(var g=0;g<a;g+=e)this._doProcessBlock(d,g);g=d.splice(0,a);b.sigBytes-=c}return k.create(g,c)},clone:function(){var a=j.clone.call(this);a._data=this._data.clone();return a},_minBufferSize:0});q.Hasher=b.extend({init:function(){this.reset()}, | |
reset:function(){b.reset.call(this);this._doReset()},update:function(a){this._append(a);this._process();return this},finalize:function(a){a&&this._append(a);this._doFinalize();return this._hash},clone:function(){var a=b.clone.call(this);a._hash=this._hash.clone();return a},blockSize:16,_createHelper:function(a){return function(b,d){return a.create(d).finalize(b)}},_createHmacHelper:function(a){return function(b,d){return e.HMAC.create(a,d).finalize(b)}}});var e=f.algo={};return f}(Math); | |
(function(i){var p=CryptoJS,f=p.lib,q=f.WordArray,f=f.Hasher,j=p.algo,k=[],r=[];(function(){function f(a){for(var b=i.sqrt(a),d=2;d<=b;d++)if(!(a%d))return!1;return!0}function g(a){return 4294967296*(a-(a|0))|0}for(var b=2,e=0;64>e;)f(b)&&(8>e&&(k[e]=g(i.pow(b,0.5))),r[e]=g(i.pow(b,1/3)),e++),b++})();var m=[],j=j.SHA256=f.extend({_doReset:function(){this._hash=q.create(k.slice(0))},_doProcessBlock:function(f,g){for(var b=this._hash.words,e=b[0],a=b[1],h=b[2],d=b[3],c=b[4],i=b[5],j=b[6],k=b[7],l=0;64> | |
l;l++){if(16>l)m[l]=f[g+l]|0;else{var n=m[l-15],o=m[l-2];m[l]=((n<<25|n>>>7)^(n<<14|n>>>18)^n>>>3)+m[l-7]+((o<<15|o>>>17)^(o<<13|o>>>19)^o>>>10)+m[l-16]}n=k+((c<<26|c>>>6)^(c<<21|c>>>11)^(c<<7|c>>>25))+(c&i^~c&j)+r[l]+m[l];o=((e<<30|e>>>2)^(e<<19|e>>>13)^(e<<10|e>>>22))+(e&a^e&h^a&h);k=j;j=i;i=c;c=d+n|0;d=h;h=a;a=e;e=n+o|0}b[0]=b[0]+e|0;b[1]=b[1]+a|0;b[2]=b[2]+h|0;b[3]=b[3]+d|0;b[4]=b[4]+c|0;b[5]=b[5]+i|0;b[6]=b[6]+j|0;b[7]=b[7]+k|0},_doFinalize:function(){var f=this._data,g=f.words,b=8*this._nDataBytes, | |
e=8*f.sigBytes;g[e>>>5]|=128<<24-e%32;g[(e+64>>>9<<4)+15]=b;f.sigBytes=4*g.length;this._process()}});p.SHA256=f._createHelper(j);p.HmacSHA256=f._createHmacHelper(j)})(Math); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Function to change byte to string | |
// No Edits Necessary | |
function bin2String(array) { | |
return String.fromCharCode.apply(String, array); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Function to normalise Query String | |
// No Edits Necessary | |
function getQueryString(ref) { | |
var qs= ref.split('?'); | |
var result = {}, queryString = qs[1], | |
re = /([^&=]+)=([^&]*)/g, m; | |
while (m = re.exec(queryString)) { | |
result[decodeURIComponent(m[1])] = decodeURIComponent(m[2]); | |
} | |
return result; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Based on https://github.com/mozilla/openbadges/wiki/How-to-hash-&-salt-in-various-languages. | |
// No Edits Necessary | |
function hashEmailAddress(email, salt) { | |
var hash = CryptoJS.SHA256(email+salt); | |
return 'sha256$'+ hash; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="UTF-8" ?> | |
<Module> | |
<ModulePrefs title="Open Badges Issuer Gadget" width="800" height="700" author="David Mullett" | |
description="This is a modified version of Martin Hawksey's (http://mashe.hawksey.info) Open Badge Issuer for Google Sites. This gadget allows you to integrate the the Mozilla Open Badges Issuer API into Google Sites. To use insert into a Google site and set a base url for your hosted assertions Which should be the external URl to the deplyed web app adding ?claim_code= to the end. To allow users to collect their badges direct them to the web address of the Site page containing the gadget adding ?claim_code={insert the rest of their assertion url} to the url" | |
author_email="thedavidmullett@gmail.com" scrolling="true"/> | |
<UserPref name="script_url" display_name="Base Url" datatype="string" default_value="" required="true"/> | |
<Content type="html"> | |
<![CDATA[ <script src="https://backpack.openbadges.org/issuer.js"></script> | |
<script type="text/javascript"> | |
<!-- | |
var code = []; // initialize assesor array | |
var prefs = new gadgets.Prefs(); | |
function getQueryString() { | |
var ref = document.referrer; | |
var qs= ref.split('?'); | |
var result = {}, queryString = qs[1], | |
re = /([^&=]+)=([^&]*)/g, m; | |
while (m = re.exec(queryString)) { | |
result[decodeURIComponent(m[1])] = decodeURIComponent(m[2]); | |
} | |
return result; | |
} | |
function init() { | |
var base_url = prefs.getString("script_url"); | |
var codes = getQueryString().claim_code; | |
code = codes.split(","); | |
if (codes != "") { | |
for ( var i = 0; i < code .length; i++ ) { | |
code[i] = base_url + code[i]; | |
} | |
document.getElementById("collectBadge").style.display='block'; | |
document.getElementById("msg").style.display='none'; | |
} | |
} | |
gadgets.util.registerOnLoadHandler(init); | |
// --> | |
</script> | |
<p id="msg">No claim code</p> | |
<p id="collectBadgeWrapper"><div style="font: 100% Verdana, Arial, Helvetica, sans-serif; text-align: center; color: #000000; width: 46em; | |
background: #FFFFFF; margin: 0 auto; text-align: center; padding: 0 20px;"> | |
<p> </p> | |
<p><a href='javascript:void(0);' onclick='OpenBadges.issue(code, function(errors, successes) | |
{ });' id="collectBadge" style="display:block"><strong>Click here to<br /> | |
Claim your badge!</strong></a></p> | |
<p><img src="http://www.openbadges.org/wp-content/themes/openbadges2/media/images/logo.png"</img></p> | |
</div> | |
</p> ]]> | |
</Content> | |
</Module> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Function for finding out the Element IDs of the Badges Issuer form, we do this to capture the 'Choose Badge' ID to prefill. | |
// No Edits Necessary | |
function getElementIDS(){ | |
var form = FormApp.getActiveForm(); | |
for (var i in form.getItems()) { | |
Logger.log(form.getItems()[i].getTitle() + ': ' + form.getItems()[i].getId() + ': ' + form.getItems()[i].getType()); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// With thanks to 'thoughtcrime' on StackOverFlow - http://stackoverflow.com/questions/22079353/write-a-unique-code-when-submit-google-form-script | |
// Function for generating a random 8 character string to use as a unique ID. | |
// No Edits Necessary | |
function genRandomString(len, bits){ | |
bits = bits || 36; | |
var outStr = "", newStr; | |
while (outStr.length < len) | |
{ | |
newStr = Math.random().toString(bits).slice(2); | |
outStr += newStr.slice(0, Math.min(newStr.length, (len - outStr.length))); | |
} | |
return outStr.toUpperCase(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment