Get data from webpage using Google Apps Script and Yahoo Query Language

Question

Using Google Apps Script, I've written the following function to extract a piece of information to a spreadsheet:

function myFunction(symbol, elemento) {

  var url   = "http://www.example.com/query?symbol=" + symbol;

  switch (elemento) {
    case 'one':
      var xpath='//*[@id="sectionTitle"]/h1';
      break;
    case 'two':
      var xpath='//*[@id="headerQ"]/div[1]/div/span[2]'
      break;
  }
  var query = "select * from html where url = '" + url + "' and xpath = '" + xpath + "'";

  var yql   = "https://query.yahooapis.com/v1/public/yql?format=json&q=" + encodeURIComponent(query);

  var response = UrlFetchApp.fetch(yql);
  var json = JSON.parse(response.getContentText());

  switch(elemento){
    case 'one':
      return json.query.results.h1;
      break;
    case 'two':
      return ponto(json.query.results.span.content);
      break;
  }
}

Now, this works OK when typing the function into a cell, but "sometimes" I get the error #ERROR! in a cell with the note:

TypeError: Can't read "h1"  property of null. (line 54).

Deleting that cell and typing the function again usually works.

Why is this function volatile (ie: it does work, but only sometimes)?

Answer 1

You will need to do some error checking with your fetch. Any request over the internet may fail.

If no results are found the results object value will be null. I put in a quick backoff, but you may need to play with the numbers to suit your needs.

var response = UrlFetchApp.fetch(yql);
var json = JSON.parse(response.getContentText());
var backoff = 1;
while((json.query.results == null || response.getResponseCode() != 200)){
  Utilities.sleep((Math.pow(2,backoff)*1000) + (Math.round(Math.random() * 1000)));
  response = UrlFetchApp.fetch(yql);
  json = JSON.parse(response.getContentText());
  backoff++;
}