A simple Google Apps Script RestQuery

In GAS Rest Library migration, you can see a port of Rest to Excel library from VBA to Google Apps Script. This handles many complex queries and generally can execute them in a few lines of code using the libraries – see Using the mcpher library in your code. GAS is more suited to this kind of work, and it’s kind of suboptimal to do things in a VBA way – unless you are planning to support both platforms with the same apps. 


Here’s a simple GAS restquery without any dependencies. The objective for this example is to use the Wikipedia stats API (stats.grok.api), to concatenate a bunch of queries into a single Google Sheet. 


The grok data looks like this. I chose this since it’s a little funky, in that it uses data values as key names, so we have to do a little bit of unusual coding compared to normal rest query response data.

{ "daily_views": {
        "2014-01-07": 13763,
        "2014-01-04": 9673,
        "2014-01-05": 1533,
        "2014-01-02": 10111,
        "2014-01-03": 10558,
        "2014-01-13": 13507,
        "2014-01-01": 9588,
        "2014-01-12": 11835,
        "2014-01-08": 13685,
        "2014-01-09": 13534,
        "2014-01-11": 10200,
        "2014-01-10": 12154
    },
    "project": "en",
    "month": "201401",
    "rank": 255,
    "title": "london"
}
Page Content hide
1 The results
2 The code

The results

After creating an empty sheet called ‘grok’, I run the function testGroks() and get this

The code

// get wiki stats
function testGroks() {

  // we'll use these headings
  var headings = ["month", "title","date", "dailyViews"];
  
  // run these queries
  var queries = [ {month:"201308", title: "London"} ,{month:"201308", title: "Paris"} ];
  
  // build up an array of results
  var values = [];
  
  // append each query to the last
  for (var j = 0 ; j < queries.length ; j++ ) {
    values.push.apply (values,testGrok (headings, queries[j]));
  }
  
  // clear the sheet
  var ss= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("grok");
  ss.getDataRange().clearContent();
  
  // need to extend the sheet if necessary
  var howMany =  ss.getMaxRows() - values.length  ;
  if (howMany < 0 ) ss.insertRowsAfter(ss.getMaxRows(), -howMany);
  
  var howMany = ss.getMaxColumns() - headings.length ;
  if (howMany < 0 ) ss.insertColumnsAfter(ss.getMaxColumns(), -howMany);
  
  // add the headings
  ss.getRange(1, 1,1,headings.length).setValues([headings]);
  
  // and the data
  if (values.length) { 
    ss.getRange(2, 1, values.length,headings.length).setValues(values);
  }
 
  // called for each query
  function testGrok (headers,q) {
    var v= [] , url = "http://stats.grok.se/json/en/";
    
    // construct URL and do the rest query
    var response = UrlFetchApp.fetch(url + q.month + "/" + q.title);
    var data = JSON.parse(response.getContentText());
    var results = data["daily_views"];
    
    // strangely, the data returns date values as keys, rather than members of an array
    for ( var k in results) {
      // date format is yyyy-mm-dd - convert so we can parse
      var r = [], y = parseInt(k.slice(0,4),10), m =  parseInt(k.slice(5,7),10) , d = parseInt(k.slice(8,10),10); 
      r[headers.indexOf("date")] = new Date(y, m - 1, d);
      r[headers.indexOf("dailyViews")] = results[k];
      r[headers.indexOf("month")] = data.month;
      r[headers.indexOf("title")] = data.title;

      // another row
      v.push (r);
    }
    return v;
  }
}

For more like this, see  From VBA to Google Apps Script . Why not join our forum.