Parse Json from Google spreadsheet cell

Question

In google spreadsheet cell i have this text:

{"age_max":65,"age_min":18,"flexible_spec":[{"interests":[{"id":"6002867432822","name":"Beauty"},{"id":"6002991733794","name":"Beauty & Care"},{"id":"6003177110133","name":"Natural Beauty"},{"id":"6003211042524","name":"Health and Beauty Care"},{"id":"6003393295343","name":"Health And Beauty"},{"id":"6003460329503","name":"Beautiful Skin"},{"id":"6004111438209","name":"Facial care"}]}],"genders":[2],"geo_locations":{"countries":["SK"],"location_types":["home","recent"]},"locales":[2,33],"targeting_optimization":"none","publisher_platforms":["facebook"],"facebook_positions":["feed","right_hand_column","instant_article"],"device_platforms":["mobile","desktop"]};

Its JSON from Facebook API getting from Supermetrics.

Now i want to parse this cell, but this code doesnt work :-/

I am using this function in spreadsheet "=parseTargeting(A1)"

and this custom function in Script editor.

 function parseTargeting(jsonData) {




    var flexible_spec = jsonData["flexible_spec"];
    var maxAge = jsonData["age_max"];
    var minAge = jsonData["age_min"];

    var interestsBasics = jsonData["flexible_spec"][0]["interests"][0]["name"];


    var interestsBasicsCelkem = jsonData["flexible_spec"][0]["interests"].length-1;
    var interests = "";
    var output = [];

    for(var i = 0; i<=interestsBasicsCelkem; i++){

      interests += jsonData["flexible_spec"][0]["interests"][i]["name"]+ "\n";  


    }

    var returnVek = "Vek:"+minAge + " - " + maxAge+" \n";
    var returnInterests = "Zájmy:"+interests;


    var returnString = returnVek + returnInterests;



  return returnString;

}

This function return always Undefined. If i add this code

" var jsonData = {"age_max":65,"age_min":18,"flexible_spec":[{"interests":[{"id":"6002867432822","name":"Beauty"},{"id":"6002991733794","name":"Beauty & Care"},{"id":"6003177110133","name":"Natural Beauty"},{"id":"6003211042524","name":"Health and Beauty Care"},{"id":"6003393295343","name":"Health And Beauty"},{"id":"6003460329503","name":"Beautiful Skin"},{"id":"6004111438209","name":"Facial care"}]}],"genders":[2],"geo_locations":{"countries":["SK"],"location_types":["home","recent"]},"locales":[2,33],"targeting_optimization":"none","publisher_platforms":["facebook"],"facebook_positions":["feed","right_hand_column","instant_article"],"device_platforms":["mobile","desktop"]};
"

to function - then its work. But i need this function to get value from google spreadsheet cell dynamically.

I dont get it :-/ Can you help how to parse JSON from Google Spreadsheet Cell?

Tanaike · Accepted Answer · 2018-03-01 11:49:02Z

5

How about this sample script? For your json object, there is ; at the end of object. By this, it cannot be parsed. So ; is removed and parsed it.

Sample script :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSheet();
  var obj = JSON.parse(ss.getRange("A1").getValue().replace(";", ""));
  var res = parseTargeting(obj);
  Logger.log(res)
}

Note :

In this script, it supposes that your json object is in a cell "A1".
When you use this script, please copy and paste it to the script editor on Spreadsheet with the json object.

If I misunderstand your question, please tell me. I would like to modify it.

Edit :

function parseTargeting(range) {
  var content = JSON.parse(range.replace(";", "")); // Modified
  if( range != "undefined" ){
    var flexible_spec = content["flexible_spec"];
    var maxAge = content["age_max"];
    var minAge = content["age_min"];
    var interestsBasics = content["flexible_spec"][0]["interests"][0]["name"];
    Logger.log(interestsBasics);
    var interestsBasicsCelkem = content["flexible_spec"][0]["interests"].length-1;
    var interests = "";
    var output = [];
    for(var i = 0; i<=interestsBasicsCelkem; i++){
      interests += content["flexible_spec"][0]["interests"][i]["name"]+ "\n";  
    }
    var returnVek = "Vek:"+minAge + " - " + maxAge+" \n";
    var returnInterests = "Zájmy:"+interests;
    Logger.log(returnInterests);
    var returnString = returnVek + returnInterests;
  } else {
    var returnString = "No data";
  }
  return returnString; // Added
}

Note :

For your sample sheet, the values of "A4" and "A7" occur an error at var interestsBasics = content["flexible_spec"][0]["interests"][0]["name"];. Because there is no property of flexible_spec in the value.

edited Mar 1, 2018 at 11:49

answered Feb 28, 2018 at 23:18

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

libor Over a year ago

Thx for reply, but it doesnt work :-/ There is a link to testing spreadsheet - docs.google.com/spreadsheets/d/… can you pls look at this problem - in this spreadsheet there is a column with data. I need parse these column to another column. In Editor script is my script, can you look pls and tell me what is wrong? i spend a lot of time with this problem and i dont get it :-/ thx

Tanaike Over a year ago

@libor I'm sorry for the inconvenience. And thank you for sharing the sample sheet. I noticed that returnString in your script might be the value you want to return. So I modified your script and I updated my answer. Please confirm it. If that value is not what you want, can I ask you about the output you want? I would like to modify by understanding about it.

libor Over a year ago

Great, Thank you, now it is working! Thank you so much, i appreciate it

Tanaike Over a year ago

@libor Thank you for your response.

Collectives™ on Stack Overflow

Parse Json from Google spreadsheet cell

1 Answer 1

Sample script :

Note :

Edit :

Note :

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script :

Note :

Edit :

Note :

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related