Asana Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Asana Version Support

The connector leverages the Asana API to enable bidirectional access to Asana work management entities.

Establish a Connection

Connect to Asana

You can optionally set the following to refine the data returned from Asana.

• WorkspaceId: The globally unique identifier (gid) associated with your Asana Workspace to only return projects from the specified Workspace. To get your Workspace Id, navigate to https://app.asana.com/api/1.0/workspaces while logged into Asana. This displays a JSON object containing your Workspace name and Id.
• ProjectId: The globally unique identifier (gid) associated with your Asana Project to only return data mapped under the specified Project. Project Ids can be found in the URL of your project's Overview page. This will be the numbers directly after /0/.

Authenticate to Asana

Asana uses the OAuth or OAuthPKCE authentication standard.

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth App for information about creating custom applications and reasons for doing so.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): The client ID assigned when you registered your app.
• OAuthClientSecret (custom applications only): The client secret assigned when you registered your app.
• CallbackURL (custom application only): The redirect URI defined when you registered your app. For example: https://localhost:3333

When you connect, the connector opens Asana's OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. The connector obtains an access token from Asana and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

The connector refreshes the access token automatically when it expires.

OAuth PKCE

Set AuthScheme to OAuthPKCE.

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth App for information about creating custom applications and reasons for doing so.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client ID assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app. Specifying this value is optional if a value was not generated during the OAuth custom application creation process.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:3333

When you connect, the connector opens Asana's OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. The connector obtains an access token from Asana and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

The connector refreshes the access token automatically when it expires.

Service Accounts

To authenticate with a Server Account (Enterprise only), use a Personal Access Token by setting these connection properties:

• InitiateOAuth: OFF.
• OAuthAccessToken: Your access token value.

For more more information, see the Asana documentation for personal access tokens and service accounts.

OAuth Scopes and Endpoints

Required Scopes and Endpoint Domains for Asana

When integrating with Asana, your application needs specific permissions to interact with the API.

These permissions are defined by access scopes, which determine what data your application can access and what actions it can perform.

This topic provides information about the required access scopes and endpoint domains for the Asana connector.

Understand Scopes

Scopes are a way to limit an application's access to a user's data. They define the specific actions that an application can perform on behalf of the user.

For example, a read-only scope might allow an application to view data, while a full access scope might allow it to modify data.

Required Scopes for Asana

Understand Endpoint Domains

Endpoint domains are the specific URLs that the application needs to communicate with in order to authenticate, retrieve records, and perform other essential operations. Allowlisting these domains ensures that the network traffic between your application and the API is not blocked by firewalls or security settings.

Required Endpoint Domains for Asana

Create a Custom OAuth App

To obtain an OAuthClientId, OAuthClientSecret, and CallbackURL, you first need to create an app linked to your Asana account.

To create an app linked to your Asana account:

1. Log in to your Asana account.
2. Navigate to My profile Settings > Apps > Manage Developer Apps or https://app.asana.com/0/developer-console.
3. Under My apps, select New app. Specify the app name, then select Create app.
4. Once your app created, set Redirect URL to http://localhost:33333 (or a different available port of your choice), then select add.

Once you are done with creating a new app, it will be displayed on your screen. From there, you can click View Client ID to reveal your newly created app's OAuthClientId and OAuthClientSecret.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Asana connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Asana and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Asana connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM projects WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Asana APIs.

Key Features

• The connector models Asana entities as relational views, allowing you to write SQL to query Asana data.
• Stored procedures allow you to execute operations to Asana, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Asana account are immediately reflected when using the connector.
• IncludeCustomFields connection property allows you to retrieve custom fields for Tasks view. Set this property to True, to enable this feature.

Views

Views describes the available views. Views are statically defined to model Projects, Users, Workspaces, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Asana. Stored procedures allow you to execute operations to Asana, including downloading documents and moving envelopes.

Tables

The connector models the data in Asana as a list of tables in a relational database that can be queried using standard SQL statements.

Asana Connector Tables

Allocations

To Create, Update, Delete and Query the Allocations table.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the connector.

• Id supports the '=' comparison.
• Assignee supports the '=' comparison. Note : Always include WorkspaceId when specifying Assignee, either in the query or the connection string.
• Parent supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM Allocations WHERE Id = '1127092449876457'

