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

Making Google Scripts & Google Sidebars Talk

flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons) Sometimes you want a Google Sidebar element to be able to pull variables from a Google Script. The key element is google.script.run.withSuccessHandler. I’m still not entirely sure I have this in my head deeply/correctly but this is functional and might help someone else (even if that someone is just me at a later point in time when I’ve forgotten this). This one sets some default times based on spreadsheet variables stashed elsewhere and then automates some date/time entries using the form sidebar. Sidebar Code Google Script Code The entire sidebar/script setup is below in case anyone cares for some reason. Pick Your Date Start/End Times Start: End: Hide Previous Dates Submit

Interactive Google Sheets Dashboards

It’s pretty easy to put a bunch of data and charts in a spreadsheet and call it a dashboard. It became a more interesting challenge to make those charts change to reflect variables chosen via dropdown cell menus. The key it turns out is using =query. I can do some really powerful things with query and as long at the data bounds (columns/rows) are the same, I can change the content and it’ll replicate in the chart assigned to those columns/rows. For instance, I can have raw data on a sheet called data. I can use query on that data on another sheet with something like =query(data!A1:N,”select B,C,D,E,F,G,H,I,J,K,L,M,N where A=”&”‘”&A2&”‘”) That formula is going to the sheet named data and querying cells A1 through N(whatever the last row is) and selecting columns B through N where the content of cell A matches the text of cell A2 on the local page. As a result we can manipulate the contents of cell A2 and change the data being returned. In this case I did a little data validation drop down to restrict it to the three items for which we have data. Do take note that for numbers I could have just appended the cell reference (&A2 in this case) but because it was a text match I had to staple […]

Spreadsheet Karma

=QUERY({C:C,C:C},”select Col1, count(Col2) where Col1 ” group by Col1″,1) This is just so handy for getting all the unique values from a column and spitting them out with the counts of their occurrences.1 I can’t tell you how many times I’ve done this a much harder way. I saw the function above as an answer after I’d responded to a Stack Overflow question about getting unique word counts. Just another little example of how doing things in the open and being involved in communities ends up benefitting you in unexpected ways. flickr photo shared by New York Public Library with no copyright restriction (Flickr Commons) 1 If you data isn’t in column C, just change it to the right letter.