Quickly Extracting Open LMS data for Spread sheet analysis

This is the original post by @David Castro

We know that getting information for decision taking from Open LMS is a challenging (yet gratifying) job. Harnessing the power of data for making effective and efficient decisions is a dream for many, and a reality for few.

In Open LMS we believe that an LMS should enable this decision taking for any user at the moment they need it and however they need it.

There are various solutions, the most prominent being Intelliboard which is a great partner of Open LMS and integrates great with our platform.

Another solution is Watershed which can connect through an xAPI integration.

Both these solutions are great, as they come with different points of view for data and can be extended for different purposes.

In Open LMS, we provide the plugin Open Reports which allows users to gather different information from their courses in well packed and presented reports.

Since Open LMS 3.7 Maintenance Pack 2, we also provide a new Administrator report, Deferred Admin SQL.

This new report allows you to store Raw SQL reports for your site and run them when you need them. You can download your data in CSV and JSON formats. It's specifically designed for those administrators which need to take big data out of Open LMS for analysis.

Wouldn't it be great if we could connect the immense data we have in our Open LMS site with our decision taking workflows? In this blog post we'll review an easy integration we can build ourselves with Google Sheets and a bit of coding.

What you'll need

• An Open LMS site, if you don't have one, you can ask for a trial to see this tutorial in action.
• A Google account with access to Google docs

Login user history use case

Getting data

Let's say we want to review the login history of our users by reviewing the last time each user was online. We can get this information directly from the user table in Moodle.

sql SELECT * FROM {user}

I know what you're thinking; "Great. Thanks for telling me something I didn't know I could do with Moodle".

Hold your horses! Before you close this tab, what if I told you you can stream this data directly into a spreadsheet and refresh it whenever you need to. That's right! Ha, got you attention now didn't I.

Let's run this in Deferred reports to see how we can get this data onto files.

Open your site as an adminstrator, and access <your site>/blocks/reports/view.php?controller=default&courseid=1&action=deferredadminsql. You can also access Deferred reports quickly in Snap by opening the Gear menu, expanding Reports and then following Deferred Admin SQL.

Create a deferred report by clicking the +. Then set a name and add the SQL query above to the query field. Click Save and run.

After you do this, you'll be taken back to the report list, and your report will be shown as Waiting to be processed.

You can grab a cup of coffee while you wait for it to be Done processing.

When you display the Download drop down, you'll be able to see both formats we currently support for download.

Click on JSON.

Your browser may download or open a new window with a lot of text. What is this text? It's a JSON encoded file which holds all the information from your site user table. You can download the CSV file and open it with a Spreadsheet software app as well. But, we want to grab that URL we see for the JSON format.

Copy the URL for the JSON file for later use please. It should look something like this:

<Your site url>/pluginfile.php/1/block_reports/def_report_json/<report id>/dfr.json

Where <report id> is the id of the deferred report you created.

Allowing Moodle files to be downloaded using web services

Wow, I know what you're thinking. I am making you build a web service, yes. Sorry, there's a bit of tech jargon here, I hope you like the end result still.

Deferred Reports results are stored in the Moodle file data structure, this means we can get them using web services. Moodle provides a way in which we can get this information through their API, and adminisitrators can create web service access from Moodle.

Let's create a token for our admin user so we can download files.

Access your site's admin page, go to Plugins, Web services, and follow External services. At the bottom of this page, click on Add to add a new External service.

You'll be shown this screen, in which we will create a new service. We'll call it File downloads with a short name of filedownloads. Check Enabled and Can download files. Leave the rest as is.

After it has been added, you'll be shown this screen, please follow the link Add functions.

On the following screen, we will be adding some core functions that allow our service to browse files. Let's look for core_file and select core_files_get_files:browse moodle files once it's displayed in the search dropdown. When it's been added, click Save.

Now we need to create a token for a user which can use this web service.

Access your site's admin page, go to Plugins, Web services, and follow Manage tokens. At the bottom of this page, click on Add to add a new token. Enter the username for your admin user and select File downloads as the service.

Voilá! You now have a token you can use to download files from your site. Upon returning to the token list page, you'll see an alphanumeric set of characters under the column Tokens, you need to select this text for the token we just created and save it for later use.

Testing downloading files using web services

Let's write a URL which combines our service token and the URL you have for the file that the deferred report created:

<Your site url>/webservice/pluginfile.php/1/block_reports/def_report_json/<report id>/dfr.json?token=<Your file download token>

Where <Your file download token> is the token we created last section. As you can see, the difference between this URL and the first one we copied is that this one has /webservice/ between <Your site url> and the rest of the URL, and it has ?token=<Your file download token> at the end, which is the way we let Moodle know what our web service token is.

When you're done writing the URL, copy and paste it in your browser's address bar, and go!

You'll see the same JSON output you created before, but now, we are not using the user's session to get this file's contents, but rather the web service token.

Please save this new URL for later use.

Integrating with Google Sheets

Open your Google docs site and create a new Spread sheet. We're going to add a script which will allow our spread sheet to retrieve JSON data. Click on Tools and follow Script editor.

Create a new Script called ImportJSON.gs.

We're going to leverage this on an Open Source project created a while ago that allows us to read data from web endpoints. Copy the contents of this file onto your script, and save.

Close the Script editor window. What we just did is add more functionality to our spread sheet just by adding scripts. We are adding the function ImportJSON to it now. Click on the first cell of the sheet and edit it as a formula. We're going to add the new function added to get contents of the file URL we got on last step like so:

=ImportJSON("<Your site url>/webservice/pluginfile.php/1/block_reports/def_report_json/<report id>/dfr.json?token=<Your file download token>", "/", "noInherit,noTruncate,noPrefixHeaders")

Once you change the cell, you'll see a Loading text on the cell you edited. If it stays like this for too long, reload the page.

There you go! You should see the contents of your user table on your Spread sheet. You have successfully connected Moodle to a Google spread sheet!

You can now rerun your deferred report in Open LMS and this spreadsheet will automatically refresh once you change the cell where the formula with the ImportJSON function lives.

You can also add a new sheets which aggregate the values of this sheet, create charts and graphs, download it in other formats... Sky is the limit!

Conclusion

Open LMS is an amazing way to have all the power of SaaS at your fingertips. You can connect it to different data consuming tools so you can make decisions on the fly.