I’m in this season where I have so many things I want to write about, and need a push of activation energy to get my fingertips going on the ole keyboard. So, stay tuned for some new posts coming out soon!
This summer, Mr. Spreadsheet Whisperer (who’s appeared on TDAA every now and again) is teaching at a summer camp where some students have access to Google accounts (enabled with Google Classroom) and others do not due to the credentialing system in public vs. charter schooling. “What kind of summer camp needs Google Classroom??” you might be wondering? Well, this one is a sort of personal-finance-qua-entrepreneurship program and there are math and brainstorming assignments that need to be submitted.
Attempt 1: Force Copy
From day 1, we learned that Mr. Spreadsheet Whisperer was going to need to “distribute” template files into personal folders for each student so that they could work in (and ultimately submit) work based on a template file.
Google has a feature for this called “force copy.” When you receive a link in this format, you are immediately prompted to create a new version of the original template file. However, Mr. Spreadsheet Whisperer’s students would need to move that file into the correct folder location and/or share it with their instructors, which was proving to be impossible.
It was quickly becoming unmanageable and the file distribution was becoming a distraction from the fun/learning efforts underway! (Last year’s camp teachers said that they spent hours every week individually moving files into folders. Ugh!)
Attempt 2: Automate file distribution with Zapier
I have no affiliation or prior experience with Zapier and other apps like Make.com and other integration tools can likely do this just as well! I think it may also be possible with a well written Google Script. My bff + biz partner Emily helped me imagine what Zapier is capable of. My dear pal and mentor Steve showed me what is possible with file automation and middleware.
My first step: I created a file with the unique ID of the Google Folder assigned to each student. This is the “destination” for each file transfer. I did this manually by going through folders one at a time and grabbing the Unique ID from the URL.
In Zapier, I started off with a trigger that listened for new folders to be added to my source document, but I didn’t end up using it. If I could start over, I think I would design the whole solution as a “transfer” rather than a “zap.” If you know more about this than I do, please leave me a comment!
The second step (which was really the core action) looked like this:
Every few days, Mr. Spreadsheet Whisperer asks me to “distribute” a file to each of the student folders. All I do is update the File ID and then “transfer” the file using this clickpath:
And then my Google Drive looks like this…
Attempt 2.5: Distributing a “shortcut” instead of a “copy”
Once I had this rolling, Mr. Spreadsheet Whisperer asked me if I could add a shortcut to the Camp Syllabus to each folder. This is different from making a file copy because everyone gets access to the same source file, albeit from their own folder location. (This is a great tip for those of you who share files but prefer to keep a very strict folder convention in your own Drive!). I was able to essentially clone the work from the “Copy File” version and implement a “Create Shortcut” using a similar process.
Cost
At the time of writing (July 2023), I have spent $0 “real money” (not counting time, expertise) on this. I also found setting this up to be enjoyable! I signed up for a Zapier trial, which gave me 1,000 “tasks” (each file transfer = one task) and about 2 weeks to use them. When the trial expired, I transferred to the free plan which gives me 100 tasks per month. Depending on the number of files we need to transfer and the date that the tally starts over, I might be able to squeak by for another month (!!!) or else upgrade to 750 tasks/mo tier, which would cost $19.99 – well worth it! I’ll probably cancel my membership at the end of the summer unless I find another interesting way to use this technology.
Reflection and what I would do differently
- I think this is a super slick way to use technology to take care of repetitive tasks. It’s not “AI” or anything like that. It’s just using a repetitive loop to do the same thing over again and saving humans from dragging and dropping files. I LOVE this kind of automation and I think, in general, we should use it more 🙂 I’m thrilled that I got over my intimidation of Zapier and just freaking TRIED IT!
- I wish ALL teachers/camp counselors/activists/etc had access to suitable technology and and a tech team to make their jobs easier
- If I could do this again, I would:
- Skip the whole “Zap” thing and design this as a “Transfer” only. I didn’t understand that Zapier had this option.
- Make the Student be the “Owner” of the file-copy. Currently, I own all of the files and the notifications are rather annoying + the files clog up my Google Drive.
- Create a one-time solution for copying all files into a destination folder – this would be useful for the “new student” scenario, or just front-loading work in the beginning of the program.
- When a file is created, bring back the ID to a source spreadsheet so that teachers can open files (grading) by going down the column without going into each student folder
- Create a more descriptive naming convention for each file-copy (currently they all have the same name)
- Teach Mr. Spreadsheet Whisperer how to set the whole thing up so I don’t have to touch it 😉
so what?
If you find yourself thinking… “there must be a better way” … you are right! The big question then becomes – what IS that better way and how difficult is it to take advantage of? I hope this blog post sheds some light on cool things you can do with automation and my iterative process of solving problems and learning new tools. Please tell Mr. Spreadsheet Whisperer that he owes me an ice cream if you run into him around town!