Import Nested JSON via REST Message

[Rijeesh Rathna1]

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]

Hello Rijeesh,

You can fetch the ToDoSets Id as shown below:

projectToDos is the variable that store the response data: 

var projectToDos = [
  {
    "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
  }
];
for (var todo in projectToDos) {
var toDoSetId = projectToDos[todo]["dock"][1]["id"]; // this will get 5271268499 and 5270730470
}

To get the Task Id you can use: toDoTask variable store the response task received

var toDoTask = [
  {
    "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"

  }
];

for (var task in toDoTask) {
var taskId = toDoTask[task]["id"];
gs.log("TASK ID: " + taskId) // this will return 5271233931 and 5271233763
}

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

Thanks

[Rijeesh Rathna1]

Dear Mahendra,

Thank you for your response. Could you please let me know what changes I need to make on the scheduled job which pull the project details. Currently there is only one GET method which points to the /projects.json. 

The todosets are available on a separate URL which contains the project ID.

Would it be possible to connect on a webex link to check this further?

Thanks,
Rijeesh Rathnakumar

[Mahendra RC]

Hello Rijeesh,

Can you please share your scheduled job script?

Thanks

[Rijeesh Rathna1]

Dear Mahendra,

Here is the script I am using to pull the data to via scheduled job. But this is only pulling the project information. I also need the todosets and todolist.

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

Thanks,
Rijeesh Rathnakumar

[Mahendra RC · Accepted solution]

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