Open In App

How to Add Real-Time Cryptocurrency Data To Google Sheets Using Custom Functions?

Improve
Improve
Like Article
Like
Save
Share
Report

By utilizing Google Apps Script, a cloud-based JavaScript platform, and the Live Coin Watch cryptocurrency data API, this article demonstrates how to incorporate real-time cryptocurrency data into Google Sheets, a spreadsheet program within Google Workspace.

Sheets is similar in functionality to Microsoft Excel and supports the import and export of .xls (Microsoft Office 95 and newer), .xlsx, .xlsm, .xlt, .xltx, .xltm .ods, .csv, .tsv, .txt, and .tab file formats.

Problem Statement

Cryptocurrency data is decentralized, and the volatility of cryptocurrency prices presents a challenging scenario for traders to fully comprehend market conditions. To make informed decisions, traders need access to a reliable source of real-time data for performing calculations and projections based on the current value of their cryptocurrency assets. These calculations and projections are vital for informed decision-making and portfolio adjustment. However, conventional tools such as Google Sheets may not offer the necessary level of accuracy and data availability required to keep up with the dynamic cryptocurrency market.

Steps To Add Real-Time Cryptocurrency Data To Google Sheets Using Custom Functions

Step 1: Create a free account and obtain an API key from Live Coin Watch.

Obtain API Key

 

Obtain API Key

 

Step 2: Open a new or existing Google Sheets document using the link. 

Step 3: Navigate to the menu option Extensions > Apps Script to open a new Apps Script project associated with the sheets document.

Step 4: Rename the default script Code.gs to getRate.gs for our new function getRate()

Step 5: Paste the following code to getRate.gs and input your API key from Live Coin Watch at line 2:

function getRate(code) {

    var key = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”; //enter your API key here

    var code_formatted = code.toUpperCase().split(” “).join(“”); //format custom function input

    var fiat = “USD”; //enter your country’s currency code

    // build HTTP request

    var data = {

        “currency”: fiat,

        “code”: code_formatted,

        “meta”: true

    }

    var payload = JSON.stringify(data);

    var headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    var url = ‘https://api.livecoinwatch.com/coins/single’;

    var options = {

        ‘method’: ‘post’, //specify the HTTP POST method

        ‘headers’: headers,

        ‘payload’: payload

    };

    // store the response from the API request

    var response = UrlFetchApp.fetch(url, options);

    // success

    // convert response to JSON object

    var json = response.getContentText();

    var coinjson = JSON.parse(json);

    // store rate from coinjson object

    rate = coinjson.rate;

    // return rate to cell

    return rate;

}

getRate.gs

 

The getRate() function accepts a cryptocurrency code as input and performs string manipulation to format the code in uppercase and remove any spaces. The fiat currency is set to “USD”, but can be altered to another currency code.

The function makes an HTTP POST request to the coins/single endpoint through the use of the UrlFetchApp class in Google Apps Script. The request headers include the specified currency code and API key. The response from the API is processed and stored as a JSON object, from which the exchange rate for the specified cryptocurrency is saved to a variable named rate. The function concludes by returning the exchange rate (price) as its output.

Step 6: Save your project, click Run, and when prompted to accept the required permissions, select Review permissions > Go to My Project (Unsafe) > Allow. This authorization process will enable your project to external services (Live Coin Watch API).
 

Authorization required

 

Review permissions

 

Access required

 

Step 7: Format the cell or column using the menu option Format > Custom Number Format to set the currency symbol and a number of decimal places.

Custom number format

 

Step 8: Enter the formula name in the cell with the code for any given cryptocurrency on Live Coin Watch, for example, =getRate(“btc”) to return the price of Bitcoin, or =getRate(“shib”) for Shiba Inu, and press enter.

=getRate("btc")

 

=getRate("shib")

 

The code for a given coin on Live Coin Watch is found at the end of its price page URL, following the name and hyphen. For example:

 

