Google script json parse issue

Patpower
February 2, 2024
152 views
0 votes
2 Answers

I used this code to parse my webrequest response:

for (var [key,val] of Object.entries(jsonObj)) {
  console.log("Key="+key); 
  console.log("Value="+val);
}

Its working for one of my url but for another one I get:

{members=[Ljava.lang.Object;@3e0dc74, list_id=xxxxx, total_items=2.0, _links=[Ljava.lang.Object;@610c2a56}

The response is ok but the json.parse() turn it bad… The members list is not the supposed list but Ljava object?

** I see that the response is like an array inside a json, not sure on how to proceed it **

Any ideas?
Thanks!

Here the reply

{"members":[{"id":"14d4xd529edc51eac6c2","email_address":"[email protected]","unique_email_id":"421ccfc0de","contact_id":"56f0eb4b2034f1228b3ea76e5eac22c6","full_name":"www","web_id":522903574,"email_type":"html","status":"subscribed","consents_to_one_to_one_messaging":true,"merge_fields":{"FNAME":"www","LNAME":"eeee","ADDRESS":{"addr1":"22 du plaisir","addr2":"","city":"xxxxxx","state":"qc","zip":"xxxx","country":"US"},"PHONE":"","MMERGE6":"","MMERGE5":"","MMERGE9":"","MMERGE10":"","MMERGE7":"","MMERGE8":""},"interests":{"e539c5b413":true,"8b4b1cc4be":true,"ff755b6893":false,"e78e70c1ec":false,"7bb4f39707":false,"13fd433d6b":false,"d2c372d6d0":false,"8450588744":false,"461896449a":false,"d5514d2d0b":false,"5dfb9c860b":false,"5775586056":false,"d07314aaf0":false,"851b0ab3e5":false,"9323e42080":false,"5c00098a74":false,"19fcaa30f9":false,"534f03a40c":false,"bd85a86aae":false,"a5abdc2e42":false,"e7b4a3ada0":false,"970e155af0":false,"0569ab3c82":false,"09c995ef83":false,"de65267b73":false,"7dd7155626":false,"2adcefc913":false,"f87d68d423":false,"83d27bff8f":false,"934b62ae80":false,"ff41de7dff":false,"d70e507585":false,"08045c1b3d":false,"02a8543521":true,"c2a43803b1":false,"55d1bbb773":false,"27abb86269":false},"stats":{"avg_open_rate":0,"avg_click_rate":0,"ecommerce_data":{"total_revenue":0,"number_of_orders":0,"currency_code":"CAD"}},"ip_signup":"","timestamp_signup":"","ip_opt":"xx4.x2.1x.1xx","timestamp_opt":"2024-01-17T23:51:54+00:00","member_rating":2,"last_changed":"2024-01-31T13:57:04+00:00","language":"","vip":false,"email_client":"iPhone","location":{"latitude":0,"longitude":0,"gmtoff":0,"dstoff":0,"country_code":"","timezone":"","region":""},"source":"Admin Add","tags_count":0,"tags":[],"list_id":"fx3240b7","_links":[{"rel":"self","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"parent","href":"https:","method":"GET","targetSchema":"https:","schema":"https:"},{"rel":"update","href":"https:","method":"PATCH","targetSchema":"https:","schema":"https:"},{"rel":"upsert","href":"https:","method":"PUT","targetSchema":"https:","schema":"https:"},{"rel":"delete","href":"https:","method":"DELETE"},{"rel":"activity","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"goals","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"notes","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"events","href":"https:","method":"POST","targetSchema":"https:"},{"rel":"delete_permanent","href":"https:","method":"POST"}]},{"id":"be87b47fe6cebade61ced3fe638b13dc","email_address":"[email protected]","unique_email_id":"788b930fc5","contact_id":"0665a6462a9dfa2fd2350bbeecae45ff","full_name":"pat","web_id":522903562,"email_type":"html","status":"subscribed","consents_to_one_to_one_messaging":true,"merge_fields":{"FNAME":"pat","LNAME":"laf","ADDRESS":{"addr1":"20 du saint-u00e9milion","addr2":"","city":"bcccam","state":"qc","zip":"ccc","country":"CA"},"PHONE":"","MMERGE6":"","MMERGE5":"","MMERGE9":"","MMERGE10":"","MMERGE7":"","MMERGE8":""},"interests":{"e539c5b413":true,"8b4b1cc4be":false,"ff755b6893":false,"e78e70c1ec":false,"7bb4f39707":false,"13fd433d6b":false,"d2c372d6d0":false,"8450588744":false,"461896449a":false,"d5514d2d0b":false,"5dfb9c860b":false,"5775586056":false,"d07314aaf0":false,"851b0ab3e5":false,"9323e42080":false,"5c00098a74":false,"19fcaa30f9":false,"534f03a40c":false,"bd85a86aae":false,"a5abdc2e42":false,"e7b4a3ada0":false,"970e155af0":false,"0569ab3c82":false,"09c995ef83":false,"de65267b73":false,"7dd7155626":false,"2adcefc913":false,"f87d68d423":false,"83d27bff8f":false,"934b62ae80":false,"ff41de7dff":false,"d70e507585":false,"08045c1b3d":false,"02a8543521":false,"c2a43803b1":false,"55d1bbb773":false,"27abb86269":false},"stats":{"avg_open_rate":0,"avg_click_rate":0,"ecommerce_data":{"total_revenue":0,"number_of_orders":0,"currency_code":"CAD"}},"ip_signup":"","timestamp_signup":"","ip_opt":"xx.9x.1xx.1xx","timestamp_opt":"2024-01-17T23:50:35+00:00","member_rating":2,"last_changed":"2024-01-26T16:03:04+00:00","language":"","vip":false,"email_client":"","location":{"latitude":29.46,"longitude":-98.57,"gmtoff":-6,"dstoff":-5,"country_code":"US","timezone":"america/chicago","region":"tx"},"source":"Admin Add","tags_count":0,"tags":[],"list_id":"fcc73240b7","_links":[{"rel":"self","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"parent","href":"https:","method":"GET","targetSchema":"https:","schema":"https:"},{"rel":"update","href":"https:","method":"PATCH","targetSchema":"https:","schema":"https:"},{"rel":"upsert","href":"https:","method":"PUT","targetSchema":"https:","schema":"https:"},{"rel":"delete","href":"https:","method":"DELETE"},{"rel":"activity","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"goals","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"notes","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"events","href":"https:","method":"POST","targetSchema":"https:"},{"rel":"delete_permanent","href":"https:","method":"POST"}]}],"list_id":"fcc73240b7","total_items":2,"_links":[{"rel":"self","href":"https:","method":"GET","targetSchema":"https:","schema":"https:"},{"rel":"parent","href":"https:","method":"GET","targetSchema":"https:"},{"rel":"create","href":"https:","method":"POST","targetSchema":"https:","schema":"https:"}]}

Answers

- datafultech
- February 1, 2024 at 11:39 pm
- 0 votes
0
If you need to export only emails from the structure you provided, you can use this function:
```
// This function is only to demonstrate how the extraction works
// Assumes that there is a variable `payload` - already parsed JSON
function example() {
  console.log(extractEmails(payload))
}

// Extract emails
function extractEmails(payload) {
  return payload.members.map(member => 
    member.email_address
  )
}
```
Login or Signup to reply.

I stuck the response into a file and extracted it and parsed it and displayed a few items in a spreadsheet.

function myfunk10() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const obj = JSON.parse(DriveApp.getFileById("fileid").getBlob().getDataAsString());
  const hA = ["contact_id","email_address","email_client","email_type","full_name","member_rating"];
  const mvs = obj.members.map(m => [m.contact_id,m.email_address,m.email_client,m.email_type,m.fullname,m.member_rating]);
  mvs.unshift(hA);
  Logger.log(JSON.stringify(Object.keys(obj.members[0])));
  sh.clearContents();
  sh.getRange(1,1,mvs.length,mvs[0].length).setValues(mvs);
  sh.getRange(1,1,1,sh.getLastColumn()).setFontWeight("bold");
}

Execution log
7:08:26 PM  Notice  Execution started
7:08:19 PM  Info    ["id","email_address","unique_email_id","contact_id","full_name","web_id","email_type","status","consents_to_one_to_one_messaging","merge_fields","interests","stats","ip_signup","timestamp_signup","ip_opt","timestamp_opt","member_rating","last_changed","language","vip","email_client","location","source","tags_count","tags","list_id","_links"]
7:08:28 PM  Notice  Execution completed

The output:

	A	B	C	D	E	F
1	contact_id	email_address	email_client	email_type	full_name	member_rating
2	56f0eb4b2034f1228b3ea76e5eac22c6	[email protected]	iPhone	html		2
3	0665a6462a9dfa2fd2350bbeecae45ff	[email protected]		html		2

Please signup or login to give your own answer.

Click here to cancel reply.