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

editor screenshot

SPLOT-light?

So the other day I posted about how to make silent Google Form submissions. Then this morning I was looking around at headless CMS options1 and saw this one being advertised as being driven by Google Drive/Spreadsheets. Those two things came together as I mowed my lawn and I wondered if I couldn’t make a little rich text editor to construct a one-piece content creator/displayer using Google Sheets. That led to a little research into URL parameter limits. And then this evening I made this editor. The page uses Quill to take care of the rich text editor. It turns out there’s a whole world of rich text editor options out there. I’m only scratching the surface with Quill but it works fine for now. It’s bare bones. You can associate an image via a URL, make a title, and add some text. It does show some interesting possibilities though and all with very little infrastructure or real technical know how. The image preview is built by this little bit of javascript. It’s based on having a text field with the id of ‘theImage’ and then there’s a check to make sure there’s not already an image attached and if there is it replaces it. This little bit of javascript builds the Google Forms URL from the various text fields […]

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

WP JSON to Google Sheets – Reflective Data

Image from page 86 of “Refraction and motility of the eye, with chapters on color blindness and the field of vision” (1920) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) Way back in 2015, I wrote a little plugin1 to count URLs, get the word count and do other stuff so I could reflect on my blog posts. Given some (k)new knowledge2, I figured I could make a version that runs in Google Sheets and indeed I can. The reason I like this as an alternative to the plugin is that it works for anyone who has access to Google Sheets even if they can’t install plugins. Google Sheets also offers a lower barrier to messing with your own data once you get start capturing it. You can count the !s, or a variety of emoticons, or how often you use the word “spaces,” or whatever you want- all without the ability to program in php or javascript. I think it starts to open up different doors for students3 to gather their own kind of data for reflection and amusement. It starts to get at the DIY ethos inherent in the quantified self communities. The sheet is here. I’m going to build it out into something a bit more robust and plug/play in the […]

WP REST API Custom Fields to Google Maps

Manuel, the young shrimp-picker, five years old, and a mountain of child-labor oyster shells behind him. He worked last year. Understands not a word of English. Dunbar, Lopez, Dukate Company. Location: Biloxi, Mississippi. (LOC) flickr photo by The Library of Congress shared with no copyright restriction (Flickr Commons) 1 I had a site we used for the Great VCU Bike Race course that’s been sitting around. I knew it had lat/lng data for lots of urban bicycle related posts. My goal was to get it into Google Maps via the REST API. Get Custom Field in JSON By default you won’t see custom field data in your JSON endpoints. There are some plugins to make that happen but I wanted to take a stab at doing it myself. Given the documentation, it was pretty trivial.2 The code below in a plugin or the function.php file would make the lat_long custom field show up in the post JSON. Using this URL, I can get the 30 most recent posts.3 Some Map Stuff Google wants lat/lng separate but it’s coming in as one item split by a comma. These little functions split it up nicely. In javascript, split breaks up a string by the defined element (comma in this case) and returns the pieces as an array. Since I’ll get two pieces […]

Google Script, Twitter API & Google Maps

Image taken from page 12 of ‘Guide to Cambridge: the town, university and colleges … To which are added, notes upon the villages within ten miles, a map, etc’ flickr photo by The British Library shared with no copyright restriction (Flickr Commons) This is more playing with Martin‘s TAGs in Google Script and then moving it into Google Maps for visuals . . . the TAGS related functions will only work in that context but the purse Google stuff should work in any spreadsheet. Get Twitter Location by Account This function will get the user’s location as defined by their profile. So =getLoc(“twoodwar”) would return Richmond VA. This function will get the lat/long via Google Script. So =getLl(“Richmond, VA”) would return 37.5407246, -77.4360481. Keep in mind if it’s trying to get the lat/lng for someone who put “the Interwebz” as their location like OnlineCrsLady then your lat/lng may not work out that well. So that’ll let us take a chunk of TAGs data.1 and do something like this with Google Maps. I’m not going to break this chunk down quite yet. I did it at very odd hours and it works but it’s not very clean and I know there are ways to do it better.There is no shame in my game but there is some bashfulness. Pretend I’m an […]

