1

I want to import the table from this website:

https://www.arcgis.com/home/item.html?id=917fc37a709542548cc3be077a786c17#data

in this Spreadsheet:

https://docs.google.com/spreadsheets/d/13bfnQP_kgFsB7k3F_6xMxkMI4wW7ukI9TVq-aVZ3TzE/edit#gid=0

and used this function

=IMPORTHTML("https://www.arcgis.com/home/item.html?id=917fc37a709542548cc3be077a786c17#data", "table", 1)

The answer is "Imported content is empty". What else do I have to do?

player0
  • 124,011
  • 12
  • 67
  • 124
Johannes Christ
  • 89
  • 1
  • 8

2 Answers2

2

the website you trying to scrape is under JavaScript control. Google Sheets is not capable of importing JS elements. you can test this simply by disabling JavaScript for a given site and what you are left with is able to be scraped. in your case: nothing:

0

player0
  • 124,011
  • 12
  • 67
  • 124
0

Alternate option. Use a custom script to load directly the JSON data.

The script to import JSON data with GoogleSheets (credits to Paul Gambill) : https://gist.github.com/paulgambill/cacd19da95a1421d3164

And the data :

https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?f=json&where=1=1&returnGeometry=false&spatialRel=esriSpatialRelIntersects&outFields=*&orderByFields=OBJECTID%20ASC&resultOffset=0&resultRecordCount=412

Output :

CovidData

E.Wiest
  • 5,425
  • 2
  • 7
  • 12
  • Thank you, @E.Wiest! – Johannes Christ Apr 02 '20 at 14:48
  • Hi @E.Wiest The data is imported as intended at first. See [google sheet](https://docs.google.com/spreadsheets/d/1jFsVFyW0ybhbe-9Oa3_NKHwyMREGOLMKukIJh1qrFFk/edit#gid=0) But after a while the data gets lost. It is sayed that the function ImportJSON is unknown. When I open the sheet the data is loaded again. – Johannes Christ Apr 13 '20 at 18:03
  • Hmm. No problem here. The problem happens when you try to perform operations on the data ? Did you check if the script is associated with the sheet you're working with ? In Google Apps Scripts, "ImportJSON" is the name of my project and "ImportJson" the associated sheet. https://framapic.org/NMucVtxRDrea/nkMTzTB5cftu.PNG If it's OK, maybe we can try to select specifc columns to ease the loading of the data. What are the columns of interest ? – E.Wiest Apr 14 '20 at 03:48
  • Hi @E.Wiest I don´t think the problem is related to your URL. I have the same problem with other JSON imports for example in this sheet https://docs.google.com/spreadsheets/d/1qfPAjel9H9M88VYouQtOqycRu71eOj60UVnIxkMxy-k/edit#gid=806799454 with this JSON https://covid19.mathdro.id/api/confirmed When I run the ImportJSON function I get this message: Exception: Attribute provided with no value: url (line 126, file "Code") – Johannes Christ Apr 14 '20 at 18:16
  • That's odd. From here, the JSON is loaded. https://framapic.org/fXhwA8MVKjfP/LTHm6PIhP6D4.PNG. You can try the original version of the script ( != forked version) : https://raw.githubusercontent.com/bradjasper/ImportJSON/master/ImportJSON.gs – E.Wiest Apr 14 '20 at 21:38