Ask Your Question
0

Link to External data from website not working

asked 2018-02-17 21:27:57 +0200

librerOfficer gravatar image

Trying to import the table from any API link from coinmarketcap.com with Sheet>Link to external data entering the API url (e.g. https://api.coinmarketcap.com/v1/tick...) and hitting enter. In the text import dialog you can see the table and its content. After clicking OK there is no available table to import in the parent dialog. I found a very old bug report with the same problem here https://forum.openoffice.org/en/forum.... Any help?

edit retag flag offensive close merge delete

Comments

librerOfficer gravatar imagelibrerOfficer ( 2018-05-01 23:24:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-02-17 22:24:16 +0200

Ratslinger gravatar image

updated 2018-02-17 22:55:59 +0200

Hello,

Please see this question - How to Link An API into a Spreadsheet.

What you are dealing with is a JSON file. Have accessed this and similar successfully using a Python macro just for my own test.

Bare minimum Python code to retrieve the data:

import json
from urllib.request import urlopen

url = urlopen('https://api.coinmarketcap.com/v1/ticker/')
obj = json.load(url)
if int(obj[0]['rank']) == 1:
    coin_name = obj[0]['name']
    coin_price = obj[0]['price_usd']
print(coin_name)
print(coin_price)

result (as of time this was executed):

Bitcoin
10880.7

Then the code needs modification to move whatever data is wanted in the desired place whether its' a spreadsheet, document, database table, etc.

Also, the link you provided is not a bug report. Bug reports can be found here - Bugzilla.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more

Comments

BTW - Link to External data is typically for accessing data in other modules such as another spreadsheet.

Ratslinger gravatar imageRatslinger ( 2018-02-17 22:42:15 +0200 )edit

in other words: no this is not possible. At least not directly like microsoft excel can do it. Very strange that you can work with the data in the text import dialog and do things like separate by ":" but at the end you can not import it. lol.

librerOfficer gravatar imagelibrerOfficer ( 2018-02-17 23:58:00 +0200 )edit

@librerOfficer It may be your question was not clear. I answered based on the fact of retrieving the data directly from the site. If you have copied the file as .csv then just select File->Open from the menu, select your saved .csv file and use : as the separator. It will import the data 'as of' the time it was copied. This information will be outdated in minutes using this method.

Ratslinger gravatar imageRatslinger ( 2018-02-18 02:09:04 +0200 )edit

no you got me right. As I explained you can actually work with the data in the import text dialog.

librerOfficer gravatar imagelibrerOfficer ( 2018-02-18 12:41:33 +0200 )edit

A note to anyone reading this. You can link to HTML tables such as http://coinmarketcap.com/ but not to the API as in https://api.coinmarketcap.com/v1/tick... in the question. The first link contains the entire table (HTML) and the second link only a specific item.

Ratslinger gravatar imageRatslinger ( 2018-03-03 05:15:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-17 21:27:57 +0200

Seen: 1,602 times

Last updated: Feb 17 '18