Home > Enterprise >  Replace empty result coming from JSON with value
Replace empty result coming from JSON with value

Time:04-22

I am using the following script in Google Sheets to fetch a JSON object from Yahoo!Finance for certain companies:

function yahooFinance(symbol) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'   encodeURI(symbol)
      '?modules=price,assetProfile,summaryDetail,incomeStatementHistory,'
      'balanceSheetHistory,defaultKeyStatistics,financialData,calendarEvents,'
      'recommendationTrend,upgradeDowngradeHistory,majorHoldersBreakdown'
    ;
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const responseCode = response.getResponseCode();
  if (responseCode === 200) {
    const quote = JSON.parse(response.getContentText());
    return [[
      quote.quoteSummary.result[0].summaryDetail.dividendYield.fmt,
      quote.quoteSummary.result[0].summaryDetail.payoutRatio.fmt,
      quote.quoteSummary.result[0].summaryDetail.marketCap.fmt
    ]];
  }
  else {
    return "-";
  }
}

Regularly, however, for one or more keys, the value will be emptyy. In such a case, the JSON returns (an empty string). Whenever there is an empty string however, I would like to return a - back to the sheet to indicate that the query went fine, however no value was returned.

So I tried updating as follows (see the if statement):

function yahooFinance(symbol) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'   encodeURI(symbol)
      '?modules=price,assetProfile,summaryDetail,incomeStatementHistory,'
      'balanceSheetHistory,defaultKeyStatistics,financialData,calendarEvents,'
      'recommendationTrend,upgradeDowngradeHistory,majorHoldersBreakdown'
    ;
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const responseCode = response.getResponseCode();
  if (responseCode === 200) {
    const quote = JSON.parse(response.getContentText());
    return [[
      (if (quote.quoteSummary.result[0].summaryDetail.dividendYield.fmtr) quote.quoteSummary.result[0].summaryDetail.dividendYield.fmt),
      quote.quoteSummary.result[0].summaryDetail.payoutRatio.fmt,
      quote.quoteSummary.result[0].summaryDetail.marketCap.fmt
    ]];
  }
  else {
    return "-";
  }
}

Apparently, I cannot put an if-statement inside an array. How can I check each of the 3 values that I'm returning (dividendYield, payoutRatio, marketCap) if they are empty and if so, return -?

CodePudding user response:

This is how i'd do it. Additionally, it makes your code cleaner. The || works like a if/else. If the first value is defined, it will use that, if not, it will use the "-"

 function yahooFinance(symbol) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'   encodeURI(symbol)
      '?modules=price,assetProfile,summaryDetail,incomeStatementHistory,'
      'balanceSheetHistory,defaultKeyStatistics,financialData,calendarEvents,'
      'recommendationTrend,upgradeDowngradeHistory,majorHoldersBreakdown'
    ;
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const responseCode = response.getResponseCode();
  if (responseCode === 200) {
    const quote = JSON.parse(response.getContentText());
    const summaryDetail = quote.quoteSummary.result[0].summaryDetail;
    const divYield = summaryDetail.dividendYield.fmt || "-";
    const payoutRatio = summaryDetail.payoutRatio.fmt || "-";
    const marketCap = summaryDetail.marketCap.fmt || "-";
    return [[ divYield , payoutRatio, marketCap ]];
  }
  else {
    return "-";
  }
}

CodePudding user response:

With optional chaining ?., evaluation stops when a null-ish value is found. Think of using it this way:

potentially_nullish_thing?.prop_or_index_or_function

If the potentially_nullish_thing isn't null or undefined, the prop_or_index_or_function runs as you would expect. Otherwise, the whole expression evaluates to undefined.

So for the OP, pair that with the logical or || to get a default string if anything on the path to the real data is missing.

quote?.quoteSummary?.result?.[0]?.summaryDetail?.dividendYield?.fmt || '-',
// so on for the others
  • Related