Site news

Moodle Spreadsheets Made Easy!

Picture of Carl LeBlond
Moodle Spreadsheets Made Easy!
by Carl LeBlond - Friday, February 20, 2015, 8:39 PM

In the early 90's I was fresh out of engineering school and working for an environmental engineering company.  I was responsible for collecting and tabulating various chemical analysis data for my projects.  A colleague introduced me to this wonderful tool called "Lotus 123".  I was immediately hooked on spreadsheets and the underlying mathematical manipulations made possible with them.  They have been a huge part of my work, and other than the word processors, the spreadsheet is my go to software tool.

About 3 years ago when I began developing courses in Moodle I was somewhat surprised at the lack of spreadsheeting capabilities.  Other than the Moodle spreadsheet question type and some discussions on embedding Google spreadsheets, there is not much available.

My first attempt at developing a spreadsheet for Moodle focused on writing the spreadsheet portion entirely in JavaScript.  I stumbled across Ondřej Žára's Excel-like spreadsheet in "less than 30 lines of code".  Check out the JSfiddle demo.   Regardless of how elegant this implementation was its biggest drawback to me was its limited math functionality.  While I could have modified to achieve my goals there was still the issue of saving and editing the spreadsheet.  After examaning a number of alternatives I decided to focus on using dhtmlxSpreadSheets since it was GPL licensed, had decent mathematical functions and API documentation.

What I wanted in a spreadsheet for Moodle was some intermediate level math functions, the ability to save/edit sheets and group editing for team projects.  With this in mind I developed three plugins for Moodle.

The first component is a Atto HTMLEditor plugin (atto_spreadsheet) that allows users to create a new dhtmlxspreadsheet in the database and then insert a DIV with class=spreadsheet in to your content as a handle for the filter.

Create sheet

The second component is a filter (filter_spreadsheet) which converts DIV's with class="spreadsheet" into dynamic spreadsheet instances and handles all the security for editing spreadsheets by user and group membership.

Finally the manager block (block_spreadman) allows the user to view and edit all their spreadsheet in every course they are enrolled in.  It also keeps track of orphaned spreadsheets (i.e. sheets that aren't associated with a course).

Here's an active  demo spreadsheet  Only I can change the text and mathematical formulas.  Below I provide a screenshot of what the sheet appears like if you have editing rights.  Enjoy!