Loop Through REST Connector

[pankaj999]

Hi,

I have connected Asana API with QlikSense via REST connector and I have a table of all the tasks and their task_ids under a project in Asana as shown below:

What I want to do next is that iterate all the tasks_ids through this link `(https://app.asana.com/api/1.0/tasks/{task_id}/stories)` to get all the activities related to tasks.

 Basically, I need to loop in all the task_ids I have in a table called `asana_tasks` through the above link via REST connector.

Thanks

[seanbruton]

Hi There,

After you loaded the table asana_tasks then add the below just chnage the connection on the detail part to work in your confiquration enviroment. I use a fixed configuration and the connections string is a variable. I did mark in red.

The trick is the loop and variable part in your string url now $(vTaskId ):

let vNumofRowsCampaign = NoOfRows('asana_tasks)-1;
set vConcatenate = '';

for i = 0 to $(vNumofRowsCampaign)

let vTaskId = peek('id',$(i),'asana_tasks');

 TasksDetail:
LOAD * 
FROM [$(vQwcConnectionName)]
(URL IS [https://app.asana.com/api/1.0/tasks/$(vTaskId )/stories],
;

set vConcatenate = concatenate(MailChimpConnectorV2_CampaignReport);

next ;

Good Luck and enjoy.

[jordimtb]

Hi!

sorry for reusing your theme.
How did you get the REST-API connection? Are there any special settings?
I'm configuring exactly the same as in Postman but it's giving me a connection error.

thanks

[CVP1783]

Hi! I have the same problem , did you solve this issue? 

[marksouzacosta]

Hi folks,

I'm a completely new at Asana but I figure out how to use its APIs on Qlik Cloud - it should work fine on Qlik Sense on-prem with minor changes.

So, for the proposed original question we'll need two REST Connections.

1 - One to list the Tasks - in my case I did it by Project ID.
2 - One to get Task details, providing the Task ID from the previous connection.

My first REST Connection (#1) is called Get multiple tasks and it points to this End Point:
https://developers.asana.com/reference/gettasks

The green arrows is what you have to setup in your REST Connection. Note that I'm using a Personal Token to authenticate my connection - more info here https://developers.asana.com/docs/personal-access-token

Note: Allow WITH CONNECTION is a key point here. It allows us to overwrite connections parameters on the fly, for example, we can load a list of Project Ids and further dynamically load their respective Tasks.
For more information: https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

Now, to the list of Tasks for my Project ID:

LIB CONNECT TO 'Get multiple tasks';

TaskList:
LOAD
	"gid" AS "TaskID",
	"name" AS "Task Name"
;
SQL SELECT 
	"gid",
	"name"
FROM JSON (wrap off) "data";

DisConnect;

For the second REST Connection (#2) we need to use the following End Point:
https://developers.asana.com/reference/gettask

This is how I have setup the REST Connection in Qlik Cloud:

And this is the code to loop through the list of Tasks ID to collect its details - I made the list of columns short just for test purpose. Pay special attention to the WITH CONNECTION statement:

LIB CONNECT TO 'Get Task';

For Each vTaskId in FieldValueList('TaskID')

	TRACE Get Tasks Details for Task ID $(vTaskId);

    LET vGetTaskUrl = 'https://app.asana.com/api/1.0/tasks/$(vTaskId)';

    TaskDetails:
    LOAD
        "gid" AS "TaskID",
        "actual_time_minutes",
        "assignee_status",
        "completed",
        "completed_at",
        "created_at"
    ;
    SQL SELECT 
        "gid",
        "actual_time_minutes",
        "assignee_status",
        "completed",
        "completed_at",
        "created_at"
    FROM JSON (wrap off) "data" PK "__KEY_data"
    WITH CONNECTION (
    	Url "$(vGetTaskUrl)"
    )
    ;

Next vTaskId

DisConnect;

This is how the data model looks like:

Full code:

LIB CONNECT TO 'Get multiple tasks';

TaskList:
LOAD
	"gid" AS "TaskID",
	"name" AS "Task Name"
;
SQL SELECT 
	"gid",
	"name"
FROM JSON (wrap off) "data";

DisConnect;


LIB CONNECT TO 'Get Task';

For Each vTaskId in FieldValueList('TaskID')

	TRACE Get Tasks Details for Task ID $(vTaskId);

    LET vGetTaskUrl = 'https://app.asana.com/api/1.0/tasks/$(vTaskId)';

    TaskDetails:
    LOAD
        "gid" AS "TaskID",
        "actual_time_minutes",
        "assignee_status",
        "completed",
        "completed_at",
        "created_at"
    ;
    SQL SELECT 
        "gid",
        "actual_time_minutes",
        "assignee_status",
        "completed",
        "completed_at",
        "created_at"
    FROM JSON (wrap off) "data" PK "__KEY_data"
    WITH CONNECTION (
    	Url "$(vGetTaskUrl)"
    )
    ;

Next vTaskId

DisConnect;

I hope I have helped!

Regards,

Mark Costa

[marksouzacosta]

I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa