How can i insert data coming from Rest call to a servicenow table?

[Avinash Shawdar]

How can i insert data coming from an Outbound Rest Api that gets data as Json, into a staging table 

I have an outbound Rest Api that gets data as Json from an external Api. I manage to call to this Rest Api and get its data from a Ui Action, but i can't manage to insert this data into its corresponding table using GlideRecord.

I would be glad if someone has a suggestion or a comment to add on my code.

Here is my script:

try {
	var r = new sn_ws.RESTMessageV2('Get users from Json placeholder', 'get');
	var response = r.execute();
	var responseBody = response.getBody();
	var httpStatus = response.getStatusCode();
	
	var result = responseBody.split(','); //This depends on what's in your response
	
	var restGR = new GlideRecord('u_users_stagging_table');
	restGR.initialize();
        // In this part i read the data coming from the Rest Api
                restGR.name = result[1]; 
		restGR.user_name = result[2]; 
		restGR.email = result[3]; 
		restGR.street = result[4]; 
		restGR.city = result[6]; 
		restGR.zip = result[7];
		restGR.company = result[12]; 
	        
                gs.log('Row: ' +restGR.name+restGR.user_name+restGR.email+restGR.street+restGR.city+restGR.zip+restGR.company);

		restGR.sys_import_set = 'bcec9d70db0f13005c965d50cf96193c'; 
		restGR.insert();
			}
		}
		catch(ex) {
			var message = ex.getMessage();
			gs.log('Here is the error in the execution of the REST API call : '+ex,'Me');
		}

[johnnyd54]

Does the UI action have the client checkbox set true? Also are the log statements logging correctly and just nothing is getting inserted?

[Avinash Shawdar]

No, the Client checkbox is set to false because when its true the Ui Action executes its script in the user's browser, not on the server which is not what i want. And the log shows me the data coming from the Outbound Rest Api.

[harshinielath]

It will be helpful if you provide the output of the log statements. Is the data received in correct format? If not, try converting like this first

var data = JSON.parse(responseBody);

[weikiat_guaz]

Hi, 

This is what my colleague did before. I tried to understand and modify it to suit your case. Hope you can understand my explanation. 

Let's assume u_users_stagging_table is your importset table.

//Right after your "var httpStatus = response.getStatusCode();"
//Add below code
//1. I think we need to create a new importset record first.
var  importSetUserstaging = new GlideRecord("sys_import_set");
importSetUserstaging.initialize();
importSetUserstaging.mode = "asynchronous";
importSetUserstaging.state = "loading";
importSetUserstaging.table_name = 'u_users_stagging_table';
importSetUserstaging.short_description = 'import_set_description';
var is_id = importSetUserstaging.insert();

//2. Then only you parse your json data and insert into importset table
this.parseUserData('u_users_stagging_table',responseBody);

//3. At last, call the tranform function
this.transformData(importSetUserstaging);
//Add below code end

//Create a new funtion called parseUserData
parseUserData : function(import_set_table,responseBody){
	var gr = new GlideRecord(import_set_table);
	gr.initialize();
	var parser = new JSONParser();
	var parsed = parser.parse(responseBody);
	var _length = parsed.length;
	for(var i=0; i<_length; i++){
		// In this part i read the data coming from the Rest Api 
		gr.insert();
	}			
},

//Create a new funtion called transformData
transformData : function(importsetgr){
	//Mark the import set as Loaded
	importsetgr.state = "loaded";
	importsetgr.update();
	// transform the import set
	var t = new GlideImportSetTransformer();
	t.transformAllMaps(importsetgr);
},