How to import data from external application using API?

[Jeff316]

Hi All,

The day I have been dreading has arrived.  I have been given 12 API "paths" to an external application where I am to connect from ServiceNow and import their data into 12 custom tables I will create in ServiceNow. Can anyone point me to the document or post that describes how to connect to an external application using API and import the contents into custom table each night.  I don't need to authenticate. I'm going through the MID Server. I already took one of their API strings and used the ServiceNow API Explorer and could connect. I did a GET and saw a line of data returned. We are on London version.

[Jeff316]

My script

try {

   //Send REST get to grab json blob

   var r = new sn_ws.RESTMessageV2(‘noc.fnf.com Data Center List', 'get');

   r.setStringParameter('Accept', 'application/json');

   r.setStringParameter('key', 'Private');

   var response = r.execute();

   var responseBody = response.getBody();

   var httpStatus = response.getStatusCode();

   gs.log("Response Body " + responseBody);

   var parser = new JSONParser();

   var parsed = parser.parse(responseBody);

   gs.log("Parsed JSON" + parsed);

   /*for (var n in parsed.Locations.Location[0]){ //use this to see your json objects

   gs.log("Parsed[" + n +"]= " + parsed.Locations.Location[0][n]);

}*/

gs.log(parsed);

}catch(ex) {

   var message = ex.getMessage();

}

//Create a new import set

var crImpSet = new GlideRecord('sys_import_set');

crImpSet.initialize();

crImpSet.mode = 'synchronous';

crImpSet.table_name = 'u_fnf_import_data_centers'; //Set the extended importset table

crImpSet.state = 'loading';

crImpSet.insert();

var locs = parsed.Locations.Location;

gs.log("Parsed Array Size locs = " + locs.length);

var restGR = new GlideRecord('u_fnf_import_data_centers'); //Query extended import set rows table

//Loop through the json chunks until 0 remain creating import set rows to be transformed

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

   restGR.initialize();

   restGR.u_id = locs[i].id;

   restGR.u_description = locs[i].description;

   restGR.u_key = locs[i].key;

   restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above

   restGR.insert();

}

//At this point we populate the import set and its rows then the transform map does its thing automatically

Using script above

 In system log Error: Parsed Array Size locs = undefined

Obviously the script example is referencing Location which I believe for me, would be my target table. I will try a few different syntax to see what happens.

Response Body {"isError":false,"message":"","value":[{"description":"Central","key":"CentralUS","id":2,"extraProperties":{}},{"description":"East 2","key":"EastUS2","id":1,"extraProperties":{}},{"description":"Other","key":"Other","id":5,"extraProperties":{}},{"description":"West 2","key":"WestUS2","id":3,"extraProperties":{}},{"description":"West Central","key":"WestCentral","id":4,"extraProperties":{}}]}

[Jeff316 · Accepted solution]

Here is how I did it, in case anyone else needs help.

try {

//Send REST get to grab json blob
var r = new sn_ws.RESTMessageV2('noc.sd343.com Data Center List', 'get');
r.setStringParameter('Accept', 'application/json');
r.setStringParameter('key', 'Private');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();

gs.log("Response Body " + responseBody);
var parser = new JSONParser();
var parsed = parser.parse(responseBody);
gs.log("Parsed JSON" + parsed);
/*for (var n in parsed.value[0]){ //use this to see your json objects

gs.log("Parsed[" + n +"]= " + parsed.value[0][n]);

}*/

gs.log(parsed);

}catch(ex) {

var message = ex.getMessage();

}
//Create a new import set
var crImpSet = new GlideRecord('sys_import_set');
crImpSet.initialize();
crImpSet.mode = 'synchronous';
crImpSet.table_name = 'u_fnf_import_data_centers'; //Set the extended importset table
crImpSet.state = 'loading';
crImpSet.insert();
var locs = parsed.value;
gs.log("Parsed Array Size locs = " + locs.length);
var restGR = new GlideRecord('u_fnf_import_data_centers'); //Query extended import set rows table

//Loop through the json chunks until 0 remain creating import set rows to be transformed

for (var i = 0; i < locs.length; i++) {
restGR.initialize();
restGR.u_description = locs[i].description;
restGR.u_key = locs[i].key;
restGR.u_id = locs[i].id;
restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above
restGR.insert();

}