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 -?
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 "-";
}
}
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