CODEWITHSUNDEEP
javascriptjsongoogle-apps-script
Arfater Rahman
Arfater Rahman

Reputation: 133

Convert JSON response to Google Sheet ( Google Apps Script)

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)

}

enter image description here

Upvotes: 1

Views: 1848

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

So your JSON response based on postman app is

var zohoipekuetview65 = {"Leads":[{"Yearly_Sales":"$ 1,000.00","Email":"[email protected]","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":"[email protected]","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":"[email protected]","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.

Upvotes: 1

Related Questions