Introduction

Sakai is our LMS at Loyola University Chicago. This post will include some setups I often use and since these steps are needed only every semester, I prefer to write them down as a reminder for myself, while hoping that the post would also be useful for my readers. This post will be expanded in the future as new setups needed.

Motivation

Labster integration with Sakai had some unresolved issues in the Fall 2022 semester. The Gradebook sync problems seem to occur when one adds Labster simulations through the Assignments module.

I did not want to transfer grades manually not only because I have many students but also I am concerned by the data consistency. Instead, I developed the following workflow to do the manual grade import by a mechanical and repeatable manner.

Workflow

1. Exporting Grades from the Labster Dashboard

Download All grade data from Labster dashboard (Figure 1). You would get all of your class grades available in the Labster server in .csv format, which means:

  • All simulations
  • All attempts per student
  • Grades in percent values
  • Progress percentages
  • Durations
  • Data and time
Figure 1
Figure 1. All grade data.

2. Excel Manipulations

To import grades into Sakai Gradebook, UVID is required as first column in .CSV file. It can be extracted from Email column by the following steps:

  • Insert a new column next to Email column.
  • Select Email column.
  • Click Data > Text to Columns.
  • Select Delimited > Delimiters > Other: @. (Figure 2)
  • Click Finish.
Figure 2
Figure 2. Extract `UVID` values from email addresses.
  • Rename Email column to Student ID and copy it as first column.
  • Rename Last Name as Name and delete all columns except Student ID, Name, Simulation name, and Score (/100). (Figure 3)
Figure 3
Figure 3. Required Excel columns.
  • Rename Simulation name to actual sim name and append the max points in brackets. e.g. Lab Safety [110] and calculate each grade accordingly with zero decimals. And delete Score (/100) column. (Note that you must copy and paste the values Lab Safety [110] before you delete this column.) (Figure 4)
Figure 4
Figure 4. Scale the grades.
  • The remove duplicate attempts and assign the highest score, sort by Student ID > A to Z and Lab Safety [110] > Largest to Smallest (Figure 5)
Figure 5
Figure 5. Sort the grades.
  • Select Student ID column and click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. (Figure 6)
Figure 6
Figure 6. Highlight duplicate `Student ID`s.
  • Delete all values except the highest score for every student (Figure 7). Sakai does not show any errors for blank lines, so I keep them to save time.
Figure 7
Figure 7. keep only max scores for each student.
  • Finally, save this worksheet as a new .csv file. e.g. Lab-Safety.csv.
  • Import Lab-Safety.csv using Sakai > Gradebook > Import/Export menu.

Conclusion

I hope this workflow is useful. Please let me know if you use this method and how you like it. Also, please let me know if you have alternative solutions.