Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.
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.
// 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 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);
}
// 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;
}
// 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);
}
/*
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);
// Function to change byte to string
// No Edits Necessary
function bin2String(array) {
return String.fromCharCode.apply(String, array);
}
// 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;
}
// 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;
}
<?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>&nbsp;</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>
// 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());
}
}
// 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