JSON Parsing issue

[Vijay Baokar]

Hi Folks,

I need to store JSON response to a custom table. Currently i am triggering REST call using scheduled job and i am able to see the JSON response in logs however now i need to store those responses in my custom table where i have created few fields, i have below script which is not working , i mean the response is not getting stored in table. I believe there is some issue with parsing :

var r = new sn_ws.RESTMessageV2('***', 'GET PO Data');

    r.setStringParameterNoEscape('Token', access_token);

    r.setStringParameterNoEscape('query', gs.getProperty('*****));

    var response = r.execute();

    var responseBody = response.getBody();

    var httpStatus = response.getStatusCode();

// var responseData = '{"data":[{"msdyn_name":"1","name":"Item1"},{"id":"2","name":"Item2"}]}'; // Example JSON response

 

 var parsedResponse = JSON.parse(responseBody);

        for (var i = 0; i < parsedResponse.length; i++) {

    var item = parsedResponse[i];

    var gr = new GlideRecord('my_table');

gr.initialize();

    gr.u_msdyn_postalcode = item.msdyn_postalcode;

    gr.u_hpi_productnumber = item.hpi_productnumber;

    gr.u_msdyn_address2 = item.msdyn_address2;

    gr.u_modifiedon = item.modifiedon;

gr.insert();

Below is the sample response:

[{"@odata.etag":"W/\"162761876452\"","msdyn_name":"WO-021954572","createdon":"2025-04-06T00:17:34Z","msdyn_workorderid":"f18e2789-7c12-f011-9988-000d3a9c970e","msdyn_workordersummary":"AutomationTest_sfjdf","hpi_primaryphone":"0076543210","modifiedon":"2025-04-06T00:19:55Z","msdyn_systemstatus":690970000,"statecode":0,"hpi_diagnostics":null,"hpi_serviceofferid":"WOSFF0"},{"@odata.etag":"W/\"162761876399\"","msdyn_name":"WO-021954573","createdon":"2025-04-06T00:17:48Z","msdyn_workorderid":"f84f8a90-7c12-f011-9988-000d3a9c970e","msdyn_workordersummary":"---Test Order","hpi_primaryphone":"000076543210","modifiedon":"2025-04-06T00:19:43Z","msdyn_systemstatus":690970000,"statecode":0,"hpi_diagnostics":null,"hpi_serviceofferid":"WOSFF0"}]

[Mohith Devatte]

Hello @Vijay Baokar ,

Try using 

 gr.u_msdyn_postalcode =parsedResponse[i].msdyn_postalcode;

 gr.u_hpi_productnumber =parsedResponse[i].hpi_productnumber;

Also in the sample JSON i don't see the attributes named as msdyn_postalcode ,hpi_productnumber Make sure your response has those JSON attributes

Thanks

Mohith Devatte

[Vijay Baokar]

Hi @Mohith Devatte it didn't work the way you suggested. I believe using parsedResponse[i].msdyn_postalcode; is same as item.msdyn_postalcode; because we are already using below syntax:

var item = parsedResponse[i];

[J Siva]

Hi @Vijay Baokar 
The issue is with the payload data. I tried to parse the payload using a background script and received an 'Unexpected Escape Character' error. 
The Key:Value pairs @odata.etag":"W/\"162761876452\" and @odata.etag":"W/\"162761876399\" are causing an issue.

Regards,
Siva

[Vijay Baokar · Accepted solution]

Hi @J Siva It got resolved with below syntax:

var resobj = JSON.parse(responseBody);

var parsedResponse = resobj.value;