Google Apps Script: How to retrieve values from deeply nested object having nulls and empty elements-CodePudding

Trying to retrieve {raw} values from all {type} keys in the object of this URL. @Tanaike made this code which worked well when all of {type} keys have clean data without any missing or null. But it didn't work when some of {type} keys have null elements or are empty, getting an error:

TypeError: Cannot read properties of null (reading 'reportedValue')

In @Tanaike's code below, I like to achieve something like if {type} key doesn't have {raw} value, array = [[type, ''], ....], and remove any elements of null. What should be changed in this line? Thank you!

var array = obj.timeseries.result.map(o => types.flatMap(type => o[type] ? [type, ...o[type].map(({ reportedValue: { raw } }) => raw)] : []));

function test() {

  var url = 'https://query2.finance.yahoo.com/ws/fundamentals-timeseries/v1/finance/timeseries/CRWD?lang=en-US&region=US&symbol=CRWD&padTimeSeries=true&type=annualTaxProvision,trailingTaxProvision,annualPretaxIncome,trailingPretaxIncome,annualInterestExpenseNonOperating,trailingInterestExpenseNonOperating,annualLongTermDebt,quarterlyLongTermDebt,annualCurrentDebt,quarterlyCurrentDebt,annualCashCashEquivalentsAndShortTermInvestments,quarterlyCashCashEquivalentsAndShortTermInvestments,annualFreeCashFlow,trailingFreeCashFlow&merge=false&period1=493590046&period2=1672980169&corsDomain=finance.yahoo.com'
  
  var obj = UrlFetchApp.fetch(url, { muteHttpExceptions: true }).getContentText();
  obj = JSON.parse(obj);  //Convert strings to object
  var types = obj.timeseries.result.flatMap(({ meta: { type } }) => type);
  var array = obj.timeseries.result.map(o => types.flatMap(type => o[type] ? [type, ...o[type].map(({ reportedValue: { raw } }) => raw)] : []));
  array = Object.keys(array).map(k => [...array[k]]); // Convert object to array
  console.log(array)

}

CodePudding user response：

Looks like there's two different errors that could occur. One for a series missing all of its values and one where the series has null entries.

