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 […]
When @cogdog & @twoodwar Were Building Websites From Google Spreadsheets Years Ago https://t.co/Im7oCecvpg — Keegan?Long-Wheeler (@KeeganSLW) February 28, 2018 This post from Keegan kicked of this demo.1 The goal is to create a pretty flexible template for using Google Forms to Google Sheets to simple website. I opted to do this one in Vue but may get around to doing it in plain JS or jquery if time allows. The first thing you need to do is make the form and put in some entries. You’ll be happier if your form prompts aren’t super long. Make them readable just keep them concise.2 Once you’ve made a few entries so you have some data to work with, publish your spreadsheet as HTML. File>Publish to the Web. Click OK and what not. Don’t bother going to that link as Google has made that a fake promise. You just need the ID of the document in your current view. It’ll be something painful like 12WsyLvtfIPJkCXgEXsRkrI6dS0_K6brJikZqzkQa8TU and is visible in the URL after the /d/ but before the /edit. You can see the spreadsheet here. It doesn’t really matter but !full transparency! With that ID in hand, you want to go to a URL like so. You could manipulate the number in the URL to hit different pages in the spreadsheet. https://spreadsheets.google.com/feeds/list/12WsyLvtfIPJkCXgEXsRkrI6dS0_K6brJikZqzkQa8TU/1/public/values?alt=json You should […]