Convert JSON response to Google Sheet ( Google Apps Script)

Question

I have been trying to figure out to insert the JSON response into google Sheet in Google Apps Script with below code but for some reason I am getting error while trying to run.

please see screenshot and below code.

    function myFunction() {

  var key_67 = 'YYYYYYYYYYYYYYYYYY';
  var ss_67 = SpreadsheetApp.openById(key_67);
  var sheet_67 = ss_67.getActiveSheet();
  sheet_67.getRange('A1:AZ10000').clearContent();
 var url = 'https://creator.zoho.com/api/json/arfater/view/Leads_Report?authtoken=XXXXXXXXXXXXXXXXXXXX&scope=creatorapi&zc_ownername=ipekuet';
 var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
Logger.log(data);



  var stats=[]; //create empty array to hold data points



  //The following lines push the parsed json into empty stats array

    stats.push(data.Yearly_Sales); //temp
    stats.push(data.Email); //dewPoint
    stats.push(data.Phone); //visibility

  //append the stats array to the active sheet 
  sheet_67.appendRow(stats)

}

What happens when you Logger.log(json) before you put it through JSON.parse()? — Jordan Rhea, May 14 '17 at 17:20
Ruben - Thanks for your reply.. I tried what you have suggested but getting same error .. here is what I tried with `var response = UrlFetchApp.fetch(url); var json = response.getContentText(); Logger.log(json); var data = JSON.parse(json);` — Arfater Rahman, May 15 '17 at 03:33
error : **SyntaxError: Unexpected token: v (line 11, file "ConvertoCSVFromJSON")** — Arfater Rahman, May 15 '17 at 03:36
Here is the API response for your convenience `{ "Leads": [ { "Yearly_Sales": "$ 1,000.00", "Email": "test12@zoho.com", "Phone": "123-032-03323", "Potentially": 50, "State": "NY", "ZipCode": "10021", "Street": "11 Penn Plz", "Country": "USA", "ID": "2198633000000063039", "City": "New York", "Name": "Shafater Rahman" } ] }` — Arfater Rahman, May 15 '17 at 03:55
I am not totally sure, but it looks like the response is already a JSON object and you don't need to run it through JSON.parse() because JSON.parse() expects a string. See this answer: http://stackoverflow.com/questions/14432165/uncaught-syntaxerror-unexpected-token-with-json-parse — Jordan Rhea, May 15 '17 at 06:02
Here is what I get response when I call API from "postman" (Chrome Extension) ---------- `var zohoipekuetview65 = {"Leads":[{"Yearly_Sales":"$ 1,000.00","Email":"test@zoho.com","Phone":"123-032-03323","Potentially":50,"State":"NY","ZipCode":"10036","Street":"1515 Broadway","Country":"USA","ID":"2198633000000063029","City":"New York","Name":"Arfater Rahman"}]};` — Arfater Rahman, May 15 '17 at 07:01
when I run below code there is no error but no row have been saved in Google Sheet except "Undefined" in each cell `function myFunction() { ......// truncated some initial code var response = UrlFetchApp.fetch(url); var data = JSON.parse(JSON.stringify(response)); Logger.log(data); var stats=[]; //create empty array to hold data points stats.push(data.Yearly_Sales); stats.push(data.Email); stats.push(data.Phone); sheet_67.appendRow(stats) }` — Arfater Rahman, May 15 '17 at 07:04

score 1 · Answer 1 · answered May 16 '17 at 21:40

So your JSON response based on postman app is

var zohoipekuetview65 = {"Leads":[{"Yearly_Sales":"$ 1,000.00","Email":"test@zoho.com","Phone":"123-032-03323","P‌otentially":50,"Stat‌e":"NY","ZipCode":"1‌0036","Street":"1515 Broadway","Country":"USA","ID":"2198633000000063029","City":‌"New York","Name":"Arfater Rahman"}]};

When I use that response as is:

function JsonResponse(){
 var json ='var zohoipekuetview65 = {"Leads":[{"Yearly_Sales":"$ 1,000.00","Email":"test@zoho.com","Phone":"123-032-03323","P‌otentially":50,"Stat‌e":"NY","ZipCode":"1‌0036","Street":"1515 Broadway","Country":"USA","ID":"2198633000000063029","City":‌"New York","Name":"Arfater Rahman"}]} '
 var data = JSON.parse(json);
Logger.log(data); 
}

I get the same error as you:

SyntaxError: Unexpected token: v

Which leads me to believe your response from API has this term var zohoipekuetview65 (Not really sure as to why? a bug perhaps)

The below code splits the response string to give you the JSON response only

function trialParse(){
var json ='var zohoipekuetview65 = {"Leads":[{"Yearly_Sales":"$ 1,000.00","Email":"test@zoho.com","Phone":"123-032-03323","P‌otentially":50,"Stat‌e":"NY","ZipCode":"1‌0036","Street":"1515 Broadway","Country":"USA","ID":"2198633000000063029","City":‌"New York","Name":"Arfater Rahman"}]} '
Logger.log(JsonResponse(json))
}
function JsonResponse(response){
  Logger.log(response)
  var json = response.split("=")[1]
  var data = JSON.parse(json);
  Logger.log(data);
  return data  
}

Just call the above function in your code using var data = JsonResponse(json)

Final Note: As mentioned by Jordan Rhea you can use Logger.log(json) to output the response to your logs. To view your logs goto Views>Logs, it will show you the response you receive from Api.

Convert JSON response to Google Sheet ( Google Apps Script)

1 Answers1