Process REST Payload and Pagination

[Community Alums]

Hello Everyone,

I am working on integration where ServiceNow makes call to 3rd party tool using REST Message and we have a GET method defined bring incident data in ServiceNow. We have established a connection and we are getting the data in below format. Now the problem is, 1) The field value (key) contains spaces in it, how to process that data and insert into ServiceNow incident table 2) We have 60000 records to be processed, how all can be processed using pagination, looking for script.

Note: there are fields with spaces: ex. "Assignee Login ID" and some field has colon in it "Test: "

Payload received (receiving the data with offset=2000)

{"entries":[

{
"values": {
"Entry ID": "INC000000000623",
"Submitter": "gash.te.net",
"Ebond_ValidateFlag": null,
"Submit Date": "2015-11-03T19:36:06.000+0000",
"Ebond_WorkLogID": null,
"Assignee Login ID": "gash.te.net",
"z1D_SV_IA_Organization": null,
"Last Modified By": "AR_ESCALATOR",
"Last Modified Date": "2024-09-20T02:40:37.000+0000",
"Status": "Closed",
"Short Description": ".",
"z1D_AssigneeManagerLogin": null,
"z1D_SV_IA_Region": null,
"Notifier Listening": "Not Listening",
"z1D_SV_IA_Site": null,
"z1D_DefaultCompanyAction": null,
"z1D_SkipIfSmartITInstalled": null,
"z1D Status Integer Parm02": null,
"Vendor Name": null,
"z1D Status Integer Parm01": null,
"z1D_SV_IA_Department": null,
"z1D_SV_IsFormDirty": null,
"z1D_AssociationType02": null,
"z1D_SV_IA_Company": null,
"Owner Support Company": "IT Security",
"Owner Group ID": "SGP000000001025",
"z1D_SV_IA_SiteGroup": null,
"z1d_SV_AIOPs_NotImpacted": null,
"Owner Group": "Security",
"Impact_OR_Root": null,
"z2AF_Act_Attachment_2": null,
"z1D_googleMapApiKey": null,
"Test:NTT:SNOW_SYSID": null,
"Test:NTT:SNOW_FLAG": null,
"z2AF_SV_DD_Attachment": null,
"z1D_AssigneeManager": null,
"Assignee Groups": "1000000020;",
"z1D_DefaultCompanyInteger": null,
"SV_Password_MCSM": "***",
"z1D Char16": null,
"z1D Date01": null,
"z1D_SV_SetDetailedDescription_Assignment_Activity": null,
"z1D_SV_SetDescription_Assignment_Activity": null,
"z1D_SV_SetMessageID_Assignment_Activity": null,
"z1D_PreviousAssignedCompany": "Test
"z2AF_Act_Attachment_1": null,
"status_reason2": null,
"Assigned Group Shift Name": null,
"Shifts Flag": null,
"InstanceId": "IDHAA5V0G7RG3ANXJH5H1BB94O5537",
"z1D_Assignee_Email": null}

]}}],

"_links": {
"self": [
{
"href": "/api/arsys/v1.0/entry/HPD:Help%20Desk/INC000000000623"
}
]
}
}

[Shivalika]

Hello @Community Alums 

First proceed with replacing these keys in to field names 

var cleanKey = key.replace(/[^a-zA-Z0-9_]/g, "_");  - this replace all the spaces with underscore. Additionally you can make it to lowercase. 

Then you can proceed with mapping in any  business rule . 

Something like below 👇 

