Covalent API + Google Sheets = friends

I will be making a series of technical posts about the Covalent API, and this would be the first one.

So I decided to see whether I could marry Covalent API functions with Google Sheets / Excel. Having Data in “Excel” enables us to do data analysis and join Ethereum data with other sources of data.

Coingecko, a popular website for crypto prices already allows for such functionality. Specifically, in https://blog.coingecko.com/import-coingecko-cryptocurrency-data-into-google-sheets/ authors provide a Google sheet template to access the Coingecko REST API.

As such, let’s not reinvent the wheel, and try to use existing functionality for Covalent API. The function that enables us to call REST functions and parse JSON results is called importjson. Let’s explore the body of the function.

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
try{
// /rates/EUR
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var json = JSON.parse(content);
var patharray = xpath.split(".");
//Logger.log(patharray);
for(var i=0;i<patharray.length;i++){
json = json[patharray[i]];
}
//Logger.log(typeof(json));
if(typeof(json) === "undefined"){
return "Node Not Available";
} else if(typeof(json) === "object"){
var tempArr = [];
for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== "object") {
return json;
}
}
catch(err){
return "Error getting data";
}
}

So, as we can see, it’s nothing fancy! We load the JSON data from the URL, take the XPath value and parse the result with it. Easy.

To call a Coingecko function, you do the following:

=importjson("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum","0.current_price")

Playing with the Covalent API, you can craft a similar request:

=importjson("https://api.covalenthq.com/v1/pricing/tickers/?tickers=ETH&key=YOURKEY","data.items.0.quote_rate")

Essentially, both the Coingecko and Covalent APIs provide JSON, which you have to parse. You parse it with a custom xpath.

Result:

Anyways, I hope you enjoyed my tutorial!