Having issues getting api data to write to my google sheet.

[Ben Anderson]

Hello,

I am very new to coding and am trying to repurpose examples from the internet to understand how all of this code works, however after over a week of being unsuccessful I am seeking help with the below code. I am trying to get my feet wet by pulling api data from a game i play and am having trouble getting it to parse into individual cells. Here is a copy of the code i'm using and a snippet of the json response from the log. Any help with getting the code to work would be greatly appreciated.

CODE:

function TextSpacedAPICallTestingCenter()

{

   var BaseUrl = "https://play.textspaced.com/api"

   var ss = SpreadsheetApp.getActiveSpreadsheet();

   var sheet = ss.getActiveSheet();

   var APIKey = sheet.getRange(1,2).getValue();

   //var Authorization = "Bearer"+ APIKey

   // Call the TextSpaced.com API to request system commodities list

  var response = UrlFetchApp.fetch(BaseUrl+"/system/commodities",{ "headers":{"Authorization":"Bearer "+APIKey}});

  // Parse the JSON reply

  var json = response.getContentText();

  var data = JSON.parse(json);

  Logger.log(response.getContentText());

  var fact = response.getContentText();

  var sheet = SpreadsheetApp.getActiveSheet();

 

 // functions above this line

  var headerValue = "commodity_id";  

  var startRow = 2;

  var endRow = startRow + headerValue.length - 1;

  var column = 1;

  var range = sheet.getRange(startRow, column, endRow, 1);

  sheet.getRange(2,1).setValue([data.headerValue]);

}

JSON data:

{"commodity_id":13,"body_id":8263,"stock_amount":8263,"commodity_remaining":98201842,"commodity_maximum":98201950,"commodity_name":"Aluminium","commodity_short_name":"Al","commodity_illegal":false,"commodity_rarity":550,"commodity_category":1,"commodity_sub_category":13,"body_list":"Project 1","computed":{"can_mine":true}},"2":{"commodity_id":51,"body_id":8265,"stock_amount":8265,"commodity_remaining":983568,"commodity_maximum":1005361,"commodity_name":"Antimony","commodity_short_name":"Sb","commodity_illegal":false,"commodity_rarity":7,"commodity_category":1,"commodity_sub_category":15,"body_list":"Luminescent Planet","computed":{"can_mine":true}},"3":{"commodity_id":51,"body_id":8269,"stock_amount":8269,"commodity_remaining":779464,"commodity_maximum":779464,"commodity_name":"Antimony","commodity_short_name":"Sb","commodity_illegal":false,"commodity_rarity":7,"commodity_category":1,"commodity_sub_category":15,"body_list":"Chemical Planet","computed":{"can_mine":true}},"4":{"commodity_id":33,"body_id":8265,"stock_amount":8265,"commodity_remaining":4974627,"commodity_maximum":4974627,"commodity_name":"Arsenic","commodity_short_name":"As","commodity_illegal":false,"commodity_rarity":43,"commodity_category":1,"commodity_sub_category":15,"body_list":"Luminescent Planet","computed":{"can_mine":true}},"5":{"commodity_id":33,"body_id":8269,"stock_amount":8269,"commodity_remaining":2477441,"commodity_maximum":2479466,"commodity_name":"Arsenic","commodity_short_name":"As","commodity_illegal":false,"commodity_rarity":43,"commodity_category":1,"commodity_sub_category":15,"body_list":"Chemical Planet","computed":{"can_mine":true}},"6":

I am trying to break it down into the individual headers. Some of them won't be needed but if i can get them to cells i am fluent enough in excel formulas to extract the ones i'm looking for. So if it's not possible to write just the data i'm looking for from this that's annoying but i can live with it.

[Tanaike]

I think that your "JSON data:" is invalid JSON data. Your "JSON data:" is the value of "Logger.log(response.getContentText())"?

And, about "I am trying to break it down into the individual headers.", can you provide your expected situation?

[Ben Anderson]

My goal is to have " commodity_id" , body_id, stock_amount, commodity_remaining, commodity_maximum ,   commodity_name ,  etc.. as column headers with the relevant information underneath it.

[Tanaike]

Thank you for replying. I understood your reply as an answer to my 2nd question. Can you provide the answer to my 1st question?

[Ben Anderson]

