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

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

We need to open a new Google Sheet to get started.  To do this, go to your Google Drive and click New.  A Dropdown will appear.  Click Google Sheets and a new spreadsheet will open for you.

part2image1

In the top left of your new Spreadsheet you will see the name is ‘Untitled Spreadsheet’.  Click that Text and give it a new name such as XMRBuyOrders.   Next, go to the bottom of the spreadsheet and find the Tab named Sheet1.  Double click the text and rename it to APIPull.  This is the sheet we are going to put the API data from Poloniex into.  You can name them whatever you choose, and though I do not use spaces in my names out of habit, spaces are fine also.  Every time you make a change in a Google Sheet it is automatically saved.  There is an Undo and Redo button on the top row next to the printer.  I have needed these many times.

Let me remind you here of the first rule of working on any document, spreadsheet or programming on a computer.  Save a copy periodically.  This can be done by clicking File, and choosing Make a Copy from any Google Application you are using.  When trying to clear data about five days into my programming, I accidentally chose the delete column function instead of the clear column function.  This wiped out everything I had been working on in my spreadsheet, and I would have had to start from scratch if not for my saved copy.

Next, we need to do our first piece of programming.  The language used in Google Scripts is very similar to Java, though not all the functions work exactly the same and not all Java options are available in Scripts.  If you want to do some research into how Java programming is done, you can visit W3Schools at the link below.  This is not needed to do what I am going to show you, but it is informative, and may be helpful as you try things on your own and need to use functions I have not covered.  Also, Google Search is your friend when you need to figure out something new.

http://www.w3schools.com/js/default.asp

Open a new Google Apps Script from the New button on your main Google Drive screen.  On the New pull down menu, select More >, then Google Apps Script.  You may see this referred to as GAS when you are searching the Internet for help on Scripts.

part2image2

A new scripts screen will open.  Click ‘Untitled Project’ and change the name to something like XMRScript.

part2image3

We can name our function, or routine, at this time.  In the script you just opened, the function name defaulted to myFunction.  You can have many functions within a single Script, but we are going to use just one to do everything here.  I am changing the name myFunction to processXMRAPI.  Everything within the top and bottom bracket { } following this name is part of the function.

******************************

VERY IMPORTANT – DO NOT MISS THIS

We are about to start writing code into our script.  At the bottom of each Page where any Script is modified is a link like this:

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

Please open that link to copy the code you use into your own script.  For some reason, when you copy from a web page directly into your code, Google Scripts sometimes sees an ‘illegal character’. This is due to the single quote ( ‘ ) not being interpreted correctly on the copy. Usually if you retype each single quote in your script the error will disappear.

******************************

We need to tell the Script where to find the Spreadsheet so the two can interact.  This will be our first line of code.  The link we will use is found in the Web Address of the Spreadsheet.  There is a long 44 character unique identifier consisting of random numbers and letters in the URL address itself.  You need to copy that identifier.  For the Spreadsheet I created I have…

Address:

https://docs.google.com/spreadsheets/d/1eE2qlIszcpIysIdy4QVgPpm0pKixswzYtVXKobP8dUk/edit#gid=0

Identifier:

1eE2qlIszcpIysIdy4QVgPpm0pKixswzYtVXKobP8dUk

Using this identifier, insert the following code in your function to link your Script to your Spreadsheet.  The // before text denotes a comment.  You may want to add to the comments to document what each step in the process does as you go.  This way when you look back later you can easily remember what you were doing.

*

// Link the script with a spreadsheet using the identifier found in the spreadsheet URL
var ss = SpreadsheetApp.openById(‘Paste Your 44 Character Identifier Here’);

*

Your Script will look something like this when you are done…

part2image4

Please click Save at this point and be sure you are not receiving the ‘illegal charater’ error message.  If you are, you can use the code from the ‘see what my script looks like’ link at the bottom of each page.  It will copy and paste the exact same text without an error from Google.  I do not know why.

Now we are able to write code in this script that uses and changes values in the associated spreadsheet.  For example, you might want to pull data from Poloniex and populate the spreadsheet with the information.  We will be doing that in the next lesson.

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

Click Here to continue to Part Three.

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