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.
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 […]
Because I love Alan. Here’s the API version in Google Script to grab YouTube stats. It does a bit more than the previous XPath version and you can set it to be triggered repeatedly. I’m going to add a loop to add multiple videos etc. in the near future but it’s a good start for anyone who’s doing research on stuff like this. It is funny what you might notice when you can see the data like this. I triggered it manually twice just to get a few lines in there. Notice that between the first two entries there are no additional views but a chunk more likes/dislikes. Makes me wonder if people are just weighing in without watching or if the data are collected differently resulting in some delay. Here’s the script1 and it’s pretty well commented up. You’ll need an API key. 🙂 You do see some weird stuff in the raw JSON. Like there’s a Favorites field. Does that exist in YouTube? I didn’t really think about it until it came up 0 for every video . . . even Gangnam Style. Here’s the result running every hour on a video that I’m hoping changes a bit. I got it off the trending page so it has to be cool right? 1 It took me a good […]
APIs can give you much more power but they are often overkill for what people are trying to around here- lightweight social media Here’s a lightweight example of how you can use Google Sheets and the IMPORTXML function to grab quite a bit of data from various video pages with no API or technical skills. Straight off, we’re going to want the URL of the video. We’ll put that in column A and we’ll use it as a variable in all our other formulas. Getting the Paths to the Data =IMPORTXML(A2,”(//*[contains(@class, ‘watch-title’)])”) So how’d that come to be? A2 is just asking what URL we want to go to. The XPATH stuff gets a little more interesting. It’s going to look for any class that is named watch-title. I found out the title was in that div by right clicking on the title and choosing inspect in Chrome. The appended  will only give us the first item that meets those qualifications. Otherwise the title shows up twice. The rest of the formulas are pretty much variations on that theme. =IMPORTXML(A2,”//*[contains(@class, ‘watch-view-count’)]”) – View count =IMPORTXML(A2,”(//*[contains(@class, ‘like-button-renderer-like-button’)])”) – Likes count =IMPORTXML(A2,”(//*[contains(@class, ‘like-button-renderer-dislike-button’)])”) – Dislikes count =IMPORTXML(A2,”(//*[contains(@class, ‘yt-user-info’)])”) – User name Throw in a video of your own if you’d like.
I’m messing around with some workflows to figure out how I’m spending some of my time . . . and, as a result, spending some of my time on how I spend my time. One of those efforts is trying to get some decent numbers around how my own efforts towards rampages support are going. I have my gut feelings but figured I’d see if I could work out a low energy way to document some of it in a more specific manner. I’m currently playing with a simple IFTTT recipe. It takes an email to a specific address with a specific hashtag (#vcusupport in this case) and puts it into a spreadsheet. I modify the subject line so that it’s #vcusupport HELP ME! | faculty | aggregation |. The regex1 below pulls out the elements and puts them in separate columns. Giving me something like the data below. Grabs content between two characters (in this case my “pipes”). =REGEXEXTRACT(B:B,”\|([^()]+?)[\|].*?(([^()]+?)[\|])”)) I started to do some work with Zapier as well. I actually prefer it in a number of ways (much more granular data and other neat things to pull from email) but the price was way too high for this type of use. I might try a more direct API route within Google if I end up feeling the need […]
via the magical XKCD Fun being a fairly relative term . . . but I’m amused. The Meat You can write custom functions in Google Spreadsheets and then use them like other built in functions. I didn’t realize that. The script below1 grabs all the URLs from a chunk of text. You could add it to your spreadsheet in Google by going to Tools>Script Editor and opening a blank project. Replace all of the content with this and then save it. You can now use it like other functions by putting =findULR(A1) (assuming A1 is the text chunk you want). It spits out a single cell with the URLs in it on individual lines and with a count of the URLs found at the top of that cell. I’ve commented up the script below in case you want to understand/change it to better suit your needs. Next Steps The regex works well about 90-95% of the time. So it sure beats doing it by hand but it could be improved. You can try your own stuff here. The post I’m using there is pretty messy so it’s a good thing to check against. It’s hard to guess what odd things people will do with URLs. I’ve already seen people doing stuff like “”http://blah.com””. No idea why they did double quotes. […]