Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

126
Vistas
How to handle #Error in Google Sheets when a JSON property does not exist?

I am using the following script in Google Sheets to retrieve certain company (retresented by ticker symbol) properties (in JSON) from Yahoo!Finance and present them in adjacent cells in the sheet.

I am retrieving 3 properties; this list will be expanded in the future. I am currently trying to achieve proper error-handling.

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

The problem is: if a certain property does not exist for a certain symbol, Google Sheets returns an error: #Error TypeError: Cannot read property 'marketCap' of undefined (line 43).

My question is: how can I still return a - (hyphen) in this case?

about 4 years ago · Juan Pablo Isaza
2 Respuestas
Responde la pregunta

0

Replace the line of code by

const marketCap = summaryDetail.marketCap == null ? '-' : summaryDetail.marketCap.fmt;
about 4 years ago · Juan Pablo Isaza Denunciar

0

Consider set a formula to the cell where you're binding the value.

Example adapted from this answer:

var cell = sheet.getRange("B5");
cell.setFormula("=IFNA(B5,'Invalid value')");
about 4 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda