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-02-2022

Dear Mahendra,

It looks like we are almost reaching the destination. Many thanks for your great support. Now I am getting the TODOSET ID against each project but the value is not in the correct format. Eg : Testing Project should have a todosetid of 5270730470 but its showing as 5.27073047E9.

The next part is to create the todolist for each todosets.

The below link will pull the list of todolist for one specific project have a specific todoid.

https://3.basecampapi.com/5434106/buckets/29064341/todosets/5270730470/todolists.json

========OUTPUT============

[ { "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", "url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233931.json", "app_url": "https://3.basecamp.com/5434106/buckets/29064341/todolists/5271233931", "bookmark_url": "https://3.basecampapi.com/5434106/my/bookmarks/BAh7CEkiCGdpZAY6BkVUSSIuZ2lkOi8vYmMzL1JlY29yZGluZy81M...", "subscription_url": "https://3.basecampapi.com/5434106/buckets/29064341/recordings/5271233931/subscription.json", "comments_count": 0, "comments_url": "https://3.basecampapi.com/5434106/buckets/29064341/recordings/5271233931/comments.json", "position": 1, "parent": { "id": 5270730470, "title": "To-dos", "type": "Todoset", "url": "https://3.basecampapi.com/5434106/buckets/29064341/todosets/5270730470.json", "app_url": "https://3.basecamp.com/5434106/buckets/29064341/todosets/5270730470" }, "bucket": { "id": 29064341, "name": "Testing", "type": "Project" }, "creator": { "id": 40763538, "attachable_sgid": "BAh7CEkiCGdpZAY6BkVUSSIpZ2lkOi8vYmMzL1BlcnNvbi80MDc2MzUzOD9leHBpcmVzX2luBjsAVEkiDHB1cnBvc2UGOwBUSSIPYXR0YWNoYWJsZQY7AFRJIg9leHBpcmVzX2F0BjsAVDA=--5660307ffc385008d69923fcdeb06c22a3937cd7", "name": "Rijeesh", "email_address": "rijeeshnr@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", "avatar_url": "https://bc3-production-assets-cdn.basecamp-static.com/5434106/people/BAhpBJIAbgI=--a56466e14d21f36db...", "company": { "id": 3144462, "name": "Rijeesh" } }, "description": "", "completed": false, "completed_ratio": "0/2", "name": "Test 2", "todos_url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233931/todos.json", "groups_url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233931/groups.json", "app_todos_url": "https://3.basecamp.com/5434106/buckets/29064341/todolists/5271233931/todos" }, { "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", "url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233763.json", "app_url": "https://3.basecamp.com/5434106/buckets/29064341/todolists/5271233763", "bookmark_url": "https://3.basecampapi.com/5434106/my/bookmarks/BAh7CEkiCGdpZAY6BkVUSSIuZ2lkOi8vYmMzL1JlY29yZGluZy81M...", "subscription_url": "https://3.basecampapi.com/5434106/buckets/29064341/recordings/5271233763/subscription.json", "comments_count": 0, "comments_url": "https://3.basecampapi.com/5434106/buckets/29064341/recordings/5271233763/comments.json", "position": 2, "parent": { "id": 5270730470, "title": "To-dos", "type": "Todoset", "url": "https://3.basecampapi.com/5434106/buckets/29064341/todosets/5270730470.json", "app_url": "https://3.basecamp.com/5434106/buckets/29064341/todosets/5270730470" }, "bucket": { "id": 29064341, "name": "Testing", "type": "Project" }, "creator": { "id": 40763538, "attachable_sgid": "BAh7CEkiCGdpZAY6BkVUSSIpZ2lkOi8vYmMzL1BlcnNvbi80MDc2MzUzOD9leHBpcmVzX2luBjsAVEkiDHB1cnBvc2UGOwBUSSIPYXR0YWNoYWJsZQY7AFRJIg9leHBpcmVzX2F0BjsAVDA=--5660307ffc385008d69923fcdeb06c22a3937cd7", "name": "Rijeesh", "email_address": "rijeeshnr@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", "avatar_url": "https://bc3-production-assets-cdn.basecamp-static.com/5434106/people/BAhpBJIAbgI=--a56466e14d21f36db...", "company": { "id": 3144462, "name": "Rijeesh" } }, "description": "", "completed": false, "completed_ratio": "0/2", "name": "Test 1", "todos_url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233763/todos.json", "groups_url": "https://3.basecampapi.com/5434106/buckets/29064341/todolists/5271233763/groups.json", "app_todos_url": "https://3.basecamp.com/5434106/buckets/29064341/todolists/5271233763/todos" } ]

=================================================

But we need to create change the project ID and todoset ID automatically in the end point to get the todolist of each todosets.

I have created a new table called "u_project_todos" and this will have the information of each todolist (field are : todolist_id, todoset_id, todolist_name).

Can you please help to achieve this?

Many thanks in advance.

Mahendra RC · ‎09-02-2022

Hello Rijeesh,

Please change the below code lines in your script from

allToDoIds.push(Project["dock"][1]["id"]); //STORING ALL THE TODO IDS iN ARRAY
            impsetrow.u_todoid = Project["dock"][1]["id"];

replace this with the below:

            allToDoIds.push(Project["dock"][1]["id"].toString()); //STORING ALL THE TODO IDS iN ARRAY
            impsetrow.u_todoid = Project["dock"][1]["id"].toString();

ALSO YOU CAN MARK THE RESPONSE AS HELPFUL IF MY RESPONSE IS HELPING YOU.

Thanks

Rijeesh Rathna1 · ‎09-02-2022

Dear Mahendra,

Thanks a million for your outstanding support.

Would it be possible to help me to get the TODOs as well? This is how a project looks like... These are the main tasks and their sub tasks.

Can you please help me to get this?

Mahendra RC · ‎09-02-2022

Hello @Rijeesh Rathnakumar ,

Could you please share the updated script and also the response body that you will get for TODO details. Once I get the response body I will be able to help you more efficiently.

ALSO YOU CAN MARK OTHER PREVIOUS RESPONSE AS HELPFUL IF MY RESPONSE WERE HELPFUL FOR YOU.

Thanks

Rijeesh Rathna1 · ‎09-02-2022

Dear Mahendra,

Here is my updated script.

I have created a new table called "u_project_todos" and this needs to have the information of each todolist (field are : todolist_id, todoset_id, todolist_name).

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"].toString()); //STORING ALL THE TODO IDS iN ARRAY
impsetrow.u_todoid = Project["dock"][1]["id"].toString();
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('X_2250', 'Default GET');
// 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();

}
}

Thanks,
Rijeesh Rathnakumar