What is the best way to split a variable containing a json from an API and then to put it into a specific cell?

Question

Here is my code:

var pUrl = "http://api.perk.com/api/user/id/";
var tk = "/token/";

var options = {
  "method" : "GET",
  "contentType": 'application/text'
}

function perkTvApi1() {
  var response = UrlFetchApp.fetch("http://api.perk.com/api/user/id/515098/token/01133528575d15554742e5bb9bc1fc484fd95ac2/", options);
  Logger.log(response.getContentText());
}

I'm trying to figure out how to split the response variable so I can then put it into a spreadsheet row which corresponds to a time stamp.

I haven't been able to find any kind of split function, like I would use in javascript, in Script Services. I'm running out of ideas and approaches.

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

The easiest way is to use the JSON library: the two most useful functions are JSON.parse() and JSON.stringify().

Both are native to Google Apps Script, so you'd call something like

var object = JSON.parse(response)

in perkTvApi1. Now, the object contains an actual object, which is exactly what you want.

Then, it's just a matter of setting the right cell, so if you wanted cell A1 to be the first name:

SpreadsheetApp.getActiveSheet().getRange('A1').setValue(object["firstName"]);

edited May 23 '17 at 10:32

Community

1
1

answered May 22 '14 at 20:24

JZL003

426
5
16

oops, you beat me to it :) – rGil May 22 '14 at 20:40
1

@rGil But you can bet who he will choose...(hint, the teacher with 1,500 rep) – JZL003 May 22 '14 at 23:02
I wish I could give credit to both of you since you both answered 2 different parts. But I have to give it to JZL003 who provided more complete information since he included information about how to parse, via links, as well as the Spreadsheet. – user3431106 May 25 '14 at 22:17
What if I needed to update a specific row? If I had just inserted a one would appendrow append the new row? – user3431106 Jun 08 '14 at 04:21
Can you make a new question(it's an involved explanation)? – JZL003 Jun 08 '14 at 16:28

score 0 · Answer 2 · edited May 23 '17 at 12:10

JSON.parse() is the method you want. Check out this thread

function myFunction() {
  var options = {
  "method" : "GET",
  "contentType": 'application/text'
}

var response = UrlFetchApp.fetch("http://api.perk.com/api/user/id/515098/token/01133528575d15554742e5bb9bc1fc484fd95ac2/", options);
var jsonResponse = JSON.parse(response)
Logger.log(jsonResponse.firstName);
Logger.log("Key length: " + Object.keys(jsonResponse).length);

var keys = Object.keys(jsonResponse);

//Loop through the keys array to push the json object into an array
//The array can then be used to set values in spreadsheet range
//May need to create 2d array depending on how you want your rows/columns arranged.

var ssData = [];
for (var i in keys){
  ssData.push(jsonResponse[keys[i]]);
}

Logger.log(ssData);

}

What is the best way to split a variable containing a json from an API and then to put it into a specific cell?

2 Answers2