CODEWITHSUNDEEP
javascriptjsongoogle-apps-scriptgoogle-sheets
user1903663
user1903663

Reputation: 1725

how can I loop through a unciode json string in javascript?

I get the following string returned by the CBOE options api:

{u'inputs': {u'stock_price_max': 50.0, u'high_low_stock_max': None, u'time_frame': u'middle', u'hv30_max': None, u'high_low_stock_min': None, u'symbols': None, u'hv30_min': None, u'low_strike': 3.0, u'high_strike': 4.0, u'industry_codes': None, u'spread_ask_price_max': None, u'stock_price_min': 10.0}, u'output': [{u'stock_price': 43.2, u'stock_hi_lo_percent': 72.9651, u'symbol': u'EWZ', u'industry_code': 55501010, u'max_gain': 0.52, u'high_strike_otm_percent': 0.463, u'low_strike_otm_percent': 2.7778, u'spread_ask': 0.48, u'spread': u'43/42 Put', u'expiry': u'2019-04-18', u'max_gain_to_spread_ask_percent': 108.3333, u'hv30': 27.3836}, {u'stock_price': 41.37, u'stock_hi_lo_percent': 21.7957, u'symbol': u'FXI', u'industry_code': 55501010, u'max_gain': 0.26, u'high_strike_otm_percent': 0.8944, u'low_strike_otm_percent': 2.103, u'spread_ask': 0.24, u'spread': u'41/40.5 Put', u'expiry': u'2019-05-17', u'max_gain_to_spread_ask_percent': 108.3333, u'hv30': 20.2925}

I want to loop through it and place elements into cells in a Google spreadsheet. I have this code:

function myFunction() {
  var response = UrlFetchApp.fetch(endpoint);
  var data = response.getContentText();
  sheet.getRange("A8").setValue(data);
}

This puts the entire string into cell A8.

I have tried to loop through the string with 

for (i = 0; i < jsonlen; i++) {
    sheet.getRange("A:A").setValaue(data['output']['symbol']);
}

This returns "undefined". So problems are: 1) how can I extract the elements I need form the "output" part of the string 2) put the symbols into A3, A4 etc then stock_price into B3, B4 etc 3) how to identify the length of the string in order to make the loop work correctly until the string has been entirely looped over?

Many thanks!

Upvotes: 1

Views: 75

Answers (1)

Tanaike
Tanaike

Reputation: 201513

  • You want to retrieve the values of symbol and stock_price the property of output and want to put them to the columns "A" and "B" of the active Spreadsheet, respectively.
  • You want to put the values from the row 3.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • the property of output has an array. And symbol and stock_price are in the array.
    • So at first, it is required to prepare the values for putting to Spreadsheet.

Modified script:

function myFunction() {
  var response = UrlFetchApp.fetch(endpoint);
  var data = JSON.parse(response.getContentText());

  var values = data.output.map(function(e) {return [e.symbol, e.stock_price]});
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(3, 1, values.length, values[0].length).setValues(values);
}
  • In this case, the values are put to the row 3 of column "A" and "B" on the active sheet.

Note:

  • In your case, if data is actually the string value of {u'inputs': {###}, u'outputs': [###]} which has the unicode literal of python 2, u is required to be replaced. So in this case, please put data = JSON.parse(data.replace(/u\'|\'/g, "\"").replace(/None/g, "null")); before var values = data.output.map(function(e) {return [e.symbol, e.stock_price]});. Please be careful this.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 1

Related Questions