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 · ‎09-06-2022

Dear Mahendra,

Can you please help me here?

Thanks,
Rijeesh Rathnakumar

Mahendra RC · ‎09-08-2022

Hello @Rijeesh Rathnakumar ,

All your question related to integration cannot be answered in 1 question. I would request you to close this queston my marking the appropriate response as correct, because this was related to one specific question which is I believe answered.

Please raise another question for this query and will try to help you. Also you can search this on community how to configure REST Message you will get lot of articles.

Thanks

Rijeesh Rathna1 · ‎09-09-2022

Dear Mahendra,

The initial objective mentioned was the same as I need to get the project and its TODO details. I was able to get the project details from beginning and you had helped me to get the TODOSET ID. Many thanks for that. Now the remaining part is to get the TODO List which was mentioned in my initial request. Please see below the question i raised the community.

"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. "

Anyways, you have helped me to achieve an additional steps.

Thanks,
Rijeesh Rathnakumar