🔔Black Friday Super Sale! 40% Off on All Products. Limited time offer!⏰

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

by | Dec 27, 2017

Last Updated On: August 16, 2023

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.

wep app URL in Google Sheet

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.

HTTP report

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.