Cannot Set Value of JSON Data to Separate Cells

I wrote up the following script, which pulls all the data in just fine. But when I try to break it up into different cells, it returns nothing. What am I missing here?

function myMonitor() {
//GET KEY

var sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“NINFO”);
**var rowNum = sh.getRange(‘F6’).getValue(); **

//POST CALL

** var url = “http://monitor.incognito.org/pubkeystat/stat”; **
** var data = { “mpk”: rowNum**
** };**

** var options = {**
** ‘method’ : ‘post’,**
** ‘contentType’: ‘application/json’,**
** ‘payload’ : JSON.stringify(data)**
** };**

** var response = UrlFetchApp.fetch(url, options);**
** var txt= response.getContentText();**
** var d=JSON.parse(txt);**

//RETURN RESULTS BACK TO GOOGLE SHEET

var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“MONITOR”);

sh1.getRange(1, 1).setValue(d);
}

Result:

{Role=PENDING, SyncState=SHARD SYNCING, Alert=true, CommitteeChain=2, NextEventMsg=12 to be COMMITEE, Status=ONLINE}

Now if I try the same thing, and switch the last line to:

sh1.getRange(1, 1).setValue(d.Role);
sh1.getRange(1, 2).setValue(d.SyncState);
sh1.getRange(1, 3).setValue(d.Alert);
sh1.getRange(1, 4).setValue(d.CommitteeChain);
sh1.getRange(1, 5).setValue(d.NextEventMsg);
sh1.getRange(1, 6).setValue(d.Status);

It completes its execution but returns nothing. Any ideas?

I tested your sample data and it returned to separate cells as expected. But can you try this approach?

Code:

Object.keys(d).forEach(function (key, index){
  sh1.getRange(1, index + 1).setValue(d[key]);
});

Behavior:

• Iterates all the keys of object d and is placed in 1st row (1 value per cell which is similar to what your individual setValue in each cells do)

Output:

EDIT:

• The d value seems to be in an array. Try this one instead:

d.forEach(function (data, row){
  Logger.log(data);
  Object.keys(data).forEach(function (key, index){
    Logger.log(key)
    sh.getRange(row + 1, index + 1).setValue(data[key]);
  });
});

Note:

• Since I can't test the actual response on the fetch method, to simulate your data, I used:

  var d = {Role:"PENDING", SyncState:"SHARD SYNCING", Alert:true, CommitteeChain:2, NextEventMsg:"12 to be COMMITEE", Status:"ONLINE"};

• The above code should work if your d has value of:

  {Role=PENDING, SyncState=SHARD SYNCING, Alert=true, CommitteeChain=2, NextEventMsg=12 to be COMMITEE, Status=ONLINE}

EDIT2:

• Since your response is inside an array, you need to access it like d[0]
• Provided code below will also add a header, and will loop all results (if there were multiple data returned)

Code:

// header
sh.getRange(1, 1, 1, Object.keys(d[0]).length).setValues([Object.keys(d[0])]);
// contents
d.forEach(function (data, row){
  Object.keys(data).forEach(function (key, index){
    sh.getRange(row + 2, index + 1).setValue(data[key]);
  });
});

Output: