Exporting course registration results from SCORM Cloud to Microsoft Excel

Updated on May 14, 2018

In this example, I’ll demonstrate how to use the SCORM Cloud Node.js client library to export course registration data and the SheetJS xslx module to write the exported data to a Microsoft Excel workbook.

Note: The Node.js JavaScript runtime must be installed before you can install the packages and run the code in this article. If you are on a Mac with Homebrew installed, run the brew install node command.

Initial setup

If you have not already, create a directory for your project. Initialize npm to create a package.json file, then install the scormcloud-api-wrapper and xslx modules.

$ mkdir scormcloud-export && cd scormcloud-export
$ npm init -y
$ npm install scormcloud-api-wrapper xlsx --save

API credentials

When connecting to the SCORM Cloud API, you are required to provide API credentials in the form of an Application ID (App ID) and a secret key. You can access this information from the Apps / API page.

Click the Details link to view the secret key for an application. The App ID for the application is also listed at the top of this page.

Retrieving registration results

Using your API credentials, construct an instance of the API client. You can do this from the Node REPL by running the node command without a filename.

// Import the SCORM Cloud API client.
var SCORMCloud = require('scormcloud-api-wrapper');

// Create an instance with your API credentials.
var api = new SCORMCloud('appid', 'secretKey');

The getRegistrationListResults method retrieves a list of registration results. Registration results include the completion status, success status, total time spent, and the overall score. Results can optionally be limited to a specific course or learner id.

api.getRegistrationListResults(
  function (error, result) {
    if (error) throw error; console.log(result);
    /*
    [
      {
        id: '2ffab123-cb7c-4744-af8e-493a6c74e65b',
        courseId: '810348d9-318e-48d5-b352-a1f6eb3a92cd',
        courseTitle: 'State Capitals Quiz',
        lastCourseVersionLaunched: '0',
        learnerId: 'jane.doe@example.com',
        learnerFirstName: 'Jane',
        learnerLastName: 'Doe',
        email: 'jane.doe@example.com',
        createDate: '2017-11-09T20:00:00.000+0000',
        firstAccessDate: '2017-11-10T21:40:00.000+0000',
        lastAccessDate: '2017-11-10T21:44:47.000+0000',
        completedDate: '2017-11-10T21:44:47.000+0000',
        registrationreport: {
          format: 'course',
          regid: '2ffab123-cb7c-4744-af8e-493a6c74e65b',
          instanceid: '0',
          complete: 'complete',
          success: 'passed',
          totaltime: '322',
          score: '84'
        }
      },
      ...
    ]
    */
  }
);

Writing data to an Excel workbook

For writing the data to an Excel workbook, we’ll bring in the xlsx module and use the sheet_to_json helper function to create a worksheet. After creating the worksheet, it can be appended to the workbook and written to disk.

Expanding on the above example, build an array of objects from the result of the call to getRegistrationListResults.

The example below creates and writes to the Registration Results.xlsx file.

// Import the SCORM Cloud API client and SheetJS.
var SCORMCloud = require('scormcloud-api-wrapper');
var XLSX = require('xlsx');

// Create an instance with your API credentials.
var api = new SCORMCloud('appid', 'secretKey');

api.getRegistrationListResults(
  function (error, result) {
    
    if (error) throw error;

    // We're going to build an array of objects.
    let data = [];

    // Headers are generated based on the keys of the objects.
    result.forEach(function (report) {
      data.push({
        "courseId":         report.courseId,
        "courseTitle":      report.courseTitle,
        "learnerId":        report.learnerId,
        "learnerFirstName": report.learnerFirstName,
        "learnerLastName":  report.learnerLastName,
        "lastAccessDate":   report.lastAccessDate,
        "completedDate":    report.completedDate,
        "complete":         report.registrationreport.complete,
        "success":          report.registrationreport.success,
        "totaltime":        report.registrationreport.totaltime,
        "score":            report.registrationreport.score
      })
    });

    // Create a new workbook.
    let wb = XLSX.utils.book_new();

    // Returns a worksheet from an array of objects.
    let ws = XLSX.utils.json_to_sheet(data);
    
    // Append the worksheet to the new workbook.
    XLSX.utils.book_append_sheet(wb, ws, "Registration Results");

    // Write the file to disk.
    XLSX.writeFile(wb, "Registration Results.xlsx");

  }
);

Initial setup

API credentials

Retrieving registration results

Writing data to an Excel workbook

Further reading