Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Asana provides formulas that can query Asana data. The following three steps show how you can automate the following task: Search Asana data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the Asana data records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as AsanaConnection1, or a connection string. The connection string consists of the required properties for connecting to Asana data, separated by semicolons.

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

  • WorkspaceId: Set this to 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: Set this to 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/.

  Connect Using OAuth Authentication

  You must use OAuth to authenticate with Asana. OAuth requires the authenticating user to interact with Asana using the browser. See the "Getting Started" chapter of the help documentation for a guide to using OAuth.

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Asana data, such as Archived.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM projects WHERE Archived = '"&B4&"'","OAuthClientId="&B1&";OAuthClientSecret="&B2&";CallbackURL="&B3&";Provider=Asana",B5)
3. Change the filter to change the data.