ALA Data Migration

This is a Python program using Selenium and the Google Sheets API to provide an interface between two disparate software as a service (SaaS) web systems to greatly reduce the volume of data entry. This was a fun freelance project that will save this company approximately 100 hours over a year’s time. Neither site offers an API for a more traditional solution, necessitating the following approach.

The data input is a Google Sheet containing the exported csv of student grades from SaaS #1, the package that provides homeschooling curriculum.

From here, I modify the Google Sheet via the Python Google Sheet API and cleanse/standardize the data. The name is flipped to last name, first name, some columns are deleted, a letter grade is created based on the grade percentage, and a new column A is inserted to contain meta data.

Next, the program uses Selenium to open a Google Chrome page, login to Saas #2, the reporting service. The grade entry page is loaded and a loop commences, iterating through each row of the Google Sheet. The student is found, their final grade page is loaded, and a grade is pasted into each class.

The structure of the spreadsheet is such that each student’s classes are grouped together. So only when the last class’s grades are entered will the program then click the Save button on the page to commit the grades. This is to increase the speed at which the program executes–waiting for the page to save after each class grade is entered would be far longer of a wait.

If the loop finds a blank line in the spreadsheet, it ends the loop and exits, displaying the total number of students processed.

If the student or class isn’t found, a notation is made in column A of the Google Sheet so the administrator can followup and rectify any misspellings in the data. If the grade submission was successful, a simple ‘x’ is added to the column.

Once the program has finished, the administrator can resolve any student name or class name misspellings and rerun the program. Any lines marked with an ‘x’ are skipped, speeding up subsequent executions.

This project resulted in saving the customer approximately 100 hours over the course of a year by not having to manually transfer the grades from one system to another. Human data entry error is also eliminated.

Othello

This project was a fun exploration of controlling terminal layouts and simulating a classic board game. Some of the features:

  • user-definable board size (from 4 to 10 spaces square)
  • screen updates dynamically without need for scrolling
  • valid moves indicated on-screen by a “.”
  • piece “flipping” animation
  • running tally of player scores