如何处理#错误在谷歌表当JSON属性不存在?

Question

我在Google Sheets中使用以下脚本从雅虎检索某些公司(由股票代码表示)属性(JSON) !财务并在工作表的相邻单元格中显示。

我正在检索3属性;这个列表将在未来扩展。我目前正在努力实现正确的错误处理。

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

问题是:如果某个符号的某个属性不存在，Google Sheets返回一个错误:#Error TypeError: Cannot read property 'marketCap' of undefined (line 43).

我的问题是:在这种情况下，我如何仍然返回-(连字符)?

Answer 1

将该行代码替换为

const marketCap = summaryDetail.marketCap == null ? '-' : summaryDetail.marketCap.fmt;

Answer 2

考虑将公式设置为要绑定值的单元格。

改编自此回答的例子:

var cell = sheet.getRange("B5");
cell.setFormula("=IFNA(B5,'Invalid value')");