Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers

I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter.

Full disclosure here, I don’t really know all that much about cryptocurrencies. I knew about Bitcoin and Ethereum but really didn’t really invest any time and money into looking at these growing forms of value exchange.

But now I’m a little curious.

I thought it would be fun to see what the value of XRP was against a fairly standard metric like the USD and keep a record of this exchange rate daily over a month or so (Mrs Yagi just read the ‘fun‘ in the last sentence and rolled her eyes).

I also thought it would be pretty cool to see how many exchange providers (Gateways) that conduct USD-XRP exchanges are around and see if there is much difference their exchange rate day-to-day among them.

Fortunately for me, the XRP Ledger provides a freely available open-source ledger that can be accessed via the Ripple Data API. The API can return JSON objects from which we can extract the data we need.

Of course, to record and display the daily exchange-rates I went straight to Google Sheets. I gathered the relevant XPR data using Google Apps Script’s UrlFetchApp Class and pushed it to my Google Sheet with SpreadsheetApp Class.

Here is a live embed of the XRP Google Sheet that is updated via a daily time trigger via Google Apps Script.

You can check out the XRP sheet directly here:

XRP Google Sheet

As always, read what you need and move on.

Parameters

Our parameters are simple:

1. We want to know all the USD/XRP Gateways that are registered in the Ripple Data API.
2. We want to know their exchange rate.
3. We want to check daily.

Ripple Data API v2

Finding What we need

Firstly, I didn’t really know how many USD/XRP exchanges were available. I wasn’t sure if the Ripple Data API received daily records of all of their registered exchanges or not.

Get Exchange Volume

Looking through the docs, I found that the Get Exchange Volume method had the information I needed.

The Get Exchange Volume method would return a JSON body containing information on:

• The issuer of exchanges
• The base currency like USD, CNY, EUR, ULT, BTC, BCH (Crypto and fiat currency shortcodes).
• The counter currency XRP, USD, EUR etc.
• The exchange rate between the two currencies.
• The issuing id of the exchange gateway.
•  The amount exchanged for the designated period.

To call the API you would use the following URL:

Go ahead and copy and paste it into a browser tab to see what comes up.

We can determine the daily exchange for each by adding the following to the end of the URL above:

This would produce a JSON a little like this:

Get All Gateways

Great! However, I also wanted a way to find the human-readable name of the exchange issuer. This would help me to better see the different USD-XRP exchanges that registered with Ripple when I post it to my Google Sheet. For this, I found that the Get All Gateways method.

This method list all the know Exchange Gateways by base currency and displays:

• Account
• Human readable name
• Assets like logo images
• The date the gateway started

To call the method you simply use the following URL:

This will result in a JSON object like this:

For our purpose, we only need the USD gateways that we have collected the exchange rates for with our Get Exchange Volume method earlier.

Now we have our two Ripple API methods we can move on to our Google Apps Script code to import the results we want into our Google Sheet.

The Google Sheet

Before writing the Google Apps Script code, I first created a Google Sheet called XRP with a sheet tab name of “Exchange Volume”.  On the header row I added the titles:

• • Date Issuer
  • Issuer Name
  • Base Counter
  • Exchange Rate
  • Amount

The Code

For clarity, I set up the Google Apps Script project into 3 files. You can put them all in one file, but I found this neater. The files are as follows:

• Code.gs  – The main code that runs the entire project and fetches and displays the data.
• XRP_ExchangeVolume.gs – This extract all the data from our Get Exchange Volume Ripple API call and puts in a 2d array that is friendly for setting into our Google Sheet.
• XRP_Gateways.gs – This extracts all the data from our Get All Gateways Ripple API call and stores all the USD gateway account names and ids.

Code.gs

This is the core file that has our main run function, some universal function like our URL Fetch, a function to join the Human readable Gateway info to our exchange volume data and finally a function to push all our data to our google sheet.

NOTE: You can pop out this code and put it beside the tutorial so you can follow along with the explanation if you want. 

Globals

There are two global variables for this project (lines 14 and 15). The SPREADSHEET id, which is the ID for my XRP Google Spreadsheet. You can see the spreadsheets id in the URL if you follow the link to it. You’ll need to change this to your own ID. You’ll also need to change the SHEET_NAME if you want to change that to something else.

run()

The run() function is our main driving function.

It first calls the exchangeVolXRP() function which returns a 2D array of the following: 

