CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Colten
Colten

Reputation: 11

Parsing JSON Returned Data in Google Scripts

I am new at coding on Google Scripts, and I am trying to understand how to move the data I am receiving from a JSON formatted file into a spreadsheet.
I am attempting to pull financial data, but if I could process the simple example below it would help me know how to code for all other JSON formats I am encountering.

I know the basics.
I can link the spreadsheet and I can parse it out using the two commands below.
It is the step after that where I push the data to a spreadsheet I am having difficulty with.

var responseAPI = UrlFetchApp.fetch(url); 
var parcedData = JSON.parse(responseAPI.getContentText());

I would guess I would take the sample file below and put the Months in column A.
The Survived Flag in column B.
The bills/other tag in column C.
The expense description in column D, and the expense amount in column E.

{"JANUARY": {
   "bills":[
     ["Electric",122.46],
     ["Credit",155.44],
     ["Mortgage",440.05]],
   "other":[
     ["Food",188.33],
     ["Clothes",89.28]],
   "Survived":"Y"},
 "FEBRUARY":{
   "bills":[
     ["Electric",129.46],
     ["Credit",155.44],
     ["Mortgage",440.05],
     ["Car",298.77],
   "other":[
     ["Food",218.33],
     ["Clothes",49.28]],
   "Survived":"N"},
 "MARCH":{
   "bills":[
     ["Electric",119.46],
     ["Credit",155.44],
     ["Mortgage",440.05]],
   "other":[
     ["Food",218.33],
     ["Clothes",49.28],
     ["Insurance",250.98],
   "Survived":"Y"}}

Yes, I would like some help with the script, but also, if you could provide an explanation of what you are doing and how it works that would be great so I can walk myself through it next time.

Thanks!
Colten

Upvotes: 0

Views: 4606

Answers (2)

Colten
Colten

Reputation: 11

This is close to what I want to do, or as close as I can get. I used the actual JSON file I want to pull from for this example. I do not know how to get around the Hardcoding of the Key. I will have to keep researching. I also want to either Append other currency values all the way down the sheet, or create new sheets for each one automatically. This is a start though.

// Pull data and populate screadsheet
function pullJSON() {

  var ss = SpreadsheetApp.openById('Your Spreadsheet ID Here')
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  // Clear Columns A through E
  sheet.getRange('A2:E20000').clearContent();

  var url="https://poloniex.com/public?command=returnOrderBook&currencyPair=ALL";
  var response = UrlFetchApp.fetch(url); // get feed
  var json = JSON.parse(response.getContentText()); //


  var currency = "BTC_XMR";
  var asks = [];
  var bids = [];
  asks.push(['askRate', 'askAmount']);
  bids.push(['bidRate', 'bidAmount']);
  for(var key in json.BTC_XMR.asks)
  {
    asks.push(json.BTX_XMR.asks[key]);
    bids.push(json.BTC_XMR.bids[key]);
  }

  askRange = sheet.getRange(1, 2, asks.length, 2);
  askRange.setValues(asks);
  bidRange = sheet.getRange(1, 4, bids.length, 2);
  bidRange.setValues(bids);
  currencyRange = sheet.getRange(1, 1, asks.length, 1);
  currencyRange.setValue(currency);

}

Upvotes: 1

Serge insas
Serge insas

Reputation: 46794

I was curious to see how to get all the properties of the objects because their structure is not that simple so I gave it a try, not sure that I'm right but it could be a good starting point.

Below is the code, the first part of it is simply to build an object with the same structure , the second part parses it and builds an array that is directly written into the spreadsheet.

I only used one month in my test, but that should not be an issue, it should read all the months in the object. `` Feel free to comment /criticize if I'm wrong, as I said it was my curiosity that makes me try it ;)

(there are many logs here and there, I use it to check where I am...and to let you see yourself how it goes)

function myFunction() {
  var obj = {};
  var atr = {}
  atr["bills"] = [["Electric",122.46],["Credit",155.44],["Mortgage",440.05]];
  atr["other"] = [["Food",188.33],["Clothes",89.28]];
  atr["Survived"] = "Y";
  obj["JANUARY"] = atr;
  Logger.log("obj = "+JSON.stringify(obj)) ; 
  var ssData = [];
  var keys = Object.keys(obj);
  Logger.log("keys = "+JSON.stringify(keys));
  for (var k in keys ) {
    var atr = Object.keys(obj[keys[k]]);
    Logger.log("atr of keys = "+JSON.stringify(atr));
    var row = [];
    row.push(keys[k]); // the month in first column
    for(var a in atr){
      Logger.log("atr[a] = "+atr[a]);// that's the  key (bills, survived...)
      var val = obj[keys[k]];// that ais the value of each key
      Logger.log("val[atr[a]] = "+JSON.stringify(val[atr[a]]));
      row.push(atr[a]+" = "+val[atr[a]]);// the values are written in each cell withe their keys
    }
    ssData.push(row);// each row is pushed in the final array
  }  
  SpreadsheetApp.getActiveSheet().getRange(1,1,ssData.length, ssData[0].length).setValues(ssData);
}

Upvotes: 1

Related Questions