Creating Your Own Spreadsheets and Charts Using Poloniex API – Part 3

If you have reached this page without going through the previous instructions, please Click Here to start at Part 1.

Reminder: Please remember to copy and paste your script code from the link at the bottom of this page.  If you do not you may get an ‘illegal character’ message when you try to save your script.

We just finished linking the Script with our code in it to the Spreadsheet we want to store the data on.  There are a couple more preliminary steps we need to take before we pull and populate the data into our spreadsheet.  The first of these is to define within the Script the sheet (tab) name within the spreadsheet I want to work with.  As we move forward, we are going to be creating other sheets (tabs) with different names.  By defining each name I can keep the script simple.

Putting the line of code below next I am telling the script any time I refer to APIPullSheet, I want it to work with the tab I have named APIPull in the spreadsheet.

*

var APIPullSheet = ss.getSheetByName(“APIPull”);

*

The second preliminary step is to clear the data currently in the cells we are going to populate in the spreadsheet.  When our script runs, let’s say there are 1000 lines in the Ask columns.  Ten minutes from now the script runs again automatically, but this time there are only 990 lines.  If we don’t clear the cells first, the process will overwrite the first 990 rows with the new data, but will keep the last 10 rows from the previous run.  This can throw off our numbers in a big way.

I put in there to clear columns A through D, from Row 2 to 19,999 to be on the safe side.  We should never have that many rows when pulling a single currency, but you can change that number to be smaller or larger if you want.  Here is the code used to clear the cells.

*

// Clear Columns A, B, C & D
APIPullSheet.getRange(‘A2:D19999’).clearContent();

*

Now we are ready to get the numbers.  We need to define the URL from Poloniex to use to pull data.  In Part 1 I pointed you toward the Poloniex API Documentation.  In there you learned you can pull all kinds of information from the Public APIs.  I am going to be pulling the ‘returnOrderBook’ data, which will give me the Ask (sell) and Bid (buy) information you can see on the Poloniex Exchange for any coin.  I chose this because it is data you can use and the API for it is simple.  If you need a refresher on how the data is pulled and what it looks like, please refer back to Part 1.

As previously discussed, I want to pull all of the data from the Order Book for XMR (Monero) from Poloniex.  The URL I would use to see the data (which I created using the Poloniex API Documentation) is below.  This is also the same URL I can use in my Script to reference the data.  I put a depth of 19999 because Poloniex only returns the top 50 asks and bids if you do not specify.  19999 is sure to return every row available.

https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_XMR&depth=19999

Defining this URL in the script just requires the line of code below.  This is the simplest code within the script, and we will be defining many variables this way.  It simply says…

  1. var create a variable (AKA a field)
  2. url – call the variable ‘url’
  3. =”http” make the field equal to the Polo API web address

*

var url = “https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_XMR&depth=19999 “;

*

Next, we have to use a couple commands Google Scripts offers which perform specific tasks.  These functions are:

  1. fetch Pulls data from a URL
  2. parse Decipher that data from JSON into something the program can use

We are simply going to pull data using these commands into new variables we define.  In the first line we create a variable named responseAPI, and put the raw data from the Poloniex URL in that field.  This data is still in JSON format and we cannot really use it in the spreadsheet yet.

*

var responseAPI = UrlFetchApp.fetch(url);

*

And then we take that JSON formatted data, and parse (break) it into fields we can use on a Spreadsheet.  The data is put into a field called parcedData.

*

var parcedData = JSON.parse(responseAPI.getContentText());

*

Once these steps are run, all of the information fetched (pulled) from Poloniex is in text format in the parcedData field.  We can’t really work with the data yet because while Google Scripts knows there are two sets of data at this point, asks & bids, it needs the fields within these broad categories defined.  This is a lengthy topic to discuss, so I will cover it in Part 4 by itself.

Click Here to see what my Script looks like at this point.

Click Here to continue to Part Four.

Please recognize a lot of effort went into figuring this process out and documenting it to save you time, with the goal of giving you information you can use to make more BTC in the future.  If this is useful information for you, I would appreciate any tips you are able to send to the following BTC address.

1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

Advertisements