1. Date of API call
2. Issuer ID
3. Base Currency (This should always return USD)
4. Counter Currency (This should always return XRP)
5. Exchange rate from USD-XRP for the current day(The current worth of one XRP in USD)
6. Exchange volume for the current day

For example, it will return something like this:

This  2d array is stored in the xrpXE_vol variable.

Similarly, on line 24 we grab Ripple API Gateway Data. This return the Gateway account names and ids for USD exchanges like so:

Next, we call the addGatewayToXEvol(gatewayName, xrpXE_vol) which inserts the human-readable name into the xrpXE_vol array for each gateway exchange (Line 27). 

Finally, the run() function calls the displayDataToSheet(xrpXE_vol) taking in the new updated xrpXE_vol with its added exchange names and adds it to the first empty row in the XRP Google Sheet, Exchange Volume tab. 

fetchAPI(url)

The fetchAPI(url) function is called from both the exchangeVolXRP() function and the XRP_Gateways(). Its main aim is to make use of Google Apps Script Url Fetch Service to gather the API JSON data from Ripple. 

The fetchAPI(url)  takes a URL, which in our case is the request to the Ripple API for our selected data. Both exchangeVolXRP() and XRP_Gateways() functions have this URL variable at the top of their respective functions – more on this when we investigate these functions. 

If you look at the portion of the script above, line 10 creates a variable called response. This variable is the returned values gathered from the UrlFetchApp.fetch() method. The fetch method can take one URL and an optional set of parameters that are added inside a curly bracket object.

For our purposes we are not too worried if the fetch call to our API throws an error, we just want to ignore it and carry on. Do do this we set the muteHttpExceptions optional parameter to true.

Next, if we have a response from the fetch request (line 12), we want to get the text from the data returned in the response variable which will be JSON data (Line 14). We then take this JSON data and parse it ready to be returned to the function it was run from in the other two script files.

addGatewayToXEvol(nameData, XEdata)

This function is executed from the run function on line 27. Its purpose is to splice in the human-readable name gathered from the Gateway data into the XRP exchange volume data.

The addGatewayToXEvol(nameData, XEdata) function takes two parameters which are the 2d arrays returned from the Exchange Volume (XEdata) and Gateway Data (nameData) from the Ripple API.

Here we loop through both the parameters (lines 11 & 12) to compare the Issuer ID from the XEdata to the nameData ID. If there is a match, then we grab the human-readable name from the nameData and  splice it into the corresponding row of the XEdata (lines 14-16).

All this will update the xrpXE_vol back in the run() function so that it now contains something like the following:

displayDataToSheet(data)

This is the final function run for the project. It takes the new updated xrpXE_volvariable and stores as the parameter data.  It then adds that data to the bottom of our selected sheet.

Line 9 and 10 of our displayDataToSheet(data) function gathers the row length and height of our data.

Remember our data is a 2d array with the first level containing an array of arrays of each USD-XRP Issuers and their exchange rates and volumes. It appears for us that there are two registered to the Ripple API, Bitstamp and Gatehub. So this means there will be a length of 2 rows.

The number of columns can be gathered from the length of the first nested array, this array would contain all the data for one of the issuers. In our case, the number of columns would be 7.

The next step is to grab the Spreadsheet and the sheet tab we are working. We do this by invoking the Google Apps Script SpreadsheetApp Class (line 12). First, we get the sheet by its ID, which we have stored in our Globals at the top of the Code.gs file. Then we grab the sheet tab by its name.

We’ll store all this in the sheet variable for later.

On line 15, we will store the row number of the next empty line as newLine using the getLastRow() method.

We will then take all the row, column and new line values and use them to get the range of cells from the next empty row beneath the current data (line 16). We do this with the getRange() method which can take 4 parameters:

1. Start Row
2. Start Column
3. Number of Rows
4. Number of Columns

Finally, we set that new range with our newly fetched Ripple Data. This will display the data on our sheet.

XRP_ExchangeVolume.gs

This file contains one function, the exchangeVolXRP() which is called from the main run() function in the Code.gs file.

NOTE! Pop this code out using the pop-out tool in the codes toolbar so it is easier for you to follow along. 

exchangeVolXRP()

The exchangeVolXRP() function fetches the Ripple Exchange Volume API data extracting just the USD-XRP exchanges and stores values like the start time of the fetch, the issuer id, the base currency (USD), the counter currency (XRP), the exchange rate and the exchange amount. It then returns all the relevant values as a 2D array to the run() function.

First, we store the URL to our daily Exchange Volume in our url variable (lines 15-16) that is immediately sent to our fetchAPI(url) function discussed earlier. This returns a JSON parsed object with all the data we need and stores it in the xeVolData variable.

