How to create a candlestick chart with the OHLC endpoint?

Introduction

final-candle-stick-chart-in-sheets

Candlestick charts are great for visualizing price movements in the cryptocurrency market. With Coinranking's API, you can easily create candlestick charts in Google Sheets to track your favorite coins' open, low, and close (OHLC) prices.

This guide will walk you through the steps to create a candlestick chart for Bitcoin at a monthly interval.

Step 1: Get the API key from Coinranking

Before you start, you'll need a Coinranking API key.

Sign in and copy your API key. Make sure this API key is from the Professional Plan since this is a paid endpoint. The key would be essential for making requests to our API.

Step 2: Setup Google Sheets

Open a new Google Sheets document where you want to display the candlestick chart. This is where your candlestick chart would be set up.

setup-sheets

Step 3: Open up Apps Script

In your Google Sheets document, go to Extensions > Apps Script.

Name your project “Bitcoin candlestick”.

setup-app-scripts

Then, delete any code in the script editor and paste the following code:

function getCandlestickData() {
  var uuid = 'Qwsogvtv82FCd'; // Bitcoin UUID
  var apiKey = 'YOUR_API_KEY_HERE';
  var url = 'https://api.coinranking.com/v2/coin/' + uuid + '/ohlc?interval=month';
  var options = {
   method: 'get',
   headers: {
    'x-access-token': apiKey
   }
  };
 
  try {
   var response = UrlFetchApp.fetch(url, options);
   var data = JSON.parse(response.getContentText());
   if (data && data.data && data.data.ohlc) {
    var ohlcData = data.data.ohlc;
    var candlestickData = [];
    // Convert OHLC data to candlestick format
    for (var i = 0; i < ohlcData.length; i++) {
     var candle = [];
     const startingAt = new Date(ohlcData[i].startingAt * 1000).toISOString()
     candle.push(startingAt);
     candle.push(parseFloat(ohlcData[i].open));
     candle.push(parseFloat(ohlcData[i].high));
     candle.push(parseFloat(ohlcData[i].low));
     candle.push(parseFloat(ohlcData[i].close));
     candlestickData.push(candle);
    }
    // Add headers
    var headers = [['Date', 'Open', 'High', 'Low', 'Close']];
    var finalData = headers.concat(candlestickData);
    return finalData;
   } else {
    console.error('Error: No OHLC data found in the response.');
    return 'Error: No OHLC data found in the response.';
   }
  } catch (error) {
   console.error('Error fetching or parsing data:', error);
   return 'Error fetching or parsing data: ' + error;
  }
}

Make sure to replace ‘'YOUR_API_KEY_HERE” with your actual Coinranking API key that you had copied from your API dashboard.

You should have something like this:

setup-app-scripts

Click on the disk icon to save your code. Click on “Run” to check if the code works. It should not show an error message.

Step 4: Add your data

After saving the script, go back to your Google Sheet, enter =getCandlestickData() in the cell where you want to display the candlestick chart data, and press Enter.

You should have a table with headings that show the date, open, high, low, and close.

add-data

Step 5: Create your chart

Once you have added your data, you can now create your chart.

First, select your data.

Then Click on Insert > Chart and then scroll down to find the candlestick chart under “Chart Type”.

You should have the candlestick chart showing the monthly Bitcoin prices.

final-candlestick-chart

Step 5: Set a trigger

To automatically refresh the price, we need to set up a time-driven trigger in Apps Script.

  1. In the Apps Script editor, find and click on the clock icon or Triggers button on the left sidebar. This opens the Triggers page.

  2. Click the + Add Trigger button at the bottom left of the screen. A form will appear. Here, select “getCandlestickData” under the “Choose which function to run” dropdown. In the Select event source dropdown, choose Time-driven.

Now, decide when and how often you want your script to run. You can select specific options like an Hour timer for it to run every hour, or a Daytimer to run at a specific time each day, among others.

Step 6: Save your trigger

After setting everything up, click “Save”. You might need to authorize your script to run under your account. Follow the prompts to allow permissions.

And that's it!

You've successfully set up your Google Sheets to display a candlestick chart using the Coinranking API. This setup can be customized further to track multiple coins with different intervals.

Don't forget, the Coinranking API has lots of other useful information besides live prices.

Feel free to check out more endpoints and different coins and make your spreadsheet better.