Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
To find out how the Elevate Team at University Campus Suffolk are using this
system, please visit the Elevate blog (
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();
// 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 = "<<YOUR SITE>>/";
// 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 = "row=" + lastrow;
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\nMany Thanks\n\nThe Elevate Team\n\nPlease visit our blog for more information -";
// Using the MailApp function of Apps Script to send the email to the person
MailApp.sendEmail(email, "Claim your Badge - " + badgename + "!", emailText);
// Based on works by Martin Hawksey -
// Initialising some of these as blank to to keep human readable structure
var badgeTemplate = {
"recipient": "",
"salt": "",
"issued_on": "",
"badge": {
"version": "0.5.0",
"name": "",
"image": "",
"description": "",
"criteria": "",
"issuer": {
"origin": "",
"name": "Elevate",
"org": "UCS",
"contact": ""
// The doGet function builds the Assertion JSON blob for each badge and needs to be setup as a trigger --> doGet --> Spreedsheet --> On open
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 row and type passed in claim_code
var data = getQueryString("?"+claim_code);
var name = response.getRange(data.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(data.row, 1).getValue();
var email = response.getRange(data.row, 4).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"); = 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());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment