How to parse JSONs in Sheet A column A to Sheet B?

[madein]

I have a google spreadsheet with Sheet A having JSONs in column A :

and I'd like to populate Sheet B with the data with respective rows and columns like :

However I'm not entirely sure how to start (newbie to apps script). I'm a bit confused about the whole process. First, declaring sheet. I've seen people using the following:

function parseJSON() 

{ 

    var ss = SpreadsheetApp.getActiveSpreadsheet(); // Gets the Active Spreadsheet

    var sheet = ss.getSheets()[0]; // Gets the first sheet

    var parsed = JSON.parse(json); sheet.appendRow([json.items[i].id, json.items[i].name, json.items[i].url]); 

}

but I have in 1 active spreadsheet from sheet A to sheet B. So would i need to declare a second sheet (my sheet B)?

    var ss = SpreadsheetApp.getActiveSpreadsheet(); // Gets the Active Spreadsheet

    var sheet1 = ss.getSheets()[0]; // Gets the first sheet

    var sheet2 = ss.getSheets()[1]; // Gets the second sheet           

    sheet2.appendRow([json.items[i].id, json.items[i].name, json.items[i].url]);

Also how do i declare what are headers and the values according to the JSON? Should the headers be declared?

const headers = ["dish_name","dish_price","dish_quantity"];

or is it possible to get the header from the JSON? How about the corresponding values? Does Apps Ssripts accept foreach loop?

[Andrew Roberts]

You'll probably find some useful code for "JSON unfurling" in here: https://github.com/bradjasper/ImportJSON

[madein]

Actually, I would rather not use ImportJSON. Though, how to use  ImportJSONFromSheet? Found no examples/documentation on how to use this.

[Andrew Roberts]

You could parse it in a couple of stages: the first one to get the headers and the second to extract the values to the right column. Although if the headers/field names are constant you could just hard-code them and add them manually.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/23fc1adf-a86e-406e-abf4-22d71b3788b2n%40googlegroups.com.

[Andrew Roberts]

If you put up a link to an example of your sheet containing the JSON someone may help you with the code.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/wdAl_9BOVXo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/92f48d09-268d-48ad-a633-d1a1d0826111n%40googlegroups.com.

[Timothy Hanson]

I use this. Might be helpful

My JSONs look like  this

{ "elements": [ {

"Type": "Person", "Organization Name": "Now What?!", "Title": "Collective member",

Contexts your relationship lives in": [ "We have periodic one on one zoom calls", "We've met in person but don't live near one another" ],   },

                {

"Type": "Person", "Acme LCC", "Title": "Something", 

Contexts your relationship lives in": [

"We have periodic one on one zoom calls", "We've met in person but don't live near one another" ],

}   ],

"connections": [

{ "Id": 105502, "From": 39073, "To": 39076, "Type": "Collaborator",

"Weight": 3,

}

]

}

Use like this

//get Json Data for elements

  var data=FetchJSON(jsonlink ,separator, "elements");

Where "separator" is the delimiter for parsing the elements of an array

from above

Contexts your relationship lives in": [ "We have periodic one on one zoom calls", "We've met in person but don't live near one another" ], becomes "We have periodic one on one zoom calls|We've met in person but don't live near one another" if the separator is a PIPE "|"

//get Json Data for element

function FetchJSON(jsonlink ,separator, what) {

// Set Fetch Variables

  var response     = UrlFetchApp.fetch(jsonlink);

  var responseText = response.getContentText();

  var responseJson = JSON.parse(responseText);

  if(what=="elements"){

  let myDico = new Map();

  responseJson.elements.forEach(function(key){

    Object.keys(key).forEach(function(item){

       myDico.set(item,'')

    })

  })

  var elementKeys = []

  myDico.forEach(function(value, key) {

    elementKeys.push(key)

  });

  var data = responseJson.elements.map(e => elementKeys.map(f => {

      return e[f] instanceof Array ? e[f].join(separator) : e[f];

  })); 

  data.unshift(elementKeys);

  

  } 

  else if(what=="connections"){

   let myDico2 = new Map()

   responseJson.connections.forEach(function(key){

    Object.keys(key).forEach(function(item){

       myDico2.set(item,'')

    })

  })

  var connectionKeys = []

  myDico2.forEach(function(value, key) {

    connectionKeys.push(key)

  })

  var data = responseJson.connections.map(e => connectionKeys.map(f => {

      return e[f] instanceof Array ? e[f].join(separator) : e[f];

  })); 

  data.unshift(connectionKeys);

  };

  //data = data.map(x => x.map(y => typeof y === 'undefined' || null ? "" : y));

  return data

}

[Timothy Hanson]

Import Json works well also

https://github.com/bradjasper/ImportJSON

using the above JSON example

 //get Json Data for element

  var data=ImportJSON(jsonlink,"/elements","noInherit,noTruncate,rawHeaders");