My apologies, that's what i get for skimming. Yes that is where i got the json data.

[Tanaike]

Thank you for replying. From "that's what i get for skimming. Yes that is where i got the json data.", I understood that your obtained data is invalid JSON data as I have already mentioned in my 1st comment. So, how about confirming it again?

[Ben Anderson]

Alright, so I changed the Logger.log function. 

Code:

var response = UrlFetchApp.fetch(BaseUrl+"/system/commodities",{ "headers":{"Authorization":"Bearer "+APIKey}});

 var json = response.getContentText();

 var data = JSON.parse(json);

 Logger.log(data);

To me, the output looks exactly the same, so i'm not sure how exactly to go about confirming it's valid json data.

Here is a snippet of the logged output:

{86={commodity_sub_category=1.0, body_list=Delta Hub, commodity_maximum=1.121982E7, body_id=8268.0, commodity_remaining=1.1219544E7, stock_amount=8268.0, commodity_name=Lithium, commodity_category=1.0, commodity_short_name=Li, commodity_rarity=40.0, commodity_illegal=false, commodity_id=3.0, computed={can_mine=true}}, 169={commodity_remaining=58421.0, commodity_short_name=Ta, commodity_illegal=false, body_id=8265.0, stock_amount=8265.0, commodity_name=Tantalum, commodity_maximum=58421.0, commodity_category=1.0, commodity_rarity=1.0, commodity_sub_category=5.0, body_list=Luminescent Planet, commodity_id=73.0, computed={can_mine=true}}, 192={commodity_rarity=108.0, commodity_category=1.0, stock_amount=8265.0, commodity_illegal=false, body_id=8265.0, commodity_id=23.0, commodity_maximum=1.6480584E7, commodity_name=Vanadium, computed={can_mine=true}, commodity_short_name=V, body_list=Luminescent Planet, commodity_remaining=1.6480584E7, commodity_sub_category=5.0}, 110={commodity_name=Platinum, body_id=8261.0, computed={can_mine=true}, commodity_maximum=2472452.0, body_list=Rock Planet, commodity_category=1.0, commodity_illegal=false, commodity_sub_category=10.0, commodity_rarity=39.0, stock_amount=8261.0, commodity_remaining=2472424.0, commodity_short_name=Pt, commodity_id=78.0}

[Tanaike]

Thank you for replying. From your reply, I confirmed that your 1st provided "JSON data:" is different from your reply.

And, the value shown with Logger.log(JSON.parse(response.getContentText())) cannot be directly used as the JSON data. So, as I have already mentioned in my 1st comment, can you provide the value of Logger.log(response.getContentText()) instead of Logger.log(JSON.parse(response.getContentText()))?

[Ben Anderson]

The very first post had what I referred to as "JSON DATA." That was the output of Logger.log(response.getContentText()); I will paste it again here for reference. It's part of the data not the full thing since it was such a large data set the logs truncated it anyway so I didn't see a need to paste the entire log. If this bit isn't sufficient please let me know.

{"commodity_id":13,"body_id":8263,"stock_amount":8263,"commodity_remaining":98201842,"commodity_maximum":98201950,"commodity_name":"Aluminium","commodity_short_name":"Al","commodity_illegal":false,"commodity_rarity":550,"commodity_category":1,"commodity_sub_category":13,"body_list":"Project 1","computed":{"can_mine":true}},"2":{"commodity_id":51,"body_id":8265,"stock_amount":8265,"commodity_remaining":983568,"commodity_maximum":1005361,"commodity_name":"Antimony","commodity_short_name":"Sb","commodity_illegal":false,"commodity_rarity":7,"commodity_category":1,"commodity_sub_category":15,"body_list":"Luminescent Planet","computed":{"can_mine":true}},"3":{"commodity_id":51,"body_id":8269,"stock_amount":8269,"commodity_remaining":779464,"commodity_maximum":779464,"commodity_name":"Antimony","commodity_short_name":"Sb","commodity_illegal":false,"commodity_rarity":7,"commodity_category":1,"commodity_sub_category":15,"body_list":"Chemical Planet","computed":{"can_mine":true}},"4":{"commodity_id":33,"body_id":8265,"stock_amount":8265,"commodity_remaining":4974627,"commodity_maximum":4974627,"commodity_name":"Arsenic","commodity_short_name":"As","commodity_illegal":false,"commodity_rarity":43,"commodity_category":1,"commodity_sub_category":15,"body_list":"Luminescent Planet","computed":{"can_mine":true}},"5":{"commodity_id":33,"body_id":8269,"stock_amount":8269,"commodity_remaining":2477441,"commodity_maximum":2479466,"commodity_name":"Arsenic","commodity_short_name":"As","commodity_illegal":false,"commodity_rarity":43,"commodity_category":1,"commodity_sub_category":15,"body_list":"Chemical Planet","computed":{"can_mine":true}},"6":

[Tanaike]

Thank you for replying. In your provided data, it seems that each property is like "###":{"commodity_id":###,,,} . Namely, the key is number and the value is {"commodity_id":###,,,}. But, your provided data is not followed this. But, you say your provided data is from Logger.log(response.getContentText()). And, in your script, it seems that no error occurs at var data = JSON.parse(json) So, I'm confused about understanding your situation. I apologize for this.

[Tanaike]

For example, if it supposes that {"commodity_id":13,,,} is "1": {"commodity_id":13,,,}, how about the following sample script?

function TextSpacedAPICallTestingCenter() {

  var data = {

    "1": { "commodity_id": 13, "body_id": 8263, "stock_amount": 8263, "commodity_remaining": 98201842, "commodity_maximum": 98201950, "commodity_name": "Aluminium", "commodity_short_name": "Al", "commodity_illegal": false, "commodity_rarity": 550, "commodity_category": 1, "commodity_sub_category": 13, "body_list": "Project 1", "computed": { "can_mine": true } },

    "2": { "commodity_id": 51, "body_id": 8265, "stock_amount": 8265, "commodity_remaining": 983568, "commodity_maximum": 1005361, "commodity_name": "Antimony", "commodity_short_name": "Sb", "commodity_illegal": false, "commodity_rarity": 7, "commodity_category": 1, "commodity_sub_category": 15, "body_list": "Luminescent Planet", "computed": { "can_mine": true } },

    "3": { "commodity_id": 51, "body_id": 8269, "stock_amount": 8269, "commodity_remaining": 779464, "commodity_maximum": 779464, "commodity_name": "Antimony", "commodity_short_name": "Sb", "commodity_illegal": false, "commodity_rarity": 7, "commodity_category": 1, "commodity_sub_category": 15, "body_list": "Chemical Planet", "computed": { "can_mine": true } },

    "4": { "commodity_id": 33, "body_id": 8265, "stock_amount": 8265, "commodity_remaining": 4974627, "commodity_maximum": 4974627, "commodity_name": "Arsenic", "commodity_short_name": "As", "commodity_illegal": false, "commodity_rarity": 43, "commodity_category": 1, "commodity_sub_category": 15, "body_list": "Luminescent Planet", "computed": { "can_mine": true } },

    "5": { "commodity_id": 33, "body_id": 8269, "stock_amount": 8269, "commodity_remaining": 2477441, "commodity_maximum": 2479466, "commodity_name": "Arsenic", "commodity_short_name": "As", "commodity_illegal": false, "commodity_rarity": 43, "commodity_category": 1, "commodity_sub_category": 15, "body_list": "Chemical Planet", "computed": { "can_mine": true } }

  };

  var ar = Object.values(data);

  var header = Object.keys(ar[0]);

  var sheet = SpreadsheetApp.getActiveSheet();

  var values = [header, ...ar.map(o => header.map(h => h == "computed" ? o[h].can_mine : o[h]))];

  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);

}

This is a simple sample script. So, please modify this to your actual situation.

[Ben Anderson]

That worked! is there a way to pick out specific ones to record on my sheet while ignoring others? for instance i do not care about "commodity_maximum" but i do care about "commodity_remaining" and "commodity_name"

[Tanaike]

Thank you for replying. From `That worked!`, I understood your question was resolved.

About your new question of `is there a way to pick out specific ones to record on my sheet while ignoring others? for instance i do not care about "commodity_maximum" but i do care about "commodity_remaining" and "commodity_name"`. Unfortunately, I cannot understand it. Can you provide the detail of your new question?

[Tanaike]

For example, if you want to use your expected values, how about modifying var header = Object.keys(ar[0]); to your expected values like var header = ["sample1","sample2",,,];?