Import Nested JSON via REST Message

Rijeesh Rathna1 · ‎08-28-2022

Dear Team,

I am trying to connect my ServiceNow instance with Basecamp 3. The objective is to sync the project and its related TO DO list and its tasks.

To get the details of all the projects, I am using the endpoint https://3.basecampapi.com/5434106/projects.json and here is the payload I receive.

[
{
"id": 29067529,
"status": "active",
"created_at": "2022-08-28T07:55:38.974Z",
"updated_at": "2022-08-28T07:58:55.839Z",
"name": "Test Project 2",
"description": "",
"purpose": "topic",
"clients_enabled": false,

"dock": [
{
"id": 5271268498,
"title": "Message Board",
"name": "message_board",
"enabled": true,
"position": 1
},
{
"id": 5271268499,
"title": "To-dos",
"name": "todoset",
"enabled": true,
"position": 2
},
{
"id": 5271268500,
"title": "Docs \u0026 Files",
"name": "vault",
"enabled": true,
"position": 3
}
],
"bookmarked": true
},
{
"id": 29064341,
"status": "active",
"created_at": "2022-08-27T10:24:04.444Z",
"updated_at": "2022-08-28T06:05:08.373Z",
"name": "Testing",
"description": "This is a test project",
"purpose": "topic",
"clients_enabled": false,
"dock": [
{
"id": 5270730469,
"title": "Message Board",
"name": "message_board",
"enabled": true,
"position": 1

},
{
"id": 5270730470,
"title": "To-dos",
"name": "todoset",
"enabled": true,
"position": 2
},
{
"id": 5270730471,
"title": "Docs \u0026 Files",
"name": "vault",
"enabled": true,
"position": 3

}
],
"bookmarked": true
}
]

The issue that I am facing is, I need to get the ID todoset of each project. For example, I need to get 5271268499 as the TODOSET ID of Test Project 2 in a field on the project table.

Also, the next complicated part is that I need to go through each project and get the todo list and its tasks. This can be taken using the endpoint https://3.basecampapi.com/5434106/buckets/29064341/todosets/5270730470/todolists.json. This will show all the todo list and task of Test Project 1 and will show the below json. But how do I get the list of each project as the project ID and TODOSET ID needs to be changed to get its applicable data.

Can someone please help?

TO DO and TASK Payload sample.

[
{
"id": 5271233931,
"status": "active",
"visible_to_clients": false,
"created_at": "2022-08-28T06:04:34.491Z",
"updated_at": "2022-08-28T06:05:08.272Z",
"title": "Test 2",
"inherits_status": true,
"type": "Todolist",
"position": 1,
"parent": {
"id": 5270730470,
"title": "To-dos",
"type": "Todoset"
},
"bucket": {
"id": 29064341,
"name": "Testing",
"type": "Project"
},
"creator": {
"id": 40763538,
"name": "Rijeesh",
"email_address": "rijeesh@gmail.com",
"personable_type": "User",
"title": null,
"bio": null,
"location": null,
"created_at": "2022-08-27T10:23:45.063Z",
"updated_at": "2022-08-27T12:08:19.067Z",
"admin": true,
"owner": true,
"client": false,
"time_zone": "Asia/Muscat",

"company": {
"id": 3144462,
"name": "Rijeesh"
}
},
"description": "",
"completed": false,
"completed_ratio": "0/2",
"name": "Test 2"

},
{
"id": 5271233763,
"status": "active",
"visible_to_clients": false,
"created_at": "2022-08-28T06:03:50.132Z",
"updated_at": "2022-08-28T06:04:13.540Z",
"title": "Test 1",
"inherits_status": true,
"type": "Todolist",
"position": 2,
"parent": {
"id": 5270730470,
"title": "To-dos",
"type": "Todoset"

},
"bucket": {
"id": 29064341,
"name": "Testing",
"type": "Project"
},
"creator": {
"id": 40763538,
"name": "Rijeesh",
"email_address": "rijeesh@gmail.com",
"personable_type": "User",
"title": null,
"bio": null,
"location": null,
"created_at": "2022-08-27T10:23:45.063Z",
"updated_at": "2022-08-27T12:08:19.067Z",
"admin": true,
"owner": true,
"client": false,
"time_zone": "Asia/Muscat",

"company": {
"id": 3144462,
"name": "Rijeesh"
}
},
"description": "",
"completed": false,
"completed_ratio": "0/2",
"name": "Test 1"

}
]

Appreciate if someone can please help me to get this done.

Mahendra RC · ‎09-01-2022

Hello Rijeesh,

Please check once with below code:

getProjectitems();
    function getProjectitems(){
    try { 
		var r = new sn_ws.RESTMessageV2('X_2250', 'Default GET');
		r.setStringParameterNoEscape('id', '');
		var response = r.execute();
		var responseBody = response.getBody();
		var httpStatus = response.getStatusCode();
		this.ParseBasecampData(responseBody);
    }
    catch(ex) {
		var message = ex.message;
    }
}
function ParseBasecampData(r) {
        //create an import set
        var impset = new GlideRecord('sys_import_set');
        impset.initialize();
        impset.state = 'loading';
        impset.mode = 'asynchronous';
        impset.table_name = 'u_project_inbound';
        impset.insert();
        var response = JSON.parse(r);
        gs.info('First Value=' + response[0].name);
        var allToDoIds = [];  //CREATE ARRAY TO TO STORE TODO IDS
        for (var i = 0; i < response.length; i++){
            var Project=response[i];
            gs.info("Project=" + response[i]);
            var impsetrow = new GlideRecord('u_project_inbound');
            impsetrow.initialize();
            impsetrow.sys_import_state = 'pending';
            impsetrow.sys_import_set = impset.getUniqueValue();
            impsetrow.u_id = Project.id;
            impsetrow.u_project_name = Project["name"];
            impsetrow.u_description = Project["description"];
            allToDoIds.push(Project["dock"][1]["id"]); //STORING ALL THE TODO IDS iN ARRAY
            impsetrow.u_todoid = Project["dock"][1]["id"];
            impsetrow.insert();
        }
        impset.state='loaded';
        impset.update();
        new GlideImportSetTransformer().transformAllMaps(impset);
		// USING allToDoIds in for loop to execute the API for each todo in allToDoIdsl
		for (var todo in allToDoIds) {
			var r = new sn_ws.RESTMessageV2('YOUR_REST_MSG', 'HTTP_METHOD');
			// you can pass the single ToDo id as allToDoIds[todo] to get the data for single ToDo
			// If API can accept multiple ToDo id then pass all allToDoIds.join(",") this will pass all the todo Ids in one go.
			r.setStringParameterNoEscape('id', allToDoIds[todo]);
			var response = r.execute();
			var responseBody = response.getBody();
			var httpStatus = response.getStatusCode();
			// Call your function to read the response
        }

in your response function you can use

var toDoTask = response;
for (var task in toDoTask) {
	var taskId = toDoTask[task]["id"];
}

Please mark this as helpful/correct, if it answer your question.

Thanks

View solution in original post

Rijeesh Rathna1 · ‎08-28-2022

Also, here is the documentation of BaseCamp3

https://github.com/basecamp/bc3-api

Rijeesh Rathna1 · ‎08-28-2022

Here is the code I am using the get the project name, id and description..

getProjectitems();
function getProjectitems(){
try {
var r = new sn_ws.RESTMessageV2('X_2250', 'Default GET');
r.setStringParameterNoEscape('id', '');
var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
this.ParseBasecampData(responseBody);
}
catch(ex) {
var message = ex.message;
}
}
function ParseBasecampData(r) {
//create an import set
var impset = new GlideRecord('sys_import_set');
impset.initialize();
impset.state = 'loading';
impset.mode = 'asynchronous';
impset.table_name = 'u_project_inbound';
impset.insert();
var response = JSON.parse(r);
gs.info('First Value=' + response[0].name);
for (var i = 0; i < response.length; i++){
var Project=response[i];
gs.info("Project=" + response[i]);
var impsetrow = new GlideRecord('u_project_inbound');
impsetrow.initialize();
impsetrow.sys_import_state = 'pending';
impsetrow.sys_import_set = impset.getUniqueValue();
impsetrow.u_id = Project.id;
impsetrow.u_project_name = Project.name;
impsetrow.u_description = Project.description;
impsetrow.u_todoid = Project.dock.id;
impsetrow.insert();
}
impset.state='loaded';
impset.update();
new GlideImportSetTransformer().transformAllMaps(impset);
}

Pavankumar_1 · ‎08-28-2022

Hi,

what is the error that you are receiving?

what are the values that you are able to receive?

Thanks,

Pavankumar

If it helps please click Accept as Solution/hit the Thumb Icon.
ServiceNow Community MVP 2024.
Thanks,
Pavankumar

Rijeesh Rathna1 · ‎08-28-2022

Dear Pavan,

I am not getting any error message as I am not sure how to configure it.

Right now, I am getting the project name, ID and the description. Now, I need the todoset number of each project and then create todo list on another table mapping the todoset ID.

Thanks,
Rijeesh