Trump Dump Data & Playing with TAGS

Image from page 60 of “Children’s ballads from history and folklore” (1886) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) Driven mad by curiousity after this Matthew Baldwin tweet, I built this little thing. It uses the amazing Martin Hawksey’s TAGS for gathering the Tweets in Google Sheets and then displays it with Vue.js (which I’m sort of learning). It led me to realize that I could extend TAGS without much effort. My first attempt was to write two custom functions to get favorites and retweets. Turns out that was pretty straight forward given all Martin’s work. The TAGS element (TAGS.get) links me into Martin’s library and that’s that. So very easy once you know and then you’re just navigating the Twitter API. Turns out I can do something similar to get the Twitter bio. Initially, I just stuck these in like you’d do normal functions … =getRT(A1) or whatever and it soon ate up all the processing time allowed for my Google Scripts. That caused other things to break. Lesson learned. I then opted to set the value in the script and write that value to the cell. This little script runs on the spreadsheet change trigger and checks to see if there are any blank cells in the Favorites column (column U) […]

Google Script Folder Copying Postmortem

Image from page 93 of “Elementary and dental radiography” (1813) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) This is the blow-by-blow documentation of a failure of sorts. Nothing makes me angrier than failing to deliver on something I said I could/would do. I ended up delivering what was needed but the way I had to do it was ugly and time-intensive. All of this happened because of two things- insufficient initial testing and not enough knowledge on my end at a couple of key steps. I’ve got lots of ideas I need to test out now that I’ve learned a few things the hard way but I thought it’d be beneficial to see how I tried to think through this thing when various paths failed . . . and with that welcome to my postmortem. I thought we had the Social Work Google Docs Digital Portfolio thing figured out. I even wrote a blog post. Events coalesced to remind me that I do not know enough stuff. However, I do intend to learn from painful mistakes like this and give you my tears and frustration for you free of charge.1 Initial tests felt a bit slow but nothing too bad. Google Scripts will timeout after 6 minutes but I thought we’d be ok […]

WordPress – Comments to Spreadsheet

Image from page 981 of “A system of instruction in X-ray methods and medical uses of light, hot-air, vibration and high-frequency currents : a pictorial system of teaching by clinical instruction plates with explanatory text : a series of photographic cli flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) This is a bit odd but if you work in education it’s quite possible it’ll come up . . . Sometimes people just want the blog comments in a nice friendly CSV file. That might be for grading, it might be for further analysis in Voyant or something like that. What I need to do is write a little plugin for the export like Alan did for posts but until I get around to that I took advantage of another plugin Alan wrote to expand comments to the 100 most recent comments. So since I have lots of comments available in the feed, I can just use the =IMPORTXML function to port right into Google Sheets. There’s probably a smart way to parse out multiple XML fields at a time but this seems to work ok. All the functions I used are broken down in the sheet embedded below. Most are simply some version of IMPORTXML(b1,”//title”). B1 is the URL for the feed and the […]

Social Work Digital Portfolio – Google Docs Style

Image from page 589 of “Modern magic. : A practical treatise on the art of conjuring.” (1885) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons) Our Social Work program has traditionally done large paper-based student portfolios. They wanted to move to something digital. That led to some conversations about Google Docs and our options there. They needed the ability to- provision a set of folders and documents to individual students allow the students to edit/add to the folders stop editing rights at a certain date make the student folder anonymous for faculty reviewers The solution I ended up coming up with uses a Google Spreadsheet with some custom Google Script. It’s based on a spreadsheet with column A being the student email and column B being the anonymous number or name. You make a parent folder (Social Work 2017) and put the spreadsheet and the folder (student portfolio) you want to copy in the folder. You add the student emails and secret IDs to the spreadsheet. The script is activated via custom-menu element imaginatively entitled ‘Share Files’ and it copies the student portfolio for each student email listed, names it with the addition of the secret ID, and gives the student editor rights to their particular folder and its contents. It also writes the […]