Fetch External Resources with Google Apps Script

You can use the class UrlFetchApp to let your script communicate with other hosts over the Internet, and fetch resources by fetching URLs. Your script can use the URL Fetch service to issue HTTP and HTTPS requests as if it were itself a browser. 

You can use this to grab information for your Google Docs, Sheets etcetera from an enormous range of online APIs providing data about weather, travel, entertainment, financial data and much, much more.

The general syntax of such a call is:

var response = UrlFetchApp.fetch(url [, options]);

The url parameter is, unsurprisingly, the URL of the service being called; the optional parameter options (which I won’t use in this example) can supply further information where it’s needed by the remote service, and is often used where authentication is required.

Let’s demonstrate the class by using the Telize (www.telize.com) GeoIP API. This is an online service that will return information about the calling IP address.

Here’s the script:

function getIpInfo() {
 var url = "http://www.telize.com/geoip";
 var result = UrlFetchApp.fetch(url);
 var out = Utilities.jsonParse(result.getContentText());
 var doc = SpreadsheetApp.getActiveSpreadsheet();
 var cell = doc.getRange('a1');
 var index = 0;
 for (var i in out) {
   var value = out[i];
   cell.offset(index, 0).setValue(i);
   cell.offset(index, 1).setValue(value);

This API, like many others, uses the JSON format for the object it returns; here’s an example raw result, gained by calling the service from my own browser here in Spain (of course, since the script runs on Google’s servers, the IP address information returned when I run the script will refer not to my own PC, but to the Google server making the call):

{"country":"Spain","dma_code":"0","area_code":"0","ip":"","asn":"AS6739","continent_code":"EU","isp":"Cableuropa - ONO","longitude":-4,"latitude":40,"country_code":"ES","country_code3":"ESP"}

In the script I’ve used the Utilities.jsonParse() service to decode the response and write it into the spreadsheet.

When first run, the script asks for authorization:

Click image to enlarge

Click image to enlarge

After that’s been accepted, the script can gather and return the information. Here’s the result:

Click image to enlarge

Click image to enlarge

You can use similar techniques to access external resources and services. The responses may not always be in the JSON format, like this example; the response could be XML, plain text, or another proprietary format, but Apps Script has plenty of tools to allow you to wrangle most data into a useable form.

UPDATE: As +Riël Notermans has correctly pointed out, the line:

var out = Utilities.jsonParse(result.getContentText());

would be better if it were:

var data = JSON.parse(result.getContentText());

as Utilities.jsonParse() still works but is now deprecated.


Leave a Reply

Your email address will not be published. Required fields are marked *