Google Sheet Web Scraping Example

The Context Middlebury has an interesting setup. They call them Go Links. It’s basically a version of tinyURL that anyone at Middlebury (students included) can use to make customized Middlebury links. If you’re on campus, you can use go/whatever and off-campus you have to use go.middlebury.edu/whatever.1 This replaced a giant redirect list. I’m not sure how I feel about it. In some ways it’s kind of fun and useful but it feels like it creates considerable technical2 debt in the long run. It also habituates people to getting to things in ways that require rethinking outside of the campus network. I also don’t know how you end up removing these when the students graduate or when the usage dies out. I guess you’d do something around tracking usage and auto-deleting after a certain period of inactivity. Otherwise this just grows forever. I don’t think you want this chart to keep going up. There are currently 1,144 entries that start with C. That includes links like critical-conversation, critical-conversations, criticalconversation, criticalconversations . . . so you can see how this plays out. I found the statement below as I delved a bit deeper into the system. It’s a tricky spot. Go Links are something that faculty and students expect and use. I can’t help but see them as an ever-growing layer of […]


Parallel Practice Logging in Google Forms/Sheets

The goal of this project was to have students logging their practice on translation and a separate non-translation activity.1 It’s a neat idea and I proposed doing it via Google Forms/sheets because it was a really short timeline. Here’s a walkthrough of how it ended up with version two. It’s likely we’ll have a more packaged third version that will integrate some more elements. There’s not too much unique going on in the script but here are a few of the hassles and how I dealt with them. Connect Weekly Data for Stacked Bar Chart I needed a way to group the data logged per week. ISOWEEKNUM gives you the week number that a particular date occurs in. So this little bit sets the last row of column N to be the ISOWEEKNUM function and referneces the date the form was submitted. This is handy and I’d never run across it before. Keep it Sorted I was trying to figure out how to do the sorting on the QUERY but this seemed easier. This function just keeps the entire sheet ordered by the week number. That way if people enter data later, it doesn’t mess up the graph. Authorizing IMPORTRANGE via Script From the beautiful people at Stackoverflow comes this gem. Previously, I could create the sheet and put the […]