Cobbling Together a Searchable Twitter Friends/Followers Contact List in Google Spreadsheets

Have you ever found yourself in the situation where you want to send someone a Twitter message but you can’t remember their Twitter username although you do know their real name? Or where you can remember their twitter username or their real name, but you do remember who they work for, or some other biographical fact about them that might appear in their Twitter biography? If that sounds familiar, here’s a trick that may help…

… a searchable Twitter friends and followers contact list in Google Spreadsheets.

It’s based on Martin Hawksey’s rather wonderful Export Twitter Followers and Friends using a Google Spreadsheet (I have to admit – Martin has left me way behind now when it comes to tinkering with Google Apps Script…!) To get started, you’ll need a Google docs account, and then have to indulge in a quick secret handshake between Google docs and Twitter, but Martin’s instruction sheet is a joy to follow:-) Follow the *** Google Spreadsheet to Export Twitter Friends and Followers *** link on Martin’s page, then come back here once you’ve archived your Twitter friends and/or followers…

..done that? Here’s how to make the contact list searchable… I thought it should have been trivial, but it turned out to be quite involved!

The first thing I did was create a drop down list to let the user select Friends or Followers as the target of the search. (Martin’s application loads friends and followers into different sheets.)

The next step was to generate a query. To search for a particular term on a specified sheet we can use a QUERY formula that takes the following form:

=query(Friends!B:E,”select B,C,D,E where D contains ‘JISC'”)

Friends! specifies the sheet we want to search over; B:E says we want to pull columns B, C, D and E from the Friends sheet into the current sheet; the select statement will display results over four columns (B, C, D and E) from Friends for rows where the entry in column D contains the search term JISC.

To pull in the search term from cell D1 we can use a query of the form:

=query(Friends!B:E,concatenate(“select B,C,D,E where D contains ‘”,D1,”‘”))

The =concatenate formula constructs the search query. Make sure you use the right sort of quotes when constructing the string – Google Spreadsheets seems to prefer the use of double quotes wherever possible!

To search over two columns, (for example, the real name and the description columns of the twitter friends/follower data) we can use a query of the form:

=query(Followers!B:E,concatenate(“select B,C,D,E where C contains ‘”,D1,”‘ or D contains ‘”,D1,”‘”)

Again – watch out for the quotes – the result we want from the concatenation is something like:

=query(Followers!B:E,concatenate(“select B,C,D,E where C contains ‘Jisc’ or D contains ‘Jisc’)

so we have to explicitly code in the single quote in the concatenation formula.

Unfortunately, the query formula is case sensitive, which can cause the search to fail because we haven’t taken (mis)use of case into account in our search term. This means we need to go defensive in the query formulation – in the following example, I force everything to upper case – search corpus as well as search terms:

=query(Followers!B:E,concatenate(“select B,C,D,E where upper(C) contains upper(‘”,D1,”‘) or upper(D) contains upper(‘”,D1,”‘)”)

The final step is to define the sheet we want to search – Friends! or Followers! – depending on the setting of cell B1 in our search sheet. I had idly though I could use a concatenate formula to create this, but concatenate returns a string and we need to define a range. In the end, the workaround I adopted was an if statement, that chooses a query with an appropriate range set explicitly/hardwired within the formula depending on whether we are are searching Friends or Followers. Here’s the complete formula, which i put into cell E1.

=if(B1=”Friends”,query(Friends!B:E,concatenate(“select B,C,D,E where upper(C) contains upper(‘”,D1,”‘) or upper(D) contains upper(‘”,D1,”‘)”)),query(Followers!B:E,concatenate(“select B,C,D,E where upper(C) contains upper(‘”,D1,”‘) or upper(D) contains upper(‘”,D1,”‘)”)))

I now have a query sheet defined that allows me to search over my friends or followers, as required, according to their real name or a search term that appears in their biography description.