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');
		}

[Avinash Shawdar]

Thanks a lot for you help. I will test it and reply to you.

[Naveen4]

Hi,did you checked the log by printing the Result is it coming correctly.

try printing the log and where you are using this code(BR or Schedulr job or Script include).

Thanks,Naveen

[Avinash Shawdar]

I am using it in a UI Action script that gets executed on the server side to be able to call my Outbound Rest API. And here are my logs that print the Json Parsed list of users: 

[Naveen4 · Accepted solution]

Hi,

PFB screenshot.

and in the Log i can able to see a lot of records not only one record  please correct me am i am wrong .

Please use the insert funtion in a loop as appropriate and execute the query by commenting the address related fields.

and please make sure that you if you are getting multiple records  from response you have to use the insert query in a loop.

Note:

https://community.servicenow.com/community?id=community_question&sys_id=3d215be9dbdcdbc01dcaf3231f9619d3

follow this link it might helps you to insert multiple records.

Thanks,Naveen

[Avinash Shawdar]

Hi Naveen,

 i appreciate your help. I have managed to insert that data into my staging (intermediate) table between Rest outbound Message and sys_user.

For that i have simply used :

         my_glide_record_object.Column_Name_From_Intermediate_table = data; to set the values into the gliderecord. 

         my_glide_record_object.insert(); to insert the data into a for loop to iterate over rows, where length represents the length of my parsed Data from the called Rest Message.

Here is the code sample that gets the job done : 

for(var j=0; j<length; j++){
			restGR.u_name_stg = nameTbl[j]; 
			restGR.u_user_name_stg = usernameTbl[j]; 
			restGR.u_email_stg = emailTbl[j]; 
			restGR.	u_street_stg = addressTbl[j]; 
    			restGR.insert();			
		}