In responding to some data requests, I delved into the WP tables to pull some rampages data. All users ever . . . I need to set up something more automated but for this I dumped the MySQL tables as CSVs and then just imported them to Google Sheets. With very minimal functions, I got this data. My favorite function combos are to pull the unique values from a range =unique and then do a =countif off those values. It’s a really quick way to get graphs like these.
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 […]
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 […]
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 […]
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 […]
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.
Sometimes the options given in Google Forms just won’t quite work for what you want to do. Maybe you want a particular look, or an interaction, or whatever that Google Forms just won’t do. Luckily, it’s not too hard to make a custom form that can do whatever you want and still has the ability to write the submitted data to a Google Spreadsheet and the form HTML is still served by Google. The following steps should get you up and running and comments in the scripts should provide additional details. Make a new spreadsheet in Google Sheets. Go to Tools>Script Editor Select all that stuff and replace it with the content below. Replace the string of ****** with the ID of your spreadsheet. Then save it. If you get any permissions prompts approve them. Make a new HTML page (File>New>HTML File) and name it index.html Select all and replace it with this.1 Save it. To make sure things work, let’s publish it (Publish>Deploy as Web App). Now go to that URL and submit something and see if it goes to the spreadsheet. If so, great. Now you can start customizing the form to reflect your needs. This form should now write to a spreadsheet like this. Do keep in mind that each form field you want to write to […]
I’ve been doing my Google JSON display using Angular but I wanted to see what I could do with jQuery. This is based on the post here by Amit (to whom I am grateful for all the great stuff he puts out) with minor updates due to changes in how Google does things. Do make sure you’ve published your sheet at HTML and note that the 1 in the URL structure is the first page if you have multiple sheets.
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.