Parsing JSON Returned Data in Google Scripts

Question

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

The first step is to really think about how you want to structure the data in your spreadsheet. I wouldn't write a single line of code until you are very happy with that. If you change your data layout in the sheet it could mean a complete rewrite of the code. — Spencer Easton, Nov 04 '16 at 13:14
I did specify this in the question, however, I am not tied to that particular layout. Once I get the data into the spreadsheet I can manipulate it with filters/sorts/formulas... but at the moment I cannot get it over there and that is what I am looking for. Also, I don't necessarily want someone to just write the code and I copy it, I want to understand it... I want to learn to fish... that way I can rewrite if needed to give me different results in the future. — Colten, Nov 04 '16 at 14:00
Do you want all expenses in one row? If you want moultiple rows how is that supposed to be handled? especially with bills and other. Do the month labels repeat for every expense? Does the expense type label repeat for every expense? What about the survival? Just saying what's in each column doesn't really specify the format. — Robin Gertenbach, Nov 04 '16 at 15:10
Let's ignore Survived if that is easier. I think it just makes things difficult and is not really necessary, though it is there in the JSON data. I would like the Month in Column A. Column B would contain "bills" or "other", Column C would say "Electric" or "Food" or "Insurance." Column D would contain the amount. I am not really set on this layout. Whatever is easiest and makes sense will work. — Colten, Nov 04 '16 at 16:39
Possible duplicate of [Import JSON data into Google Spreadsheet](http://stackoverflow.com/questions/2658980/import-json-data-into-google-spreadsheet) — Rubén, Nov 05 '16 at 16:21

Serge insas · Answer 1 · 2016-11-04T23:17:35.823

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);
}

Thanks Serge, this got me really close. [link](https://docs.google.com/spreadsheets/d/1Xff0RmLzcdQvzlQG-WbMyKUtVZW5u6On_TaVn57iwv8/pubhtml?gid=0&single=true) As you can see it pulled out the text, but it did not put the data into rows. It put all the detailed text into B1 & C1. Is there a way to get it to do that? Ideally I would like five rows when I am done with January. 1 row for each amount. — Colten, Nov 05 '16 at 01:50
I was able to take your code and put the cells into CSV format... all the data in one cell. I am trying now to break that data down from CSV into a table with a row for every 2 values. For example, my CSV cell is 1 string like **Electric, 122.46, Credit, 155.44, Mortgage, 440.05**. I am trying to take that 1 cell and make it into 3 rows. — Colten, Nov 05 '16 at 03:51
could you post a picture of the expected result ? I'm not sure I understand exactly how the layout is supposed to look like.Also, the link above shows data that don't look like data in your question so an example of the actual data you are using would be great. — Serge insas, Nov 05 '16 at 08:43
Sure, here is the [Spreadsheet Results](https://docs.google.com/spreadsheets/d/1niMLbY8MePNqq00YG4tfIZiGVp-uX8vl_gVr8Xl9tAk/pubhtml?gid=0&single=true) I would like to see when it is done. The data in the link above is the actual data I am going to want to pull apart eventually, and there is a lot of it. You can see from looking at it why I wanted to learn a simple example first. Thanks for helping! — Colten, Nov 05 '16 at 10:34
@Colten: Please add the relevant information about your question on the question itself. — Rubén, Nov 05 '16 at 16:18

score 1 · Answer 2 · edited Nov 05 '16 at 16:16

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);

}

Parsing JSON Returned Data in Google Scripts

2 Answers2