How to Send Quiz Report to Google Sheets or to an Email Box

With ActivePresenter, after creating a course, you still can send report data (course result) to an HTTP address even if you don’t have an LMS. Using this method, you can send the report to your server and process the report data in your own way. The report formats are in XML or JSON format. In case you don’t have a web server, you can send the report data to a Google Sheets document or to an email instead. In this tutorial, I will show you the steps to send a quiz report to Google Sheets or to an email by using Google Apps Script.

1. Create a Google Sheets Document

Here I created a sample Google Sheets to receive the report data. As you can see, the column header row is the field of report data. You should clone my document to ensure everything is correct. To clone it, please open my document, then select Make a copy from the File menu.

2. Add Code to Google Script to Process Report Data

This is a sample code that I am using to insert data to Google Sheets and send report data via email. Remember to change the email in the script to your target email.

• From the Google Sheets menu, go to Extensions tab > Apps Script
• Change the available email address to yours in the following code:

  var TO_ADDRESS = "namnt@atomisystems.com"; // Email to receive the report data
  function doPost(e) {
    try {
      var report = JSON.parse(e.parameter.report);
      
      // Insert report data to Google Sheets
      var doc     = SpreadsheetApp.getActiveSpreadsheet();
      var sheet   = doc.getSheets()[0]; // get the first sheet
      var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
      var nextRow = sheet.getLastRow()+1; // get next row
      var row     = [ new Date() ];
      // loop through the header columns
      for (var i = 1; i < headers.length; i++) { // start at 1 to avoid Timestamp column if(headers[i].length > 0) {
          row.push(JSON.stringify(report[headers[i]])); // add data to row
        }
      }
      sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
      
      // Send report data via email
      MailApp.sendEmail({
        to: TO_ADDRESS,
        subject: "Report Data" + " - " + report.Name,
        htmlBody: JSON.stringify(report)
      });
      
      return ContentService    // return json success results
            .createTextOutput(
              JSON.stringify({"result":"success",
                              "data": JSON.stringify(report) }))
            .setMimeType(ContentService.MimeType.JSON);
    } catch(error) { // if error return this
      Logger.log(error);
      return ContentService
            .createTextOutput(JSON.stringify({"result":"error", "error": e}))
            .setMimeType(ContentService.MimeType.JSON);
    }
  }
  

• Customize everything if you want (Optional)
• Click Save.

3. Publish the Script as a Web App

• Select the Deploy button > New deployment.
• The publish options are:
  • Description to add a new description.
  • Execute the app as “Me” (the email of the course author).
  • Who has access to the app: Anyone, even anonymous can access it.
• Follow the instructions to finish deploying the app. At the final step, please copy the web app URL to put it into the ActivePresenter reporting option.

4. Fill in the Report Options Section

Enter the web app URL above to the HTTP address and remember to select JSON format for report data in the Report Options section in ActivePresenter.

Now, each time learners finish their courses, the report data will be inserted into Google Sheets and a report will be sent to your email address.

You should improve the code to make the report looks better. I also made another version of this to make the report easier to read and better email format: https://docs.google.com/spreadsheets/d/1RIwhVmHLREYnXUMw2Y8lf3Whkkv1SB_aIC90j4LJvQ8/edit#gid=2103940916

That’s how to send quiz reports to Google Sheets or email without an LMS in ActivePresenter. Download the app and feel free to contact us.