-1

I have a list of around 500 foundations and companies and i need to fetch more data like address, description, lat long, email or phone number, from the google and FB APIs I have found something more or less like this, but it won't work in OpenRefine, i need some help please.

I think it is possible to do beacause there is an addon called spotlight for spreadsheets that is able to fetch Google URLs and Lat Long https://www.youtube.com/watch?v=Vj1v3QfyrgA&feature=youtu.be

https://maps.googleapis.com/maps/api/place/findplacefromtext/output?parameters/json?input=value&inputtype=textquery&fields=photos,formatted_address,name,rating,email,geometry&key=YOURKEY

I have created the FB and Google places and google staic map API already. I have tried different queries, but it won't work unless it is in GREL

value.parseJson().geonames[0].lng
value.parseJson().geonames[0].geonameId
value.parseJson().geonames[0].lat
He11bran
  • 3
  • 3
  • 1
    Can you share more information about what you tried (what steps did you do in OpenRefine) and what results you got? Did you get any results from the API? A screenshot of the results in OpenRefine might also be helpful to understand the problem – Owen Stephens Jul 24 '19 at 08:33
  • I been trying with: (Create column GMapsR240719 at index 1 by fetching URLs based on column institucion using expression grel:"http://ajax.googleapis.com/ajax/services/language/detect?v=1.0&key=&q=" + escape(value.substring(0,128),"url") And been getting in two different rows, firstable something like: http://maps.googleapis.com/maps/api/geocode/json?sensor=false&Institución=%C2%BFSab%C3%ADas+que%3F And in the second column something like: { "error_message" : "This API project is not authorized to use this API.", "results" : [], "status" : "REQUEST_DENIED" } – He11bran Jul 24 '19 at 23:59

1 Answers1

0

From the error "This API project is not authorized to use this API" the first thing to check is that you have enabled the Places and/or Geocoding API in the Google Developer console, and you have setup an API Key and that it is showing as a credential for the Places API and/or the Geocoding API

Next step, take OpenRefine out of the equation and check you can get a simple API call working in your browser - using one of the Google Places API examples:

https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=Museum%20of%20Contemporary%20Art%20Australia&inputtype=textquery&fields=photos,formatted_address,name,rating,opening_hours,geometry&key=<INSERT YOUR API KEY HERE>

If that gives you the same error, you know the issue is not with OpenRefine.

If you are still having problems in OpenRefine make sure you are creating URLs in OpenRefine that are valid for the appropriate Google API based on the documentation for that API and that you are using the correct API key.

Owen Stephens
  • 1,550
  • 1
  • 8
  • 10
  • Thank you very much sir @owen Stephens, you were absolutely right, there were some issues with the API Key, i configured it differently and it worked like a charm, i have now the Json from every location i was looking for. Now I have to parse it and clean it into several different columns that i mentioned before, I really appreciate your answer. It was pretty helpful. If you have some reference to do the parsing in the best way i would love to read from you. I am currently working into it, but I am not sure if my way is gonna be the best. Regards and thank you. – He11bran Jul 27 '19 at 18:13
  • I am currently using [ value.parseJson().results[0].geometry.location.lat] But I'm getting the error: Error: parseJson failed: Unexpected character ('<' (code 60)): expected a valid value (number, String, array, object, 'true', 'false' or 'null') at [Source: (StringReader); line: 1, column: 2 – He11bran Jul 27 '19 at 18:48
  • @He11bran I'm sure I can help, but will need to know which Google API Call you are using and ideally an example of one of your results in order to see what is going wrong – Owen Stephens Jul 28 '19 at 14:14
  • I have gotten some results like: Google Maps ..... I used the: Cloud SQL Cloud Storage Google Cloud APIs Google Cloud Storage JSON API Places API Maps Static API BigQuery API Service Usage API to get the Json, and the GREL [value.parseJson()] and getting the Error: parseJson failed: Unexpected character ('<' (code 60)): .. – He11bran Jul 29 '19 at 18:25
  • You are getting back HTML not JSON so using parseJson in OpenRefine will not work. Can you give an example of a URL you used to obtain this result (omitting your api key and any other private information)? – Owen Stephens Jul 31 '19 at 06:46
  • I am trying to fetch the Json from the Google API places using the query `https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=value&inputtype=textquery&fields=place_id&key=YOUR_API_KEY` But I don't seem to get much results, can you guys help me to find the right Regular Expressions to do fetch the most amount of data in Json format for the proyect I am currently working on, I have the names of the institutions, and the HGoogle API, i want to get the data using OpenRefine, please help. BTW, haven't been able to replicate the fetching of the HTML using these same tool – He11bran Aug 20 '19 at 01:19
  • Mr @Owen Stephens I have found the code used in openrefine for the HTML that i got, it goes something like this: GREL `https://www.google.com/maps/search/?api=1/ajax/services/language/detect?v=1.0&key=&q=" + escape(value.substring(0,128),"url")` with these code i got something like: – He11bran Aug 21 '19 at 23:36
  • I think you need to investigate the correct use of the Google map APIs - I'm afraid that isn't something I'm very knowledgable about. I would suggest making sure you've read all the relevant documentation and look for some tutorials on using these. At that point if you are still not able to get the results you require you can raise appropriate questions about the use of the APIs. Until you have this sorted you won't be able to successfully use OpenRefine to extract the relevant information from the data returned from the API – Owen Stephens Sep 05 '19 at 09:43