Mentoring Day - Google Forms to Docx reports

This was another fun one brought about by Covid. Every year we have a "Mentoring Day" at the school. On these days, there are no lessons and students only come in (ideally with their parent(s)/guardian(s)) for their pre-booked meeting slot with their tutors to discuss how everything is going and set targets for the rest of the year.

With the added complication of them being on remote learning at the time, we couldn't even go through the questions with them easily.

So the compromise was to send the questions to the parent/guardians in advance, then on the day they would have a five minute video chat with the tutors to go through their answers and set their goals.

To make this easier for the staff, the questions would be sent using Google Forms and then put together into a concise Word document for each student with their answers and targets that could be uploaded to our Student tracker system.

The form itself was simple to set up - mostly paragraph/text answers, with drop downs for House, Tutor Group and Year Group.

Once the form had been sent out and replies started coming back in, they had to be formatted as required, as it would be a bit friendlier for staff to work through than a 1200 row Google Sheet!

Version 1a - The template

I started off by coding in the creation of a template using the docx Python module

The first version required the person compiling it all to save the results Sheet as an Excel file and then run my Python script on it.

def setupTemplate(document, dateSubmitted):
    if dateSubmitted == "None":
        return("#","#","#")
    dateSubmitted = dateSubmitted.split(" ")[0].split("-")
    section = document.sections[0]
    header = section.header
    paragraph = header.paragraphs[0]
    paragraph.alignment=2
    try:
        paragraph.add_run().add_picture(
          os.path.join(sys._MEIPASS,'files/AdmiralLogo.png'),width=Inches(2.0))
    except:
        paragraph.add_run().add_picture('AdmiralLogo.png',width=Inches(2.0))

    paraHeading = document.add_heading(level=0)
    runHeading = paraHeading.add_run('Mentoring Day Questionnaire Results - Feb 2021')
    paraHeading.alignment=1
    runHeading.font.size = Pt(16)

    footer=section.footer
    paragraph = footer.paragraphs[0]
    paragraph.alignment=0
    paragraph.text = "Date form submitted: " + dateSubmitted[2] + 
      "/" + dateSubmitted[1] + "/" + dateSubmitted[0] +
      "\t\t" + "Date report generated: " + datetime.datetime.now().strftime('%d/%m/%Y')

    paraStudentName = document.add_paragraph()
    paraStudentName.paragraph_format.space_before = Pt(0)
    paraStudentName.paragraph_format.space_after = Pt(0)
    runStudentName = paraStudentName.add_run("Name: ")
    runStudentName.bold = True
    paraTutorGroup = document.add_paragraph()
    paraTutorGroup.paragraph_format.space_before = Pt(0)
    paraTutorGroup.paragraph_format.space_after = Pt(0)
    runTutorGroup = paraTutorGroup.add_run("Tutor Group: ")
    runTutorGroup.bold = True
    paraYear = document.add_paragraph()
    paraYear.paragraph_format.space_before = Pt(0)
    runYear = paraYear.add_run("Year: ")
    runYear.bold = True

    return paraStudentName, paraTutorGroup, paraYear

This created the basic layout of the information sheet, including the school logo and the basic information like name, tutor group and year group.

Version 1b - Getting the data

This first version required you to save the Google Sheet as an Excel file. I then used the openpyxl library to parse the spreadsheet.

This code basically went through row-by-row, cell-by-cell and saved the question list (the first row) and then the answers to an array, with a couple of formatting tweaks. To complicate things, some questions only applied/were shown to certain year groups (e.g. only Year 11s were asked if they already had a college/apprenticeship lined up).

The questions and answers were then formatted correctly and put into the Word document created using the template above.

