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 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.

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


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.

Should automatically update to the name of the current sheet

4. Under the Name and Save Request form, set the name of the 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!

6. Please note that 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.

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”.

2. Go back to API Connector and click the “Requests” tab at the very top of the Add-on, then click “ADD NEW”.

3. 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.

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

Leave a Comment