Pagination to import all records with custom data source script

[Sunil62]

Hello Experts,

We have a requirement to pull 600K records from external application and transform the data into Servicenow. Usually I prefer data stream action as it automatically takes care of pagination. But we do not have integration hub license and forced to use script to pull and transform the data.

I have explored Concurrent import sets but it seems that this not fit into our requirements. We are calling api with rest message where initially we load 1000 records and response contains url to run next set of 1000 records. It means we have to run the data source to load next set of records with the page response of previous hit. 

Please provide pointers how I can run this kind of import in a loop till it finishes pulling all records from external application. In simple, how pagination can be done in data source using script.

Thanks,

Sunil Safare 

[Bruno · Accepted solution]

Here's a suggestion. I can be more assertive if I share how the call is made and the structure of the response payload.

var nextUrl = null;
do{
	var endpoint = nextUrl || 'initial endpoint';
	nextUrl = null;
	
	try {
		var r = new sn_ws.RESTMessageV2('name of the REST message', 'method name');
		r.setEndpoint(endpoint);
		r.setStringParameterNoEscape('XXX', XXX); //if necessary
		var response = r.execute();
		var responseBody = response.getBody();
		var httpStatus = response.getStatusCode();

		if (httpStatus == 200) {
			responseBody = JSON.parse(responseBody);
			if(responseBody.nextUrl)
				nextUrl = responseBody.nextUrl;

			//insert your code;
		}
	} catch (ex) {
		gs.log(ex.message);
	}
}while(nextUrl);

You need to make sure that the field that returns the next URL is empty in the last call, otherwise the code will enter an infinite loop.

There are other alternatives, but I need more information to suggest them.
I hope this helps.

[Sunil62]

Hello Bruno,

Thanks a lot for the code. I have marked your reply as answer as it helps others.

I was trying the similar code with for loop but I was not passing the parameter correctly and hence the records were not inserting correctly.

var size = 500;

var page = 0;

for(var i =0; i<= parseInt(page); i++){

   //Rest message code to pass size and page variables 

   //*** In the rest message I should pass 'i' variable instead of page variable and then the records were inserted correctly

   import_set_table.insert(record);

   page = json_obj.pages.totalCount;

}

Thanks,

Sunil Safare

[SK Chand Basha]

Hi @Sunil62 

https://www.servicenow.com/community/now-platform-forum/pagination-and-rest-api-on-servicenow/m-p/10...

https://www.servicenow.com/community/developer-articles/paginated-glide-record-expanded-gr-method-su...

Mark it helpful and Accept Solution!! If this helps you to understand.