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.
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.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.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")