SELECT * FROM Allocations WHERE Assignee = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM Allocations WHERE Parent = '1208149945266501'

Insert

StartDate, EndDate, Assignee and Parent are mandatory columns for inserting into the Allocations table. For example:

INSERT INTO Allocations (StartDate, EndDate, AssigneeId, ParentId, EffortType, EffortValue) VALUES ('2024-01-11', '2024-02-11', '1208125247305564', '1207717475644852', 'hours', 20)

Update

The following is an example of how to update the Allocations table:

UPDATE Allocations SET StartDate = '2024-01-11', EndDate = '2024-02-11', Assignee = '1208125247305564', Parent = '1207717475644852', EffortType = 'hours', EffortValue = 20 WHERE ID = '12345'

Delete

The following is an example of how to delete from the Allocations table:

DELETE FROM Allocations WHERE ID = '1234'

Columns

Attachments

To Delete and Query from the Attachments in the tasks.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or ID is required to make a request and the rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Attachments WHERE Id = '1134618934491707'

SELECT * FROM Attachments WHERE TaskId = '1128092964672854'

Delete

Following is an example of how to delete from Attachments table:

DELETE FROM Attachments WHERE ID = '1134618934491707'

Columns

GoalMembership

To Create, Delete and Query memberships in the Goal.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• GoalId supports the '=' comparison.
• MemberId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM GoalMembership

SELECT * FROM GoalMembership WHERE Id = '1126938837961834'

SELECT * FROM GoalMembership WHERE MemberId = '1126938837961830'

SELECT * FROM GoalMembership WHERE GoalId = '1126938691750986'

Insert

GoalId and MemberId is a mandatory column for inserting into GoalMembership table. For example:

INSERT INTO GoalMembership (GoalId, MemberId) VALUES ('1161963899354167', '1126938837961830')

Delete

Following is an example of how to delete from GoalMembership table:

DELETE FROM GoalMembership WHERE ID = '1161963899354167'

Columns

Goals

To Create, Update, Delete, and Query the Goals table.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• IsWorkspaceLevel supports the '=' comparison.
• TeamId supports the '=' comparison.
• TimeId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• PortfolioId supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Goals

SELECT * FROM Goals WHERE Id = '1126938837961834'

SELECT * FROM Goals WHERE IsWorkspaceLevel = 'false'

SELECT * FROM Goals WHERE TeamId = '122334345323466' AND WorkspaceId = '1126938837961830'

SELECT * FROM Goals WHERE TimeId = '1243436322145547'

SELECT * FROM Goals WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Goals WHERE PortfolioId = '1205440033794767'

SELECT * FROM Goals WHERE ProjectId = '1203125557018928'

Insert

UserId and Goals is a mandatory column for inserting into Goals table. For example:

INSERT INTO Goals (Name, DueOn, StartOn, IsWorkspaceLevel, liked, WorkspaceId, OwnerId, TimePeriodId) VALUES ('Sample Goal', '2023-10-31', '2023-09-15', 'false', 'false', '1203124527069362', '1203124437606434', '1203124434984659' )

Inserting multiple Followers into Goals table. For example:

INSERT INTO Goals (Name, WorkspaceId, OwnerId, Followers) VALUES ('Sample Goal', '1203124527069362', '1203124437606434', '1206414724399714,1207035318501516' )

Update

Following is an example of how to update a Goals table:

UPDATE Goals SET DueOn ='2023-10-20' WHERE ID = '1128092864672888'

Delete

Following is an example of how to delete from Goals table:

DELETE FROM Goals WHERE ID = '1161963899354167'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PortfolioItems

To Create, delete, and Query Items in the Portfolio.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The PortfolioId is required to make a request and rest of the filter is executed client side within the connector.

• PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioItems WHERE PortfolioId = '1173026173301164'

Insert

ProjectId and PortfolioId is a mandatory column for inserting into PortfolioItems table. For example:

INSERT INTO PortfolioItems (ProjectId, PortfolioId) VALUES ('1173023668354420', '1173026173301164')

Delete

Following is an example of how to delete from PortfolioItems table:

DELETE FROM PortfolioItems WHERE ProjectId = '1173023668354420' AND PortfolioId = '1173026173301164'

Columns

PortfolioMembership

