How To Get Last Done Price For Crypto On Google Sheet Using FTX API

Recently, I found out that FTX provides free API for their market data. So I went ahead to write a script on Google Sheet that will retrieve the last done price. The only caveat is we can only do this for coins that are listed on FTX.

To learn more about the different types of API that FTX provides, you can refer to the documentation here.

This tutorial will be broken down into 3 parts:

• Get all the market data from FTX
• Get last done price and copy to sheet
• Add a menu item to run script

Authorize script to run

To start, go to Extensions -> Apps Script

This should open up a new tab window. You can rename the project at this point, I’m going to name mine Get Price From FTX

When you try to debug or run the script, it will prompt Authorization required.

Just go ahead and Review permission -> login Google account/select account -> Advanced -> Go to Untitled project

PART 1: Get all the market data from FTX

You can clear the content and add the following code. What this function does is to get the entire market data from FTX and return the values.

function getMarketDataFromFTX(){
  var response = UrlFetchApp.fetch("https://ftx.com/api/markets");
  var markets = JSON.parse(response.getContentText()).result;  
  return markets;
}

You can set a breakpoint at Line 4 and run Debug, to see the market data that is returned from calling the api.

Just click on Stop when you are done.

Part 2: Get last done price and copy to sheet

Add the following code to get last done price and copy data to active sheet.

function updatePrice(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var markets = getMarketDataFromFTX();
  var rowsToLoop = sheet.getLastRow();
  var ftxPairValue;
  var cryptoData;
  var ftxColIndex = 1; // column index of the ftx pair
  var currentPriceColIndex = 2; // column index of where you want to write currentPrice

// i = 2 because want to start looping from row 2
  for(let i =2; i <= rowsToLoop; i++)
  {
    ftxPairValue = sheet.getRange(i,ftxColIndex).getValue();    

    //if there are values in ftxPairValue, then get price
    if(ftxPairValue){
      cryptoData = markets.find(o=>o.name === ftxPairValue);
      sheet.getRange(i,currentPriceColIndex).setValue(cryptoData.last);
    }
  };
}

There are 3 variables in red that you will need to update according to your google sheet data.

Now Save, Select the function and Run.

After running, you should see the last done price populated on your sheet.

Part 3: Add a menu item to run script

The last part is to add a custom menu where you can run the script via UI. Add the follow code and run it.

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("CustomMenu") //name of your menu
  .addItem("updatepriceFromFTX","updatePrice")
  .addToUi();
}

There are 2 items in red that you have to change according to the name that you want. If you refresh your google sheet, you should see the CustomMenu now.

You can just click on this to update the last done price.

Hope you find this useful.