TypeError: Cannot read property '0' of undefined (Google Sheets / App Script)

Question

I would like to know what I need to adjust in the script so that the results appear in the spreadsheet:

TypeError: Cannot read property '0' of undefined (line 7)

function Cartola() {
  var url = "https://api.cartolafc.globo.com/mercado/destaques";
  var response = UrlFetchApp.fetch(url);
  var data = response.getContentText();
  var result = JSON.parse(data);
  
  var apelido = result.Atleta[0].apelido;
  var foto = result.Atleta[0].foto;
  var clube_nome = result.Atleta[0].clube_nome;
  var posição = result.Atleta[0].posicao;
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear()
  var headerRow = ['apelido','foto','clube_nome','posição'];
  sheet.appendRow(headerRow);
  for(var i=0;i<result[0].Atleta;i++){
    var row = [result.Atleta[i].apelido,result.Atleta[i].foto,result.Atleta[i].clube_nome,result.Atleta[i].posicao];
    SpreadsheetApp.getActiveSheet().appendRow(row);
  }
}

Try Logger.log(data) after result and then check view logs to see what is the output of that object. Maybe it is already a json object and you don't need to parse it. — soMario
@Marios response is always of type: object, specifically of class HttpResponse. There are no variants. More Specifically, When dealing with json, getContentText() is always needed and it returns a json string and you always need to parse it. — TheMaster

Diego Diego · Accepted Answer · 2020-09-11T19:53:36

data is an array, so you need to access the element first:

  var apelido = result[0].Atleta.apelido;

I think you're trying to print every player to spreadsheet, but what you've written is only looking at one of the players. Please first look at the data returned by the API and understand its structure. Looks something like this:

[
  {
    "Atleta": {
      "atleta_id": 68952,
      "nome": "Mário Sérgio Santos Costa",
      "apelido": "Marinho",
      "foto": "https://s.glbimg.com/es/sde/f/2019/05/30/cd8a7f9b0744e105efa0a0c572d37d6f_FORMATO.png",
      "preco_editorial": 5
    },
    "escalacoes": 996124,
    "clube": "SAN",
    "clube_nome": "Santos",
    "clube_id": 277,
    "escudo_clube": "https://s.glbimg.com/es/sde/f/organizacoes/2014/04/14/santos_60x60.png",
    "posicao": "Atacante",
    "posicao_abreviacao": "ATA"
  }
]

Once you understand the data, then consider this modified script, which uses batch operations to run much more quickly.

function Cartola() {
  var url = 'https://api.cartolafc.globo.com/mercado/destaques';
  var response = UrlFetchApp.fetch(url);
  var results = JSON.parse(response.getContentText());
  
  var table = [['apelido','foto','clube_nome','posição']];
  for (var i = 0; i < results.length; i++) {
    var r = results[i];
    var apelido = r.Atleta.apelido;
    var foto = r.Atleta.foto;
    var clube_nome = r.clube_nome;
    var posição = r.posicao;
    table.push([apelido, foto, clube_nome, posição]);
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear().getRange(1, 1, table.length, table[0].length).setValues(table);
}

TypeError: Cannot read property '0' of undefined (Google Sheets / App Script)

1 Answers