How to loop through object of API response array in outbound REST API?

[SNewbies]

Hello!

I am receiving an Object with arrays from API response. Data array displays column ids instead of column names.

I need help with how to pass all the received data into table. 

Example of received data:

{ "page": 1, "total_pages": 3, "count": 30, "limit": 10, "offset": 0, "next_params": { "offset": 10, "limit": 10 }, "previous_params": null, "next_link": "", "previous_link": null, "results": { "columns": [ { { "type": "integer", "id": "57a0e63b-5306-4152-9d19-468904573654", "name": "EMP ID:" }, { "type": "string", "id": "f125797e-2b47-47ff-a8ef-797580234543", "name": "Status:" }, { "type": "string", "id": "dedca38-e1e6-4349-b082-236898709009",
"name": "First Name:" }, { "type": "string", "id": "7779ae87-9290-43af-8ef2-234567898766", "name": "Last Name:"
}, ], "data": [ { "57a0e63b-5306-4152-9d19-468904573654": "298", "f125797e-2b47-47ff-a8ef-797580234543": "Active", "6dedca38-e1e6-4349-b082-236898709009": "John", "7779ae87-9290-43af-8ef2-234567898766": "Doe"
}, { "57a0e63b-5306-4152-9d19-468904573654": "299", "f125797e-2b47-47ff-a8ef-797580234543": "Active",
"6dedca38-e1e6-4349-b082-236898709009": "Bob", "7779ae87-9290-43af-8ef2-234567898766": "Marley" } ] }

I am parsing the data and putting it into an array with Column name and data assigned to that column.

I can get data from first object into an array but I am not able to traverse through all of the received data and assign it to an array.

How can I do that?

Any help is appreciated.

Thank you

[Astik Thombare]

Hi @SNewbies ,

Here's how you can process the API response object with column IDs and populate a table in ServiceNow:

1. Parsing the Data:

Here's how to iterate through the received data and create an array with column names and corresponding data:

// Get the data object from the API response
var dataObject = response.results;

// Define an empty array to store the processed data
var processedData = [];

// Extract column names from the "columns" array
var columnNames = [];
for (var i = 0; i < dataObject.columns.length; i++) {
  columnName = dataObject.columns[i][0].name; // Access the first element for name
  columnNames.push(columnName);
}

// Iterate through each data row in the "data" array
for (var i = 0; i < dataObject.data.length; i++) {
  var rowData = {}; // Create an object to store data for each row
  for (var key in dataObject.data[i]) {
    rowData[columnNames[dataObject.data[i].indexOf(key)]] = dataObject.data[i][key];
  }
  processedData.push(rowData);
}

This code iterates through the "columns" array to extract column names and stores them in the columnNames array. Then, it loops through each row in the "data" array. Inside the loop, it creates an empty object (rowData) to hold data for each row. It iterates through each key-value pair in the current row object. Using the corresponding index in the columnNames array (based on the key order), it assigns the value to the appropriate column name in the rowData object. Finally, it pushes the rowData object containing the processed data for that row into the processedData array.

2. Populating the Table in ServiceNow:

ServiceNow doesn't have a built-in table creation or data population functionality through scripting. However, you can achieve this using a combination of GlideRecord and GlideAggregate:

• Create a GlideRecord object for the target table:

var targetTable = 'your_table_name'; // Replace with your actual table name
var gr = new GlideRecord(targetTable);

• Loop through the processedData array and insert each row:

for (var i = 0; i < processedData.length; i++) {
  gr.initialize(); // Clear the record for each iteration

  // Set values for each column using processedData
  for (var colName in processedData[i]) {
    gr.setValue(colName, processedData[i][colName]);
  }

  // Insert the record
  gr.insert();
}

This code loops through the processedData array. For each row, it creates a new GlideRecord object (gr) and initializes it. Then, it iterates through each column name and corresponding data in the current row of processedData. It uses gr.setValue to set the value for the corresponding column in the GlideRecord object. Finally, it calls gr.insert to insert the record into the target table.

Additional Considerations:

• Make sure you have appropriate permissions to create and write to the target table in ServiceNow.
• Error handling can be added to manage potential issues during data processing or insertion.

Remember:

• Replace 'your_table_name' with the actual name of the table you want to populate.
• This approach assumes the target table has columns with names matching the ones extracted from the API response. You might need to adjust the code if there's a mismatch.

If I helped you with your case, please click the Thumb Icon and mark as Correct.

Thanks,
Astik

[SNewbies]

Hello!

Thank you for your response. 

When I am trying to send the data to table, record is getting created but there is no value in it. I added log statement for where we are setting the table values and it displayed,

for (var colName in processedData[i]) {
    gr.setValue(colName, processedData[i][colName]);
  }

Log statement:

gs.log("COL: " + colName);

gs.log("Values: " + processedData[i][colName]);

Output is displayed as,

COL: replaceAll

Values: 

function (from, to) {
return this.replace(from.toString(), to, "g");
}

Can you please help me with this last part.

All your help is appreciated.

Thanks