Let’s take a look at the shortened version of that xeVolData data again. I’ll highlight the bits we will want to extract:

Once we have the data we go ahead and store the start_time (Line 20). The start time is found inside an array indicated by the square brackets “[]”. It’s in the zeroeth position of the “rows” object array. The object key for the start time is the ...erhm… “start_time”.  So to grab that value we would do the following:

All the other data we want can be found in the “components” object. Instead of typing out the path each time we will stuff it into a variable we’ll call exchanges (Line 24):

On line 25, we will create an empty array called xeUSD_XRP to store all our data for each of our exchanges.

Line 27, starts our loop through all the components. We only want to get exchanges that have a base currency of “USD” and a counter currency of “XRP”. If these conditions are met we will store our selected data into our xeUSD_XRP variable.

Once all the data is found, we then return it back to our run() function in our Code.gs file.

XRP_Gateways.gs

This file contains two functions. The main function for this file is the XRP_Gateways() function which fetches the data and returns gateway names and their associated ID’s. This function makes use of the getUSDgateways(data) that extracts just the USD gateway names and IDs. 

NOTE! Pop this code out using the pop-out tool in the codes toolbar so it is easier for you to follow along. 

XRP_Gateways()

The xrp_gateways() function first stores the Gateways Data API URL in the url_gateway variable. It then fetches the JSON parsed data using the fetchAPI() function mentioned in the Code.gs section of this tutorial storing it in getXRPgateway (line 13).

ThegetXRPgateway contains an object of data with key values set as currencies. Let’s take a look at the USD object key values and I will highlight what we will extract:

We just want the USD Gateways so we will store the just the USD object values (The ones you see above) in the dataUDS array (Line 15):

We’ll then run our getUSDgateways(dataUSD) function to extract just the Gateway ID and name (Line 17) and store it in our gatewaysUDS variable which we will finally return to the main run() function in our Code.gs file.

getUSDgateways(data)

The getUSDgateways(data) is called from the XRP_gateways() function. It takes the fetched USD gateway data and extracts it.

Inside the USD object is an array containing an object group of each gateway. To get each gateways data we loop through the base data selecting the name and account information (Lines 28-29) in each iteration and push them to a gateways variable that we created on line 23. This data is then returned back to the XRP_gateways() function.

Run the project code

That’s all the code you need. Go back to the run() function in the Code.gs file. Let’s go ahead and execute it to see if it works. In the Google Apps Script editor menu, select our run() function and hit the play button.

You will have to go through and grant all the permissions the first time around.

Running the project daily with a time trigger

We want to be able to run this code on a daily basis to keep a record of exchange rates by exchanges for each day.

Do do this we need to head back to the run() function in the Code.gs file.

In the menu bar of your Google Apps Script Editor, go to Edit > Current Project’s Trigger.

This will load your current project’s trigger dashboard. Down the bottom right you will find an Add trigger button. Give it a click.

This with give you a popup window. Change the fields to match the data below:

The function you want to run is the ‘run’ function. Keep the deployment as head. Change the event source to Time-driven and then change the type of time-based trigger to Day Timer. You can choose which hour bracket of the day you want to run your project. Leave the failure notifications as is.

Go down to the bottom right and click save.

That’s it. You are all ready to receive daily updates on the USD-XRP exchange rates for all the available issuers.

Conclusion

You made it! Nice one.

Hopefully, this tutorial has given you a bit of an idea on how to extract and display JSON data from APIs onto a Google Sheet with Google Apps Script. In general, any REST API is going to have very similar characteristics. This means you should be able to carry what you have learnt in this tutorial over to extract JSON data from other REST APIs using Google Apps Scripts UrlFetch Class.

Her is the link again to the Google Sheet and its associated Google Apps Script project. You can Make a copy of the sheet and then go to Tools > Script editor and you will find the code in there. You will have to change the SPREADSHEET global for your script to work:

XRP – Google Sheet 

So what do you plan to use what you have learnt on? I would love to hear how you are applying your skills. Let us know in the comments below.

In the next tutorial, we’ll look at how I created the Daily Exchange Rate sheet tab you can see below. This should give you a bit of inspiration for working with currency exchange data along with a couple of great tips and tricks for displaying sparklines, conditional formatting and displaying data in another sheet that is updated from an external source:

Creating an Exchange Rate Analysis Page to Compare Between Two Exchange Gateways in Google Sheets 

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi