Yearbook Photos vs. the Pandemic

Feb 25, 2021

We haven't had kids on campus to get their school photos taken this year. So, I decided to have parents take yearbook portrait photos themselves and upload them via a Google Form. The problem: the spreadsheet created by the Google form has a link to the uploaded file but doesn't include the actual name of the file, just the Google Drive file ID. In order to upload the photos to our yearbook software and have the portrait photos automatically load with student names associated to image files, I need the image name in the spreadsheet. A perfect opportunity to practice some Python programming! So, I hacked together the following solution. If you need to do something similar, you will have to edit the code a bit to match your own environment.

1. Use this Google Apps Script to create a spreadsheet that lists the uploaded files from the Google Form file uploads folder and download the spreadsheet as a CSV file. I called it: 'listing.csv'
2. Download the responses spreadsheet created by the form as a CSV file. I called it: 'responses.csv'
3. Put them both in the same folder and create an empty plain text file called 'final.csv'
4. Copy/paste the following python code into a plain text document and save it in the same folder. I called it: 'compare.py'
5. Run the program from a command line (>python compare.py) and it will populate the empty CSV with a combo of the data from both source CSVs.

import csv

# create arrays to hold data
response_rows = []
listing_rows = []
parts = []

# read responses into list
with open('responses.csv', 'r') as csvfile:
	csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
	for row in csvreader:
		response_rows.append(row)

# read listings into list	
with open('listing.csv', 'r') as csvfile:
	csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
	for row in csvreader:
		listing_rows.append(row)
		
# get image IDs from responses, find row in listing, 
# add student name, grade, and filename to output.csv
for row in response_rows:
	#make the URL match with listing_rows format
	ID = row[4].replace("https://drive.google.com/open?id=","https://drive.google.com/file/d/")
	ID = ID + "/view?usp=drivesdk"
	
	#find the URL in listing_rows
	result = list(filter(lambda x: ID in x, listing_rows))
	tostr = str(result)
	
	# pull out just the image filename
	parts = tostr.split(",")
	imagename = parts[0].replace("[['","")
	imagename = imagename.replace("'","")
	
	# add it to the row
	row.append(imagename)
	therow = str(row)
	therow = therow.replace("[","")
	therow = therow.replace("]","")
	therow = therow.replace("'",'"')
	print(therow)
	print("writing to file...")
	
	# open final.csv for writing
	with open('final.csv','a') as final:
		final.write(therow + "\n")

	

< back