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

After creating a course in ActivePresenter, you can send report data (course results) to an HTTP address even if you are not using an LMS. This method enables you to send the report to your server and process the report data in your own way. The report formats are available in XML or JSON. In case you lack a web server, you can send the report data to a Google Sheets document or to an email instead. This tutorial will guide you through the steps to send a quiz report to Google Sheets or an email using Google Apps Script.

1. Create a Google Sheets Document

I’ve created a sample Google Sheets document to receive the report data. As you can see, the row with column headers represents the fields of the report data. To ensure accuracy, it’s recommended that you clone my document. To do this, please open my document, and then select Make a copy from the File menu.

2. Add Code to Google Script to Process Report Data

Here is a sample code I am using to insert data into Google Sheets and send report data via email. Please remember to modify the email address in the script to match your intended recipient.

• From the Google Sheets menu, go to Extensions tab > Apps Script
• Replace the provided email address with your own 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.

From now on, whenever learners complete their courses, the report data will be added to Google Sheets, and a report will be sent to your email address.

You should enhance the code to improve the appearance of the report. I’ve also created an alternative version to make the report easier to read and format the email more effectively: 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 using ActivePresenter.

Please feel free to download the application and reach out to us if you have any questions.