editor screenshot

SPLOT-light?

So the other day I posted about how to make silent Google Form submissions. Then this morning I was looking around at headless CMS options1 and saw this one being advertised as being driven by Google Drive/Spreadsheets. Those two things came together as I mowed my lawn and I wondered if I couldn’t make a little rich text editor to construct a one-piece content creator/displayer using Google Sheets. That led to a little research into URL parameter limits. And then this evening I made this editor. The page uses Quill to take care of the rich text editor. It turns out there’s a whole world of rich text editor options out there. I’m only scratching the surface with Quill but it works fine for now. It’s bare bones. You can associate an image via a URL, make a title, and add some text. It does show some interesting possibilities though and all with very little infrastructure or real technical know how. The image preview is built by this little bit of javascript. It’s based on having a text field with the id of ‘theImage’ and then there’s a check to make sure there’s not already an image attached and if there is it replaces it. This little bit of javascript builds the Google Forms URL from the various text fields […]

Spreadsheet Karma

=QUERY({C:C,C:C},”select Col1, count(Col2) where Col1 ” group by Col1″,1) This is just so handy for getting all the unique values from a column and spitting them out with the counts of their occurrences.1 I can’t tell you how many times I’ve done this a much harder way. I saw the function above as an answer after I’d responded to a Stack Overflow question about getting unique word counts. Just another little example of how doing things in the open and being involved in communities ends up benefitting you in unexpected ways. flickr photo shared by New York Public Library with no copyright restriction (Flickr Commons) 1 If you data isn’t in column C, just change it to the right letter.

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

Another Google Sheets Tip – Find Non-Standard Characters

flickr photo shared by Lynn Friedman under a Creative Commons ( BY-NC-ND ) license We’re still messing with manipulating data in Google Sheets. I hate to think of humans going through and manually parsing things that the machine should do so I’m putting all this out in the hopes of saving someone somewhere some time. It’s also meant to help our internal people see how it works so it might be more broadly applied. In this case, we’re just looking for a line break and using that to cut out a chunk of text. This Stack Overflow post gave me the char(10) piece so I could find the line break. You can see the details above and more on unicode characters here. It’s broken in two pieces for clarity. Piece one finds the number of characters prior to the line break. The second piece takes that number of letters from the left. I find manipulating text in spreadsheets to be a constant and practical use of algebra.

30

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.

Scraping Wikipedia User Data w Google Spreadsheets

creative commons licensed ( BY-SA ) flickr photo shared by nojhan Alice Campbell in the VCU library hosted a Wikipedia edit-a-thon today. It was interesting and we had a variety of faculty and even some students show up. Gardner joked at one point whether we had a leader board for edits. It got me thinking. I remembered that Wikipedia keeps track of the edits of logged in users and I figured I’d take a shot at scraping some of that data so we’d have a rough idea of how many edits were made by our group. I started off by looking at the contributions page. This URL will get you the page for my user name. https://en.wikipedia.org/wiki/Special:Contributions/Woodwardtw I used the IMPORTHTML formula in Google Spreadsheets.1 It was easy because this was the first list on the page. You can see in the image above that you have the choice between trying to grab a list or a table. The other variable is what number that element is from the top of the page. You can see the working document embedded below. I considered parsing out2 the ..(+30)..3 but after talking to Alice that wasn’t the kind of data that would travel well. She was more interested in number of edits which, as it turns out, is available on the Edit […]