To Create, Delete and Query memberships in the Portfolio.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND UserId] OR PortfolioId OR ID is required to make a request and rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• UserId supports the '=' comparison.
• PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioMembership WHERE WorkspaceId = '1172686716695308' AND UserId = '1172686741877880'

SELECT * FROM PortfolioMembership WHERE PortfolioId = '1172684730399265'

SELECT * FROM PortfolioMembership WHERE ID = '1172684730399266'

Insert

PortfolioId and UserId is a mandatory column for inserting into PortfolioMembership table. For example:

INSERT INTO PortfolioMembership (PortfolioId, UserId) VALUES ('1172684730399265', '1172686741877880')

Delete

Following is an example of how to delete from PortfolioMembership table:

DELETE FROM PortfolioMembership WHERE PortfolioId = '1172684730399265' AND UserId = '1172686741877880'

Columns

Portfolios

To Create, update, delete, and query from Portfolios table.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND OwnerId] OR ID is required to make a request and rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• OwnerId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Portfolios WHERE WorkspaceId = '1172686716695308' AND OwnerId = '1172686741877880'

SELECT * FROM Portfolios WHERE Id = '1172684730399265'

Insert

WorkspaceId is a mandatory column for inserting into Portfolios table. For example:

INSERT INTO Portfolios (Name, Color, WorkspaceId)  VALUES ('Test', 'dark-pink', '1172686716695308'

Update

Following is an example of how to update a Portfolios table:

"UPDATE Portfolios SET Name = 'IT-Test' WHERE ID = '1173026173301164'

Delete

Following is an example of how to delete from Portfolios table:

DELETE FROM Portfolios WHERE ID = '1172684730399265'

Columns

ProjectMembership

To Create, Delete and Query memberships in the Project.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectMembership WHERE Id = '1127092449876445'

SELECT * FROM ProjectMembership WHERE ProjectId = '1127092449876444'

Insert

MemberId and ProjectId is a mandatory column for inserting into ProjectMembership table. For example:

INSERT INTO ProjectMembership (MemberId, ProjectId) VALUES ('1161963899354167', '1128092964672848')

Note : MemberId should be ID of a user for insert.

Delete

Following is an example of how to delete from ProjectMembership table:

DELETE FROM ProjectMembership WHERE MemberId = '1161963899354167' AND ProjectId = '1128092964672848'

Note : MemberId should be ID of a user for delete.

Columns

Projects

To Create, Update, Delete and Query the Projects table.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• workspaceId supports the '=' comparison.
• TaskId supports the '=' comparison.
• TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM projects WHERE workspaceId = '1126938837961830'

SELECT * FROM projects WHERE Id = '1128092864672888'

SELECT * FROM projects WHERE TaskId = '1130092964672468'

SELECT * FROM projects WHERE TeamId = '1129514033997890'

In case of UseTypeaheadSearch property set to true, only Name and WorkspaceId columns would be filterable server side with Name supporting '=, LIKE' comparison when LIKE used only in the following ways:

SELECT * FROM Projects WHERE Name = 'abc'

SELECT * FROM Projects WHERE Name LIKE 'abc'

SELECT * FROM Projects WHERE Name LIKE 'abc%'

SELECT * FROM Projects WHERE Name LIKE 'abc%' AND WorkspaceId = '1126938837961830'

Insert

WorkspaceId or TeamId is a mandatory column for inserting into Projects table. For example:

INSERT INTO Projects (Name, Color, StartOn, WorkspaceId, DueOn, public, UserId) VALUES ('ProjectName', 'dark-pink', '2020-03-01', '1126938837961830', '2020-04-30', false, '1130062547672468')

Inserting multiple Followers into Projects table. For example:

INSERT INTO Projects (WorkspaceId, Followers) VALUES ('1126938837961830', '1206414724399714,1207035318501516' )

Update

Following is an example of how to update a Projects table:

UPDATE Projects SET TeamId = '1129514033997890' WHERE ID = '1128092864672888'

Delete

Following is an example of how to delete from Projects table:

DELETE FROM Projects WHERE ID = '1128092864672888'

Columns

ProjectStatuses

To Create, Delete and Query from ProjectStatuses table. A project status is an update on the progress of a particular project, and is sent out to all project followers when created.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectStatuses WHERE Id = '1127092449876445'

SELECT * FROM ProjectStatuses WHERE ProjectId = '1127092449876444'

Insert

ProjectId is a mandatory column for inserting into ProjectStatuses table. For example:

INSERT INTO projectstatuses (Color, Title, Text, ProjectId) VALUES ('red', 'Status1', 'StatusNotes', '1127092449876444')

Delete

Following is an example of how to delete from ProjectStatuses table:

DELETE FROM ProjectStatuses WHERE ID = '1127092449876445'

Columns

Sections

To Create, Update, Delete, and Query from Sections table. A section is a subdivision of a project that groups tasks together.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Sections WHERE Id = '1127092449876457'

SELECT * FROM Sections WHERE ProjectId = '1128092964672848'

Insert

ProjectId is a mandatory column for inserting into Sections table. For example:

INSERT INTO Sections (Name, ProjectId) VALUES ('SectionName', '1128092964672848')

Update

Following is an example of how to update a Sections table:

UPDATE Sections SET Name = 'IT-Pending' WHERE ID = '1127092449876457'

Delete

Following is an example of how to delete from Sections table:

DELETE FROM Sections WHERE ID = '1127092449876457'

Columns

StatusUpdates

A status update is an update on the progress of a particular object, and is sent out to all followers when created.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• ParentId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM StatusUpdates

SELECT * FROM StatusUpdates WHERE Id = '1126938837961834'

SELECT * FROM StatusUpdates WHERE ParentId = '1126938837961834'

Insert

Text, StatusType and ParentId are mandatory columns for inserting into StatusUpdates table. For example:

INSERT INTO StatusUpdates(ParentId,Title,Text,StatusType) VALUES('1203125557018928','asd','tewr','on_track')

Delete

Following is an example of how to delete from StatusUpdates table:

DELETE FROM StatusUpdates WHERE ID = '1161963899354167'

Columns

Stories

To Create, Update, Delete and Query from stories table. A story represents an activity associated with an object in the Asana system. Stories are generated by the system whenever users take actions such as creating or assigning tasks, or moving tasks between projects.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or ID is required to make a request and rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Stories WHERE Id = '1126938691750986'

SELECT * FROM Stories WHERE TaskId = '1129514033997892'

Insert

TaskId is a mandatory column for inserting into Stories table. For example:

INSERT INTO Stories (Text, TaskId, IsPinned) VALUES ('InProgress', '1129514033997892', false)

Update

Following is an example of how to update a Stories table:

UPDATE Stories SET Text = 'story line' WHERE ID = '1126938691750986'

Delete

Following is an example of how to delete from Stories table:

DELETE FROM Stories WHERE ID = '1126938691750986'

Columns

SubTasks

To Create, update, delete, and query from SubTasks table.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• ParentTaskId supports the '=' comparison.

For example, the following query is processed on server side and it is a required filter:

SELECT * FROM SubTasks WHERE ParentTaskId = '1127092449876457'

A simple SELECT query will only return the first level of subtasks.

SELECT * FROM SubTasks

There may be up to five levels of subtasks below a task. (Note, Asana does not recommend using sub-subtasks). If additional levels of subtasks are needed, a subquery can be used:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT ID FROM SubTasks)

