0

What is the JavaScript equivalent of ImportXML?

Have a Google spreadsheet of 10,000 rows that each contain a city and state. To find their zip code, neither the ImportXML formula nor LibreOffice's FilterXML have worked.

citizen
  • 353
  • 3
  • 13
  • have a look at this [post](http://stackoverflow.com/questions/4143901/access-google-spreadsheet-or-google-data-api-only-with-javascript) – Michael Andorfer Jun 26 '16 at 15:11
  • Thanks, @mian, but I am trying to write a Google Apps script that replicates the ImportXML functionality. Because Google Apps scripting replicates JavaScript, I am looking for a JavaScript alternative to ImportXML. – citizen Jun 26 '16 at 16:08
  • 1
    This is a classic [XY Problem](http://meta.stackexchange.com/a/66378/206345). Do you really want to know the JS equivalent of ImportXML? (There isn't _one_, there may be many... ImportXML does a lot, and you could write an equivalent service in JavaScript. Etc.) OR, do you just want a way to get the (primary?) zip code of many thousands of cities, in a Google Spreadsheet? Do you need to know how to access an external service (UrlFetch) or parse the returned XML (XML Service)? Or would an answer [like this](http://stackoverflow.com/a/16860598/1677912) help? – Mogsdad Jun 26 '16 at 18:30
  • can you please share what site your trying to pull the importxml in with? or which source your wanting to get the zip from? Cities have multiple zips so if its only based on city,state do you want back a list of the zips in a city? – Aurielle Perlmann Jun 26 '16 at 23:23
  • Here is the spreadsheet command (which is limited to 50 entries), @AuriellePerlmann. Formulas not shown take the first zip code listed. `IMPORTXML(concatenate("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&city=",SUBSTITUTE(E2," ","%20"),"&state=",F2),"//span[contains(@class, 'zip')]//text()")` – citizen Jun 27 '16 at 01:12

1 Answers1

3

Ok so to answer you question I have both includes a simple apps script on getting the piece of data you want and also a simpler importxml call that does work with the option to return only 1 or all of them:

for importxml it says 50 calls but honestly I used to use importxml for everything and you can load much more than that, they just load as the data returns, so not in order but you can do more than 50 easily

to simplify your import xml first here is a modification to the construction of the url string:

=SUBSTITUTE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&city="&E2&"&state="&F2," ","+")

You can still use a %20 if you want instead of the + but i switched it only because that is what the usps site defaults to, i tested both though and both work.

Here I was using instead of concatenate you can you & which effectively does the same thing but is easier for readability. Then you can wrap the whole thing with your substitute to catch any and all spaces in your concatenated data.

Ill add an image below where you can see the output in this order:

To pull in the list as a semicolon delimited in one cell:

=JOIN(";",IMPORTXML(SUBSTITUTE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&city="&E2&"&state="&F2," ","+"),"//*[@class='zip']"))

To get only the first zip code listed:

=SPLIT(IMPORTXML(SUBSTITUTE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&city="&E2&"&state="&F2," ","+"),"//*[@class='zip']/text()")," ")

For the ability to pull in all the related zip codes (as a list):

=IMPORTXML(SUBSTITUTE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&city="&E2&"&state="&F2," ","+"),"//*[@class='zip']")

and finally if you really want to still use this as an app script, here is a simple method:

  function zipcodes(url) {
  var found, html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html) content = html.match(/<span class="zip" style="">(\d+)<\/span><span class="zip/gi)[0].match(/zip" style="">(\d+)<\/span>/i)[1];
  }
  return content;
}

enter image description here

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26