Sunday 12 March 2017

Marking assessments and generating certificates automatically via Google sheets, app script, forms, slides and Form Publisher


We recently had a requirement for users to complete a short assessment, to record which users passed, and for those who did, to receive a certificate.
To do this I used Google Apps script, Google Apps and a great add-on, Form Publisher

Here's a diagram of how it all works...




In the background collection sheet of the assessment quiz, formulae work out whether the answers are correct and score them a 1 or a 0 -  A formula at the end assigns either a 1 (pass) or a 0 (fail) based on the target score. Those who have achieved a pass mark, have their details pulled through into another tab, using a simple query.  e.g

=query('Form responses 1'!Y2:AE,"select Z,AA WHERE (Y=1)")

A similar tab can be created to pull through those who have failed just using Where =0 instead

How to then pass the details into a certificate is achieved using Google Apps Script.  This script populates a new form, which uses the Form Publisher add-on and submits it.


var sheetName = 'Mail Merge'; // enter the name of the sheet

function GenerateCertificate() {
  
  var CERTIFICATE_CREATED = "CERTIFICATE_CREATED";  //Define flag used to stop duplicates
  
  var form = FormApp.openById('abcdefghijklmnopq123456789');  //your form id goes here
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
   var data = sheet.getDataRange()  // Get all non-blank cells
   .getValues()     // Get array of values

      var startRow =1;  // First row of data to process 
    
   for (var i=0; i<data.length; i++){
     
    var formResponse = form.createResponse();
    var items = form.getItems();

    var row = data[i];
         var processedstatus = row[4];  // column - Column containing the flag to stop duplciates  (A=0)
     
      if (processedstatus != CERTIFICATE_CREATED) {     //If Flag text not found then.....
     
        //Take the first column data and input this into the next form  (e.g Full Name)
     var items = form.getItems(FormApp.ItemType.TEXT)
     var formItem = items[0].asTextItem();
    var response = formItem.createResponse(row[0]);     
    formResponse.withItemResponse(response);

         //Take the second column data and input this into the next form (e.g. Name of assessment)
    var formItem2 = items[1].asTextItem();  
    var response = formItem2.createResponse(row[1]);     
    formResponse.withItemResponse(response);

        //Take the third column data and input this into the next form  (e.g. date completed)
    var formItem3 = items[2].asTextItem();
    var response = formItem3.createResponse(row[2]);     
    formResponse.withItemResponse(response);

        //submit the form
    formResponse.submit();
    Utilities.sleep(500);
    
        //set the flag to stop duplicates
      sheet.getRange(startRow + i, 5).setValue(CERTIFICATE_CREATED);
      
       SpreadsheetApp.flush();
      }

  }

};

The script will only process those rows without the flag "CERTIFICATE_CREATED" in column E, once it has processed that row, it adds that text, to prevent duplicates being created.

In the background, when the script populates the user name, assessment and date into the next form and submits it.  Form Publisher merges the fields into a certificate template I set up in Google Slides, saves the certificate as a pdf, and then emails it to the person who took the test.

A useful feature of using a second form, processed by a script, is that sheets for different assessments / courses can use that same template and same 'certificate generator' form to create appropriate certificates.  All that is needed is the script in a background collector sheet to populate that form with the required data.

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Awesome workflow, James!

    Can this be modified to work without Google Forms and Forms Publisher? That is, to just generate the certificates manually via Sheets & Slides?

    Thanks.

    ReplyDelete
    Replies
    1. Thanks Ted! The 'mail merge' populating of the Google Slide is done via Form Publisher, so without this step, the process of generating the certificates would have to be completely manual - copy and pasting rather than script based.

      Delete
  3. Few questions: 1) Do you need to create a separate google for the input of the data you want on the certificate before you create the script? and 2) Do you create the Certificate Template with Form Publisher before your run the script?

    Lastly, anyway you can modify this to show what is needs to be changed by the developer and what is "standard" or should be kept to make the script work?

    ReplyDelete