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.

=IMPORTFEED("http://bionicteaching.com/comments/feed",,TRUE,10)[code]

<iframe src="https://docs.google.com/spreadsheets/d/1gFqlqdZLcUo6OsaAzKVvKKObsU5chjAhJzDhDBsRlgE/pubhtml?gid=1536296137&amp;single=true&amp;widget=true&amp;headers=false" width="100%" height="300px"></iframe>

<h2>REGEXEXTRACT</h2>
We were trying to pull comments with email addresses and some other data so I ended up cutting/pasting the comments table view in the WP dashboard into Google Sheets. That got what we wanted but the email address ended up in one cell jumble in with some other stuff. The following function grabs the email address. I found it on <a href="http://www.regular-expressions.info/email.html">this site</a> and added a bit to deal with people who used upper case letters in their emails.

[code]=REGEXEXTRACT(A1,"\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}\b")

¹