JSON display from spreadsheet

Google Form to Video Display

This is another SPLOT-ish tool that takes videos uploaded through a Google Form and then displays them.1 This particular proof of concept was built in about ten minutes as a result of a conversation with our World Language faculty who are going to be doing student-to-student video work with people from other countries. We’re likely to use Zoom as the platform. It makes recording the video easy and the compression of the video is pretty impressive. Initially I was concerned about file size but between that and seeing that Google allows up to 10GB file uploads via their vanilla forms. The caveat there is that the “file upload option is only available for G Suite customers with a Google Form shared within their organization.” Good enough for our purposes but a bit limited if you wanted to use it more broadly. Form Stuff I opted to auto-log the VCU email address since the file upload required that anyway and only used two other elements- a file upload piece and a single categorical element. You could get much more complex if you had the desire. Just make sure you’ve created a results spreadsheet and published it to the web. The Pieces My form is here. The spreadsheet is here.NOTE: Google now creates two IDs when you publish this. There’s an ID […]

Silent Submission of Google Forms

If you tuned in about half an hour ago, you’d have seen how we’re triggering channel creation in Slack based on a custom post type getting published. One of the other tricks we wanted to happen as a result of that was the creation of a Google Folder. There are a variety of ways to play this but some of the easier ones would require some options we have blocked on our VCU accounts. I could have gone around that via a personal account and then subsequent sharing but it seemed like it’d be more fun to do it this way. I knew I could trigger script events based on form submissions and that I could use the data in the form as variables as well. I also knew I could fill out form variables via URL parameters. What I didn’t know was whether I could submit a Google Form without actually hitting submit. Turns out you can. Take your normal form URL. https://docs.google.com/a/vcu.edu/forms/d/e/1FAIpQLScK2wgma6Oicv_ZY9i-6tg_w9RfEKKkgiAFJDw15jJnmr5ofQ/viewform?entry.1431785794 You can get one of the pre-filled URL patterns like so . . . Which gives you a URL like this. You can see my pre-filled response ‘fish tank’ at the end of the url. https://docs.google.com/forms/d/e/1FAIpQLScK2wgma6Oicv_ZY9i-6tg_w9RfEKKkgiAFJDw15jJnmr5ofQ/viewform?usp=pp_url&entry.1431785794=fish+tank Now to make it auto submit ‘fish tank’ you have to change one piece and add an element at the […]

Setting Cell Values – Google Forms/Sheets Workflow

flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) Often, people really like the workflows enabled by Google Forms but they’d like it to go one step farther . . . like adding up two submitted items, or running a particular function/formula against the submitted data. You can certainly go in there and manually enter formulas or drag down to apply them to additional cells but that sucks and if you’re automatically displaying this data live somewhere it’s an impediment to a solid workflow. Most importantly, it sets a human to do something that a machine ought to be doing- that is the path to Skynet assuming control. We can and must resist! Subjugate the machines whenever possible! The script below is broken into three parts and shows two different ways to set the value for columns based on data entered in a form. Part 1 – This is pretty much default information that lets the script know which spreadsheet and which page it’s working with. The getLastRow() is really handy for applying this to data as it is entered. This example form writes data to columns A – K. In example one, we do the math internally and just spit the result into the cell. That’s handy for lots of stuff and can be […]

Book Review Site via Angular and a Google Form

I had an interesting conversation with a pair of elementary school teachers. It led to me saying I’d put our two older kids on Good Reads in lieu of some less pleasant ways of documenting their reading. It’s not a place I’d necessarily drop a group of 3rd graders. As we spoke, I figured I could build a little version of one very quickly with Angular and a Google Form. It’s worth noting that once you set up one Angular page tied to a Google Sheet, you can just copy that over and make websites very, very quickly. It’s just a matter of re-aligning the json data. I hadn’t done anything with Angular in a while and while doing this I found out a few other neat little tricks that’ll be of use at some point. One was this slick star rating CSS option. It will show the star rating based on a div percentage. I really liked it but my rating scale was 1 to 5 and Google didn’t really support doing something with percentages. However, I didn’t realize you could do math right in Angular. It turns out you can and I did it like so. The entry.gsx$_cyevm.$t part is just the name of the data chunk that holds the 1 to 5 rating scale. I can divide […]

Pre-Filling Forms via URL

I have to figure out a rather unpleasant and boring thing. I am, however, learning some fairly odd and interesting tricks as a result. This is one that might be useful to someone. Google Forms You can pre-fill Google form entries with a URL. That might be useful if you had 720 students in groups of 6 reviewing one another but didn’t want to build a form with 720 student names or build a 120 forms with 6 student names. I don’t think I’m going to end up using this for this purpose1 but maybe it’ll prove useful to someone else and it’s dead simple. Step one – Build your form. Step two – Go to Responses in the Form Editor view and select “Get pre-filled URL”. You then fill out the form the way you want and it creates the URL. In this case, I’m filling out a multiple choice question and a free form text entry. https://docs.google.com/forms/d/1P5_6vTv53MEKCEjd87xecI483goNqDg1-nPlFH84Mz0/viewform?entry.1615031756=Bob+Smith&entry.1012634392=I,+for+one,+have+always+admired+the+number+two. Now, you might wonder what would happen if in the URL you set a multiple choice answer to something not available as an option- like ‘Freddy Kruger’ for the first field in the form. I wondered that. It just comes up blank in the spreadsheet.2 Sadly, as I mucked around I couldn’t come up with a decent way to hide the […]