Origin Story The 2020 All Stars project1 is tied to the FotoFika site supporting photography faculty teaching online due to COVID. One of the concerns that came up in discussions was that this year’s graduating class wouldn’t be getting the personal connections that normally happen during exhibitions. That led to a discussion around Mike Mandel’s baseball card series for photographers back in the 1970s. The idea here is that we’ll generate baseball card-style images for graduating students. We’ll have a second portion where curators will be invited to comment on specific artist cards and their comments will be integrated into the card. Some portion of the cards will eventually be printed.2 Technical Stuff of Interest The site ends up being interesting in a few ways. It’s running off a Google Form for image uploads etc. What you may or may not know already is that Google stopped letting you serve images from folders in any sort of decent way a while back.3 Sure you can do stuff like what the Awesome Table people suggest here but the image quality is garbage and I couldn’t get a number of the URL parameters to work correctly. I opted to ask Jeff for an answer. He came up with the following Google Script which is pretty cool. It takes the file ID and […]
Well not really but I’ll explain it anyway. Gravity to Google There are a number of plugins that tie Gravity Forms to Google Sheets but most (all?) of them rely on the ability to create an application to get authentication tokens. Many institutional accounts turn off that capability. VCU has turned that off. As a result, I ended up going the more manual route with this Google Script integration. Note the Martin Hawksey mention! I won’t re-write their documentation here but it does work and it’s relatively easy to set up. Counting Unique Things Now that we had our data showing up, I wrote some stuff to summarize it. Nothing too fancy, but I do find myself using =sort(UNIQUE(Sheet1!E2:E)) which gets the unique values in column E and sorts them and then doing a =COUNTIF(Sheet1!E:E,”=”&A2) to count how many times those unique values show up. That all works pretty well for a summary look but there was also a desire for monthly data. That’s complicated by the data being added live on a continuous basis and the date field coming in with a full date/time stamp. I considered, and attempted, a variety of paths and ended up using =query. It works well and I thought some of the details were kind of neat. I also knew that I would forget […]
My click bait titles are so powerful! This isn’t anything special but it does show a couple of Google Script patterns that we reuse all the time. Folders are made in other folders! Documents are made in those folders. Things are shared. Ownership is assigned! So many things. The following script takes a form submission and then . . . makes a folder in a particular folder, shares it with particular people, sets the ownership makes a document within that sub-folder, shares it with particular people, sets the ownership adds them as a viewer on a different folder deals with some issues VCU has with students having both @vcu.edu and @mymail.vcu.edu email addresses but without them behaving as if they are the same in Google Details are in the comments. The trigger is on form submission.
The idea that data can flow to different places for different purposes is one of the key concepts I want people to believe in. Different technologies and different interfaces have different affordances depending on what you’re trying to do. In this case, we’ve built some online training for students. As part of that training they need to sign off indicating they read various rules and safety advice. We’re using Gravity Forms to collect that information. We’re going to set a special notification email that’s easier to parse in addition to the regular email that gets sent out (that one is oriented towards student confirmation and alerting the individual faculty). Gravity Forms Notification We’re just going to put the student email and faculty email in the subject line with a space between them. I did some fancier stuff early but went back to this when I realized what we were doing just wasn’t complex enough to justify extra drama. I set the from name to Health Hub Logger so it’d be easier to write the filter in GMail. Notifications in Gravity Forms are pretty straight forward but you can find out more on their site. GMail Filter I then setup a filter in GMail so that I could be confident that the Google Script could find these emails and that I […]
This is a pretty specific thing but the concepts ought to be broadly applicable and interesting for the 3 to 5 people who will end up reading this. It’s a fairly amusing blend of less standard Google Functions and a bit of Google Script to do something fairly decent that had been quite a bit of hassle to do previously. We have Social Work students who are assigned to various supervisor/liaison people. There are a lot of students. We wanted students to be able to submit a form to Google Drive and we’d keep track of all this and show only the relevant data to the various supervisors. Files from Form Setting up a form that requires you to be logged in and accepts files is now very easy in Google Forms. It also remains easy to log that information to a spreadsheet associated with the form. Merging the Data The student email address became the unique ID that would allow us to tie the form submission to the list of students and their programs, liaisons etc. Now we needed a formula to link these two sheets via email. I started with =VLOOKUP but that would have required the student email to be the leftmost column in the data and that would be awkward for other things. After some banging […]
Here’s a neat little pattern that might interest others. We got a version of the question below yesterday. Is there a way to automatically get the links from the search linked below into a spreadsheet? https://www.google.com/search?q=site%3Aedu+filetype%3Apdf+syllabus+education&oq=site%3Aedu+filetype%3Apdf+syllabus+education Then, from there, is there a way to automagically get the pdf files into a Drive folder? Step 1: Get Google Search into Google Sheets At first it seemed this would be really simple. Amit1 had done this really well back in 2015. Unfortunately, Google has started blocking this . . . even when you do it within Google Sheets/Scripts. This made me sad. Browser emulators and Python were dancing in my head but it seemed a bit too complex for a one time action. Instead of over-complicating things, I opted to use a Chrome plugin called Scraper. I’ve had it installed for a long time. It lets you easily do xpath scraping of websites. You can see in action in the video below. I also used the search settings to change the number of sites per page up to 100. Once I captured the info to the clipboard I just pasted it into Google Sheets. Step 2: Save the PDFs Now I just needed to loop through the URLs and save the PDFs to a particular Google Folder. This google script gets my […]
Jeff and I are doing a THAT Camp workshop that’s trying to talk about the scale/scope/potential of Google Sheets in the big box of Digital Humanities related options. Depending on how the workshop goes, I’ll revise this post to be more useful. Getting Data In There are quite a few ways to pull data into Google Sheets. In addition to uploading or converting csv, Excel or other fairly standard options you have a variety of internal Google Functions that will let you pull stuff in that might be useful. The sheet below demonstrates one built in import function on each sheet tab. The function is delineated in cell A1 of each sheet. These are all built in functions that are pretty straight forward. At the other end of the spectrum, you can write your own Google Scripts to import data from APIs. Below is an example that uses the YouTube API to gather data on a variety of videos every hour. Publishing Data Obvious publishing formats for your spreadsheets include PDF, CSV, webpage, Excel, etc. But there are also additional options like JSON which are not seen in the GUI interface. The JSON endpoint is patterned like https://spreadsheets.google.com/feeds/list/12WsyLvtfIPJkCXgEXsRkrI6dS0_K6brJikZqzkQa8TU/1/public/values?alt=json Displaying Data At the most basic level you have the option to create charts and graphs that are live linked to the […]
We had a list of rampages sites in a Google Spreadsheet and wanted to know when they were created. I started to look that up but only managed to do it twice before I gave up and went in search of another way. In this case it took two little bits of code. This first piece is active on our generic site-wide plugin. It adds the blog’s creation date, last updated, and post count to the base JSON data. That’ll be handy in the future if we want to checkup on sites with only one query rather than multiple queries. This second piece is a Google Script that makes a function that I can call in the sheet by typing =getCreationDate(“http://someurl.com/”) The two together answer my immediate problem but the JSON modifications have some long-term value for us and might be useful to someone else.
It’s a bit awkward to see who did what in a Google Doc via the version history. That’s something faculty often want to do. The easiest path I found was to name the oldest and newest versions. Then click to show only named versions. That does it pretty well. The video above shows it with more detail.
Google Sheets often thinks it’s smarter than you. That’s helpful at times and irritating at other times.1 The Google Form to Sheets path is one place where that can come up repeatedly and in ways that are hard to see. For instance, if you use the Time entry on forms, Google Sheets tries to coerce it into a date structure while leaving you seeing just the time in the sheets view. That leads to weird and unpredictable stuff if you’re trying to use Google Scripts to interact with that cell data based on what you see. For instance, this is data from the time entry field as seen on the sheet side. Note the two places you might expect to see truth. Now if we get that same data via Google Script . . . Cell data that you’d expect to just be that time element returns as Sat Dec 30 1899. The time is right though. If you manually change the format of that column to plain text things work . . . but it gets re-set on all subsequent submissions. My solution so far has been the following Google Script set to fire on every new form submission. In my case, I’m changing the format of the latest entry in the spreadsheet in columns F and G to […]