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

13

Documentation – Google Doc Drawing Tip

I’ve created documentation in lots of platforms with lots of people over the last 15 years. These attempts tend to fail, or fail to thrive, for a variety of reasons. I can’t address all of the human factors but I can look harder at a few of the mechanical ones. Here are a few of the questions I ask myself. Who is supposed to be contributing to the documentation? What tools do they use now? What is the fewest number of tools we can use? What enables the most people the easiest path to creating/editing? Based on those considerations, my latest attempt is to use Google Docs. It’s a super common tool that our entire team is familiar with. We can easily make it available online to anyone we want. It’s also a single tool that will all of our basic documentation needs (video is another matter). The place Google Docs doesn’t do well is in creating a public-facing static index or search box for all the content. I’m looking into API options around that at the moment. I a, doing at least one thing that I believe might make a difference. For instance, all of the images are inserted as drawings. That’s a little thing but one that eliminates a tool and allows for flexibility down the road. Ordinarily, […]

10

Weekly Web Harvest (weekly)

A Map Of All The Divorces – On The Media “Is this someone who thinks that marriages are ruined because of environmental factors, and wants to avoid those environments? Or someone who really wants to date a divorcee, and is looking for the greatest concentration of them? Or is it someone who wants to know the best place to launch their divorce-themed business (shared-custody calendars, studio apartment rentals)? Also, even if this person exists, anywhere, this data would be useless to them. Patch’s divorce map shows divorces as a raw number, rather than divorces-per-capita.” tags: data journalism weekly mapping divorce My Heart Feeds a Series of Tubes – an ode to Yahoo Pipes tags: data pipes yahoo weekly tweet Posted from Diigo. The rest of my favorite links are here.

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

08

Copying Google Forms/Sheets Between Users

When working with Google Forms, there are two elements- the sheet (where the responses go) and the form. It’s not obvious how you might allow another user to copy both a form and a sheet in Google. So . . . assuming you set the document so that anyone can see it (or share it directly with whomever) . . . You can append copy to the form URL and it’ll copy the blank spreadsheet and the form. https://docs.google.com/forms/d/1pf0xf1G_-C-p9QXjw1L5BjvZC1SHTGd71hvGBjuFGN4/copy If you append copy to the sheet URL, it’ll copy the spreadsheet with content and the form. https://docs.google.com/spreadsheets/d/1Lrq5sbr-Q_8TFky81OSMFgTO1OnQ8bbN1G05m0q3dVQ/copy

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

Fridge Poetry – Google Sheets as Database

Back in 2013 I built a refrigerator poetry page using javascript. I really wanted to make it so anyone could add any words they wanted in some easy way but didn’t have the skills to do that at the time. It’s been hanging out in the back of my mind since then and the bits and pieces I’ve learned since then now make it pretty straightforward. This url will prompt you to copy a Google Sheet. That’s a nice little trick- just append /copy to the public sheet where it normally says /edit?usp=sharing if you copy the typical sharing URL. Sadly, it only works for Sheets. Once we’ve published the sheet, we now have a JSON feed of the data and the little URL trick in the Google Sheets gives you the URL to the fridge poetry page with Sheet ID included as a parameter. (That’s the chunk after the question mark.) http://bionicteaching.com/fridgepoetry/google_words.php?id=1KExHjArU6ZAR2l2r00XYWHAAUen-Z6WOQGchHWmPs-4 The PHP to make all the words into divs is below. I usually do something like feed->entry->$t to parse out the JSON but that didn’t work with the Google Sheet data.1 I still don’t quite get things well enough to know why but this alternate path works well. Part of the difference there is using the TRUE parameter on the json_decode. The other new element for me […]

03

Weekly Web Harvest (weekly)

Podcast: the only way to get evidence-based policy is to embrace ambiguity in science / Boing Boing “The reality is that, in the world of science and research, the “truth” is messy, and evolving. There’s this moment in an interview that every researcher dreads: “Yes, Dr Knowitall, but are you certain?” But researchers say to me: “The problem is Tracey, people with no scientific discipline can say what they like. They can say we need more prisons to reduce crime, the HPV vaccine is causing chronic fatigue or the climate has warmed by more or less than it has. And in response what do we have? Caveats, probabilities and error bars!” So we keep the messy bits to ourselves. We flatten out uncertainties because we’re afraid that scientific uncertainty will be used against us. Co-opted by extremists. Used as fodder for headlines. Or we stay silent while others do. Because in reality researchers work always with uncertainty. It’s the nature of the beast.” tags: weekly truth lies reality Beware of easy work — Medium tags: weekly philosophy art tweet Peeple | Character is Destiny “Looking at everyone in the three ways you could possibly know someone – personally, professionally and romantically – you can provide a rating and review on everyone you come in contact with, while allowing yourself to […]