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 […]

Playing with Words – Google Sheets to jQuery Drag/Drop

flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) This is a modification of the old refrigerator poetry concept based on a request from some of our World Languages professors but it’ll likely have some broader applicability. It essentially allows for three major things. You can create draggable elements (words, phrases, any HTML) from a Google spreadsheet You can create destination areas for those elements (also through the Google ss) You can make it so that elements that don’t match those destinations won’t “stick” there (spreadsheet again) It might be easier to see what’s up through the short video below. This was done mostly in jQuery but there are also a few Google Script elements that make life easier. The page below has everything except the CSS. It’s decently commented I think. The only real trouble I had was figuring out where/when to feed in the draggable/droppable elements. It kicked through after a bit of experimentation but I have a long way to go with javascript. Google Script There’s a directions sheet in the spreadsheet where I wrote a custom function. That allows me to write =getId() in the cell and have it generate the right URL for whatever spreadsheet it is in. You can also automatically copy my spreadsheet by going to the following […]

Free Speech? Random Scenario Generator

Talking to Dan about his sports law course resulted in this random generator which might be fun for others. It’s still developing but I like the potential for reinforcing some concepts about free speech in a fun way that allows you to repeatedly explore the topic without it getting tedious. I thought this would be a few minutes of work but I believe that Google has shifted the structure of their JSON feeds from the Spreadsheet. It could also be that I am insane. This particular experience did remind, rather unpleasantly, that I don’t fully understand how nested JSON parsing works. I ended up in the right place but only through about 40 minutes of slamming my head into various walls of misunderstanding. I also need to spend a bit of time applying the DRY concept to this bloated mess.

Hide/Show Angular via URL Variables

flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) For some reason I’ve been hiding/showing things a lot recently. One nice little trick I saw (and have already used) was based on the target class. You can see it working – hidden here and expanded here. Notice the #details piece in the URL. The CSS that makes it work is below. Nice and simple. That’s nice if it’s one element you can address via an id. It doesn’t work if you want to hide and show multiple items which might have the same id. I wandered around a bit and found a more interesting way to do it in Angular. The following javascript gets the variable from the URL. It’s straight from this article. So this URL http://bionicteaching.com/tools/hcps/hcps-tools.html?school=varina_hs would pass varina_hs to the variable school. If the school=varina_hs chunk is gone, then the box is invisible. I need to add that variable to the Angular scope so it can be accessed in the template. So now that we have that we can use it. Wandering around a bit I found ng-if. It’s essentially a way to show or hide and element based on whether the statement returns true. This code will return TRUE under either of two conditions. If the JSON element entry.gsx$school.$t has the […]

Portfolio Work – Interweaving the Personal API

I know. The title is pure click-bait. That’s part of why this blog is so wildly popular.1 I’ve been building a new portfolio site2 and I think some of this is kind of interesting even if it sounds boring. There are a few different goals in play. One challenge is to create a site that stays up to date with minimal work on my end. It’s a parallel of the small-pieces-loosely-joined mentality. I want tiny-actions-over-time (from the aforementioned small pieces) rather than widely-spaced-herculean efforts. I’m also trying to make sure that it fits in well with my current workflow and that I’m capturing the work I do elsewhere in ways that make sense. Another focus is to keep any work highly portable. I’ve had to re-enter data a number of times as I’ve migrated and I don’t want to do that any more. That’s going to be made possible mainly through some new API options and by working on my API/JSON, JavaScript skills. I’ll probably have to do chunks of it over anyway but I like to pretend I wont. I’ve got a ways to go but I’ve made some decent progress. The basic template/visuals are handled by Bootstrap. I’ve also got some simple Angular views, Timeline JS, JSON from Google sheets, WordPress WP Rest API v2, and Pinboard’s API. […]

Drag/Drop/Sort to Google Doc Creation