// A pull of the data at asker's url
var obj = '{"timeseries":{"result":[{"meta":{"symbol":["CRWD"],"type":["annualLongTermDebt"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualLongTermDebt":[null,null,{"dataId":23123,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":7.38029E8,"fmt":"738.03M"}},null,{"dataId":23123,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":7.39517E8,"fmt":"739.52M"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["trailingFreeCashFlow"]},"timestamp":[1667174400],"trailingFreeCashFlow":[{"dataId":26185,"asOfDate":"2022-10-31","periodType":"TTM","currencyCode":"USD","reportedValue":{"raw":5.93996E8,"fmt":"594.00M"}}]},{"meta":{"symbol":["CRWD"],"type":["trailingInterestExpenseNonOperating"]},"timestamp":[1667174400],"trailingInterestExpenseNonOperating":[{"dataId":20064,"asOfDate":"2022-10-31","periodType":"TTM","currencyCode":"USD","reportedValue":{"raw":2.5269E7,"fmt":"25.27M"}}]},{"meta":{"symbol":["CRWD"],"type":["annualFreeCashFlow"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualFreeCashFlow":[{"dataId":26185,"asOfDate":"2019-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":-6.5613E7,"fmt":"-65.61M"}},{"dataId":26185,"asOfDate":"2020-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1.2456E7,"fmt":"12.46M"}},{"dataId":26185,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":2.92723E8,"fmt":"292.72M"}},null,{"dataId":26185,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":4.41095E8,"fmt":"441.10M"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["trailingTaxProvision"]},"timestamp":[1667174400],"trailingTaxProvision":[{"dataId":20145,"asOfDate":"2022-10-31","periodType":"TTM","currencyCode":"USD","reportedValue":{"raw":3.067E7,"fmt":"30.67M"}}]},{"meta":{"symbol":["CRWD"],"type":["trailingPretaxIncome"]},"timestamp":[1667174400],"trailingPretaxIncome":[{"dataId":20136,"asOfDate":"2022-10-31","periodType":"TTM","currencyCode":"USD","reportedValue":{"raw":-1.44422E8,"fmt":"-144.42M"}}]},{"meta":{"symbol":["CRWD"],"type":["annualTaxProvision"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualTaxProvision":[{"dataId":20145,"asOfDate":"2019-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1367000.0,"fmt":"1.37M"}},{"dataId":20145,"asOfDate":"2020-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1997000.0,"fmt":"2.00M"}},{"dataId":20145,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":4760000.0,"fmt":"4.76M"}},null,{"dataId":20145,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":7.2355E7,"fmt":"72.36M"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["annualPretaxIncome"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualPretaxIncome":[{"dataId":20136,"asOfDate":"2019-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":-1.3871E8,"fmt":"-138.71M"}},{"dataId":20136,"asOfDate":"2020-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":-1.39782E8,"fmt":"-139.78M"}},{"dataId":20136,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":-8.7869E7,"fmt":"-87.87M"}},null,{"dataId":20136,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":-1.60023E8,"fmt":"-160.02M"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["quarterlyCashCashEquivalentsAndShortTermInvestments"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"quarterlyCashCashEquivalentsAndShortTermInvestments":[null,null,null,{"dataId":23033,"asOfDate":"2021-10-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":1.907508E9,"fmt":"1.91B"}},{"dataId":23033,"asOfDate":"2022-01-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":1.996633E9,"fmt":"2.00B"}},{"dataId":23033,"asOfDate":"2022-04-30","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":2.152736E9,"fmt":"2.15B"}},{"dataId":23033,"asOfDate":"2022-07-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":2.318858E9,"fmt":"2.32B"}},{"dataId":23033,"asOfDate":"2022-10-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":2.466551E9,"fmt":"2.47B"}}]},{"meta":{"symbol":["CRWD"],"type":["annualCashCashEquivalentsAndShortTermInvestments"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualCashCashEquivalentsAndShortTermInvestments":[{"dataId":23033,"asOfDate":"2019-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1.91655E8,"fmt":"191.66M"}},{"dataId":23033,"asOfDate":"2020-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":9.12064E8,"fmt":"912.06M"}},{"dataId":23033,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1.918608E9,"fmt":"1.92B"}},null,{"dataId":23033,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1.996633E9,"fmt":"2.00B"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["quarterlyLongTermDebt"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"quarterlyLongTermDebt":[null,null,null,{"dataId":23123,"asOfDate":"2021-10-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":7.39145E8,"fmt":"739.14M"}},{"dataId":23123,"asOfDate":"2022-01-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":7.39517E8,"fmt":"739.52M"}},{"dataId":23123,"asOfDate":"2022-04-30","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":7.39889E8,"fmt":"739.89M"}},{"dataId":23123,"asOfDate":"2022-07-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":7.40261E8,"fmt":"740.26M"}},{"dataId":23123,"asOfDate":"2022-10-31","periodType":"3M","currencyCode":"USD","reportedValue":{"raw":7.40633E8,"fmt":"740.63M"}}]},{"meta":{"symbol":["CRWD"],"type":["annualInterestExpenseNonOperating"]},"timestamp":[1548892800,1580428800,1612051200,1635638400,1643587200,1651276800,1659225600,1667174400],"annualInterestExpenseNonOperating":[{"dataId":20064,"asOfDate":"2019-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":428000.0,"fmt":"428.00k"}},{"dataId":20064,"asOfDate":"2020-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":442000.0,"fmt":"442.00k"}},{"dataId":20064,"asOfDate":"2021-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":1559000.0,"fmt":"1.56M"}},null,{"dataId":20064,"asOfDate":"2022-01-31","periodType":"12M","currencyCode":"USD","reportedValue":{"raw":2.5231E7,"fmt":"25.23M"}},null,null,null]},{"meta":{"symbol":["CRWD"],"type":["quarterlyCurrentDebt"]}},{"meta":{"symbol":["CRWD"],"type":["annualCurrentDebt"]}}],"error":null}}'
obj = JSON.parse(obj);  //Convert strings to object

// for each object in `obj.timeseries.result`, `.meta.type` names a key in
// that object that contains the data array. That key may not exist. Some 
// entries in the data array may be null.
var mangled = obj.timeseries.result.map(o => {
  // pull the key
  const type = o.meta.type[0];
  // if there's no attribute of that name, return an empty array of values
  if (!(type in o)) return [type, []];
  // otherwise return an array of values, being careful of `null`s
  return [
    type,
    o[type].map(value => value == null ? null : value.reportedValue.raw),
  ];
});

console.log(mangled)