=getRate("___CORE)

 

Step 9: Add another script getVolume.gs and create a getVolume() function to return the coin’s 24-hour trading volume:

function getVolume(code) {

    var key = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”; //enter API key here

    var code_formatted = code.toUpperCase().split(” “).join(“”); //format custom function input

    var fiat = “USD”; //enter your country’s currency code

    var data = {

        “currency”: fiat,

        “code”: code_formatted,

        “meta”: true

    }

    var payload = JSON.stringify(data);

    var headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    var url = ‘https://api.livecoinwatch.com/coins/single’;

    var options = {

        ‘method’: ‘post’,

        ‘headers’: headers,

        ‘payload’: payload

    };

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var coinjson = JSON.parse(json);

    volume = coinjson.volume;

    return volume;

}

getVolume.gs

 

The getVolume() function accepts a cryptocurrency code as input and performs string manipulation to format the code in uppercase and remove any spaces. The fiat currency is set to “USD”, but can be altered to another currency code.

The function makes an HTTP POST request to the coins/single endpoint through the use of the UrlFetchApp class in Google Apps Script. The request headers include the specified currency code and API key. The response from the API is processed and stored as a JSON object, from which the trading volume for the specified cryptocurrency is saved to a variable named volume. The function concludes by returning the calculated trading volume as its output.

Example usage:

Call the getVolume() custom function from the cell using =getVolume(“btc”) to return the 24-hour trading volume of Bitcoin.
 

 

Step 10: Add another script getLink.gs to return a hyperlink to the coin’s listing on Live Coin Watch using the function getLink()

function getLink(code) {

    var key = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”; //enter API key here

    var code_formatted = code.toUpperCase().split(” “).join(“”); //format custom function input

    var fiat = “USD”; //enter your country’s currency code

    var data = {

        “currency”: fiat,

        “code”: code_formatted,

        “meta”: true

    }

    var payload = JSON.stringify(data);

    var headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    var url = ‘https://api.livecoinwatch.com/coins/single’;

    var options = {

        ‘method’: ‘post’,

        ‘headers’: headers,

        ‘payload’: payload

    };

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var coinjson = JSON.parse(json);

    name = coinjson.name;

    var nameTrim = name.split(” “).join(“”);

    var url = “https://livecoinwatch.com/price/” + nameTrim + “-” + code_formatted;

    return url;

}

getLink.gs

 

The getLink() function accepts a cryptocurrency code as input and performs string manipulation to format the code in uppercase and remove any spaces. The fiat currency is set to “USD”, but can be altered to another currency code.

The function makes an HTTP POST request to the coins/single endpoint through the use of the UrlFetchApp class in Google Apps Script. The request headers include the specified currency code and API key. The response from the API is processed and stored as a JSON object, from which the name of the specified cryptocurrency is saved to a variable named name. The function constructs a URL using the coin name and code and returns the URL as its output.

Example usage:

Call the function getLink(“shib”) to return the URL for the Shiba Inu coin page on Live Coin Watch.

=getLink("shib")

 

You can create similar functions to display various data elements returned by the coins/single and other endpoints from the Live Coin Watch API.
 

Step 11: Create a script named addRow.gs for a new function addRow().

function addRow() {

    var key = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”; //enter API key here

    var code = “BTC” //enter your coin or token’s code

    var fiat = “USD”; //enter your country’s currency code

    var data = {

        “currency”: fiat,

        “code”: code,

        “meta”: true

    }

    var payload = JSON.stringify(data);

    var headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    var url = ‘https://api.livecoinwatch.com/coins/single’;

    var options = {

        ‘method’: ‘post’,

        ‘headers’: headers,

        ‘payload’: payload

    };

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var coinjson = JSON.parse(json);

    name = coinjson.name;

    rate = coinjson.rate;

    volume = coinjson.volume;

    //generate timestamp

    var timestamp = new Date().toLocaleString().replace(‘,’, ”);

    var sheet = SpreadsheetApp.getActiveSheet();

    sheet.appendRow([timestamp, name, code, “$” + rate, “$” + volume, getLink(code)]);

}

addRow.gs

 

The addRow() function retrieves the latest information for a specified cryptocurrency, with a default code of “BTC”, and the fiat base currency of “USD”.

The function makes an HTTP POST request to the coins/single endpoint through the use of the UrlFetchApp class in Google Apps Script, including the specified currency code and API key in the request headers. The response from the API is processed and stored as a JSON object, from which the name, exchange rate, and trading volume of the specified cryptocurrency are extracted.

The addRow() function then appends a new row to the active spreadsheet, including the current timestamp, cryptocurrency name, code, exchange rate, trading volume, and a URL linking to the cryptocurrency’s information on Live Coin Watch. The URL is obtained by calling our getLink() function with the cryptocurrency code as input.

Step 12: From the Apps Script project view, navigate to the Triggers view (clock icon) and click on the + Add Trigger button. Select the function addRow(), Select event source Time-driven Select type of time-based trigger: Minutes timer Select minute interval: Every minute.

Triggers View

 

Add Trigger to Project

 

Step 13: Add a row to label the data columns to your preference, then insert a chart to your sheet from the menu option Insert > Chart, Chart type: Scatter chart, set the X and Y axis to the timestamp and rate column range 2:1000, and adjust the Vertical axis limits for the desired price range.

Scatter Chart

 

Scatter Chart

 

You can review each execution by clicking the three dots to the far right of your Time-based trigger on the Triggers page and selecting the Executions option.

Select Executions Option

 

Select Executions Option

 

Step 14: Add another script convertCrypto.gs and create a convertCrypto() function to convert between cryptocurrencies.

function convertCrypto(base, target, amount) {

    var key = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”; //enter your API key here

    var base_formatted = base.toUpperCase().split(” “).join(“”); //format custom function input

    var target_formatted = target.toUpperCase().split(” “).join(“”); //format custom function input

    var data = {

        “currency”: “USD”,

        “code”: base_formatted,

        “meta”: true

    }

    var payload = JSON.stringify(data);

    var headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    var url = ‘https://api.livecoinwatch.com/coins/single’;

    var options = {

        ‘method’: ‘post’,

        ‘headers’: headers,

        ‘payload’: payload

    };

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var coinjson = JSON.parse(json);

    var baseRate = coinjson.rate;

    var baseCost = baseRate * amount;

    data = {

        “currency”: “USD”,

        “code”: target_formatted,

        “meta”: true

    }

    payload = JSON.stringify(data);

    headers = {

        ‘Content-type’: ‘application/json; charset=UTF-8’,

        ‘x-api-key’: key

    };

    url = ‘https://api.livecoinwatch.com/coins/single’;

    options = {

        ‘method’: ‘post’,

        ‘headers’: headers,

        ‘payload’: payload

    };

    response = UrlFetchApp.fetch(url, options);

    json = response.getContentText();

    targetjson = JSON.parse(json);

    var targetRate = targetjson.rate;

    var targetAmount = baseCost / targetRate;

    return targetAmount;

}

convertCrypto.gs

 

The convertCrypto() function takes in three parameters: the base cryptocurrency, the target cryptocurrency, and the amount of the base cryptocurrency to be converted. The API key is used in the request header the base and target cryptocurrencies are formatted to be used in the API request. The function then makes two HTTP requests to the Live Coin Watch API endpoint coins/single to retrieve the exchange rate of both the base and target cryptocurrencies. The base rate is used to determine the cost of the base cryptocurrency in USD, and the target rate is used to convert the cost of the base cryptocurrency to the target cryptocurrency. Finally, the function returns the converted amount of the target cryptocurrency.

Example usage:

1. =convertCrypto(“usdt”, “eth”, 2000): Convert 2000 USDT to Ether. 

Convert 2000 USDT to Ether

 

2. =convertCrypto(“btc”, “usdt”, 1): Convert 1 Bitcoin to USDT.

Convert 1 Bitcoin to USDT

 

3. =convertCrypto(“btc”, “xrp”, 1): Convert 1 Bitcoin to XRP.

Convert 1 Bitcoin to XRP

 

Conclusion

In conclusion, the integration of real-time cryptocurrency data into Google Sheets is a useful tool for traders to keep track of their cryptocurrency assets and make informed decisions. By using Google Apps Script and the Live Coin Watch cryptocurrency data API, users can easily retrieve and display up-to-date information about their assets, including exchange rates, trading volumes, and URLs linking to further information. The process involves creating custom functions in Apps Script, making HTTP POST requests to the Live Coin Watch API, and parsing the response data to extract the required information. Functions can be scheduled to run automatically at specified intervals, ensuring the sheet data remains up-to-date. Additionally, traders can create charts to visualize the data and identify trends over time. In conclusion, utilizing Google Sheets and Apps Script allows users to efficiently model and analyze their investment portfolios through the use of dynamic charts and real-time data. This empowers investors to not only gain a deeper understanding of their portfolio’s performance but also to make well-informed decisions that can maximize returns and minimize risks in the volatile world of cryptocurrency. By harnessing these technologies, investors can stay agile and proactive in their approach to managing their assets, setting themselves up for long-term success in an increasingly digital financial landscape.



Last Updated : 08 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads