bradjasper / ImportJSON

Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet
GNU Lesser General Public License v3.0
2.1k stars 1.06k forks source link

How to use "allHeaders" option but only return row 1 (ie 2nd row) #231

Closed ozbrewer closed 2 years ago

ozbrewer commented 2 years ago

I'm calling ImportJSON and need to preserve the order of the json fields, so have to specify allHeaders option. But I only want to return the 2nd row from the urlfetch. Being a relative newbie to coding, I'm struggling to tell importJSON to preserve the order AND return only the 2nd row. Any help on syntax please?

Here is the function I wrote that creates the url (works) and passes it to the importJSON function specifying just the fields I'm looking for to be returned - but the order must be retained.

And yes, my cacheservice lines are commented out as I'm struggling to get them to work as well.

Thanks

**
 * Script to pull Yahoo Finance stats and extended financial data into 2 arrays
 */

function YFTechnicals(symbola) {
  symbola = symbola || "CBA.AX";
  symbola = encodeURI(symbola); 
//  Utilities.sleep(Math.floor(Math.random() * 5000))

// Turn it into a URL
  var url1 = 'https://query2.finance.yahoo.com/v7/finance/options/'+ symbola;
  Logger.log(url1);
//  return url1;

// Create a cache service
//  var cachetech = CacheService.getUserCache();
//  var cachedtech = cachetech.get("technicals");
//  if (cachedtech != null) {
//    return cachedtech;
//  }
//  var resulttech = ImportJSON(url1,"/optionChain/result/quote/twoHundredDayAverage,/optionChain/result/quote/fiftyDayAverage,/optionChain/result/quote/trailingPE,/optionChain/result/quote/forwardPE,/optionChain/result/quote/bookValue,/optionChain/result/quote/priceToBook,/optionChain/result/quote/trailingAnnualDividendRate","noInherit,noTruncate,noHeaders");
//  var contentstech = resulttech.getContentText();
//  cachetech.put("technicals",contentstech,1800); // cache for 30 minutes
//  return contentstech;
//  Logger.log(contentstech);

// Original function to get data (uncached)
 return ImportJSON(url1,"/optionChain/result/quote/twoHundredDayAverage,/optionChain/result/quote/fiftyDayAverage,/optionChain/result/quote/trailingPE,/optionChain/result/quote/forwardPE,/optionChain/result/quote/bookValue,/optionChain/result/quote/priceToBook,/optionChain/result/quote/trailingAnnualDividendRate","noInherit,noTruncate,allHeaders,row==1");
// Logger.log(ImportJSON(url1,"/optionChain/result/quote/twoHundredDayAverage,/optionChain/result/quote/fiftyDayAverage,/optionChain/result/quote/trailingPE,/optionChain/result/quote/forwardPE,/optionChain/result/quote/bookValue,/optionChain/result/quote/priceToBook,/optionChain/result/quote/trailingAnnualDividendRate","noInherit,noTruncate,allHeaders"));
}
ozbrewer commented 2 years ago

Can close this issue, Learned that I can call the function from google sheets using Index and return only the 2nd row. I now call the function as follows: =INDEX(YFTechnicals(CBA.AX),1,0)