2

I'm trying to pull the latitude/longitude from an API call to Google Maps. The code I am using is the following:

var text gMaps = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & URLEncode(SearchAndReplace([Related Staff - Address]," ","+")) & "&key=xxxxxxxxxxxxxxxxxxxx";

var text lat = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord" & "&rid=" & [Record ID#] & "&apptoken=xxxxxxxxxxxxx" & "&_fid_113=";    

    "javascript: {" &
   
         "$.get('" & $gMaps & "',function(data,success) {" &
            "console.log(data);" &
            "var geo = data.getElementsByTagName('geometry')[0].getAttribute('lat');"  &
            "console.log(geo);" & 
            "$.get('" & $lat & "'+geo);" &
            "window.location.reload();" &
        "});" &
"}"

This is for a button in Quickbase to put the info into 2 fields. The example above is only dealing with the latitude for now. This code is giving me a null result, I'm familiar with javascript, but still need a lot of learning.

Here's the xml response that I'm pulling from.

XML

Thanks for any help provided.

Dana

Thadd
  • 21
  • 3
  • Please update your question to provide a sample of the XML _as text_ (not an image) so we may test with real data. – kmoser Aug 18 '20 at 00:20

3 Answers3

0

You can use Javascript's DOMParser and XPath features to extract the lat and lng from an XML string:

let xmlString = `
    <GeocodeResponse>
     <status>OK</status>
     <result>
      <type>sample</type>
      <name>Sample XML</name>
      <location>
       <lat>37.4217550</lat>
       <lng>-122.0846330</lng>
      </location>
     </result>
     <result>
      <message>The secret message</message>
     </result>
    </GeocodeResponse>
`

var parser = new DOMParser();
var xmlDoc = parser.parseFromString(xmlString, "text/xml"); //important to use "text/xml"

var nsResolver = xmlDoc.createNSResolver( xmlDoc.ownerDocument == null ? xmlDoc.documentElement : xmlDoc.ownerDocument.documentElement);

var lat = xmlDoc.evaluate('//lat', xmlDoc, nsResolver, XPathResult.STRING_TYPE, null ).stringValue;
var lng = xmlDoc.evaluate('//lng', xmlDoc, nsResolver, XPathResult.STRING_TYPE, null ).stringValue;

console.log(lat, lng) // 37.4217550 -122.0846330

Also see Parse XML file with JavaScript and plot on Google Maps

kmoser
  • 8,780
  • 3
  • 24
  • 40
  • Thanks kmoser - I think I'm close on my code, it's worked before, but there's something different with this XML that's not quite working. – Thadd Aug 17 '20 at 21:16
0

It doesn't look like you're finishing your lat query string variable. Your variable ends up being "yourdomain.quickbase.com/db/yourdbid?act=API_EditRecord&apptoken=&rid=&_fid_113=" and so fid 113 is never actually assigned a value. Even though you're running the JavaScript below no where is it being told to be assigned to fid 113. Also, it's worth nothing that JavaScript injection is soon going to be a thing of the past in Quick Base so this might be something you want to throw into a code page instead which would be easier anyways because then you could use the geocoder object that the Google Maps API provides.

JMarch
  • 11
  • 1
  • Thanks JMarch - That part is working. For testing I'm using an alert to show the result, which is null. I'm trying to get the geo variable to show the lat for testing, and I can't get that far. Once that works, the part that writes the latitude into the field will work. I've parsed other XML using this formula, but something is not working with this XML. – Thadd Aug 17 '20 at 21:13
  • With my limited knowledge, I feel like it has to do with there being child elements or something like that?? – Thadd Aug 17 '20 at 21:20
0

I would advise to use a Code Page inside of Quick Base instead a formula field for this solution. With a code page you can brig in an XML parser library to handle the response and then make then next API call to Quick Base with the latitude and longitude values. That would be the best practice.

Erich Wehrmann
  • 464
  • 2
  • 11