(function processRecords() {

    var PAGE_SIZE = 2000; // Adjust as needed

    var OFFSET = 0;

    var hasMoreRecords = true;

    while (hasMoreRecords) {

        var restMessage = new sn_ws.RESTMessageV2('<INTEGRATION_NAME>', '<HTTP_METHOD_NAME>'); // Replace with Integration Name & Method

        restMessage.setQueryParameter("offset", OFFSET);

        restMessage.setQueryParameter("limit", PAGE_SIZE);

        var response = restMessage.execute();

        var responseBody = response.getBody();

        var responseJSON = JSON.parse(responseBody);

        if (!responseJSON.entries || responseJSON.entries.length === 0) {

            hasMoreRecords = false;

            break;

        }

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

            var recordData = responseJSON.entries[i].values;

            var newRecord = new GlideRecord('<TARGET_TABLE>'); // Replace with target table name

            newRecord.initialize();

            for (var key in recordData) {

                if (recordData.hasOwnProperty(key)) {

                    var cleanKey = key.replace(/[^a-zA-Z0-9_]/g, "_"); // Normalize field names

                    var value = recordData[key];

                    // Custom Field Mapping (Replace with actual mappings)

                    if (cleanKey == "<FIELD_1>") newRecord.<FIELD_1> = transformValue(value, "<FIELD_1>");

                    else if (cleanKey == "<FIELD_2>") newRecord.<FIELD_2> = transformValue(value, "<FIELD_2>");

                    else if (cleanKey == "<FIELD_3>") newRecord.<FIELD_3> = transformValue(value, "<FIELD_3>");

                    else newRecord[cleanKey] = value; // Store as-is if no mapping is defined

                }

            }

            newRecord.insert();

        }

        OFFSET += PAGE_SIZE; // Move to the next batch

    }

    gs.log("Data import completed.");

})();

// Function to transform field values (if needed)

function transformValue(value, fieldName) {

    if (fieldName === "<DATE_FIELD>") return new GlideDateTime(value); // Convert string date to GlideDateTime

    if (fieldName === "<REFERENCE_FIELD>") return getReferenceSysID(value, "<REFERENCE_TABLE>", "<REFERENCE_FIELD>"); // Resolve reference field

    return value;

}

// Function to fetch Sys ID for reference fields

function getReferenceSysID(fieldValue, tableName, lookupField) {

    var gr = new GlideRecord(tableName);

    gr.addQuery(lookupField, fieldValue);

    gr.query();

    return gr.next() ? gr.sys_id.toString() : '';

}

Please replace all the field names and table names and integration methods with whatever specific you are using. You can also change the methods I have provided which is generic approach. 

But this is path you should go on to work on this requirement. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY

[Community Alums]

Hello @Shivalika,

Thank you so much for your quick response, in case if I don't know the size of records in 3rd party tool (I just speculated above). How can go through each page, how can i utilize the nextpageurl from the response?

{"next":[{"href":"https://test.com/api/arsys/v1.0/entry/HPD:Help%20Desk?startIndex=0%3Fq=%27Status%27=%22Open%22&offset=2000&pageSize=500&RecordInstanceDataPageQuery=&fields=values%28Incident%20Number%29"}]

Regards,

Tejas

[Shivalika]

Hello @Community Alums 

I am assuming you are getting the nextpageUrl in your responseabody. In that case you can just modify the above code as below 👇 

var baseUrl = "<API_BASE_URL>?startIndex=0&pageSize=500"; // Replace with actual base URL

function fetchRecords(url) {

    var restMessage = new sn_ws.RESTMessageV2('<REST_MESSAGE_NAME>', 'GET'); // Replace with REST message name

    restMessage.setEndpoint(url);

    restMessage.setRequestHeader("Accept", "application/json");

    var response = restMessage.execute();

    var responseBody = response.getBody();

    var parsedResponse = JSON.parse(responseBody);

    if (parsedResponse.entries && parsedResponse.entries.length > 0) {

        processRecords(parsedResponse.entries);

    }

    // Recursively fetch next page if available

    if (parsedResponse.nextPageUrl) {

        fetchRecords(parsedResponse.nextPageUrl);

    }

}

function processRecords(entries) {

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

        var recordData = entries[i].values;

        var gr = new GlideRecord('<TARGET_TABLE>'); // Replace with target table name

        gr.initialize();

        // Example: Mapping fields dynamically

        gr.setValue('<FIELD_1>', recordData["<KEY_1>"]); // Replace FIELD_1 and KEY_1

        gr.setValue('<FIELD_2>', recordData["<KEY_2>"]); // Replace FIELD_2 and KEY_2

        gr.insert();

    }

}

// Start the process

fetchRecords(baseUrl);

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY

[Community Alums]

Thank you, Shivalika,

I am trying to get the next pageURL but getting:

Script: [object Object]

I am trying to read the href value mentioned at the bottom of each response

 Trying with below code:

if (parsedResponse._links.next) {

    var url = parsedResponse._links.next;

    gs.print(url);

}