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 Rathnakumar 

You need call the API to get the in below for () loop to get the Task and sub task details please make the necessay changes to call the API you can pass allToDoIds[todo] as a todoset ID:

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

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

Thanks

[Rijeesh Rathna1]

Dear Mahendra,

Sorry, I didnt understand it correctly as I am very weak in coding. Can you please help me to get this configured correctly?

I am sure that a coding person can understand but as I am very weak in coding, I am not getting what exactly is required to be done on ServiceNow side.

Thanks,
Rijeesh Rathnakumar

[Mahendra RC]

Hello @Rijeesh Rathnakumar 

You have created REST Message that you are calling to get the Project data as shown below:

var r = new sn_ws.RESTMessageV2('X_2250', 'Default GET');

SImilarly you have to create a Rest Message to get the ToDoSet details if the rest message is not already created.

Just 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"].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 toDoSetRequest = new sn_ws.RESTMessageV2('X_2250', 'Default GET');
            toDoSetRequest.setEndpoint("https://3.basecampapi.com/5434106/buckets/29064341/todosets/" +allToDoIds[todo] + "/todolists.json"); 
            //toDoSetRequest.setStringParameterNoEscape("id", "");
            var toDoSetResponse = toDoSetRequest.execute();
            var toDoResponseBody = toDoSetResponse.getBody();
            var httptoDoStatus = toDoSetResponse.getStatusCode();
            gs.info("ToDoList Response: " + toDoResponseBody);
        }
}

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

Thanks

[Mahendra RC]

Hello @Rijeesh Rathnakumar ,

Did you got a chance to check with above updated script if you what you are getting in response?

Thanks

[Rijeesh Rathna1]

Dear Mahendra,

Actually I am not sure how to configure the REST Message for todolist as the endpoint to call the todo needs to have the project name and the todoset ID to get the todolist.

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

Here 29064341 is the project ID and 5270730470 is the todoset ID. How can we achieve this? Can you please help?

Thanks,
Rijeesh Rathnakumar