Performing HTTP GET/POST from Google Spreadsheets

When I manage a project, I often use Google spreadsheets for quick project calculations and projections (budgets, date calculations for timelines, team utilization / composition, etc). I built my business days calculator tool to help with the various date calculations required during project scheduling, but it would be nice to be able to make the business days calculation directly from a Google spreadsheet. It’s fairly straightforward to make external calls to web services with HTTP GET / POSTs directly from a Google spreadsheet. Here’s how:

1. Open your Google spreadsheet.

2. Click on Tools, Script editor

script editor

3. Add the following code:

function urlTest1() {
 var response = UrlFetchApp.fetch("http://httpbin.org/user-agent");
 return response.getContentText();
}

The httpbin.org site is an excellent testing tool. The URL I used above will return the user-agent string in JSON format.

4. Go back to the spreadsheet, and call the custom function:

script-result

Success!

The above example is performing an HTTP GET request. If you need some POST some data, Google’s got you covered. We’ll POST some data, again using httpbin.org:

function urlTest2() {

  var payload =
   {
     "foo" : "bar",
     "date": "2014-08-09"
   };

   var options =
   {
     "method" : "post",
     "payload" : payload
   };

   var response = UrlFetchApp.fetch("http://httpbin.org/post", options);
  
   return response.getContentText();
}

Calling the above method in the spreadsheet yields:

script_both_tests

The above examples are all derived from Google’s documentation.

These external calls to web services are extremely useful. It would be very easy to build up a client dashboard or report that relied on some external data using these methods. Just retrieve the data with UrlFetchApp.fetch, parse the return values as desired, and build custom charts, graphs, or reports in Google spreadsheets with the values.