When executing a query without a ParentTaskId filter, due to the Asana API design, subtasks are only retrieved on a per task basis. The connector will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. Another request will be made for each task to retrieve any subtasks associated with the task. In theory, this is similar to executing the following query:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT ID FROM Tasks WHERE ProjectId IN (SELECT ID FROM Projects))

Due to the number of required requests, performance may be impacted.

Insert

ParentTaskId is a mandatory column for inserting into Projects table. For example:

INSERT INTO SubTasks (ParentTaskId, Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('1167713816822372', 'subtask1', '1167715005778471', 'later', '2020-03-25', '2020-04-01', false, 1167713816822364, 'SubTask Notes', 'changes_requested', '1167715005778471')

Inserting multiple Followers into SubTasks table. For example:

INSERT INTO SubTasks (ParentTaskId, Followers) VALUES ('1167713816822372', '1206414724399714,1207035318501516' )

Columns

Tags

To Create, Update and Query from Tags table. A tag is a label that can be attached to any task in Asana. It exists in a single workspace.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Tags WHERE Id = '1129517083601713'

SELECT * FROM Tags WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Tags WHERE TaskId = '1128092964672854'

Insert

Workspaceid is a mandatory column for inserting into Tags table. For example:

INSERT INTO Tags (Name, Color, WorkspaceId, Notes) VALUES ('TestTag', 'dark-blue', '1126938837961830', 'sample notes')

Update

Following is an example of how to update a Tags table:

UPDATE Tags SET Name = 'IT-1' WHERE ID = '1158271967831255'

Columns

Tasks

To Create, Update, Delete and Query from Tasks table.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the connector.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.
• Assignee supports the '=' comparison.

• TagId supports the '=' comparison.
• SectionId supports the '=' comparison.
• UserTaskListId supports the '=' comparison.
• ModifiedAt supports the '>, >=' comparison.
• CompletedAt supports the '>, >=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM Tasks WHERE Id = '1127092449876457'

SELECT * FROM Tasks WHERE ProjectId = '1128092964672848'

SELECT * FROM Tasks WHERE Assignee = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM tasks WHERE TagId = '1129517083601713'

SELECT * FROM tasks WHERE SectionId = '1127092449876457'

SELECT * FROM tasks WHERE UserTaskListId = '1126938837961837'

SELECT * FROM Tasks WHERE ProjectId = '1200222223676674' AND ModifiedAt > '2024-10-09 02:43:21.933'

SELECT * FROM Tasks WHERE ProjectId = '1200222223676674' AND CompletedAt > '2024-10-31 17:10:28.268'

When executing a query without one of the above filters, due to the Asana API design, tasks must be retrieved per project. The connector will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. In theory, this is similar to executing the following query:

SELECT * FROM Tasks WHERE ProjectId IN (SELECT ID FROM Projects)

SELECT * FROM Tasks WHERE ModifiedAt > '2024-10-09 02:43:21.933'

SELECT * FROM Tasks WHERE CompletedAt > '2024-10-31 17:10:28.268'

Due to the number of required requests, performance may be impacted. Note, tasks may also be orphaned and not belong to a project. The connector will not be able to retrieve orphaned tasks.

If the UseTypeaheadSearch connection property is set to true, only the Name and WorkspaceId columns are filterable server-side. The Name column supports '=' and 'LIKE' comparisons, but only when 'LIKE' is used in one of the following forms:

SELECT * FROM Tasks WHERE Name = 'abc'

SELECT * FROM Tasks WHERE Name LIKE 'abc'

SELECT * FROM Tasks WHERE Name LIKE 'abc%'

SELECT * FROM Tasks WHERE Name LIKE 'abc%' AND WorkspaceId = '1126938837961830'

Insert

ProjectId is a mandatory column for inserting into Tasks table. For example:

INSERT INTO Tasks (Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('Task1', '1167715005778471', 'later', '2020-03-25', '2020-04-01', false, 1167713816822364, 'TaskNotes', 'changes_requested', '1167715005778471')

Update

The following is an example of how to update the Tasks table:

UPDATE Tasks SET Browser = 'ie' WHERE ID = '1167713816822371'

Delete

The following is an example of how to delete from the Tasks table:

DELETE FROM Tasks WHERE ID = '1167713816822371'

Columns

TaskTemplates

To Delete and Query the task templates.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

• Id supports the '=' comparison.
• ProjectId supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM TaskTemplates WHERE Id = '1127092449876457'

SELECT * FROM TaskTemplates WHERE ProjectId = '1208149945266501'

Delete

The following is an example of how to delete from the TaskTemplates table:

DELETE FROM TaskTemplates WHERE ID = '1234'

Columns

TeamMembership

To represents a users connection to a team.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TeamMembership WHERE UserId = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM TeamMembership WHERE TeamId = '1126938837961832'

SELECT * FROM TeamMembership WHERE Id = '1126938837961832'

Insert

UserId and TeamId is a mandatory column for inserting into TeamMembership table. For example:

INSERT INTO TeamMembership (UserId, TeamId) VALUES ('1161963899354167', '1126938837961832')

Delete

Following is an example of how to delete from TeamMembership table:

DELETE FROM TeamMembership WHERE UserId = '1161963899354167' AND TeamId = '1126938837961832'

Columns

Teams

A team is used to group related projects and people together within an organization. Each project in an organization is associated with a team.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Teams WHERE Id = '1126938837961832'

SELECT * FROM Teams WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Teams WHERE UserId = '1126938691750986' AND workspaceid = '1126938837961830'

Insert

WorkspaceId is a mandatory column for inserting into Teams table.In the WorkspaceId only the organisation ID can be used to create teams. Teams can not be created for workspaces which are not part of a organisation. For example:

INSERT INTO Teams (Name, Description, HTMLDescrption, WorkspaceId) VALUES ('Salessssk', 'sales developers should be members of this team.', '<body> <em>All</em> developers should be members of this team.</body>', '1126938837961830')

Columns

TimeTrackingEntries

Returns time tracking entries for a given task.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TimeTrackingEntries WHERE Id = '1204862911885011'

SELECT * FROM TimeTrackingEntries WHERE TaskId = '1203479447771600'

When executing a query without one of the above filters, due to the Asana API design, time tracking entries must be retrieved per task. The connector will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. Then requests to get time tracking entries on each task will be made. In theory, this is similar to executing the following query:

SELECT * FROM TimeTrackingEntries WHERE TaskId IN (SELECT ID FROM Tasks)

Insert

TaskId is a mandatory column for inserting into TimeTrackingEntries table. For example:

INSERT INTO TimeTrackingEntries (TaskId, DurationMinutes, EnteredOn) VALUES ('1203125557018947', 3600, '2024-01-01')

Update

Following is an example of how to update a TimeTrackingEntries table:

UPDATE TimeTrackingEntries SET DurationMinutes = 4000, EnteredOn = '2023-01-01' WHERE ID = '1208387811113769'

Delete

Following is an example of how to delete from TimeTrackingEntries table:

DELETE FROM TimeTrackingEntries WHERE ID = '1208387811113769'

Columns

WorkspaceMembership

To Create, Delete and Query memberships in the Workspace.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkspaceMembership WHERE WorkspaceId = '1126938837961830'

SELECT * FROM WorkspaceMembership WHERE UserId = '1126938691750986'

SELECT * FROM WorkspaceMembership WHERE ID = '1126938837961834'

Insert

UserId and WorkspaceId is a mandatory column for inserting into WorkspaceMembership table. For example:

INSERT INTO WorkspaceMembership (UserId, WorkspaceId) VALUES ('1161963899354167', '1126938837961830')

Delete

Following is an example of how to delete from WorkspaceMembership table:

DELETE FROM WorkspaceMembership WHERE UserId = '1161963899354167' AND WorkspaceId = '1126938837961830'

Columns

Workspaces

To Update and Query from Workspaces table. A workspace is the highest-level organizational unit in Asana. All projects and tasks have an associated workspace.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Workspaces WHERE Id = '1126938837961835'

SELECT * FROM Workspaces WHERE UserId = '1126938691750986'

Update

Following is an example of how to update a Workspaces table:

UPDATE Workspaces SET Name = 'IT-Eng' WHERE ID = '1126938837961835'

Columns

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Asana Connector Views

CustomFields

To view the Custom Fields in the project or workspace.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side in the connector.

• ProjectId supports the '=' comparison.

For example, the following query is processed server side:

SELECT * FROM CustomFields WHERE ProjectId = '1126938691750986'

Columns

Events

To view the events in projects and tasks.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The resource ID will be either Project or Task Id. The rest of the filter is executed client side within the connector.

• ResourceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Events WHERE ResourceId = '1128092964672854'

Columns

PremiumWorkspaceTasks

To search tasks in premium workspaces.

Table Specific Information

Select

This table fetches all the tasks belonging to a premium workspace. It would also fetch orphaned tasks which do not belong to any project. Without WorkspaceId, the table will first fetch all the workspaces, and then fetch the data only for premium workspaces.

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Assignee supports the '=,IN' comparison.
• ModifiedAt supports the '=,<,<=,>,>=' comparison.
• StartOn supports the '=,<,<=,>,>=' comparison.
• DueAt supports the '=,<,<=,>,>=' comparison.
• DueOn supports the '=,<,<=,>,>=' comparison.
• Completed supports the '=' comparison.
• CompletedAt supports the '=,<,<=,>,>=' comparison.
• ProjectId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• CreatedById supports the '=' comparison.
• SectionId supports the '=' comparison.
• TagId supports the '=' comparison.
• FollowerId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PremiumWorkspaceTasks

SELECT * FROM Tasks WHERE WorkspaceId = '1126938837961830'

SELECT * FROM PremiumWorkspaceTasks WHERE ProjectId = '1128092964672848'

SELECT * FROM PremiumWorkspaceTasks WHERE Assignee IN ('1206041702093338', '1134886242100800')

SELECT * FROM PremiumWorkspaceTasks WHERE TagId = '1129517083601713'

SELECT * FROM PremiumWorkspaceTasks WHERE SectionId = '1127092449876457'

SELECT * FROM PremiumWorkspaceTasks WHERE ModifiedAt > '2024-10-09 02:43:21.933'

SELECT * FROM PremiumWorkspaceTasks WHERE ProjectId = '1200222223676674' AND CompletedAt >= '2024-10-31 17:10:28.268'

Columns

ProjectFollowers

To view the Project follower details.

Columns

ProjectTemplates

TO get ProjectTemplates in the Team or workspace.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• TeamId supports the '=' comparison.
• WorkspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectTemplates where Id = '1208187943633195'

SELECT * FROM ProjectTemplates where TeamId = '1207673648001734'

SELECT * FROM ProjectTemplates WHERE WorkspaceId = '1208200246477352'

In case of UseTypeaheadSearch property set to true, only Name and WorkspaceId columns would be filterable server side with Name supporting '=, LIKE' comparison when LIKE used only in the following ways:

SELECT * FROM ProjectTemplates WHERE Name = 'abc'

SELECT * FROM ProjectTemplates WHERE Name LIKE 'abc'

SELECT * FROM ProjectTemplates WHERE Name LIKE 'abc%'

SELECT * FROM ProjectTemplates WHERE Name LIKE 'abc%' AND WorkspaceId = '1208200246477352'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TaskDependencies

To query all of the dependencies of a task.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

• TaskId supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM TaskDependencies WHERE TaskId = '1208618397035168'

Columns

TaskDependents

To query all of the dependents of a task.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

• TaskId supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM TaskDependents WHERE TaskId = '1208618397035168'

Columns

TaskStatuses

Get task count of a project.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TaskStatuses WHERE ProjectId = '1136437449978157'

Columns

TimePeriods

To query time period records or full record for a single time period.

Table Specific Information

Select

The connector uses the Asana API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.

For example, the following queries are processed server-side:

SELECT * FROM TimePeriods where Id = '1200585294122707'

SELECT * FROM TimePeriods where WorkspaceId = '1126938837961835'

Columns

Users

To view the users details.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• WorkspaceId supports the '=' comparison.
• TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Users WHERE Id = '1126938691750986'

SELECT * FROM Users WHERE WorkspaceId = '1126938837961835'

SELECT * FROM Users WHERE TeamId = '1129514033997892'

In case of UseTypeaheadSearch property set to true, only Name and WorkspaceId columns would be filterable server side with Name supporting '=, LIKE' comparison when LIKE used only in the following ways:

SELECT * FROM Users WHERE Name = 'abc'

SELECT * FROM Users WHERE Name LIKE 'abc'

SELECT * FROM Users WHERE Name LIKE 'abc%'

SELECT * FROM Users WHERE Name LIKE 'abc%' AND WorkspaceId = '1126938837961830'

Columns

UserTaskList

Generated schema file.

Table Specific Information

Select

The connector will use the Asana API to process WHERE clause conditions built with the following column and operator. The [UserId and workspaceId] or ID is required to make a request and the rest of the filter is executed client side within the connector.

• Id supports the '=' comparison.
• UserId supports the '=' comparison.
• workspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM UserTaskList WHERE Id = '1126938837961837'

SELECT * FROM UserTaskList WHERE UserId = '1126938691750986' AND workspaceId = '1126938837961830'

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Asana.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Asana, along with an indication of whether the procedure succeeded or failed.

Asana Connector Stored Procedures

AddDependenciesToTask

To set of tasks as dependencies of this task, if they are not already dependencies. A task can have at most 15 dependencies.

Input

Result Set Columns

AddDependentsToTask

To Set or Unlink dependents to the task.

Input

Result Set Columns

AddFollowers

To add followers to the tasks or projects.

Input

Result Set Columns

AddProjectToTask

Add a project to a task.

Input

Result Set Columns

AddTagsToTask

To Set or Unlink dependents to the task.

Input

Result Set Columns

AddTaskToSection

Add a task to a specific, existing section. This will remove the task from other sections of the project.

Input

Result Set Columns