import specific data from a json feed

Viewed 79

folks I need your help. I have this JSON structure and I would like to import just a few items to google spreadsheets such as ID, DESCRICAO e PRECO, however I am not able to capture only these items with my code.

{
"retorno": {
"produtos": [
{
"produto": {
"id": "11822756183",
"codigo": "",
"descricao": "PLUG BORNE - BNC MACHO 50 OHMS COM BORNE - PARAFUSO PCT/10 - 062-9961 - SANTANA",
"tipo": "P",
"situacao": "Ativo",
"unidade": "D1",
"preco": "16.4700000000",
"precoCusto": "16.4700000000",
"descricaoCurta": null,
"descricaoComplementar": null,
"dataInclusao": "2021-03-25",
"dataAlteracao": "2021-03-25",
"imageThumbnail": null,
"urlVideo": "",
"nomeFornecedor": "SIMPLES SOLUTIONS COME DE EQUIP ELETRONICOS LTDA",
"codigoFabricante": "31469",
"marca": "",
"class_fiscal": "8536.69.90",
"cest": "",
"origem": "2",
"idGrupoProduto": "0",
"linkExterno": null,
"observacoes": "",
"grupoProduto": null,
"garantia": "0",
"descricaoFornecedor": "PLUG BORNE - BNC MACHO 50 OHMS COM BORNE - PARAFUSO PCT/10 - 062-9961 - SANTANA",
"idFabricante": "11822750692",
"categoria": {
"id": "980535",
"descricao": "Categoria padrão"
},
"pesoLiq": "0.00000",
"pesoBruto": "0.00000",
"estoqueMinimo": null,
"estoqueMaximo": null,
"gtin": "",
"gtinEmbalagem": "",
"larguraProduto": null,
"alturaProduto": null,
"profundidadeProduto": null,
"unidadeMedida": "Centímetros",
"itensPorCaixa": 0,
"volumes": 0,
"localizacao": "",
"crossdocking": "",
"condicao": "Novo",
"freteGratis": "N",
"producao": "T",
"dataValidade": null,
"spedTipoItem": "",
"estoqueAtual": 0,
"depositos": [
{
"deposito": {
"id": "146989099",
"nome": "Geral",
"saldo": 0,
"desconsiderar": "N",
"saldoVirtual": 0
}
}
]
}
},

Code I'm trying to use

function callBling() {
  // Call the Bling API
  var response = UrlFetchApp.fetch("apikey");
  Logger.log(response.getContentText());
   // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  Logger.log(data);
  Logger.log(data["produtos"]);
  Logger.log(data["produtos"]["produto"]);
  Logger.log(data["produtos"]["produto"]["0"]["id"]);
  Logger.log(data["produtos"]["produto"][0]["descricao"]);
  Logger.log(data["produtos"]["produto"][0]["preco"]);
}

but with this code you are not importing the data correctly into the logger

1 Answers

I believe your goal as follows.

  • You want to retrieve the values of id, descricao, preco from the JSON data in your question.
  • You want to put the retrieved values to the Spreadsheet.

Modification points:

  • In your script, for example, at Logger.log(data["produtos"]["produto"]["0"]["id"]), data["produtos"] is null. From your sample value, it's data["retorno"]["produtos"]. And, ["0"] is the key. When you want to use 0 as the index of array, please use [0]. In your sample value, the array is data["retorno"]["produtos"]. So when you want to retrieve the value of id in the 1st element of data["retorno"]["produtos"], please use data["retorno"]["produtos"][0]["produto"]["id"].
  • In order to put the retrieved values to Google Spreadsheet, it is required to create the values as the 2 dimensional array.

When above points are reflected to the script, how about the following modified script?

Modified script:

function callBling() {
  // Call the Bling API
  var response = UrlFetchApp.fetch("apikey");
  Logger.log(response.getContentText());
   // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);

  // I added below script.
  var values = data.retorno.produtos.map(({produto: {id, descricao, preco}}) => [id, descricao, preco]);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
  • When this modified script is run, the values are put to "Sheet1" in the active Spreadsheet.
  • In this modified script, it supposes that the value of data of var data = JSON.parse(json) is the sample value in your question. When the strudture of actual data is different from your sampe values, the script might not be able to be used. Please be careful this.

Note:

  • In order to check the value of values in above script, you can also test the following script using your sample values.

        var data = {
          "retorno": {
            "produtos": [
              {
                "produto": {
                  "id": "11822756183",
                  "codigo": "",
                  "descricao": "PLUG BORNE - BNC MACHO 50 OHMS COM BORNE - PARAFUSO PCT/10 - 062-9961 - SANTANA",
                  "tipo": "P",
                  "situacao": "Ativo",
                  "unidade": "D1",
                  "preco": "16.4700000000",
                  "precoCusto": "16.4700000000",
                  "descricaoCurta": null,
                  "descricaoComplementar": null,
                  "dataInclusao": "2021-03-25",
                  "dataAlteracao": "2021-03-25",
                  "imageThumbnail": null,
                  "urlVideo": "",
                  "nomeFornecedor": "SIMPLES SOLUTIONS COME DE EQUIP ELETRONICOS LTDA",
                  "codigoFabricante": "31469",
                  "marca": "",
                  "class_fiscal": "8536.69.90",
                  "cest": "",
                  "origem": "2",
                  "idGrupoProduto": "0",
                  "linkExterno": null,
                  "observacoes": "",
                  "grupoProduto": null,
                  "garantia": "0",
                  "descricaoFornecedor": "PLUG BORNE - BNC MACHO 50 OHMS COM BORNE - PARAFUSO PCT/10 - 062-9961 - SANTANA",
                  "idFabricante": "11822750692",
                  "categoria": {
                    "id": "980535",
                    "descricao": "Categoria padrão"
                  },
                  "pesoLiq": "0.00000",
                  "pesoBruto": "0.00000",
                  "estoqueMinimo": null,
                  "estoqueMaximo": null,
                  "gtin": "",
                  "gtinEmbalagem": "",
                  "larguraProduto": null,
                  "alturaProduto": null,
                  "profundidadeProduto": null,
                  "unidadeMedida": "Centímetros",
                  "itensPorCaixa": 0,
                  "volumes": 0,
                  "localizacao": "",
                  "crossdocking": "",
                  "condicao": "Novo",
                  "freteGratis": "N",
                  "producao": "T",
                  "dataValidade": null,
                  "spedTipoItem": "",
                  "estoqueAtual": 0,
                  "depositos": [
                    {
                      "deposito": {
                        "id": "146989099",
                        "nome": "Geral",
                        "saldo": 0,
                        "desconsiderar": "N",
                        "saldoVirtual": 0
                      }
                    }
                  ]
                }
              }]
          }
        };
    
        var values = data.retorno.produtos.map(({produto: {id, descricao, preco}}) => [id, descricao, preco]);
        console.log(values);

Reference:

Related