This is an experiment focused on getting content chunks from a Google Spreadsheet into a drag/drop web interface for manipulation then back to a Google Form (could be skipped but I did left it so people could add/alter other form elements as desired) and then spitting out the final product as a Google Document. The image above essentially shows the flow. It’s all done based on the one spreadsheet which has three sheets- one for the content from the form, one for the drag/drop stuff, and one for some additional content to throw into the final document. It sounds more complicated than it is . . . although it could be simplified. There is interesting potential here to create some really unique experiences without a lot of programming knowledge. Drag/Drop/Sort I looked around a bit and ended up settling on a really slick js library called Sortable. The page consists of a couple of different elements. The HTML is straight forward. This gives me a text field, and three divs – bacon related, animals, and a drop zone. The spreadsheet stuff will be added to the first two and the human will be able to drag and drop them onto the drop zone (and then sort them as well). This chunk of javascript gets the json from the second sheet […]

Google Script to Copy Row Above to Blank Row Below

I had a spreadsheet that entered blank cells when there was more than one admin for a WordPress site. So if Site_1 had two admins, I’d get two rows of data. The first row for the site would have- siteURL | siteTitle | siteAdmin but the second row for that site would have something like- < blank > | < blank > | siteAdmin I started to just drag down and fill but there was lots of data and it just felt like something for the machine to do. The following Google Script did it for me in a few seconds despite a couple thousand rows of data. Not rocket science but it might be handy for someone and it was a really convenient example of variables and loops when I had a conversation with my son last night.

Radioactive Collage Maker

Radioactive is the book that VCU’s first-year students are reading. It’s an interesting book with interesting art and collage playing a major role in the design. I hadn’t really looked at it until this morning but I ended up in a conversation and an idea came to mind. “What if we could deconstruct elements of the text (words and images) and give them to students to recombine in interesting ways?” It seemed like a fun thing to do and within reach based on work I’d done earlier with Google Spreadsheet Fridge Poetry but I wanted a few more capabilities to help people create better visual products. I took my phone and took a bunch of quick pictures of pages from the book that seemed interesting and easy to cut out. That took about 10 minutes to photograph and drop the backgrounds.1 Another 10 minutes and I had a working prototype using the old Google Fridge Poetry. Not bad but I wanted to be able to rotate/resize images and be able to save the results as an image file (something I tried but failed at doing last time around). Resizing It turned out resizing was built into jQuery with the resizeable. $( “.resize” ).resizable(); Nice but it resizes the element holding the image rather than the image itself . . . […]

Embed Collector Tool

Some people don’t have websites handy or their blogs won’t allow iframe embeds. They should still be able to have their students do stuff and get it all in one place. This is the Internet! And this tool is meant to deal with that. You can see a working example with three submissions (via a Google Form) here. It’ll take any iframe or HTML as a submission. The search box will also filter based on all the additional material associated with the element in the form. For instance in our example you can type “Sarah” and you’ll end up with one item. So the way this should work is you click here and accept the copy. You now have the spreadsheet and form. The directions are on the sheet labeled directions. I have a dramatic green arrow pointing at it in the image below. You can edit the form associated with the sheet under Form>Edit form. The only thing you have to leave/have in the form is the one called “The embed code.” Everything else can be deleted and/or changed. Stuff I Learned This pretty much a mashup of two recent projects – the personalized fridge poetry and the Angular/Google Sheets JSON stuff. This one runs entirely in javascript so to use the $_GET option, which javascript doesn’t have, I […]

Angular and Google Sheets JSON

flickr photo shared by Kecko under a Creative Commons ( BY-ND ) license Man. This was a slog and really highlighted gross inadequacies in my conceptual understanding of pretty much everything. That being said, with an end-around, it works. The whole thing is below but there were really two pieces where I got stuck and flailed away on Stackoverflow and other places for quite some time. Getting the JSON I always struggle with nested data and getting things right. It seems so stupid simple now . . . response.feed.entry but I’m often unclear when it’s periods, when it’s =>, or when it’s something entirely different. You mix that with a few levels of confusion around the process and you end up with many variables you can screw up. For instance, if I remember correctly response[‘feed’][‘entry’] also works. Searching all of the JSON I’m cheating here. I couldn’t get the scope of the filter to be all of the data returned in the JSON. It kept stopping at the first element- id. So I cheated. I wrote a formula on the spreadsheet to mash all the cells together per row and pointed the filter at that data. Ugly. But functional. I don’t know what it’ll end up being. Not what it is but now that the framework is built, we can […]