firstRow = True
added = []
skipped = []
lastRow = False
for row in inputWb.active.iter_rows():
    studentName = ""
    answers = []
    columnNumber = 0
    if lastRow:
        break
    for cell in row:
        if columnNumber == 0 and not(cell.value):
            lastRow = True
        if firstRow:
            if columnNumber>2:
                question=str(cell.value).replace(" [","").replace("[","").replace("]","")
                questionList.append(question)
        elif not lastRow:
            if columnNumber == 0:
                dateSubmitted = str(cell.value)
            elif columnNumber == 1:
                studentName=str(cell.value).rstrip().lstrip()
            elif columnNumber ==2:
                studentName=(studentName+" "+str(cell.value)).title().rstrip().lstrip()
            elif columnNumber > 2:
                answers.append(str(cell.value))
        columnNumber = columnNumber+1

    if (not(firstRow) and not(lastRow)):
        try:
            tutorGroup=answers[0][0]+str(answers[1].split(".")[0])
            if not os.path.exists("Tutor Reports\\" + tutorGroup):
                os.makedirs("Tutor Reports\\" + tutorGroup)
            if not os.path.exists("Tutor Reports\\" + tutorGroup+"\\"+
              studentName.replace(" ","")+".docx"):
                document = Document()
                paraStudentName, paraTutorGroup, paraYear = setupTemplate(
                  document, dateSubmitted)
                if paraStudentName == "#":
                    break
                paraStudentName.add_run(studentName)
                paraTutorGroup.add_run(tutorGroup)
                paraYear.add_run(answers[10].split(" ")[1])
                index=0

                for question in questionList:
                    if index > 1:
                        if question=="":
                            continue
                        if index == 10:
                            if answers[index] == "Year 7":
                                index = 11
                            elif answers[index] == "Year 8":
                                index = 12
                            elif answers[index] == "Year 9":
                                index = 13
                            elif answers[index] == "Year 10":
                                index = 14
                            elif answers[index] == "Year 11":
                                index = 15
                            continue
                        paraQuestion = document.add_paragraph()
                        runQuestion = paraQuestion.add_run(questionList[index]+"\n")
                        runQuestion.bold = True
                        runAnswers = paraQuestion.add_run(answers[index])
                        runAnswers.italic = True
                    if index < 10:
                        index = index +1
                    elif index > 10:
                        break

                paraQuestion = document.add_paragraph()
                runQuestion = paraQuestion.add_run("Any tutor comments:\n")
                runQuestion.bold = True
                runAnswers = paraQuestion.add_run("\n")
                runAnswers.italic = True
                runAnswers.font.color.rgb=RGBColor(0xFF, 0x00, 0x00)
                document.save("Tutor Reports\\" + tutorGroup+
                  "\\"+studentName.replace(" ","")+".docx")
                added.append(studentName + " (" + tutorGroup + ")")
            else:
                skipped.append(studentName + " (" + tutorGroup + ")")
        except Exception as e:
            Monitor.write("Error detected - tell Andy!")
            Monitor.write(e)
            mw.mainloop()
            pause(5.0)
            quit()

    firstRow=False

This created a folder for each Tutor Group and then saved a Docx for each student into their own tutor area. All staff needed to do now was browse to the folder and they could see all of their tutees, ready to go!

Version 2a - Connecting to Google Sheets

Now that I had the basics running, I wanted to improve it so that the staff in charge could run the report themselves (and, if it was a long term project, it could even have been scheduled).

The first part of this meant connecting to Google Sheets to access the live data, to avoid staff having to have permissions for the results sheet and logging in to download it each time.

This was actually pretty simple thanks to Google's APIs and Python guides!

I followed the main part of the guide above for the initial connection to Google and then just simply connected to my Google Sheet and used that for the data source. The rest of the code was basically identical!

service = build('sheets', 'v4', credentials=creds)
Monitor.write("Connected to Google Sheets")
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,range=RANGE_NAME).execute()
values = result.get('values', [])
Monitor.write("Loading values")

Version 2b - Compiling it to an .exe

This again was simple using the PyInstaller library. One little thing I did do was encode the program icon directly into the python file, to save copying a .ico file along with the installer/converter. I just used an online converter to encode the .ico into base64 and then did this:

icondata= base64.b64decode(icon)
tempFile= "icon.ico"
iconfile= open(tempFile,"wb")
iconfile.write(icondata)
iconfile.close()

to convert the base64 data into a logo at run time. It did make it slightly slower to start up, but not noticeably (unless you were looking for it!). That said, it was enough that I probably wouldn't do it that way in future and just stick to including the .ico file.

Summary

All in all, this was another great learning project for myself and it saved the admin team many, many hours of manually copy/pasting data between Google Sheets and Word - a great success I think!