Display Google Sheets JSON with JQUERY

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.

Grabbing JSON

flickr photo shared by Library Company of Philadelphia with no copyright restriction (Flickr Commons) What I wanted to do was grab data from the WordPress API and use that to provision chunks of my new portfolio site. The portfolio is hosted on GitHub and GitHub is HTTPS. At the moment my bionicteaching site is not HTTPS.1 That causes problems as secure and insecure are not friends. I wanted a quick and easy solution so I could continue until I do the HTTPS switch. The following is how I wandered towards a solution. A number of the things worked but don’t quite work for what I wanted. So they’re worth remembering/documenting for later and it’s kind of fun to see a mix of javascript, php, url manipulation, Google API, and the WordPress V2 API all in one little bit of wandering. My first thought was to grab the JSON via a Google Script and store it in Google Drive. I can do that but can’t seem to make it available for use the way I want. I tried messing with various URL parameters but it wasn’t flowing and I only started there because I thought it would be easy. I did eventually get the file accessible in DropBox (the only other place I could think of immediately for https file storage) […]

Discography to WordPress

flickr photo shared by Thomas Hawk under a Creative Commons ( BY-NC ) license This is in response to something Adam Croom wrote two(?) days ago. I thought it’d be an interesting proof of concept and would let me figure out some things with a purpose. I also like to have a few projects going on at once so I have things to switch between when I get frustrated. I also see this kind of information pushing/pulling as broadly applicable. Some of this stuff is no doubt uglier than it had to be but I’ll try to show some intersections that happened to occur with other projects and how certain steps might be ignored entirely if you want to be all efficient and stuff. The final plugin is here and should be a decent start to any customized import you want to run against a CSV file. Adam had information in Discogs. He wanted that information in WordPress where he could control it. I had never heard of the site, let alone seen its API. But it well documented and it took me a few minutes to realize I could get all the data I needed without even needing to authenticate. The user data was associated with collections and appending 0 would get me the root level stuff. With Adam’s […]

Insta Snoop

A while back I was messing with getting Instagram data without bothering with their API because I think their most recent API changes are really annoying. I’m also a bit fascinated with the scale of numbers in social media right now. I opted to look at Snoop Dogg’s Instagram followers and plot their change very 10 minutes. Click here or on the image to see the live chart. Get the Instagram Data w/o the API & Put it in the Database It turns out that each Instagram page has an embedded JSON file with the data I wanted. You can see it if you view the source of any page. This Stackoverflow post was kind enough to point it out and you see regex rearing it’s head again. I started out with my standard process of using Google Sheets as the database but decided I’d try MySQL because I wanted to try getting the JSON ought more cleanly. The chunk below grabs the data and puts it in the database. So that gets us the stuff we want in a nice little box on the Internet. I did try to do some fancy mysql stuff to avoid entering the change in followers as an additional field but I failed in enough ways that I just opted to proceed with the […]

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

YouTube View Count to Tweet

I was just messing around a bit more with mashing up the YouTube API post from earlier with the Twitter post from earlier today. This Google Script will get the view count of a particular video (Gangnam Style) and send out the total views and the time elapsed watching the video (assuming I did the math right). It has no real purpose (other than the scale of the number amuses me) but it’s one of those things with concepts that I’ll be able to apply more usefully in the future. That’s a gut feeling but I’m pretty confident. Don’t forget to add the Twitter library as directed here.1 1 in the Apps Script project, include the library MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_ with the identifier set to Twitter

Server Up? Notifications via Twitter/Google Script

flickr photo shared by Thomas Hawk under a Creative Commons ( BY-NC ) license Reclaim Hosting has a great status page that alerts you when stuff is not working correctly. We had a brief outage this morning and I just thought it’d be nice to see if we could proactively send out a message based on that status page but only for things relevant to our server. I asked Tim (as you can see in the tweets below) and ended up with an API that happily spit out a JSON feed. @ReclaimHosting ha ha – LOVE IT https://t.co/UrlIvwa91S — Tom Woodward (@twoodwar) February 24, 2016 Based on my recent experiments with JSON and Google Scripts I didn’t think it’d be too hard to write something to send out Tweets based on that feed. I found this snippet that dealt with the authorization portion for Twitter.1 Once I had the JSON parsed and assigned to variables, all I had to do was set it to check every 5 minutes. Now we can hook it up to the ALTLab account if we’d like, have it notify various people, etc. etc. Fun stuff . . . all done during lunch. The following script will check a JSON feed and send a Tweet out from my account letting @vcualtlab know we should check on […]

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


Google Spreadsheet Unique Item Count

Google sheets can do some neat tricks. Here’s how you can generate a chart of unique words submitted and their counts. The form for collecting a single word. Loading… The formula to paste in column C. All credit to this Stack Overflow post. =QUERY({B:B,B:B},”select Col1, count(Col2) where Col1 != ” group by Col1 label count(Col2) ‘Count’”,1) The embedded chart. In messing around with options around this, I was also looking at getting JSON data out of Google sheets. https://spreadsheets.google.com/feeds/list/1gwiU0j-o50m1L5yEU7OR-v9GSuxNyQeD5_zosjBpYvE/1/public/values?alt=json To break that down https://spreadsheets.google.com/feeds/list/YOUR_SHEETS_UNIQUE_ID/THE_NO_1_FOR_THE_FIRST_SHEET/public/values?alt=json It works with Benson as seen over here which is pretty neat. Although I haven’t gotten search working.

results of Flickr api group request

Flickr API Basics – Pulling a Group Feed

Someone at work mentioned that the Flickr WordPress widget didn’t work with group photos. We use this group as part of our (sort of just getting started) Friday photo walks. Since I’m trying to learn stuff I figured I’d play a bit with the Flickr API and see how that worked. After getting an API key, my first stop was at URLs. I thought that’d mean endpoints like on Instagram but it was the URL structures for photos. Useful and needed but not what I thought it’d be. I then saw in the right hand sidebar groups and browse. That turned out to be a list of groups. I ended up finding what I wanted at groups.pools get photos. I planned to get the information in json if I could given I’d just spent some time figuring out how that worked so I went back to the main index thinking the json response format would show me how to ask for json data given the variables I’d seen as options in groups.pools.getphotos. It didn’t. This section shows the structure of the json response. Which makes sense it just wasn’t what I was looking for. I found what I was looking for under request.formats which makes sense. This shows the URL structure. https://api.flickr.com/services/rest/?method=flickr.test.echo&name=value is the basic URL. Method is the thing […]