CODEWITHSUNDEEP
javascriptjsongoogle-sheets
jmct
jmct

Reputation: 65

Google Sheets: Parsing JSON results

Preface: I'm a complete novice at JS and have tried googling my solution for hours.

I am making an API call to a public endpoint using Google Sheets (within the script editor). Here's my current code:

function getMaterials() {
    var myUrl = "https://api.guildwars2.com/v2/account/materials?access_token=[my_access_token]";
    var jsonData = UrlFetchApp.fetch(myUrl);
    var jsonString = jsonData.getContentText();
    return jsonString;
}

Here's a snippet of what is returned:

[
  {
    "id": 12134,
    "category": 5,
    "count": 14
  },
  {
    "id": 12238,
    "category": 5,
    "count": 0
  },
  {
    "id": 12147,
    "category": 5,
    "count": 6
  },
  {
    "id": 12142,
    "category": 5,
    "count": 12
  },
  {
    "id": 12135,
    "category": 5,
    "count": 0
  },
  {
    "id": 12246,
    "category": 5,
    "count": 1
  }
]

Here's the documentation to the particular API I'm interacting with:

https://wiki.guildwars2.com/wiki/API:2/account/materials

There's no real way to filter off my call, or I'd be more than happy to use that.

I am looking to query on the "id" value, but return only the "count" value in the same query. I've tried multiple ways from various sites, but can't get anywhere. Help?

Upvotes: 0

Views: 2381

Answers (2)

jmct
jmct

Reputation: 65

I ended up finding some other JS code that did what I needed.

It allows me to feed the API through a function that formats the data into columns. I can have this data in other sheet and feed it into my main sheet.

https://gist.github.com/paulgambill/cacd19da95a1421d3164

Upvotes: 1

Brian
Brian

Reputation: 2962

I can't find the API docs for that endpoint but I guess you could transform your data to an object that uses id as keys. Depending on your use it might be better to query the API for only specific keys (ie if you only need a small amount of data) if that's possible as this would reduce the amount of data received and memory use. But failing that this should hopefully help!

var data = getMaterials(); 

// Map each object by ID
function getMaterialIdMap(data){
  var materials = {}; 
  for(var i = 0; i < data.length; i++ ){
    var material = data[i]; 
    if(material.hasOwnProperty('id')){ // Ensure we only get objects with ids
      materials[material.id] = material;
    }
  } 
  return materials
}

// Map just the count to id
function getIdToCountMap(data){
  var materials = {}; 
  for(var i = 0; i < data.length; i++ ){
    var material = data[i]; 
    if(material.hasOwnProperty('id') && material.hasOwnProperty('count')){ // Ensure we only get objects with counts and ids
      materials[material.id] = material.count; 
    }
  } 
  return materials
}

// For the full object by id
var materialMap = getMaterialIdMap(data);

// For just the counts 
var materialCountMap = getIdToCountMap(data);

// For use in the sheet =countFromId(12134)   //14
function countFromId (id){
   return materialCountMap[id];
}

function getMaterials(){
  // Just a dummy data function for live snippet replace me with your version that performs the actual request and returns data as an array of objects.
   return [
  {
    "id": 12134,
    "category": 5,
    "count": 14
  },
  {
    "id": 12238,
    "category": 5,
    "count": 0
  },
  {
    "id": 12147,
    "category": 5,
    "count": 6
  },
  {
    "id": 12142,
    "category": 5,
    "count": 12
  },
  {
    "id": 12135,
    "category": 5,
    "count": 0
  },
  {
    "id": 12246,
    "category": 5,
    "count": 1
  }
]

}var data = getMaterials(); 

// Map each object by ID
function getMaterialIdMap(data){
  var materials = {}; 
  for(var i = 0; i < data.length; i++ ){
    var material = data[i]; 
    if(material.hasOwnProperty('id')){ // Ensure we only get objects with ids
      materials[material.id] = material;
    }
  } 
  return materials
}

// Map just the count to id
function getIdToCountMap(data){
  var materials = {}; 
  for(var i = 0; i < data.length; i++ ){
    var material = data[i]; 
    if(material.hasOwnProperty('id') && material.hasOwnProperty('count')){ // Ensure we only get objects with counts and ids
      materials[material.id] = material.count; 
    }
  } 
  return materials
}


var materialMap = getMaterialIdMap(data);


// For just the counts 
var materialCountMap = getIdToCountMap(data);

// For the full object by id
function countFromId (id){
   return materialCountMap[id];
}

function getMaterials(){
  // Just a dummy data function for live snippet
   return [
  {
    "id": 12134,
    "category": 5,
    "count": 14
  },
  {
    "id": 12238,
    "category": 5,
    "count": 0
  },
  {
    "id": 12147,
    "category": 5,
    "count": 6
  },
  {
    "id": 12142,
    "category": 5,
    "count": 12
  },
  {
    "id": 12135,
    "category": 5,
    "count": 0
  },
  {
    "id": 12246,
    "category": 5,
    "count": 1
  }
]

}

Upvotes: 0

Related Questions