Automation in Education — Python and Google Forms

Technology is omnipresent. Today, business around the world is heavily reliant on automation and technological expertise. As a keen and compulsive user of technology, staying curious and identifying methods of automating routine and unstructured processes / activities has been part of my seeking. As a part of this journey, I bumped into an opportunity at my workplace (educational institute), whose automation resulted in seamless launch of a brand new initiative at supremely low cost.

The pandemic has brought several activities across the globe to a standstill. The education sector has its share of problems. Although few initiatives have moved online, several examinations have been postponed or cancelled. Hence, the institute i am currently a part of, decided to provide a mock version of the same to self-test and evaluate one’s own progress. As this was rolled out free of cost, enormous number of registrations piled up. Hence, I sensed an opportunity to automate the task of bulk score report generation and dispatch. The mock exam was setup on Google Forms for familiarity and ease of analysis.

Tools and services used for automating the process are:

  1. Google Forms
  2. Python 3
  3. Cloudinary (Cloud-based file storage and management)

Google Forms provide responses in Excel/CSV format which can be used to derive further insights.

Python is a general purpose programming language which has a wide range of utilities, especially in the area of automation. The python libraries used for this automation process are as follows:

pandas==1.2.3
Flask==1.1.2
cloudinary==1.25.0
qrcode==6.1
Pillow==8.2.0

Cloudinary is a Cloud-based file storage and management platform which can be accessed and operated upon using the Python SDK it provides.

The first part of the Score Report Generation involves assembling the excel score reports from Google Forms, cleaning and reorganizing the respective scores, level-wise and eliminating duplicates. This can be done using Excel’s VLOOKUP to organize scores and inbuilt feature of Remove Duplicates to remove duplicates.

Next, python can be used to read (pandas) data from the formatted excel files , dynamically calculate the scores, identify the result as PASS / FAIL as per the sectional cutoff. The logic used is provided below.

#PSEUDO_CODE
def return_score():
final_dict = {‘level_1’:{},’level_2’:{}}
thresh = {'level_1':{'1':80,'2':76,'3':65},'level_2':{'1':98,'2':87,'3':85} }
levels={'level_1':['level_1_page_1','level_1_page_2','level_1_page_3'],'level_2':['level_2_page_1','level_2_page_2','level_2_page_3']}
for k,v in levels.items():
file_name = 'Results levels - '+str(k)+'.xlsx'
for sheet_n in levels[k]:
score_obj = pd.read_excel(file_name,sheet_name = sheet_n)
score_obj.fillna("0",inplace=True)
json_score = (score_obj.to_dict())['data']
for score in json_score:
numerator = score[‘section_1'].split('/')
s_1= str(int(float(numerator[0])))
score['section_1’'] = v_1+'/' +'100'
if int(s_1) >= thresh['level_1']['1']:
score['f_result']='PASS'
else:
score['f_result'] ='FAIL'
# Similarly cutoff's for all levels are populated and added to a dictionary that is finally returned from the function
return final_dict

As the next step, it was required to populate the score generate into a well- designed, neat score report template to generate the interim score report. Hence, a score certificate was designed using Canva and was downloaded in the PNG format. The candidate and score details was populated on the score report image using the Pillow library. Using pillow, one can position the content and also use a font style of one’s own choice to suit the purpose.

As it is a digitally generated score report, the actual scores can be modified offline using a plethora of techniques. Hence, in order to render the score reporting structure more authentic, a QR code containing the link to the cloud copy of the same was embedded in the score report. In order to know more about QR Codes, click here.

The generated interim score report was uploaded to the cloud based file management service, Cloudinary programmatically using Cloudinary’s python sdk. Although the documentation has sufficient guidance for integration with the Django framework, slight modifications in the API calls render it eligible to be used without Django. The API returns a secure url of the file hosted via CDN (Content Delivery Network). This URL was used to generate the QR Code which stands superimposed on the image of the score report. The score report that has the QR Code is then converted to the pdf format using Pillow.

import qrcode
import cloudinary, cloudinary.uploader
qr = qrcode.QRCode(
version=1,
error_correction=qrcode.constants.ERROR_CORRECT_H,
box_size=7,
border=2)
cloudinary.config(cloud_name = 'XXXX', api_key='XXX',api_secret='XXX') #Cloudinary Python SDK format
upload_result = None
file_to_upload = 'XXX'+'.png' #dynamically generated
options = {
'public_id':'XXX',
'tags': "directly_uploaded",
'folder': 'XXX/YY'
}
if file_to_upload:
upload_result=cloudinary.uploader.upload(file_to_upload,**options)
url = upload_result['secure_url'] #API Returns a JSON which has the secure_url
qr.add_data(url)
qr.make(fit=True)
img = qr.make_image(fill_color="black", back_color="white").convert('RGB')
img.save("qr_image.png")

As the final step, the score report is then dynamically sent by e-mail with a custom message and the corresponding details using the Flask-Mail package as part of the Flask web framework.

Attached above is the workflow of the above explained automation process. Although a fairly simple puzzle to have put together, it fared very well beyond expectation. The result dispatch which was an estimated whopping 1 month post the examination manually was reduced to a mere duration of less than 2 hrs. Automation to an efficient business workflow results in saving of either time or money. In this case, it was predominantly reduction of time.

Cheers!