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.
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;
}
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).
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.
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.
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:
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;
}
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;
}
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.
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)]);
}
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.
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.
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.
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;
}
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.
2. =convertCrypto(âbtcâ, âusdtâ, 1): Convert 1 Bitcoin to USDT.
3. =convertCrypto(âbtcâ, âxrpâ, 1): Convert 1 Bitcoin to XRP.
How to Add Real-Time Cryptocurrency Data To Google Sheets Using Custom Functions?
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.