Does Google Sheets have an equivalent functions to WEBSERVICE and FILTERXML in Excel?

user_7438857 0
Viewed 640

Context: I am trying to calculate the distance between two places using either Bing Maps or Google Maps.

There is a spreadsheet here which demonstrates what I'm trying to do:

https://drive.google.com/file/d/1TsmIPDUrCOkScNqPmiVrYVXb1SRzP9Vl/view?usp=sharing

I want to return a response in a single cell from a function (that works in the same way as WEBSERVICE in Excel, but in Google Sheets instead) that calls a dev.virtualearth.net URL with a query, such as the following URL:

https://dev.virtualearth.net/REST/v1/Locations?countryRegion=New Zealand&adminDistrict=Wellington&locality=Johnsonville&postalCode=6037&addressLine=Bassett Rd&maxResults=1&o=xml&key=ArTkC_A3xj__NuzbJVV2Lq9oo8sftT1r9peJOlYpWVdvTJWDzn1HBq22A48Pn9W-0

I can see that there are functions such as IMPORTDATA, IMPORTXML, but these don't work in exactly the same way as the WEBSERVICE function in Excel.

I also need an equivalent function to FILTERXML.

google-sheets-api google-sheets web-services excel
1 Answers

As you can see, in the xml file, there is a link for a 'namespace' : xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1". You then will have to fetch data within [local-name() ='xxxxx']. As an example, put in C24

=transpose(IMPORTXML(C19,"//*[local-name() ='Point']"))

and you will get both Lat and Lon at the same time You can also retrieve all informations by

=IMPORTXML(C19,"//*")

See cells with yellow background https://docs.google.com/spreadsheets/d/1N4G_0WUQfBStCzbG3lkcAPhVAhL5EcYJbOTTIXwMswc/edit?usp=sharing

user_150193800
Related