Import Coinmap Data to Google Sheets

Coinmap is a service that keeps track of crypto ATMs and vendors across the world.This guide will get you started with pulling Coinmap data from their API via the API Connector Add-on for Google Sheets. You don’t need to create an account to pull data, so let’s jump right in!

Contents

Part 1: Create a Basic API Request URL

Let’s setup a request to get all crypto venues that: 1) serve food and 2) have opened since the beginning of the wonderful year of 2020.

  • API Root: https://coinmap.org/api
  • Endpoint: /v1/venues/?after=2020-01-01&category=food

So the full request should look like: https://coinmap.org/api/v1/venues/?after=2020-01-01&category=food

Part 2: Pull Coinmap API Data into Google Sheets

1. In order to get this data into Google Sheets, let’s copy the request URL we just made and paste it into the API URL path field in API Connector.

coinmap-img1

2. Now rename your sheet to something like “Crypto-Venues”.

coinmap-img2

3. Now go back to API Connector and under Output Settings in the “Destination Sheet” field, just press the “Set current” button and make sure the form is populated with the name we just entered for the current sheet.

coinmap-img3

4. Name your request to something like “After-Jan-2020”. Hit Save and then hit Run.

5. Congratulations! You now have a list of all crypto venues that serve food created since the beginning of 2020!

coinmap-img4

6. The “created_on” column contains time values that are in a format known as Unix Epoch Time. You can convert this to human-readable time by entering the following formula into the neighboring cell: =F2/86400+date(1970,1,1). After entering the formula, you can double click the small blue box in the bottom-right corner of the cell to apply that formula to the entire column.

coinmap-img5

Part 3: [PRO Function] Get Star Rating For All Venues

This list is quite interesting, but how do these places compare to each other? On digging into the documentation, it looks like we can get reviews for individual stores based on their id listed in the first column. Let’s get the ratings for every venue and output that to a new sheet!

1. To get started, let’s make a new sheet and call it “Ratings”.

coinmap-img6

2. Go back to API Connector and click the “Requests” tab at the very top of the Add-on, then click “ADD NEW”. Fill in the following URL request : https://coinmap.org/api/v1/venues/+++Crypto-Venues!A2:A124+++/ratings/

Note, you will need to change the range of your cells based on the output of the previous step. You will also need to make sure the Output Settings are sending this data to a new sheet, as well as give the request a name.

coinmap-img7
This request cycles through a list of values and uses them to populate the API request URL. More info here: https://mixedanalytics.com/knowledge-base/api-connector-run-multiple-queries-single-request/

4. You should get 3 columns returned on the “Ratings” sheet. From their documentation, the 3 fields contain the following information:

  • First field is the sum of all positive ratings
  • Second field is the sum of all negative ratings
  • Third field indicates if the rating was made my a user who was logged in
coinmap-img8

Leave a Comment