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