Custom Web Form to Google Sheets

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

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.

Google Script to Copy Row Above to Blank Row Below

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.

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

18

YouTube API to Google Spreadsheets

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

17

YouTube Scraping, XPath, and Google Sheets

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’)])[1]”) 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 [1] 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’)])[1]”) – Likes count =IMPORTXML(A2,”(//*[contains(@class, ‘like-button-renderer-dislike-button’)])[1]”) – Dislikes count =IMPORTXML(A2,”(//*[contains(@class, ‘yt-user-info’)])[1]”) – User name Throw in a video of your own if you’d like.

Random Stuff I Learned This Week

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

findURL – Custom Google Function Fun

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

Embed Collector Tool

Some people don’t have websites handy or their blogs won’t allow iframe embeds. They should still be able to have their students do stuff and get it all in one place. This is the Internet! And this tool is meant to deal with that. You can see a working example with three submissions (via a Google Form) here. It’ll take any iframe or HTML as a submission. The search box will also filter based on all the additional material associated with the element in the form. For instance in our example you can type “Sarah” and you’ll end up with one item. So the way this should work is you click here and accept the copy. You now have the spreadsheet and form. The directions are on the sheet labeled directions. I have a dramatic green arrow pointing at it in the image below. You can edit the form associated with the sheet under Form>Edit form. The only thing you have to leave/have in the form is the one called “The embed code.” Everything else can be deleted and/or changed. Stuff I Learned This pretty much a mashup of two recent projects – the personalized fridge poetry and the Angular/Google Sheets JSON stuff. This one runs entirely in javascript so to use the $_GET option, which javascript doesn’t have, I […]

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