Populating sheets with API data using a Fiddler

Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API. The examples use the Itunes API which will return tabular data as an array of JSON objects.

This class - Fiddler can be found in my cUseful library.

Here's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j


Populating an API with all fields from a JSON object

To start with, we need get some data from an API, and open a sheet to dump the data to.
  // lets get some data from an API
  var result = getFromItunes ("REM");
  if (!result.ok) throw result.content;

  // either create or use an existing sheet
  var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  var sheet = ss.getSheetByName ("itunesapi") || ss.insertSheet("itunesapi");
  
Now This is easy, as we can just let fiddler do its thing. It's a one liner of 3 methods chained together  
  // first of all, create a sheet with all the data, clearing it first
  new cUseful.Fiddler(sheet.clearContents())
    .setData (result.data.results)
    .dumpValues();

And you'll get this.

Populating API with some fields from an API

In this case we want just a couple of fields. It's almost exactly the same, except we filter out the columns we don't want.
// we'll use fiddler to organize the data, and populate with selected fields from the API
  // after first clearing the sheet
  new cUseful.Fiddler(sheet.clearContents())
     // use the data from the API
    .setData (result.data.results)
    // but just keep a couple of the columns
    .filterColumns (function (column) {
      return ["artistName","collectionName","trackName"].indexOf(column) !== -1;
    })
    // and write it out
    .dumpValues();

Now this is the result


Adding to an existing sheet

Now it get's a little more complicated. Rather than specifying the fields required, this time we'll use whatever fields are already on the sheet, and append the new data to whatever is already there. 

First get some additional data to append
  // Now if the columns are already set
  var result = getFromItunes ("Lady Gaga");
  if (!result.ok) throw result.content;

Now append it using these steps
  1. get the current data from the sheet into a fiddler
  2. get the column names
  3. filter the data returned from the API to reduce it to just the properties that match the column headings
  4. insert those at the end of the fiddler, and dump the values
 
  //1 get the current data
  var fiddler = new cUseful.Fiddler(sheet);
 
  //2 these are the existing columns
  var columns = fiddler.getHeaders();
  
  //3 this is the data with the stuff we dont want filtered out
  var filteredData = result.data.results.map (function (row) {
    return Object.keys(row).reduce (function (p,c) {
      if (columns.indexOf (c) !== -1) {
        p[c] = row[c];
      }
      return p;
    },{});
  });
  
  //4 use the data from the API to insert at the end
  fiddler.insertRows (undefined ,filteredData.length , filteredData)  
  
  // and write it all out
    .dumpValues();

The result - notice the new data added to the end, but only the columns that already existed



The Itunes API

Here's function used to so an Itunes search. It's only set to get the first 50  for the purposes of this demo and will need pagination to get more. 

// generaral itunes search
  function getFromItunes (searchTerm) {
  
    var response = cUseful.Utils.expBackoff( function () {
      return UrlFetchApp.fetch("https://itunes.apple.com/search" + "?term=" + encodeURIComponent(searchTerm), {
        muteHttpExceptions:true
      });
    });
    
    return response.getResponseCode() === 200 ?  {
      data: JSON.parse (response.getContentText()),
      ok:true
    } : {
      ok:false,
      code:response.getResponseCode(),
      content:response.getContentText()
    };
    
  }

For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.





Comments