University of Glasgow

Roderic Page

Playing with the my [\"material
examined\"](http://iphylo.blogspot.com/2015/04/linking-specimen-codes-to-gbif.html)
tool I\'ve been working on, I wondered whether I could make use of it
in, say, a spreadsheet. Imagine that I have a spreadsheet of museum
codes and want to look those up in GBIF. I could create a service for
[Open
Refine](http://iphylo.blogspot.com/2012/02/using-google-refine-and-taxonomic.html)
but Open Refine is a bit big and clunky, you have to fire up a Java
application and point your browser at it, and Open Refine isn\'t as
intuitive or as flexible as a spreadsheet.

It turns that Google Spreadsheets supports custom functions, including
importing JSDON from a remote data source. Following [How to import JSON
data into Google Spreadsheets in less than 5
minutes](https://medium.com/@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minutes-a3fede1a014a)
here\'s what to do:

1.  Create a new Google Spreadsheet.
2.  Click on Tools -\> Script Editor.
3.  Click Create script for Spreadsheet.
4.  Delete the placeholder content and paste the code from [this
    script](https://gist.github.com/paulgambill/cacd19da95a1421d3164).
5.  Rename the script to ImportJSON.gs and click the save button.
6.  Back in the spreadsheet, in a cell, you can type "=ImportJSON()" and
    begin filling out it's parameters.

Lets imagine we have a spreadsheet with a specimen code in cell A1, e.g.
\"FMNH 187122\".

![S1](http://lh3.googleusercontent.com/-klTT9F6dAyw/VTZVRI7S8pI/AAAAAAAADts/5jWmnu9F2rg/s1.png?imgmax=800 "s1.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="298" height="104"}

To call the material examined service, we need a function like this:

::: {style="font-family:monospace"}
=ImportJSON(CONCATENATE(\"http://bionames.org/\~rpage/material-examined/service/api.php?code=\",A1,\"&match&extend=10\"),
\"/hits/key,/hits/scientificName\", \"noHeaders\")
:::

Paste this into cell B1 (i.e., just to the right of the specimen code)
and after a short delay you should see something like this:

![S2](http://lh3.googleusercontent.com/-lufqHMuGdKc/VTZVSYHOfwI/AAAAAAAADt0/jQwfhWajyS0/s2.png?imgmax=800 "s2.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="299" height="104"}

The three parameters supplied to ImportJSON are are the query URL,
written as a spreadsheet function that grabs the specimen code from cell
A1, a list of the bits of data we want to extract from the result
(expressed as JSON paths), and some options (in this case, don\'t show
the headers). ImportJSON will grab the specimen code in cell A1, add it
to the query URL, then output the results. You should see something like
this:

The first column is the GBIF occurrence ID, the second is the scientific
name (you can add more JSON paths to get more fields).

Note that we have multiple rows as there is more than one specimen with
the code \"FMNH 187122\" in GBIF. Now, we can ask the material examined
service to return only certain taxa (such as mammals) by adding the
\"scientificName\" parameter:

::: {style="font-family:monospace"}
=ImportJSON(CONCATENATE(\"http://bionames.org/\~rpage/material-examined/service/api.php?code=\",A10,\"&scientificName=\",B10,\"&match&extend=10\"),
\"/hits/key,/hits/scientificName\", \"noHeaders\")
:::

If you put the specimen code in cell A10, and the higher taxon
\"Mammalia\" in cell B10, and paste the function above into cell C10,
then you should see something like this:

![S3](http://lh3.googleusercontent.com/-8bvNHaThxHE/VTZVTe38ZJI/AAAAAAAADt8/E0yZLM0SkuU/s3.png?imgmax=800 "s3.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="300" height="177"}

Note that now we have a single row with the mammal specimen.

It\'s a little bit fussy (you need to get the ImportJSON script, and
mess a bit with the parameters but it\'s quick and flexible, and you get
all the power of a spreadsheet to help clean the data before trying to
match it to GBIF. Plus you can do it all in your browser.


Playing with the my "material examined" tool I've been working on, I wondered whether I could make use of it in, say, a spreadsheet. Imagine that I have a spreadsheet of museum codes and want to look those up in GBIF. I could create a service for Open Refine but Open Refine is a bit big and clunky, you have to fire up a Java application and point your browser at it, and Open Refine isn't as intuitive or as flexible as a spreadsheet.

Looking up specimen codes in GBIF using Google Spreadsheet

![Ifttt](http://lh4.ggpht.com/-gBMY1HNc49M/UeOxImS--aI/AAAAAAAAC7o/vvVngSFw8qY/ifttt.png?imgmax=800 "ifttt.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="460" height="144"}Since 2009 I\'ve been running a
service that takes posts to the EvolDir mailing list and sends them to a
Twitter stream at [@EvolDir](http://twitter.com/evoldir). This service
was running on a [local
machine](http://iphylo.blogspot.com/2009/01/darwin-on-twitter.html)
which has died. Rather than wait until I rebuild that server (again), I
looked around for other ways to recreate this service. I decided to use
[IFTTT](http://ifttt.com) (\"if this then that\"), which is a wonderful
way to chain together web services.  
  
IFTTT uses \"recipes\" to chain together two services (if something
happens here, do this). For example, given a RSS feed of recent messages
to EvolDir, I can create a recipe to send the links to those posts to
Twitter:  
![Rsstwitter](http://lh3.ggpht.com/-r0tCxy8PvkA/UeOxJiEFsuI/AAAAAAAAC7w/8tbPDo07F9c/rsstwitter.png?imgmax=800 "rsstwitter.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="460" height="110"}  
Great, only EvolDir is an old-fashioned mailing list distributed by
email only. There is no web site for the list that has URLs to each
post, and no RSS feed. IFTTT to the rescue again. I created a recipe
that reads emails for a GMail account that is subscribed to EvolDir, and
each time it gets an email from EvolDir it sends the contents of the
email to a [blog on Tumblr](http://evoldir.tumblr.com/):  
![Mail2tumblr](http://lh5.ggpht.com/-RFGXVT7DvZA/UeOxKRWMnxI/AAAAAAAAC74/Imf0JQcMFks/mail2tumblr.png?imgmax=800 "mail2tumblr.png"){style="display:block; margin-left:auto; margin-right:auto;"
border="0" width="460" height="91"}  
Now each post has a web presence, and Tumblr generates a RSS feed for
the blog, so the first recipe can take that feed and every time the feed
is updated it will send that post to Twitter. Simples.


Since 2009 I've been running a service that takes posts to the EvolDir mailing list and sends them to a Twitter stream at @EvolDir. This service was running on a local machine which has died. Rather than wait until I rebuild that server (again), I looked around for other ways to recreate this service. I decided to use IFTTT ("if this then that"), which is a wonderful way to chain together web services.

Messaggi di Rogue Scholar

Looking up specimen codes in GBIF using Google Spreadsheet

Using @IFTTT to create a Twitter stream for @EvolDir