Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
The code behind a system for generating and issuing Open Badges ( 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 -->
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
// 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 = "<<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\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 -";
// 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": "",
"name": "Learning Services",
"org": "UCS",
"contact": ""
// 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;
// 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) {
var name = response.getRange(row, 2).getValue();
// Based on code from community member Serge Insas
// 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
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 = "";
badgeTemplate.expires = Utilities.formatDate(expires, "BST", "yyyy-MM-dd");
} = 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();
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
CryptoJS v3.0.2
(c) 2009-2012 by Jeff Mott. All rights reserved.
// 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=;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._data=this._data.clone();return a},_minBufferSize:0});q.Hasher=b.extend({init:function(){this.reset()},
reset:function(){;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._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,
// 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
// No Edits Necessary
function hashEmailAddress(email, salt) {
var hash = CryptoJS.SHA256(email+salt);
return 'sha256$'+ hash;
<?xml version="1.0" encoding="UTF-8" ?>
<ModulePrefs title="Open Badges Issuer Gadget" width="800" height="700" author="David Mullett"
description="This is a modified version of Martin Hawksey's ( 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="" scrolling="true"/>
<UserPref name="script_url" display_name="Base Url" datatype="string" default_value="" required="true"/>
<Content type="html">
<![CDATA[ <script src=""></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];
// -->
<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><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=""</img></p>
</p> ]]>
// 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 -
// 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