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.

Google Sheets Love – REGEXEXTRACT & IMPORTFEED

flickr photo shared by CraigMoulding under a Creative Commons ( BY-SA ) license Just two little functions in Google Sheets that came up as we tried to quickly pull comments from a blog for some other work. It was an odd scenario but the techniques should have other useful applications. IMPORTFEED The function below pulls my 10 most recent comments into a spreadsheet. You can set it to pull more but will also need to change the number of items in your RSS feed. You can find that setting under Settings>Reading. It’ll work on any RSS feed and there are a variety of other IMPORT functions that are worth checking out. This could be a useful option if you wanted to analyze the comments in one place or if you wanted to look a bit more deeply at comments on sites you don’t control. 1 1 I had to switch this to code because copy/paste led to issues with quotes messing up the formula.

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.