Pappmaskin.no : Aka Morten Skoglys nerdcave
×

Quickest way to poll a changing json and update a row in a Google spreadsheet

november 29th, 2017 by

Aka "a poor mans database".

A user asked for a playlist for one of our streaming radiochannels. We have no official playlist for that channel, and being pressed for time I hastily created a Google Spreadsheet and added a simple script for polling a json-script. It uses Google's inbuilt trigger solution for running the script every five minute. The script lacks any checks and failsafes and is meant for demo purposes.

Tip: Use http://www.jsonquerytool.com/ to figure out how to query your json.

Todo: Parse unix timestamp to make it human readable

function pullJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var url="http://psapi.nrk.no/channels/urort/liveelements/now"; // Paste your JSON URL here
  
  var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText()); //
  var dataSet = dataAll;
  
  var rows = [],
      data;
  
    //wrap in for if you need to at more rows per pass
    data = dataSet[1]; //spesific for my json. Only needs the value for currently playing. 
    rows.push([data.relativeTimeType, data.title,data.description,data.startTime,data.duration]); //change this to match your JSON entities 


  sheet.insertRowBefore(1).getRange("A2:E2").setValues(rows); //change D2 to match the number of entities you need to insert

}

Does it grow with a new line every five minutes? Yes it does!


(External link)

Posted in Code