Do Incredible Things With Clay Functions & Google Spreadsheets

Google spreadsheets come with a large number of built-in functions. But what if your spreadsheet could pull-in live data that you can manipulate and enrich with an ever growing set of functionality? For example, you can get a Google map image from an address or take a screenshot of a website from it’s url address. That’s the promise of combining Clay’s reusable functions that bring the power and flexibility of APIs to spreadsheets.

In this demo we show you how to extend sheets with Clay’s fast growing library of public functions.

What you need for this tutorial:

a. A Google account

b. Knowledge of basic coding concepts, though there will be no coding involved just copying and pasting

Let’s say you are keeping track of your net worth and some of your money is in Bitcoin and other cryptocurrencies. Since Bitcoin’s price changes all the time you’d like to have the latest token price in your spreadsheet so you can see the total amount of money that you have at any point in time.

We’ll be building a demo spreadsheet that always has the latest Bitcoin prices built in as you see below.

https://docs.google.com/spreadsheets/d/1TCGm8-iQwVOcw1701LZ23cPsq9g04DF-aBMEqQvWPNc/edit#gid=0

Before we build our own version, try opening up the example sheet below and select the Bitcoin or Ethereum cells then click Get Token Price for it to pull the latest prices into the sheet. You will need to be signed in to your Google account for it to work.

Sample Finances with Clay

Here’s what’s going on underneath the hood and how to recreate it for yourself.

In this case we are using a function written by nicoslepicos, called blockchain-token-ticker function, which is available here:

https://clay.run/services/nicoslepicos/blockchain-token-ticker

Take a moment to try it yourself on the Clay site. This function takes in a token name as an input and returns information about that token. For example if we lookup bitcoin this is the output we would get.

Input:
token: bitcoin
Output:
id:”bitcoin”
name:”Bitcoin”
symbol:”BTC”
rank:”1"
price_usd:”1106.79"
price_btc:”1.0"
24h_volume_usd:”194136000.0"
market_cap_usd:”17903503661.0"
available_supply:”16176062.0"
total_supply:”16176062.0"
percent_change_1h:”0.09"
percent_change_24h:”4.39"
percent_change_7d:”10.36"
last_updated:”1487708044"

Here’s the fun part. Let’s build our own spreadsheet that mimics this functionality. First create a new spreadsheet and enter the names of a few tokens as rows. For example your spreadsheet might look like this:

Name of Token | Price of Token   | Number of Tokens | Total Amount
ripple        |                  |         5        |
bitcoin       |                  |         9        |
ethereum      |                  |         10       |

We’re going to make a button in google spreadsheets that once clicked uses the text of the currently selected cell as the name of the token and calls the previously discussed nicoslepicos/blockchain-token-ticker function to return the current price of that token.

1. Click Tools->New Script and paste the following:

function myFunction(name) {
  var payload = {
    token: name
  }; 
  var url = 'https://clay.run/services/nicoslepicos/blockchain-token-ticker';
  var options = {
    'method': 'post',
    'payload': payload
  };
  var response = UrlFetchApp.fetch(url, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  return data
}
function getTokenPrice() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     sheet = ss.getActiveSheet(),
     range = sheet.getActiveRange();
     values = range.getValues()
values.forEach(function(value, i) {
     var data = myFunction(value[0])
     writeData(data, range, i);
  });
  
}
function writeData(data, range, i) { 
    var firstNameOffset = range.offset(i, 1);
    firstNameOffset.setValue(data[0].price_usd);
}

2. Click on Insert->Drawing and Draw a circle. You can also add some text on that circle in order to make it clear what the button does.

3. Once you insert the drawing. Right click on it and click the arrow at the top right hand corner to select add script. Enter the following:

getTokenPrice()

That’s it! Now try it out by selecting the names of the tokens and clicking your button. Watch as the price magically populates.

You can use this same technique with other Clay public functions that are constantly being added and are available here:

http://clay.run/public/services

or create your own function at clay.run/ and share it with the community!

If you need help getting started or have feedback you want to share feel free to email me, leave a comment or join our gitter.